Is SQL partitioning a great optimization?

Partitioning is often presented as the next obvious optimization once a SQL table gets large. I am not sure that is always true.

I put together a small experiment in postgres-local-partition-lab to make that a bit more concrete. The setup was simple: a 10 million row Postgres table, then the same data repartitioned by month on created_at, with baseline queries before partitioning and the equivalent queries after.

The result was mixed, which is probably the useful part.

When a query filtered directly on created_at, partitioning helped a lot. For example:

SELECT COUNT(*)
FROM events
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-02-01';

Postgres could prune away most partitions and touch much less data. Dropping one old month of data was also far cheaper than deleting the same rows from a normal table. For time-series data, that may be the strongest reason to use partitioning.

DELETE FROM events_old
WHERE created_at >= '2025-06-01'
  AND created_at < '2025-07-01';

DROP TABLE events_2025_06;

That deletion above may be the most convincing case for partitioning here. Removing old data by dropping a whole partition is very different from deleting hundreds of thousands of rows one by one.

But the gains were not universal. A query that already had a good composite index changed very little, for example:

SELECT COUNT(*)
FROM events
WHERE user_id = 42
  AND created_at >= '2026-01-01'
  AND created_at < '2026-02-01';

This was already selective, so partitioning did not add much.

A whole-table aggregation actually became slower:

SELECT date_trunc('month', created_at) AS month, COUNT(*)
FROM events
GROUP BY 1
ORDER BY 1;

If a query still has to read every partition, there is not much to prune, so the partitioned layout can just add overhead.

So my current takeaway is fairly narrow: partitioning looks promising when the important queries line up with the partition key, or when retention is a big operational problem. Outside that, I think it is safer to treat it as a targeted tool, not a default optimization.