Wednesday, August 16

PG Phriday: Dearly De-parted

Shaun's PlanetPostgreSQL
With the christening of PG Phriday at its new location, it's only fitting that we revisit a frequent boon and bane of organized storage: partitioning. When volume and scale reach a certain point that the very laws of physics become a barrier to efficiency, partitions are a crucial tool to escape the otherwise inevitable pile of unmaintainable bits. It's not feasible to reindex a multi-terabyte table with tens or hundreds of billions of rows. It's excruciating to excise portions for long-term archival, and all but impossible to perform anything but the most cursory maintenance in general. Huge tables are a partition advocate's dream. But until fairly recently, the implementation in Postgres has been... less than savory. While nothing is perfect, Postgres has never actually offered (more…)

Visualizing sar data

Mark's PlanetPostgreSQL
Hopefully you are now regularly collecting system statistics after reading Tomas Vondra’s defense of using sar.  If you don’t have anything in place that readily visualizes the data, I have some examples that you can hopefully build off on to see everything you need to see. In addition to sar, the sysstat package also has a utility called sadf to display collected data.  Simply running sadf without any arguments will show you the recently aggregated processor utilization data resulting in: gentoo 599 2017-05-25 17:10:01 UTC all %user 13.03 gentoo 599 2017-05-25 17:10:01 UTC all %nice 0.01 gentoo 599 2017-05-25 17:10:01 UTC all %system 5.16 gentoo 599 2017-05-25 17:10:01 UTC all %iowait 0.47 gentoo 599 2017-05-25 17:10:01 UTC all %steal 0.00 gentoo 599 2017-05-25 17:10:01 (more…)

Future of Postgres-XL

PostgreSQL, Tomas' PlanetPostgreSQL
You probably know that Postgres-XL is a distributed database based on PostgreSQL. A few days ago we pushed the XL 9.6 code into the public git repository. Additional details about the new stuff available in Postgres-XL 9.6 are available here. The topic of this blog post is quite different, though. I'd like to discuss some changes to the project management and development practices, and why (and how) we plan to tweak it. (more…)

More robust collations with ICU support in PostgreSQL 10

Eisentraut's PlanetPostgreSQL, PostgreSQL
In this article, I want to introduce the ICU support in PostgreSQL, which I have worked on for PostgreSQL version 10, to appear later this year. Sorting Sorting is an important functionality of a database system. First, users generally want to see data sorted. Any query result that contains more than one row and is destined for end-user consumption will probably want to be sorted, just for a better user experience. Second, a lot of the internal functionality of a database system depends on sorting data or having sorted data available. B-tree indexes are an obvious example. BRIN indexes have knowledge of order. Range partitioning has to compare values. Merge joins depend on sorted input. The idea that is common to these different techniques is that, roughly speaking, if you (more…)

What’s new in Postgres-XL 9.6

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
For the last few months, we at 2ndQuadrant have been working on merging PostgreSQL 9.6 into Postgres-XL, which turned out to be quite challenging for various reasons, and took more time than initially planned due to several invasive upstream changes. If you’re interested, look at the official repository here (look at the “master” branch for now). There’s still quite a bit of work to be done - merging a few remaining bits from upstream, fixing known bugs and regression failures, testing, etc. If you’re considering contributing to Postgres-XL, this is an ideal opportunity (send me an e-mail and I’ll help you with the first steps). But overall, Postgres-XL 9.6 is clearly a major step forward in a number of important areas. (more…)

Up to date access to postgres logs

Andrew's PlanetPostgreSQL
Some of my Italian colleagues have made a nifty little gadget called redislog for pushing postgres logs into Redis, the distributed in-memory cache. From there it can be fed into things like logstash. I thought it would be interesting instead to make the logs available via the Redis Foreign Data Wrapper as a Postgres table. That way we would have easy access to the running logs from Postgres with almost no effort. Here's what I did. First I built and installed redislog and redis_fdw. Then I added redislog to my server's shared_preload_libraries,  set log_min_duration_statement to 0 and restarted. Then I created a database called logger and did this in it: create extension redis_fdw; create server localredis foreign data wrapper redis_fdw; create foreign table (more…)

Logical Replication in PostgreSQL 10

Petr's PlanetPostgreSQL
PostgreSQL 10 is getting close to its first beta release and it will include the initial support for logical replication, which is was written primarily by me and committed by my colleague Peter Eisentraut, and is internally based on the work 2ndQuadrant did on pglogical (even though the user interface is somewhat different). I'd like to share some overview of basics in this blog post. What's logical replication? Let me start with briefly mentioning what logical replication is and what's it good for. I expect that most people know the PostgreSQL streaming master-standby replication that has been part of PostgreSQL for years and is commonly used both for high availability and read scaling. So why add another replication mechanism and why call it logical? Well, the traditional (more…)

Out of tree builds

Andrew's PlanetPostgreSQL
Recently I was asked "what's a vpath build?" If you regularly build postgres from source it's something you should know about. A vpath build is one where the build tree is kept completely separate from the source tree, so you can completely remove the build tree and your source tree is still there, clean and pristine. It's very easy to set up unless you're building with the Microsoft toolset. Essentially what you do is create the root of your build tree, change directory into that directory, and then call configure in your source tree from there.  configure knows all about setting up the vpath tree and does all the work for you. After that, you just run make etc just like you would normally. So it looks like this: mkdir mybuild cd mybuild /path/to/postgresql-source/configure make (more…)