Partitioning, Extensions & Replication

Declarative partitioning

CREATE TABLE sales (
  id        bigint GENERATED ALWAYS AS IDENTITY,
  sale_date date NOT NULL,
  amount    numeric(10,2),
  PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE sales_2025 PARTITION OF sales
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

Type	Splits by
RANGE	Value ranges — ideal for dates
LIST	An explicit list of values (region, status)
HASH	A hash of an expression, for even distribution

CREATE TABLE sales_bd PARTITION OF sales_by_region FOR VALUES IN ('BD','IN');
CREATE TABLE sales_p0 PARTITION OF sales_by_hash FOR VALUES WITH (MODULUS 4, REMAINDER 0);

ALTER TABLE sales DETACH PARTITION sales_2024;    -- instant archival, no data movement
DROP TABLE sales_2024;                            -- actually delete it, also instant

EXPLAIN SELECT * FROM sales WHERE sale_date >= '2025-01-01';  -- check partition pruning

The primary key must include the partition key. Queries that filter on sale_date only scan the relevant partitions (partition pruning); queries that do not filter on it scan everything, so partitioning is not a free speed-up — pick a key your real queries actually filter on.

Extensions

CREATE EXTENSION is the mechanism that makes Postgres feel like several databases in one. A short, non-exhaustive tour:

CREATE EXTENSION IF NOT EXISTS pg_trgm;        -- fuzzy text search
CREATE EXTENSION IF NOT EXISTS btree_gist;     -- lets EXCLUDE use btree-style types
CREATE EXTENSION IF NOT EXISTS postgis;        -- geospatial types and functions
CREATE EXTENSION IF NOT EXISTS pgcrypto;       -- gen_random_uuid, digest, crypt
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- query performance tracking
CREATE EXTENSION IF NOT EXISTS vector;         -- pgvector: embeddings and similarity search
CREATE EXTENSION IF NOT EXISTS tablefunc;      -- crosstab() for real pivots
CREATE EXTENSION IF NOT EXISTS pg_cron;        -- cron jobs inside the database

\dx                          -- list installed extensions
SELECT * FROM pg_available_extensions;

pgvector deserves a special mention because it is why Postgres is now common as the database for AI applications: store embeddings and query by similarity directly in SQL.

CREATE TABLE documents (id serial PRIMARY KEY, content text, embedding vector(1536));
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
SELECT content FROM documents ORDER BY embedding <=> '[0.1, 0.2, …]' LIMIT 5;

Replication

        writes
  App ──────────► Primary ──WAL──► Standby 1 (reads)
                              └───► Standby 2 (DR)

Streaming replication ships the WAL continuously to one or more standbys.

SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;         -- run on the primary

SELECT now() - pg_last_xact_replay_timestamp() AS lag;   -- run on a standby

• Asynchronous (default) — fast, but a failover can lose the last few transactions.
• Synchronous — the primary waits for at least one standby to confirm, trading latency for zero data loss on failover.

Logical replication ships row changes rather than raw WAL, so it can replicate a subset of tables or feed a different major version:

-- on the source
CREATE PUBLICATION school_pub FOR TABLE students, courses;

-- on the target
CREATE SUBSCRIPTION school_sub
  CONNECTION 'host=source dbname=school user=repl'
  PUBLICATION school_pub;

Logical replication is also how many teams stream a Postgres database into an analytics warehouse.

Scaling strategy, in order

1. Optimise first — indexes and query fixes usually beat hardware by 10–100×.
2. Scale up — more RAM so shared_buffers and the OS cache hold the working set.
3. Cache — Redis or Memcached in front of expensive reads.
4. Read replicas — send reports and analytics off the primary.
5. Partition and archive — keep the hot table small.
6. Connection pooling — PgBouncer, because each Postgres connection is a full OS process.
7. Shard — split data across servers (Citus is Postgres's native option). Do this last; it complicates joins and transactions considerably.

Health checks

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
SELECT count(*) FROM pg_stat_activity;
SHOW max_connections;