Skip to content

Database Partitioning Strategy

1. Decision

Choose Horizontal Range Partitioning for time-series and sequential data; choose Horizontal Hash Partitioning for uniform write distribution across shards; choose List/Directory Partitioning for explicit tenant or geographic data isolation; choose Vertical Partitioning to separate frequently accessed lean columns from heavy blob columns.

2. 30-Second Answer

Database Partitioning divides a large logical dataset into smaller, managed physical subsets (partitions) to improve query performance, manageability, and write throughput. Horizontal Range Partitioning splits rows by value ranges (e.g., created_at by month/year), enabling instant partition pruning for time-series queries but risking write hotspots on current ranges. Horizontal Hash Partitioning applies a hash function (hash(key) mod N) to distribute rows evenly across partitions, eliminating write hotspots but breaking range scan efficiency. List / Directory Partitioning routes rows explicitly based on discrete values (e.g., country_code or tenant_id). Vertical Partitioning splits table columns into separate physical storage files, isolating high-frequency narrow columns from large, infrequently accessed binary/text attributes.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
Horizontal Range Partitioning Time-series data, logs, metrics, financial transaction archives by date range High-velocity real-time writes where current timestamp creates single-partition write hotspots Low for range queries inside partition; High if scanning across all range files Medium (Requires automatic partition creation scripts) Strong within instance; Range boundary checks Medium
Horizontal Hash Partitioning Uniform write distribution, user profiles, account ledgers, high-concurrency OLTP Range queries (WHERE date BETWEEN X AND Y) requiring contiguous scans Low for single key lookups; High for range scans across all hash buckets High (Spreads writes evenly across \(N\) physical storage partitions) Strong per partition Medium
List / Directory Partitioning Multi-tenant SaaS isolation, geographic data residency compliance (GDPR) High-cardinality dynamic attributes without fixed discrete lists Low for tenant-isolated queries High (Easily isolate or migrate single tenant partitions) Strong per partition Medium
Vertical Partitioning Tables with 50+ columns containing heavy text/blob columns (TEXT, BLOB) alongside lean scalar columns Applications fetching all columns (SELECT *) simultaneously on every query Low (Reduces disk I/O page reads for hot columns) Medium (Keeps core index/table lean in memory) Strong across columns via primary key Low to Medium

4. Selection Rules

Requirement Best Choice Rationale / Example
Time-Series / Logging Data (Prometheus / Timescale) Range Partitioning Partition by created_at (daily/monthly); drop old partitions instantly via DROP TABLE
Eliminate Write Hotspots in High-QPS OLTP Hash Partitioning Partition by hash(user_id) to distribute writes evenly across 64 storage buckets
Multi-Tenant SaaS Data Residency (GDPR) List Partitioning Partition by country_code: EU partition stored on Frankfurt cluster, US in N. Virginia
Isolate Heavy TEXT/BLOB Columns Vertical Partitioning Split users into user_core (id, email, password) and user_profile (bio, avatar_blob)
Instant Purging of Historical Data Range Partitioning DROP PARTITION p_2023_01 deletes disk files in milliseconds without DELETE WAL overhead

5. Tradeoffs

  1. Range Partitioning Pruning vs Single-Partition Write Hotspotting
  2. Gain (Range): Query planner skips 95% of disk partitions when filtering by date range (Partition Pruning).
  3. Lose (Range): 100% of current incoming writes hit the latest date partition, bottlenecking single disk I/O.
  4. Hash Partitioning Uniformity vs Loss of Range Scans
  5. Gain (Hash): Perfect \(1/N\) write distribution across all storage partitions.
  6. Lose (Hash): Range queries (WHERE age BETWEEN 20 AND 30) must scan every hash partition.
  7. Vertical Partitioning I/O Efficiency vs Multi-Table Join Overhead
  8. Gain (Vertical): Core table disk pages fit 10x more rows in RAM buffer pool, accelerating index scans.
  9. Lose (Vertical): Queries requesting full entity profiles require joining two physical tables together.
  10. List Partitioning Isolation vs Unbalanced Tenant Growth
  11. Gain (List): Isolate enterprise tenants to dedicated physical partitions for security/compliance.
  12. Lose (List): An enterprise mega-tenant creates a "whale partition" that outgrows others.
  13. Declarative Database Partitioning vs Application-Level Sharding
  14. Gain (Declarative): Database engine manages routing transparently within a single DB instance.
  15. Lose (Declarative): Still bounded by single DB instance CPU/RAM limits (unlike distributed sharding).

6. Interview Counter Questions

  1. How does Partition Pruning work in PostgreSQL / MySQL query planners?
  2. Why is dropping a range partition (DROP TABLE partition_name) 1,000x faster than running DELETE FROM table WHERE date < X?
  3. How do Primary Keys and Unique Constraints interact with partitioned tables in PostgreSQL?
  4. What is the difference between Single-Level Partitioning and Composite (Sub-partitioning) Partitioning?
  5. How do you handle write hotspotting when range-partitioning time-series data?
  6. How does Vertical Partitioning differ from Columnar Storage (ClickHouse/Parquet)?
  7. What happens if an INSERT statement supplies a partition key value that does not match any existing List/Range partition?
  8. How do Global Secondary Indexes vs Local Secondary Indexes work on partitioned tables?
  9. When would you combine Hash Partitioning with Range Partitioning (Composite Hash-Range)?
  10. How do you automatically create future date partitions in PostgreSQL (pg_partman extension)?

10. Gold Standard Answers

  1. Partition Pruning: During query optimization, the planner checks WHERE conditions against partition constraints. If WHERE created_at >= '2026-01-01', it excludes all pre-2026 partition disk files from the execution plan.
  2. DELETE scans every tuple, writes individual row deletions to the Write-Ahead Log (WAL), updates secondary indexes, and requires Autovacuum cleanup. DROP PARTITION simply unlinks the physical file from the OS filesystem instantly.
  3. In PostgreSQL, any PRIMARY KEY or UNIQUE constraint on a partitioned table must include the partition key column. Otherwise, the database would have to check uniqueness across every single partition file on every insert.
  4. Single-Level uses 1 strategy (e.g., Range by Date). Composite (Sub-partitioning) uses 2 layers (e.g., Range by date at top level, then Hash by user_id inside each date partition) to prevent single-partition size bloat.
  5. Combine Hash + Range partitioning (sub-partitioning), or write incoming real-time time-series data to an in-memory buffer (Redis / Kafka) and batch ingest into historical range partitions.
  6. Vertical Partitioning manually splits a table into 2+ row-oriented tables joined by Primary Key. Columnar Storage natively stores every individual column in separate compressed files at the storage engine level.
  7. The database throws an execution error (no partition found for row). Mitigate by defining a DEFAULT partition to capture unmatched rows, or using automated background partition creation (pg_partman).
  8. Local Secondary Index: Indexed per partition; query missing partition key forces scatter-gather across all local indexes. Global Secondary Index: Single index spanning all partitions; fast lookup, but complex cross-partition updates.
  9. Use Composite Hash-Range (e.g., Hash by user_id to distribute write traffic evenly across 16 nodes, then Range by month inside each node for easy historical data purging).
  10. Use pg_partman (PostgreSQL Partition Manager extension). It runs scheduled background jobs to create future date partitions automatically (e.g. 7 days ahead) and archive/drop expired historical partitions.

8. Common Mistakes

  • Failing to include the partition key in primary/unique constraints, causing DDL migration errors in PostgreSQL.
  • Range-partitioning by date without setting up automated future partition creation (pg_partman), causing production INSERT crashes when a new month begins.
  • Running DELETE FROM table WHERE created_at < date on massive tables instead of using DROP PARTITION.
  • Hash-partitioning a time-series table and wondering why date range queries execute full table scans across all hash buckets.
  • Over-partitioning a database into thousands of tiny partitions (<10MB each), exhausting OS file descriptors and query planner memory.

9. Keywords

Database Partitioning, Horizontal Partitioning, Vertical Partitioning, Range Partitioning, Hash Partitioning, List Partitioning, Partition Pruning, Composite Partitioning, Sub-partitioning, DROP PARTITION, Local Secondary Index, Global Secondary Index, pg_partman, DEFAULT Partition.

10. Cheat Sheet

  • Range Partitioning: Best for time-series / logs (created_at). Enables fast Partition Pruning and instant DROP PARTITION purging. Risk: Write hotspots on current date.
  • Hash Partitioning: Best for uniform write distribution (hash(user_id) mod N). Eliminates write hotspots. Downside: Breaks date range queries.
  • List Partitioning: Best for multi-tenant SaaS & data residency compliance (country_code = EU, US).
  • Vertical Partitioning: Separates lean hot columns (id, email) from heavy cold columns (bio, avatar_blob) to fit more rows per disk page in RAM.
  • Postgres Constraint Rule: Primary Keys and Unique Constraints MUST include the partition key column!
  • Automation: Always deploy pg_partman to create future date range partitions automatically before they are needed.

11. Related Decisions