CodeParkPartitioning splits one big table into smaller pieces on the same database server. Sharding splits the data across separate database servers.
Partitioning: in PostgreSQL's declarative partitioning, you define a parent table and split it by range, list or hash on a key. For example, you can have one partition per month of created_at.
- A query that filters on the key reads only the partitions it needs. This is called partition pruning.
- You can remove old data by detaching or dropping a whole partition. This is much faster than a slow
DELETE.
Sharding: each server holds part of the rows. A shard key, such as tenant_id, decides which server holds each row. Sharding spreads storage, writes and load beyond what one machine can handle. But it has costs:
- Queries, joins and transactions that cross shards become hard.
- You need routing logic or a sharding layer to send each query to the right server.
In short: partitioning helps you manage and speed up one server. Sharding is horizontal scaling: adding more servers.
- Partitioning splits a table into pieces on one server.
- Partitions use range, list or hash, and queries skip unneeded partitions (partition pruning).
- You can remove old data cheaply by dropping a partition.
- Sharding spreads data across servers using a shard key.
- Sharding makes queries and transactions across shards hard.