High Performance PostgreSQL for Rails

Practicing Backfilling Techniques

Let’s practice some of the backfill techniques you’ve seen.

Using the earlier example that added a city_id column to the users table, imagine that you were creating a purpose-built intermediate table to act as the data source.

This table has these characteristics:

• A minimal set of columns
• Purpose-made index for a single query
• No constraints
• Unlogged
• Autovacuum disabled
• No other indexes if updating or deleting rows

Run these statements in psql:

sql/create_backfill_intermediate_table.sql

CREATE SCHEMA IF NOT EXISTS temp;

CREATE UNLOGGED TABLE temp.users_intermediate (
  user_id BIGINT, city_id INTEGER
);

ALTER TABLE temp.users_intermediate
SET (autovacuum_enabled = false);

Generate some ...