Partitioning benefits:

- Partition pruning: 10-100x faster queries

- Smaller indexes: Faster index scans

- Parallel operations: Query partitions in parallel

- Easy archiving: Drop old partitions instantly

- Maintenance: Vacuum/analyze individual partitions

Partitioning costs:

- Planning overhead: Choosing partition key

- Partition management: Creating/dropping partitions

- Query complexity: Must include partition key for pruning

Sharding benefits:

- Horizontal scaling: Add more servers

- Isolation: One shard failure doesn't affect others

- Geographic distribution: Data close to users

Sharding costs:

- Application complexity: Route queries to correct shard

- Cross-shard queries: Expensive or impossible

- Rebalancing: Moving data between shards

- Consistency: Distributed transactions are hard

Partition pruning provides 10-100x speedup for filtered queries

Smaller partitions mean smaller indexes and faster scans

Parallel query execution across partitions

DROP partition is instant vs DELETE millions of rows (hours)

Maintenance operations (VACUUM, ANALYZE) on individual partitions

Always include partition key in queries for pruning benefit

Choosing wrong partition key - queries don't benefit from pruning

Too many partitions - planning overhead increases

Too few partitions - partitions too large, no benefit

Forgetting to create indexes on each partition

Cross-partition queries without partition key - scan all partitions