pg_rewrite: PostgreSQL Table Partitioning

PostgreSQL table partitioning is by far one of the most popular new PostgreSQL features. Many people look to partitioned tables as a way to improve performance and broaden scalability. However, partitioning comes with a little problem: How can you partition an existing table without locking up the database? The answer is: pg_rewrite can help you with PostgreSQL table partitioning. Here you will learn how to implement pg_rewrite to help you solve partitioning problems in the most elegant way possible.

Installing pg_rewrite

pg_rewrite is Open Source and can be quickly and easily downloaded from our Github profile. Cloning the repository works as shown in the next listing:

Once this is done, we can enter the directory:

To build the code, we have to make sure that the correct version of pg_config is in the path. In my case, I have a working binary in my home directory, so I can effortlessly build the code:

If pg_config is in the path, all you have to do is to run “make install” to compile and install the code:

The next thing to do is to adjust postgresql.conf file. pg_rewrite has to be loaded as a library when the server starts. Otherwise, it won’t work. Configuring PostgreSQL to load pg_rewrite works as follows:

The wal_level has to be adjusted to make sure that the WAL contains enough information for logical decoding to work. On top of that, you'll need a sufficient number of replication slots to safely run logical decoding. pg_rewrite will need one slot to operate.

After these changes are done, restart your server and verify that the variables are properly set:

Finally, you have to enable the pg_rewrite extension to ensure that the partition_table function is available:

Voilà, you have a working version of pg_rewrite. Let's take a look at how we can use it.

Creating a sample table

After installing pg_rewrite, we can create a table - which we'll want to partition later on:

For the sake of simplicity, the table contains a couple of random values. Some are greater than zero and some are less than zero. Let's verify that:

The data seem correct, so we can move to the next step:

PostgreSQL can only identify a row if there is a primary key. Otherwise, we'll have problems and the code won't work. So it's important to ensure that there are indeed primary keys.

Creating table partitions in PostgreSQL

Before we can partition the table, we have to come up with a partitioning scheme. We want to use that scheme for our database. In my example, all I'm doing is putting negative values into one table, and positive values into another. Here's how it works:

Make sure that the primary keys and constraints are identical. Otherwise, pg_rewrite will produce an error. Remember: we want to repartition the table - we don’t want anything else to happen.

PostgreSQL table partitioning with almost no locking

Now that all the ingredients are in place, we can rewrite the data:

t_number is the source table which has to be rewritten. t_part_number is the freshly partitioned table which we want to use as intermediate storage. t_old_number is the name of the original table which will be renamed. In case something goes wrong, the old table will still be found. The advantage is that nothing can go wrong. The downside is that more storage is needed. However, that is the case DURING repartitioning anyway so it does not matter if the source table is deleted or not. We need twice the space anyway.

The final structure will look as follows:

As you can see, the original table is still in place using up 42 MB of storage. Our two partitions will need the same amount of space.

pg_rewrite is a good method to turn existing tables into PostgreSQL partitions. It only needs a short lock, which makes it superior to the long and extensive table locking needed by the PostgreSQL core.

Finally ...

• GIS databases (PostGIS) are often very large and can make full use of partitioning. So we want to share some good news: We offer ready-made OSM data (OpenStreetMap) for PostgreSQL on our new GIS site. You will find ready made PostgreSQL dumps there which can be easily imported into your system.
• For information on how to migrate partitioned data, see this informative blogpost.
• And if you're interested in learning more about how to set PostgreSQL configuration parameters, see our blog here.