Schema Changes in PostgreSQL with Less Pain

Feedbin has a problem. Its articles are stored in a PostgreSQL database, with a 32-bit primary key, that is about to overflow. The largest number you can store in 32 bits is 2,147,483,647 and Feedbin currently is up to id number 2,107,994,090 as of this moment. So there are only about 39 million ids left. At that point no new articles will be able to be created.

Feedbin ended up in this situation because

I didn’t know any better at the time.
Back when Feedbin was released, the default data-type for primary keys in Rails was 32-bit integers. Rails changed the default to 64-bit integers with version 5.1 in 2017.

Luckily, this isn’t a surprise, it’s actually something I’ve been thinking about for almost five years. In November of 2014, Feedbin hit this limit with in the table that stores the unread status of articles. That was a surprise.

However, it also wasn’t a big deal. That table did not need a primary key. The rows in that table could already be uniquely identified with a combined index of user_id + entry_id, so the fix there was to drop the column.

For the entries table, it is a big deal, and dropping this column is not an option.

There are two options:

Change the column to a bigint.
Switch to a different type of primary key like a UUID

Number 2 isn’t a great option. It would require many code changes, other schema changes and for clients that sync with Feedbin to be able to handle UUIDs.

There were a few different ways I identified to switch to a bigint.

Change the column type directly: ALTER TABLE entries ALTER COLUMN id TYPE bigit;. This one is easy, but it would mean a lot of downtime. This takes out an ACCESS EXCLUSIVE lock on the table, meaning nothing can be written or even read while this runs. It also takes a long time.
Adding a new column, backfilling the data, then switching over to the new column. I came across this solution on Stack Overflow. Until recently this is what I was planning on doing. This solution incur any downtime, but it is complicated. The process would need to be repeated for every column that refers to the entry_id as well. In my testing this took a very long time.

That brings me to option three. It turns out that I put the problem off long enough that it has been solved for me by a feature added in PostgreSQL 10 last year: logical replication. Previously, Postgres only had physical byte-for-byte replication. Logical replication offers more flexibility because it replays SQL statements like INSERT, UPDATE and DELETE on your replicas. Most importantly for this use-case is that the underlying data type of the replica does not matter as long as the data will still fit into the column.

Here’s how to do this. Let’s say there are two PostgreSQL installations on the same server. Both version 10 and 11 are installed on ports 5432 and 5433 respectively.

Make sure the replica is allowed to connect to the master database

 cat >> /etc/postgresql/10/main/pg_hba.conf  <<EOL
 host all replication 123.123.123.123/32 md5
 EOL

Set some configuration options required for physical replication. Also going to temporarily increase the min_wal_size to speed up the transfer.

 cat >> /etc/postgresql/10/main/postgresql.conf  <<EOL
 wal_level = logical
 max_wal_senders = 16
 max_replication_slots = 8
 min_wal_size = 1GB
 max_wal_size = 2GB
 EOL

Update the configuration on the replica

 cat >> /etc/postgresql/11/main/postgresql.conf  <<EOL
 max_logical_replication_workers = 8
 max_worker_processes = 16
 max_sync_workers_per_subscription = 6
 EOL

Restart the databases so that the new configuration takes effect.

 sudo service postgresql@10-main restart
 sudo service postgresql@11-main restart

Dump the schema from the master, and change all integer types to bigint.

 pg_dump --port 5432 --dbname feedbin --schema-only > schema.sql
 sed --in-place 's/integer/bigint/g' schema.sql

Create the replication role and the PUBLICATION on the master.

 psql --port 5432 --command "CREATE ROLE replication WITH LOGIN PASSWORD 'password' REPLICATION;"
 psql --port 5432 --dbname feedbin --command "CREATE PUBLICATION publication1 FOR ALL TABLES;"
 psql --port 5432 --dbname feedbin --command "GRANT SELECT ON ALL TABLES IN SCHEMA public to replication;"

Create the database, role and import the schema on the replica.

 psql --port 5433 --command "CREATE DATABASE feedbin;"
 psql --port 5433 --command "CREATE ROLE feedbin WITH LOGIN PASSWORD 'password' SUPERUSER;"
 psql --port 5433 --dbname feedbin --file schema.sql

Create the subscription on the replica.

 psql --port 5433 --dbname feedbin --command "CREATE SUBSCRIPTION subscription1 CONNECTION 'host=localhost dbname=feedbin user=replication port=5432 password=password' PUBLICATION publication1;"

Now we wait. Once the replica is caught up, there’s one more very important part.

Make sure nothing is getting written to the database. Then check the lag until it reaches 0.

 psql --port 5432 --command "SELECT  application_name,  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) lag FROM pg_stat_replication;

The output should look like this when everything is caught up:

 -[ RECORD 1 ]----+-----
 application_name | subscription1
 lag              | 0

Logical replication does not bring over sequence data like you would use for auto-incrementing primary keys. We have to copy it over as a separate step.

 sequences=$(psql --tuples-only --no-align --quiet --no-psqlrc --port 5432 --dbname feedbin --command "SELECT pg_class.relname FROM pg_class WHERE pg_class.relkind = 'S';")
 while read -r sequence; do
     count=$(psql --tuples-only --no-align --quiet --no-psqlrc --port 5432 --dbname feedbin --command "SELECT last_value FROM ${sequence};")
     echo "SELECT setval('${sequence}', ${count});" >> sequences.sql
 done <<< "${sequences}"

This creates the file with sequences.sql which contains a statement for each sequence defined in the database:

 SELECT setval('entries_id_seq', 2107994090);

Import the sequence data into the replica.

 psql --port 5433 --dbname feedbin --file sequences.sql

Finally update your application to point to the new database. That’s it!