PostgreSQL Partitioning: Tips & Tricks

Tip: Range Partitioning by Date

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

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

Laravel queries work transparently — no code changes needed.

Gotcha: Partition Key Must Be in WHERE

-- Good: uses partition pruning
SELECT * FROM logs WHERE created_at >= '2024-01-01';

-- Bad: scans all partitions
SELECT * FROM logs WHERE message LIKE '%error%';

Tip: Hash Partitioning for Even Distribution

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

Good for large tables without a natural range key.

Gotcha: Unique Constraints Must Include Partition Key

-- Won't work on partitioned table
CREATE UNIQUE INDEX ON logs (id);

-- Must include partition key
CREATE UNIQUE INDEX ON logs (id, created_at);

Tip: Detach Old Partitions

ALTER TABLE logs DETACH PARTITION logs_2023_q1;

Archive old data without affecting current queries.

Gotcha: Foreign Keys Don't Work Across Partitions

You can't reference a partitioned table from another table with a foreign key.

Tip: List Partitioning by Category

CREATE TABLE events PARTITION BY LIST (type);
CREATE TABLE events_logins PARTITION OF events FOR VALUES IN ('login', 'logout');
CREATE TABLE events_actions PARTITION OF events FOR VALUES IN ('click', 'purchase');

Gotcha: Default Partition

CREATE TABLE logs_default PARTITION OF logs DEFAULT;

Catches any rows that don't match existing partitions.

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

Table partitioning in PostgreSQL is essential for time-series data, but it's not a set-and-forget optimization. I've seen teams partition by month without considering that queries spanning partition boundaries scan all relevant partitions. The real benefit of partitioning isn't query speed — it's partition-level operations like DROP PARTITION for rolling window data. For most web application workloads, proper indexing outperforms partitioning.

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