PostgreSQL Declarative Partitioning

Tip: Range Partitioning

CREATE TABLE logs (
    id BIGSERIAL,
    created_at TIMESTAMP NOT NULL,
    message TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2024 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Gotcha: Partition Pruning

SELECT * FROM logs WHERE created_at >= '2024-06-01';

PostgreSQL only scans the relevant partition.

Tip: Hash Partitioning

CREATE TABLE users PARTITION BY HASH (id);
CREATE TABLE users_0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Gotcha: Default Partition

CREATE TABLE logs_default PARTITION OF logs DEFAULT;

Catches rows that don't match any partition.

Tip: Detach Partition

ALTER TABLE logs DETACH PARTITION logs_2023;

Archive old data without affecting current queries.

Gotcha: Unique Constraints

Must include the partition key in unique constraints.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Declarative partitioning (PostgreSQL 10+) is vastly superior to the older inheritance-based partitioning. The database manages partition routing automatically, and partition pruning works with the query planner. I use it for time-series data where partition pruning based on date ranges provides massive query speedups. The gotcha: you cannot easily move data between partitions — plan your partition boundaries carefully and consider a default partition for out-of-range data.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)