What is the difference between database partitioning and database sharding?

Database partitioning and sharding both distribute data across multiple storage units, but they operate at different levels and solve different problems. Partitioning divides a single logical table into multiple physical storage units — called partitions — that are managed by the same database instance and accessible through the same connection. The partitioned parent table is a unified object: a query against the parent table is automatically routed to the relevant partitions by the database engine, and the application sees a single table. Partitioning is used to improve query performance through partition pruning (eliminating irrelevant partitions from query execution), to improve maintenance operations (VACUUM, ANALYZE, index rebuilds run per partition rather than across the entire table), and to enable efficient data lifecycle management (dropping an old partition via DROP TABLE is orders of magnitude faster than DELETE FROM table WHERE date < X on a large table, because the partition drop is a metadata operation while DELETE is a row-by-row operation that must write to the WAL and create dead tuples for VACUUM). Sharding distributes data across multiple database instances — separate processes, often on separate physical machines — where each shard holds a subset of the data and no single database instance has access to all rows. The application (or a routing layer) is responsible for directing queries to the correct shard based on the shard key. Sharding is used to scale write throughput beyond the capacity of a single database instance (each shard handles writes for its subset of data), to scale storage beyond the disk capacity of a single machine, and to enable geographic distribution (shards in different regions serving local traffic). The trade-off of sharding is that cross-shard queries — queries that need rows from multiple shards — require the application to query each shard and merge results, because the database engine cannot automatically route and merge across instances the way it can across partitions within a single instance. Partitioning and sharding are complementary: a sharded system often uses partitioning within each shard for data lifecycle management and intra-shard query optimization.

How does Postgres partition pruning work, and what prevents it from being effective?

Postgres partition pruning is the query planner's ability to exclude partitions from query execution when the query predicate cannot match any row in those partitions. For RANGE partitioning on created_at with monthly partitions, a query with WHERE created_at BETWEEN '2025-01-01' AND '2025-03-31' will prune all partitions except January 2025, February 2025, and March 2025 — the planner knows that no other partition can contain rows with those timestamps, so it skips them entirely. The EXPLAIN output shows the pruned partitions as 'never executed' or removes them from the plan entirely. Partition pruning requires three conditions to be met: the query must include a predicate on the partition key column (a column listed in the PARTITION BY clause), the predicate must be a constant or bindable parameter evaluated before query execution (a predicate like WHERE created_at > NOW() - INTERVAL '90 days' may not prune at plan time but will prune at execution time through runtime pruning), and the constraint must be statically evaluable against the partition range or value list. Several common patterns prevent pruning from working. A query filtered on created_at::date (casting the timestamp to a date) may not prune a RANGE partition on created_at (the timestamp type) because the cast changes the data type and the planner may not propagate the constraint through the cast. A query that joins to another table and uses the join result as the partition key predicate (WHERE created_at > (SELECT max_date FROM config)) may not prune at plan time because the subquery is not evaluated during planning. A query with OR predicates on the partition key (WHERE created_at < '2024-01-01' OR status = 'refunded') does not prune based on the status predicate, and the OR structure may prevent pruning based on created_at as well if the planner cannot determine that the OR branches can be individually pruned. The most common practical failure of partition pruning is a query that filters on a non-partition-key column — a query on a date-partitioned table filtered only on org_id must scan all date partitions, because the partition key is time and the predicate does not constrain time.

When should you use RANGE, LIST, or HASH partitioning?

The choice of partition type should be driven by the primary query access pattern and the data lifecycle requirement. RANGE partitioning divides data into contiguous ranges of the partition key value — most commonly by time range (monthly, quarterly, annually) or by numeric ID range. RANGE partitioning is the correct choice when: (1) the dominant query access pattern filters on the partition key as a range predicate (WHERE created_at BETWEEN X AND Y), enabling partition pruning to eliminate historical partitions; (2) data has a natural expiration policy based on the partition key value (rows older than 90 days can be archived by detaching and dropping the oldest monthly partition); (3) write volume is concentrated in the most recent partition (new rows have the current timestamp), which means the hot partition receives most writes while historical partitions are cold and benefit from different VACUUM tuning. LIST partitioning assigns rows to partitions based on exact values of the partition key — for example, partitioning a global orders table by region ('us-east', 'eu-west', 'ap-south') or by product category. LIST partitioning is correct when: (1) the partition key is a low-cardinality categorical value, (2) queries that filter on the exact partition key value benefit from single-partition execution, (3) operational isolation between partition values is desired (a regulatory requirement that EU data be stored on EU-region infrastructure can be implemented as a LIST partition for EU customers, which can then be tablespaced to EU-region storage). HASH partitioning distributes rows evenly across a fixed number of partitions based on the hash of the partition key. HASH partitioning is correct when: (1) the primary goal is distributing write load evenly across partitions to enable parallel write operations, (2) the primary query pattern requires full-table scans that benefit from parallel partition execution, (3) there is no natural range or categorical grouping that would provide pruning benefits. HASH partitioning provides no pruning for range queries and no lifecycle management benefit (you cannot drop 'old' hash partitions because the hash function distributes data across all partitions). Use HASH partitioning only when even distribution for parallelism is the primary objective and the partition count is expected to remain fixed (adding hash partitions requires redistributing all existing data).

What should a database partitioning decision record include?

A database partitioning ADR needs five sections. First, partition type and key selection rationale: the partition type chosen (RANGE, LIST, HASH), the partition key column or expression, the rationale for why this key and type serves the primary query access pattern better than the alternatives, and the query shapes for which partition pruning will be effective vs. ineffective. 'Partitioning by created_at for performance' is not a rationale. 'RANGE partitioning on created_at monthly because 95% of queries filter on a time range of 1-90 days, enabling pruning to 1-3 partitions from a 36-partition table; queries on org_id without a time predicate will not prune and will scan all partitions — these queries currently account for 3% of query volume and run on a read replica; the time-range query pattern was chosen as the primary because it accounts for 95% of query volume and the read latency improvement from pruning 33 of 36 partitions outweighs the merge overhead added to the 3% non-pruning queries.' That is a rationale. Second, data lifecycle management model: the partition granularity (monthly, quarterly), the retention policy (rows older than N months are archived to cold storage via partition detach, partitions older than M months are dropped), the partition creation schedule (the next 3 monthly partitions must exist before data arrives, created by a cron job that runs on the 15th of each month), and the default partition policy (whether a default partition exists to catch out-of-range inserts or whether out-of-range inserts are rejected as errors). Third, cross-partition query inventory: the enumeration of queries that do not constrain the partition key and must execute across all partitions, with their frequency, expected execution time across all partitions, and whether they are served from a dedicated read replica or from the primary; this inventory must be evaluated against the current and projected partition count to determine whether cross-partition query overhead will become unacceptable as the partition count grows. Fourth, statistics and maintenance model: the ANALYZE schedule for each partition (per-partition ANALYZE is required because the partitioned parent has no statistics of its own), the AUTOVACUUM configuration for each partition, and the index propagation policy (indexes created on the parent table propagate to new partitions; indexes must be created on existing partitions explicitly or via REINDEX on each partition). Fifth, partition count ceiling and evolution policy: the maximum partition count at which the chosen partition granularity is still operationally manageable, the trigger for changing partition granularity (for example, moving from monthly to quarterly partitions when the archive-and-drop cadence can be stretched), and the procedure for repartitioning if the partition strategy needs to change (repartitioning requires creating a new partitioned table, bulk-inserting data partition-by-partition, and swapping the table reference).

2026-07-24 · ~22 min read · Decision records

The database partitioning decision record: why the partition strategy you chose determines your query pruning ceiling and your data lifecycle management surface

Database partitioning decisions are made during three sessions that never document the strategy — the schema design session that chooses a partition key based on the most obvious current access pattern without enumerating future query shapes, the data lifecycle session that adds partitioning to solve DELETE performance without evaluating the cross-partition aggregate queries running monthly, and the storage scaling session that adds hash partitions for parallel scan without recording the statistics maintenance overhead or the partition count ceiling. What none of these sessions produce is the query pruning rationale (which queries benefit from pruning and which do not, and how the partition key choice determines that), the access pattern inventory for cross-partition queries (the queries that must scan all partitions and their performance ceiling as partition count grows), or the partition maintenance model (creation schedule, statistics, index propagation, and the lifecycle of individual partitions) — the gaps that determine whether your partition strategy delivers measurable query pruning or just divides your data into isolated silos that are harder to maintain than the original table.

The 36-partition table where pruning never fired

A SaaS company's events table grows to 200 million rows over two years. Every user interaction — page views, clicks, feature usage, API calls — is recorded in a single table with columns for org_id (the tenant identifier), user_id, event_type, created_at, and a metadata JSON blob. The table is partitioned by month on created_at when it reaches 50 million rows, based on a conversation with Claude: "Our events table is getting large, should we partition it?" Claude suggests RANGE partitioning on created_at with monthly partitions as a common pattern for time-series data. The team implements it. The immediate benefit is clear: old monthly partitions can be detached and archived to cold storage without running a DELETE across hundreds of millions of rows.

Three years later, the table has 36 monthly partitions and is performing worse than expected. The primary query pattern — the dashboard query that the product's front page loads for every user — is: SELECT event_type, COUNT(*) FROM events WHERE org_id = ? AND created_at > NOW() - INTERVAL '90 days' GROUP BY event_type ORDER BY COUNT(*) DESC. This query filters on both org_id and created_at, but the partition key is created_at. The Postgres query planner evaluates the created_at predicate and correctly prunes to the 3 most recent monthly partitions (the 90-day window spans parts of 3 months). Then, within those 3 partitions, it filters on org_id as a re-check on each row it reads. Three partitions instead of 36 — a 12x reduction in the data scanned. Pruning is working exactly as designed for this query.

The problem is a second, less-examined query pattern. The billing system queries events monthly for usage-based billing calculations: SELECT COUNT(*) FROM events WHERE org_id = ? AND event_type = 'api_call' — without a created_at predicate, because the billing system pulls the count for the current billing period, which is already filtered at the application layer through a JOIN to the billing_periods table rather than a WHERE clause on created_at. The application-side JOIN means the SQL that reaches the database has no time predicate. The query planner sees a filter on org_id and event_type, neither of which is the partition key. It must scan all 36 partitions, applying the org_id and event_type filters in each. EXPLAIN ANALYZE shows 36 "Seq Scan on events_YYYY_MM" nodes, 35 of which return zero rows for any given billing query because the billing period filter is applied in application code after the database returns results. The database is doing 36 sequential scans to return approximately 3 months of data.

The billing query runs 40,000 times per day across all tenants at the start of each billing cycle. The per-query overhead — 36 partition scans rather than 3 — is small for any individual query, but across 40,000 executions it represents a sustained I/O load that competes with the primary dashboard query workload during the monthly billing cycle. The engineering team that partitioned the table documented the partition key (created_at) and the lifecycle benefit (monthly archive). They did not document which queries would not benefit from pruning or what the cross-partition query load would be as partition count grew. A query pattern that accounted for 5% of query volume at table creation was not in anyone's mind when the partition strategy was chosen. At 36 partitions, that 5% has become a billing-cycle throughput problem.

The fix — rewriting the billing query to include a created_at BETWEEN predicate derived from the billing period dates — allows the planner to prune correctly. But the rewrite requires changing the billing system's query generation logic, which had been generating the no-date-predicate query for three years across multiple services. The decision that was never written down was the complete access pattern inventory at the time of partitioning: which queries would prune, which would not, and what the operational consequence of the non-pruning queries would be as partition count accumulated over time.

The regulatory report that got slower after partitioning

A fintech company's transactions table reaches 500GB. The data retention policy requires keeping 7 years of transactions for regulatory compliance, but transactions older than 90 days are accessed infrequently — only by the compliance team running quarterly and annual reports. The primary workload (real-time transaction processing and daily reconciliation) reads only the last 30 days. The operational pain point is the monthly data purge: DELETE FROM transactions WHERE transaction_date < NOW() - INTERVAL '2555 days' (7 years) takes 4 hours on a 500GB table because DELETE is a row-by-row operation that writes to the WAL, creates dead tuples, and requires VACUUM to reclaim space. The monthly purge is affecting production write throughput during its 4-hour window.

The team partitions the transactions table by month on transaction_date. The DELETE problem is immediately solved: dropping the oldest monthly partition when it ages past 7 years is a single metadata operation that takes under a second, regardless of how many rows the partition contains. The real-time transaction processing workload benefits from partition pruning — queries filtered on the last 30 days prune to 1-2 monthly partitions from the current 84 (7 years × 12 months). The architecture is a clear improvement for the two use cases it was designed for.

Six months after partitioning, the financial regulator issues a new reporting requirement: a quarterly merchant category analysis — total transaction volume and count grouped by merchant_category_code (an 18-value enum) across all transaction dates in the reporting period — covering the full 7-year data retention window. The compliance team runs this query. It executes against all 84 partitions with no date predicate (the reporting period is all 7 years), aggregates merchant_category_code, and produces a 2-page summary report. The query took 6 minutes on the unpartitioned 500GB table. After partitioning, it takes 22 minutes.

The reason for the slowdown is the cross-partition execution overhead. The unpartitioned 500GB table was a single sequential scan — one B-tree traversal of the table, one pass through the aggregation operator, one result. The 84-partition table requires 84 sequential scans (one per partition), 84 sort operations to prepare for the aggregation within each partition, and a merge step to combine 84 partial aggregation results. The total data read is the same — 500GB — but the orchestration overhead of 84 separate scan-sort-aggregate operations is greater than the single-operation overhead on the unpartitioned table. Parallel query execution could help — running multiple partition scans simultaneously — but the database's parallel worker budget is shared across all concurrent queries, and on the primary (which handles real-time transaction writes), the parallel worker allocation for the compliance report competes with the transaction processing workload.

The compliance team runs this query quarterly from the primary because it needs current data. Moving it to a read replica would address the resource competition with write workload but would not change the 22-minute execution time — the partition count and merge overhead are the same on any replica. Caching the result would address the repeat-execution cost but the report is generated quarterly on different date ranges, making cache reuse minimal. The correct architectural solution — creating a pre-aggregated materialized view on merchant_category_code rolled up daily — would have been the right approach for the regulatory query from the beginning, and would have been obvious at partitioning time if the cross-partition query inventory had included the compliance team's reporting patterns.

The engineering leader inheriting this system finds a partitioned table that performs well for real-time transaction queries, solved the DELETE problem, but performs worse than the original table for the compliance team's most important quarterly report. The partitioning decision was correct for the problem it was designed to solve. The failure was not documenting the problem it would create for cross-partition aggregate queries, which were running on the original table and were not inventoried when the partition strategy was chosen.

Three structural properties the database partitioning decision determines

1. The partition type and the query pruning model

Postgres declarative partitioning supports three partition types, each with different pruning semantics. RANGE partitioning assigns rows to partitions based on a contiguous range of the partition key value. The partition key is typically a timestamp (transaction_date, created_at) or an integer sequence. The planner prunes RANGE partitions when the query predicate constrains the partition key to a range that intersects only a subset of partition bounds. A query with WHERE created_at BETWEEN '2025-01-01' AND '2025-03-31' on a table with monthly partitions prunes to January, February, and March 2025 — the planner evaluates each partition's range bounds (January: ['2025-01-01', '2025-02-01'), February: ['2025-02-01', '2025-03-01'), etc.) against the predicate and eliminates all partitions with non-overlapping bounds. The EXPLAIN output for a well-pruned query shows far fewer "Seq Scan" or "Index Scan" nodes than the total partition count.

LIST partitioning assigns rows to specific named partitions based on exact values of the partition key. A partition key of region with values 'us-east', 'eu-west', 'ap-south' creates three named partitions, each holding rows from the corresponding region. The planner prunes LIST partitions when the query predicate specifies exact values for the partition key — WHERE region = 'eu-west' executes only on the eu-west partition. LIST partitioning is appropriate for low-cardinality categorical values where each value group has distinct operational characteristics: a regulatory requirement that EU data reside on EU-region storage, a business requirement that certain tenants be isolated for SLA reasons, or a sharding precursor where each list partition can eventually be migrated to a dedicated instance. LIST partitioning is not appropriate for high-cardinality values (partitioning by user_id with millions of distinct values produces millions of partitions, which is operationally unmanageable) or for values with no meaningful grouping semantics.

HASH partitioning distributes rows across a fixed number of partitions based on the hash of the partition key modulo the partition count. A HASH partition on user_id with 16 partitions places each row in partition hash(user_id) % 16. HASH partitioning enables the query planner to prune to exactly one partition for a query that specifies an exact equality predicate on the partition key: WHERE user_id = 12345 can be directed to the single partition that contains rows with hash(12345) % 16 as its modulus. For all other query patterns — range predicates on the partition key, predicates on non-partition-key columns, aggregate queries without a partition key predicate — HASH partitioning provides no pruning. The primary benefit of HASH partitioning is even data distribution across partitions for parallel scan workloads and the ability to VACUUM, ANALYZE, and reindex each partition independently, distributing maintenance I/O across the partition set. HASH partitioning is difficult to evolve: adding partitions requires redistributing all existing data (because the hash modulus changes), which is a large and disruptive operation.

A fourth option — composite partitioning — uses two levels of partition hierarchy: a primary RANGE or LIST partition on the first key, with each primary partition further sub-partitioned by HASH or LIST on a second key. Composite partitioning can enable pruning on the primary key (time range) and even distribution within each primary partition (by tenant or category hash). The operational complexity is substantially higher — N × M sub-partitions must each be created, maintained, and monitored. Composite partitioning is appropriate only when neither a single RANGE nor a single LIST partition scheme can serve the primary and secondary query patterns simultaneously. The database sharding decision is the architectural alternative to composite partitioning for extreme scale: rather than adding partition hierarchy depth within a single instance, sharding distributes partitions across instances, which allows partition-per-instance operational isolation and independent write throughput scaling.

2. The cross-partition query model and the aggregate performance ceiling

Every partition strategy creates a class of queries that cannot benefit from pruning: queries that filter on non-partition-key columns, aggregate queries with no partition key predicate, and queries whose partition key constraint is determined at execution time by a subquery or parameter not known at plan time. These cross-partition queries are the performance risk that accumulates as partition count grows. Understanding which queries fall into this class and at what partition count their performance becomes unacceptable is the most important analysis the partitioning decision record must capture.

Cross-partition query execution in Postgres uses an append node in the query plan: the executor executes each partition's subplan in sequence (or in parallel if parallel append is enabled), and the results are combined through a merge or aggregation node. The overhead of cross-partition execution has three components. First, per-partition plan overhead: each partition is treated as a separate relation, with its own statistics lookup, its own cost estimation, and its own execution context initialization. For a 100-partition table, this overhead is applied 100 times. Second, per-partition sort overhead: if the query requires ORDER BY, each partition's results may need to be sorted before merge — 100 sort operations rather than one, each on a subset of the data. For aggregation queries (GROUP BY), each partition executes the aggregation independently, and the 100 partial aggregation results are merged. Third, merge overhead: combining N partial results into a final result adds O(N log N) comparison operations for sort-merge. For aggregation, the merge is typically a hash join over the partial aggregation results.

The cross-partition performance ceiling depends on the query type. For simple COUNT(*) aggregations with no GROUP BY, the per-partition overhead is small and the merge is trivial — 100 partial counts summed. This query scales well with partition count. For GROUP BY aggregations where the group cardinality is high and each partition produces many partial aggregation groups, the merge step must combine N × K partial groups (N partitions, K groups each), which is expensive for large N and K. For ORDER BY LIMIT queries where the application needs the top-K rows across all partitions, each partition must execute and sort to return up to K rows, and the merge must find the global top-K from N × K candidates. An ORDER BY created_at DESC LIMIT 10 query on a 36-partition table requires each partition to return up to 10 rows (36 execution nodes), and the merge to find the 10 most recent rows from up to 360 candidates. The per-partition overhead dominates at small K.

The practical consequence: cross-partition queries that were fast on an unpartitioned table can become dramatically slower after partitioning if the partition count is high and the query pattern does not prune. The performance degradation is not visible in the initial deployment, when the partition count is small (2-3 partitions for a recently-partitioned table), and only emerges as the partition count grows over months or years. Documenting the cross-partition query inventory at partition creation time — and projecting the performance ceiling at the expected steady-state partition count (36 monthly partitions over 3 years, 84 monthly partitions over 7 years) — is the analysis that prevents the billing query and compliance report surprises described in the opening stories.

The read replica routing decision interacts with cross-partition queries: routing analytics and compliance queries to a read replica isolates their I/O from the write primary, but does not reduce the cross-partition execution overhead. A cross-partition query that takes 22 minutes on the primary takes 22 minutes on the replica as well — the partition count and merge overhead are identical. Replica routing is the correct solution for resource isolation (preventing the compliance report from competing with real-time writes), but it is not a substitute for the correct partition strategy for the cross-partition query workload. If cross-partition queries are expected to be a significant fraction of query volume, a pre-aggregated materialized view or a dedicated analytics table may be the correct architectural complement to the partitioned base table.

3. The partition maintenance model and the operational surface

A partitioned table requires ongoing maintenance operations that an unpartitioned table does not. The operational surface of a partition strategy is the sum of these recurring tasks: partition creation, statistics collection, index management, VACUUM scheduling, and the lifecycle operations (detach, archive, drop) for aging partitions. The operational surface grows with partition count, and the maintenance burden must be accounted for in the initial partitioning decision.

Partition creation for RANGE-partitioned time-series tables must happen before data arrives. If the partition for February 2026 does not exist when the first February row is inserted, Postgres routes the row to the default partition (if configured) or rejects the insert with an error (if no default partition exists). The default partition is a safety net — it catches rows that fall outside all defined partition ranges — but it accumulates rows from multiple time periods, defeating the lifecycle management purpose of partitioning. The partition creation schedule must run reliably: a cron job that creates the next 3 monthly partitions on the 15th of each month is a common pattern. This cron job is infrastructure that must be monitored, alerted on failure, and included in disaster recovery runbooks. A failure to create next month's partition is a time-sensitive incident: inserts begin failing or routing to the default partition on the first day of the uncreated month.

Statistics collection for partitioned tables requires per-partition ANALYZE. The partitioned parent table has no heap of its own — it is a routing structure — and does not have statistics that the query planner can use. The query planner uses statistics from the individual child partitions to estimate row counts, column correlations, and histogram buckets for partition-specific queries. When a new partition is created and populated with data for the first time, it has no statistics until ANALYZE runs on it. The query planner uses default statistics estimates (assuming uniform distribution, no correlations) for the new partition until ANALYZE completes. A new partition that receives a month's worth of data before its first ANALYZE may be planned with severely inaccurate cost estimates, producing poor query plans — sequential scans where index scans are appropriate, or vice versa. AUTOVACUUM triggers ANALYZE when the partition reaches a sufficient threshold of new rows, but the threshold is configurable and may not fire until the partition has accumulated more rows than optimal. The connection pool and AUTOVACUUM configuration must account for the partition-level statistics collection frequency, particularly for high-write partitions that receive millions of rows per day.

Index management on partitioned tables requires understanding the partition index inheritance model. In Postgres, an index created on the partitioned parent table (CREATE INDEX ON events (org_id, created_at)) automatically propagates to all existing partitions and to all future partitions created via CREATE TABLE ... PARTITION OF. This is the correct approach for indexes that should apply to all partitions uniformly. The index on each partition is a fully independent B-tree index, with its own page files and its own VACUUM and bloat behavior. If a partition accumulates high dead tuple counts (due to high update or delete rates on that partition), the partition-level index bloat must be managed independently — REINDEX CONCURRENTLY on the specific partition, not on the parent table. The REINDEX on the parent creates a new index on the parent and each partition simultaneously, which is an ACCESS EXCLUSIVE lock on all partitions during the build. Use REINDEX CONCURRENTLY on the specific partition to avoid the table-level lock.

The migration strategy for a table that needs to be partitioned after it has grown to production scale is one of the most operationally complex database operations. An existing large table cannot be converted to a partitioned table in place — partitioning is a structural property set at table creation. The migration requires creating a new partitioned table with the same schema, bulk-inserting data from the original table into the new partitioned table partition-by-partition, managing the dual-write period where writes go to both tables to prevent data loss, and swapping the table references. This process typically takes hours to days for a 500GB table and requires careful coordination with the application deployment. The complexity of the migration is a strong argument for making the partitioning decision when a table is first created rather than retrofitting it after the table has grown — the initial migration from a 100-row table to a partitioned table is a 10-second operation; the migration from a 500GB table is a multi-day project.

Five ADR sections the database partitioning decision record needs

1. Partition type and key selection rationale

Document the partition type chosen (RANGE, LIST, HASH, or composite), the partition key column or expression, the rationale for why this key and type serves the primary query access pattern, and the queries for which partition pruning will be effective versus ineffective.

The key selection rationale requires enumerating the primary query patterns and evaluating each against the proposed partition key. "RANGE partitioning on created_at for time-series data" is not a rationale. A rationale specifies: which queries benefit from pruning on this key, the expected pruning fraction at steady-state partition count (a 90-day query window on 36 monthly partitions prunes 33 of 36 — 92% pruning), which queries do not prune on this key and why (billing queries filtered only on org_id without a time predicate must scan all partitions), the access frequency of non-pruning queries and their performance expectation at steady-state partition count, and the alternatives considered (partitioning on org_id via LIST, which would prune the billing query but would require one partition per tenant — unmanageable at thousands of tenants — or hash partitioning on org_id, which would prune point lookups by tenant but not time-range queries that are 95% of volume). Document why the chosen key best serves the overall query distribution, not just the most obvious query.

The partition granularity for RANGE partitions — monthly vs. quarterly vs. annually — should be documented with the rationale based on the write volume per time period (monthly granularity makes sense when each partition receives a manageable volume of rows; weekly granularity makes sense for very high write volume tables where monthly partitions would still be too large for fast DROP operations), the retention policy (quarterly granularity aligns with a quarterly archive schedule), and the cross-partition overhead at steady-state count (36 monthly partitions at 3 years is manageable; 156 weekly partitions at 3 years produces a higher cross-partition overhead for any full-scan query). The granularity should reflect the unit at which lifecycle operations (archive, drop) naturally occur.

2. Cross-partition query inventory

Enumerate the queries that do not constrain the partition key and must execute across all partitions. For each cross-partition query: the query description, the columns filtered, why the partition key is not constrained (the filter is on a different column, the filter is applied at the application layer, the predicate requires a runtime evaluation), the current execution time on the unpartitioned table or at the initial partition count, and the projected execution time at steady-state partition count.

The projected execution at steady-state partition count is the most important analysis. A cross-partition query that takes 500ms at 3 partitions (early in the table's life) may take 6,000ms at 36 partitions (3 years later) — not because the data volume grew proportionally, but because the per-partition overhead accumulated. The projection requires understanding the per-partition overhead (not the data volume): if each partition adds 50ms of orchestration overhead independent of data volume, a query that prunes 0 of 36 partitions has 1,800ms of orchestration overhead alone, before any data is read. Running EXPLAIN ANALYZE with a simulated large partition count (by creating 36 empty partitions and observing the plan node count and estimated overhead) can provide early signal on whether the cross-partition query inventory is acceptable at steady state.

The cross-partition query inventory must be revisited when new features or reporting requirements are added to the system. The compliance reporting requirement in the fintech story was a new requirement added after partitioning; if the inventory had been maintained as a living document, the new requirement would have been evaluated against it and the architectural implications (pre-aggregated view, read replica routing, query rewrite) could have been addressed proactively. The partition strategy ADR is the document against which new query patterns should be validated.

3. Data lifecycle management model

Document the data retention policy, the partition granularity that implements it, the partition creation schedule, the archive procedure (detach partition, tablespace to cold storage, or export to object storage), the drop schedule, and the default partition policy.

The default partition policy is the safety mechanism for out-of-range inserts. For RANGE-partitioned tables with a partition creation cron job, a default partition prevents insert failures if the cron job misses a cycle — out-of-range inserts accumulate in the default partition rather than failing. The default partition must be monitored: if it accumulates rows, it indicates either a cron job failure (rows from a missing monthly partition) or an application bug (rows with future or past timestamps outside the retention window). A monitoring query — SELECT tableoid::regclass, COUNT(*) FROM events WHERE tableoid = 'events_default'::regclass GROUP BY tableoid — should alert if the default partition row count grows above a threshold. Without monitoring, the default partition can silently accumulate rows from a failed partition creation, and those rows are excluded from the time-range queries that filter on the monthly partitions, producing incorrect query results without any error or warning.

The archive procedure must be documented as a runbook, not just as a policy. "Archive partitions older than 90 days to cold storage" is a policy. A runbook specifies: the SQL commands to detach the partition (ALTER TABLE events DETACH PARTITION events_2024_01), the tablespace change that moves the detached partition to cold-storage-backed tablespace (ALTER TABLE events_2024_01 SET TABLESPACE cold_storage), the verification step that confirms the detached partition is no longer included in queries on the parent table (run a query with a created_at predicate in 2024-01 and verify EXPLAIN shows the detached partition is not in the plan), and the eventual DROP TABLE command when the partition ages past the legal retention window. The backup strategy must account for partitioned tables: a logical backup (pg_dump) includes all partitions and their data; a backup that captures the parent table schema but excludes detached partitions will miss detached partition data in the restore, which may violate the retention policy if detached partitions are considered part of the live dataset.

4. Statistics and index management

Document the AUTOVACUUM configuration for each partition tier (hot partitions with high write rate vs. cold partitions with no writes), the ANALYZE schedule for new partitions, the index propagation policy, and the index maintenance procedure for per-partition bloat.

Hot partitions — the current month or the most recent partitions that receive most writes — should have AUTOVACUUM tuned more aggressively than the Postgres defaults: autovacuum_vacuum_scale_factor lowered from 0.2 to 0.05 or below, and autovacuum_vacuum_cost_delay lowered to run VACUUM more frequently. Cold partitions — historical partitions that receive no writes — should have AUTOVACUUM disabled (autovacuum_enabled = false) once the partition is detached from the parent or once the last write to that partition is confirmed. Running AUTOVACUUM on a partition that has had no writes for 6 months is wasted I/O — the partition's dead tuple count is zero and has not changed since the last VACUUM ran. The cost of disabling autovacuum on cold partitions is that any future writes (a correction import, a backfill) will not trigger automatic vacuuming; the runbook for cold partition modifications must include a manual VACUUM after the modification.

Index propagation behavior must be documented: indexes created on the partitioned parent table propagate to new partitions going forward, but indexes on existing partitions must be created explicitly if they were not defined on the parent before the partitions were created. When a new index is added to the parent table via CREATE INDEX ON events (org_id) — without CONCURRENTLY — Postgres creates the index on the parent and all existing partitions using ACCESS EXCLUSIVE locks on each partition in sequence. The total lock duration is the sum of the lock durations across all partitions. For a table with 36 partitions, each taking 10 seconds to index, the total lock exposure is 360 seconds — but the locks are sequential, not simultaneous, so each partition is blocked for its own 10 seconds, not for 360 seconds. CREATE INDEX CONCURRENTLY on the parent creates indexes on all partitions concurrently without exclusive locks, using the concurrent mechanism for each partition.

5. Partition count ceiling and evolution policy

Document the maximum partition count at which the chosen granularity remains operationally manageable, the triggers for changing partition granularity, and the procedure for repartitioning when the strategy needs to change.

The partition count ceiling is the point at which the operational overhead of the partition maintenance model exceeds its value: the cron job creates partitions that are too numerous to monitor individually, the statistics collection I/O is material, the cross-partition query overhead is too high for the non-pruning query SLO, or the query plan generation time for a query against 200+ partitions is itself a latency component. Postgres 14 and later improved planner performance for large partition counts, but a table with 500 partitions generates a query plan with 500 nodes for any cross-partition query, and the planning time for that query can exceed 100ms. Document the partition count at which this becomes observable — typically in the range of 100-300 partitions — and the trigger for moving to a coarser granularity (monthly → quarterly, quarterly → annually) or for purging old partitions more aggressively to keep the live partition count within the manageable range.

Repartitioning — changing the partition key or partition type on an existing partitioned table — is the most disruptive evolution scenario. Postgres does not support in-place repartitioning: changing from RANGE on created_at to RANGE on (org_id, created_at) requires creating a new partitioned table, migrating data, and swapping table references. The migration procedure for a large table is a multi-day project and must be executed with careful coordination: a dual-write period where the application writes to both tables, a cutover window where the reference is swapped atomically, and a verification period where query results from the new table are compared against the old. Document the repartitioning procedure in the ADR and include the estimated migration duration at current data volume, so the team has a realistic expectation of the effort required if the partition strategy needs to change. The migration strategy ADR should cross-reference the partitioning ADR for this scenario — the repartitioning migration is the highest-complexity schema migration the system may face.

The performance optimization session that first identifies the need for partitioning is the optimal time to make this decision with full context: the current query patterns are known, the growth rate is measurable, and the team that owns the table is focused on its performance. Retrofitting partitioning 18 months later, when the table has grown to 500GB and the original team has turned over, requires reconstructing the original query patterns and growth projections from scratch — exactly the archaeology problem that decision records are designed to prevent.