I've implemented partitioning in a dozen or so databases. It's made some of them dramatically faster. It's made others noticeably worse. The difference is almost always about whether the workload actually benefited from partitioning — not whether the table was "big enough."
Partitioning solves specific problems. If you don't have those specific problems, partitioning adds complexity without benefit.
When Partitioning Actually Helps
1. Table size is causing maintenance problems. VACUUM, ANALYZE, CREATE INDEX take forever because they process the entire table. With partitioning, you can run these on individual partitions. Also, dropping old data becomes instant: DROP TABLE on a partition rather than DELETE millions of rows.
2. Your queries have strong partition key filters. Partition pruning eliminates entire partitions from the query. If your queries almost always filter on the partition key (like created_at for a time-partitioned table), the planner will skip irrelevant partitions entirely.
3. You have lifecycle requirements. "Keep 90 days of data, archive the rest" is the canonical partitioning use case. Drop old partitions, add new ones. Clean and simple.
When Partitioning Doesn't Help
Cross-partition queries: If your queries rarely or never filter on the partition key, partitioning doesn't help and adds overhead.
Small tables: Partitioning a 10GB table is almost never worth it. Start thinking about it above 100GB, seriously consider it above 500GB.
Complex foreign key relationships: Partitioned tables have restrictions on foreign keys. You can't create a foreign key from a partitioned table to another table that references the partition key — only the other direction.
Write-heavy workloads with no pruning: Every INSERT has a routing overhead to decide which partition to write to.
Range Partitioning (Most Common)
-- Create partitioned table
CREATE TABLE orders (
id BIGINT NOT NULL,
user_id INT NOT NULL,
amount DECIMAL(10,2),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
status TEXT
) PARTITION BY RANGE (created_at);
-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE orders_2024_q3 PARTITION OF orders
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE orders_2024_q4 PARTITION OF orders
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
CREATE TABLE orders_2025_q1 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
-- Default partition for anything that doesn't match
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
Always create a DEFAULT partition. Without it, INSERTs that don't match any partition fail with an error. That error at 3 AM when the new year partition hasn't been created is not fun.
Creating New Partitions Automatically
You need a strategy for creating new partitions before data arrives:
-- Create next quarter's partition
CREATE TABLE orders_2025_q2 PARTITION OF orders
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
I automate this with a scheduled job (pg_cron or external cron) that creates the next partition 30 days before it's needed. The default partition acts as a safety net.
Indexes on Partitioned Tables
Create indexes on the partitioned table — they automatically apply to all partitions and new partitions as they're created:
-- Index applied to all partitions
CREATE INDEX ON orders (user_id, created_at);
CREATE INDEX ON orders (status) WHERE status != 'completed';
-- Primary key must include partition key
ALTER TABLE orders ADD PRIMARY KEY (id, created_at);
That last one is a common gotcha: the primary key must include the partition key in PostgreSQL. So if your table has a simple integer PK, you need to either include the partition key in the PK or use a unique index instead.
List Partitioning
For categorical data:
CREATE TABLE products (
id BIGINT NOT NULL,
name TEXT,
category TEXT NOT NULL,
price DECIMAL(10,2)
) PARTITION BY LIST (category);
CREATE TABLE products_electronics PARTITION OF products
FOR VALUES IN ('phones', 'laptops', 'tablets', 'accessories');
CREATE TABLE products_clothing PARTITION OF products
FOR VALUES IN ('shirts', 'pants', 'shoes', 'hats');
CREATE TABLE products_default PARTITION OF products DEFAULT;
List partitioning works well when you have queries that filter heavily by category and the categories are relatively stable. If you add new categories constantly, you're adding partitions constantly — which is maintenance overhead.
Hash Partitioning
For distributing write load evenly when you have no natural range or list key:
CREATE TABLE events (
id BIGINT GENERATED ALWAYS AS IDENTITY,
user_id BIGINT NOT NULL,
event_type TEXT,
payload JSONB,
created_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY HASH (user_id);
CREATE TABLE events_0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Hash partitioning doesn't help with pruning for range queries — it helps with parallelism and maintenance. I use it less often than range partitioning.
Migrating an Existing Table to Partitioned
This is the hard part. You can't just ALTER TABLE an existing table into a partitioned one. You need to:
- Create new partitioned table with same schema
- Copy data in batches (or use logical replication)
- Swap tables
The slow way (minimal dependencies):
-- Create new partitioned structure
CREATE TABLE orders_new (LIKE orders INCLUDING ALL) PARTITION BY RANGE (created_at);
-- ... create partitions ...
-- Copy data in batches to avoid long transaction
INSERT INTO orders_new
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';
-- Repeat for each partition
-- Swap (requires brief downtime or application-level coordination)
BEGIN;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;
The fast way (with pg_repack or logical replication for zero-downtime) is more complex but necessary for large production tables.
Common Mistakes
Not creating the default partition: And then INSERTs fail at midnight on December 31st.
Wrong partition key: Partitioning on a column you rarely filter on. The planner can't prune what it doesn't know about.
Too many partitions: Each partition is a table. PostgreSQL handles hundreds of partitions fine, but thousands of partitions can cause planning overhead. I keep it under 500.
Forgetting to create partition indexes: Without explicit CREATE INDEX, each new partition starts with no indexes. The parent index covers existing data, but you need to make sure it applies going forward.
Expecting magic: A 10-second query on a non-partitioned table doesn't automatically become 100ms after partitioning, unless the 10 seconds were specifically caused by scanning data that partitioning would eliminate.
MonPG tracks partition health — which partitions are getting VACUUMed, index health per partition, bloat by partition. This becomes important as your partition count grows.
Partitioning is a scalpel, not a sledgehammer. Use it for the right problem.