Database Partitioning: Types, Strategies, and When to Use Each

Database partitioning splits large tables into smaller physical segments within the same database instance, with the engine automatically routing queries to the correct partition. The three main strategies are range partitioning (ideal for time-series data), list partitioning (for categorical values like region), and hash partitioning (for even distribution). Partition key selection is critical — if queries don't filter on the key, all partitions get scanned, negating any benefit. PostgreSQL supports declarative partitioning since v10 with tools like pg_partman for automation; MySQL has native support with some constraints. Partitioning helps with query performance via partition pruning, faster data deletion (detach vs DELETE), and more manageable VACUUM operations. It does not solve write throughput scaling — that requires sharding. Monitoring should track per-partition row counts, pruning effectiveness via EXPLAIN, and maintenance timing.