When to partition (and when not to)

Partitioning is a tool for large tables with a range or lifecycle pattern —
not a general "make it fast" switch.

Good reasons:

Time-series data you routinely query and retain by date range (events,
logs, metrics, orders by month).
A table so large that index maintenance, VACUUM, or backups are painful.
A natural "drop old data" lifecycle — keep 13 months, drop the oldest.
Clear data tiering — recent partitions hot, old ones archived.

Bad reasons (skip partitioning):

"The table is big" with no range/lifecycle access pattern. A well-indexed
single table handles tens of millions of rows comfortably.
You want to spread load but queries don't filter on the partition key —
pruning won't trigger, so you pay complexity for nothing.
The table is small or moderate. Partitioning a 100k-row table is pure
overhead.

Rule of thumb: if you cannot name the partition key and the query
predicate that will prune on it and the lifecycle that drops old
partitions, you are not ready to partition.

Included with Data Modeling Patterns

Ready for the complete Data Modeling Patterns?