PostgreSQL Advanced Concepts: Partitioning, Parallel Query, Logical Replication, Advisory Locks

Intro

PostgreSQL advanced concepts such as table partitioning, parallel query execution, logical replication, and advisory locks are the levers that turn a functional database into a production‑grade system. Developers and DevOps engineers often copy configuration snippets without seeing how they behave under load. This guide shows the minimal change, the command that proves the change works, and the failure mode when the setting is missing or wrong.

The target audience includes backend developers, platform engineers, and technical leads who need to move from theory to a repeatable local test. Each section follows a configure‑verify‑break pattern so you can run the steps on a laptop, in CI, or on a staging host and get the same observable result.

Workflow Overview

1. Identify the resource you want to influence (e.g., a large fact table, a replication slot, a lock name).
2. Apply a single configuration change (SQL command or postgresql.conf setting).
3. Run a verification command that returns a clear success signal.
4. Document the failure signal that appears when the change is omitted or mis‑configured.

Example – enabling parallel query for a partitioned table:

CREATE TABLE events (
    event_id bigserial,
    created_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT count(*) FROM events WHERE created_at >= '2024-01-01';

The EXPLAIN ANALYZE output should show Parallel Seq Scan or Parallel Append. If the plan shows a plain Seq Scan on the parent table, the parallel setting is not taking effect – often because max_parallel_workers_per_gather is still 0 or the table has no partitions.

Hidden assumptions that surface here: the PostgreSQL version must be 13 or newer for parallel append, the max_worker_processes limit must be high enough, and the query must touch enough rows to exceed the parallel_tuple_cost threshold.

Local Pilot Plan

Run a throwaway PostgreSQL instance, apply the change, and verify the plan in under five minutes.

docker run -d --name pgpilot -e POSTGRES_PASSWORD=secret postgres:16
docker exec -it pgpilot psql -U postgres -c "CREATE TABLE events (event_id bigserial, created_at timestamptz NOT NULL, payload jsonb) PARTITION BY RANGE (created_at);"
docker exec -it pgpilot psql -U postgres -c "CREATE TABLE events_2024_01 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');"
docker exec -it pgpilot psql -U postgres -c "INSERT INTO events (created_at, payload) SELECT generate_series('2024-01-01'::timestamptz, '2024-01-31'::timestamptz, interval '1 minute'), '{}'::jsonb;"
docker exec -it pgpilot psql -U postgres -c "SET max_parallel_workers_per_gather = 4; EXPLAIN ANALYZE SELECT count(*) FROM events WHERE created_at >= '2024-01-01';"

The final EXPLAIN ANALYZE line should return a plan that includes Parallel Append and a non‑zero Workers Planned value. If the plan shows Workers Planned: 0, check max_worker_processes (default 8) and parallel_setup_cost.

Clean up:

docker rm -f pgpilot

Repeating this pilot from a clean checkout proves that the configuration is self‑contained and version‑agnostic.

Conclusion

Treat every advanced PostgreSQL feature as a testable hypothesis: change one knob, run a deterministic query, and read the plan. When the team documents the exact commands used to create objects, set parameters, and inspect output, the knowledge survives hand‑offs and CI migrations. The next step is to pick a single feature — partitioning, logical replication, or advisory locks — and write a one‑page runbook that lists the create, configure, verify, and teardown commands. That runbook becomes the reference for staging, production, and on‑call troubleshooting.