Quick answer
Table partitioning divides one logical PostgreSQL table into physical child tables according to range, list, or hash bounds. It is most useful when the partition key aligns with query predicates and data lifecycle operations, so the planner or executor can prune unrelated partitions and operations can add, detach, archive, or drop bounded data sets. Partitioning is not automatically faster, and it is not sharding: partitions remain under one PostgreSQL database’s transaction, catalog, compute, storage, and failure boundaries unless another architecture explicitly changes them.
Choose a partition key from durable access and retention patterns. Time ranges work well when most queries bound time and old data is retired in units. Tenant partitioning can create skew or too many objects. Hash partitioning can spread rows but does not create cross-node capacity. Verify pruning in the actual application plan, including prepared statements, casts, functions, and runtime parameters.
Partitioning adds operational obligations: create future partitions, route inserts, monitor a default partition, analyze new data, manage local indexes and constraints, validate attach/detach locks, and test backup and restore. A query that touches every partition can be slower due to planning and per-partition work. Adopt partitioning only after measuring the current bottleneck and demonstrating safer lifecycle or bounded query work.
Shared order-history incident stage
The shared orders table is range-partitioned by created_at, but the order-history query applies a timezone/business-date function to that column before comparing it. The team expects one recent partition; the query plan includes many partitions because the predicate cannot provide the same direct bound. The cardinality estimate and missing workload-shaped index already made each scan expensive, so failed pruning multiplies the work.
Prepared execution adds nuance. PostgreSQL can perform pruning during planning or execution when parameters become known, but the exact statement and expression determine what is possible. A console plan using a literal may prune differently from the application’s prepared statement. Responders inspect the real query shape and both plan-time and execution-time evidence rather than declaring partitioning broken.
The backfill also placed rows into the default partition because a future monthly partition was missing. Attaching a new partition may require validating bounds against existing data, and an unconstrained default partition can be scanned under a strong lock during validation. A hurried repair can therefore add lock contention to the incident.
Operations rewrite the date filter as a semantically correct half-open UTC range, create and analyze the missing partition through a reviewed lifecycle, and verify pruning and result correctness. Index changes are built per partition with the concurrent limitations documented below. Recovery still requires user SLI, pool wait, lock wait, buffers, planning time, and write health.
Core mechanism and evidence boundary
Range partitioning maps non-overlapping intervals; list partitioning maps explicit values; hash partitioning maps a modulus and remainder. The parent is a partitioned relation that routes rows and represents the hierarchy. PostgreSQL 18 enforces partition bounds during routing. An insert with no matching partition fails unless a default partition accepts it. A default partition is an exception path to monitor, not a substitute for future-partition automation.
Partition pruning removes partitions whose bounds cannot satisfy a query condition. Planning-time pruning can reduce the initial plan. Execution-time pruning can remove subplans during initialization or when parameters change inside a parameterized plan. EXPLAIN ANALYZE may show subplans never executed or loops that reveal runtime behavior. Constraint exclusion is a related, older mechanism and should not be conflated with declarative partition pruning.
Predicates must be compatible with the partition bounds. A direct half-open range on the partition key is easier to reason about than wrapping the key in a function or applying an inconsistent cast. Rewriting requires correctness analysis: timezone, inclusive/exclusive endpoints, precision, and daylight-saving business rules. Never drop a business-date requirement merely to obtain a prettier plan.
Indexes on a partitioned parent are virtual definitions backed by indexes on partitions. Creating an index on the parent normally creates matching child indexes, including for future partitions. PostgreSQL 18 does not support CREATE INDEX CONCURRENTLY directly on the partitioned parent. A lower-blocking procedure creates the parent index with ONLY, builds each child index concurrently, and attaches them; the exact operation and validity must be checked.
Unique and primary-key constraints on a partitioned table have restrictions because uniqueness must be enforceable without searching arbitrary partitions. Partition-key columns must participate under PostgreSQL’s documented rules. Do not remove a partition key from a business identity or invent application-side uniqueness without an invariant design.
Partition-wise joins and aggregates are separate planner features with configuration and suitability boundaries. They can move work into matching partitions but may increase planning or memory use. Do not make them part of the partitioning business case unless the real workload and PostgreSQL 18 settings demonstrate benefit. Pruning unrelated data is a clearer first objective.
Subpartitioning is permitted, but PostgreSQL does not prove that every subpartition bound is a logical subset of its parent beyond the enforced routing rules described for the defined hierarchy. Each level multiplies objects, index work, future provisioning, and failure recovery. Use a second level only for a separately measured access or lifecycle requirement.
Lifecycle automation must be idempotent and observable. Creating the same future partition twice should not result in an unreviewed alternate object; failing halfway through child indexes must be visible; and a default-partition alert needs an owner. Provision enough future range to survive scheduler outage, but do not create an unlimited horizon of catalog objects.
Statistics need a hierarchy-aware schedule. Bulk loading a detached table and then attaching it can avoid foreground row-by-row work, but the loaded child still needs constraints, indexes, analysis, validation, and backup decisions. Parent and child evidence should identify whether a misestimate is global or isolated to one partition.
These behaviors, commands, lock modes, and constraint rules are PostgreSQL-specific, not a SQL standard or universal database interface. Planning time, pruned partition count, shared_blks_read, and per-partition rows are evidence, not a stable OpenTelemetry semantic convention. Partition names, tenant keys, query IDs, and raw predicates do not belong in metric labels.
Minimal reproducible PostgreSQL 18 example
Create a small range-partitioned table in a disposable PostgreSQL 18 database:
CREATE TABLE partitioned_orders (
id bigint NOT NULL,
tenant_bucket integer NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL,
total_cents integer NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE TABLE partitioned_orders_2026_08
PARTITION OF partitioned_orders
FOR VALUES FROM ('2026-08-01 00:00:00+00')
TO ('2026-09-01 00:00:00+00');
CREATE TABLE partitioned_orders_default
PARTITION OF partitioned_orders DEFAULT;
CREATE INDEX ON partitioned_orders (tenant_bucket, created_at DESC, id DESC);
ANALYZE partitioned_orders;Compare a direct range with a transformed column:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, created_at, total_cents
FROM partitioned_orders
WHERE tenant_bucket = 7
AND created_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
AND created_at < TIMESTAMPTZ '2026-08-08 00:00:00+00'
ORDER BY created_at DESC, id DESC
LIMIT 100;Use application-equivalent prepared execution in the lab:
PREPARE recent_orders(integer, timestamptz, timestamptz) AS
SELECT id, created_at, total_cents
FROM partitioned_orders
WHERE tenant_bucket = $1
AND created_at >= $2
AND created_at < $3
ORDER BY created_at DESC, id DESC
LIMIT 100;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
EXECUTE recent_orders(7,
TIMESTAMPTZ '2026-08-01 00:00:00+00',
TIMESTAMPTZ '2026-08-08 00:00:00+00');Inspect which child relations execute, loops, planning time, buffers, and result order. Then test missing, default, boundary, daylight-saving, wider-range, and no-match cases. Rehearse attach/detach with bounded lock_timeout and known constraints. Never operate on the production default partition without first inspecting ownership, bounds, row count, and lock impact.
Failure modes and dangerous misconceptions
Partitioning before measuring. It adds catalogs, plans, indexes, jobs, and failure modes. Demonstrate a query or lifecycle bottleneck first.
Calling partitioning sharding. Local partitions do not add independent nodes or failure domains. They share database resources.
Using a high-cardinality partition per tenant. Thousands of partitions can inflate planning, maintenance, and catalog work. Model skew and object count.
Assuming schema implies pruning. Verify the actual statement and plan. Functions, casts, broad ranges, and prepared execution change evidence.
Ignoring the default partition. It can silently accumulate rows when lifecycle automation fails and complicate later attach validation.
Creating all maintenance on the parent concurrently. PostgreSQL has specific limitations for partitioned-parent concurrent index creation. Rehearse the supported per-partition attach procedure.
Dropping partitions as a casual delete. Detach/drop changes data availability and retention. Require backup, legal, audit, replica, and rollback decisions.
Expecting every partition to share statistics and health. New or skewed partitions need analysis and monitoring; one hot child can dominate resources.
Using a function because it looks equivalent. A business-date function may preserve user semantics but conceal the direct partition bound. Derive correct UTC bounds outside the column expression when possible, and test edge cases instead of deleting semantics.
Provisioning a partition inside the request path. DDL needs locks, catalog work, ownership, retries, and monitoring. Create future partitions through controlled operations rather than making an unexpected timestamp trigger schema mutation.
Detaching without query compatibility. Old and new application versions, reports, foreign keys, and backups may still expect the data through the parent. Treat lifecycle as a versioned contract.
Security, privacy, capacity, and cost implications
Partition names and bounds can reveal dates, regions, tenants, personal data categories, or retention policy. Default-partition contents may expose routing mistakes. Restrict catalog access and lifecycle privileges, avoid embedding customer identifiers in object names, and audit attach, detach, archive, and drop operations.
Do not put partition name, tenant, order, query ID, request ID, trace ID, or raw bound in metric labels. Use bounded table class, operation, environment, database role, and lifecycle state. Detailed child identity can remain in restricted operational records.
Capacity includes per-partition indexes, catalog rows, planning memory/time, maintenance jobs, backups, WAL, replica replay, and future-partition headroom. More partitions can enable cheap data retirement but make cross-range queries and schema changes more expensive.
Retention is a correctness and governance contract. Detaching or dropping a partition must respect backup, restore, legal hold, analytics, and audit requirements. Performance convenience does not authorize deletion.
Testing and production validation
Test exact lower and upper bounds, no matching partition, default routing, late data, future data, timezone conversion, daylight-saving transitions, and duplicate identity constraints. Verify old and new application versions against the same hierarchy.
Compare direct, transformed, prepared, wide-range, and no-match predicates. Capture planning time, executed child relations, loops, rows, buffers, duration, and returned order. Test concurrency and data growth; a one-partition demo does not predict hundreds of children.
Rehearse partition creation, constraint preparation, default-partition validation, attach, concurrent child-index creation, parent-index attachment, detach, archive, rollback, and failure cleanup. Apply bounded lock and statement timeouts and monitor replicas, WAL, storage, writes, and invalid indexes.
After rollout, verify pruning in the deployed application plan, not a hand-written approximation. Confirm user SLI, pool and lock wait, planning/execution tails, maintenance automation, default-partition emptiness policy, backups, and write correctness.
Run scale tests with the expected partition count several years into the retention horizon. Measure parse and planning time, plan memory, execution initialization, and operational commands. A design that performs well with three monthly children may produce unacceptable planning or migration overhead with hundreds.
Test failover and restore. Confirm partition definitions, attached state, indexes, constraints, and default routing survive the supported backup/restore procedure. Verify lifecycle automation does not race recovery or create overlapping bounds after a delayed run.
For removal, first detach under the reviewed lock mode when that supports the recovery plan, validate that live queries no longer require the data, archive it with checksums and access controls, and only then consider dropping. Measure replica and backup consequences at each step. Performance testing never overrides retention policy.
Operations checklist
- Prove the query or lifecycle problem that partitioning addresses.
- Choose a key from durable predicates and retention operations; model skew and object count.
- Define range boundaries, timezone semantics, default behavior, and future-partition automation.
- Verify planning-time and execution-time pruning for the real prepared statement.
- Test boundary, no-match, default, late, future, and broad-range data.
- Create and analyze new partitions before traffic depends on them.
- Rehearse constraint validation, attach/detach locks, index attachment, and failure cleanup.
- Respect unique/primary constraint restrictions and business invariants.
- Monitor planning time, child count, buffers, writes, WAL, replicas, storage, and maintenance.
- Keep sensitive identifiers and bounds out of metric labels and public artifacts.
- Protect archive, backup, restore, legal hold, and audit requirements.
- Close only after user and lifecycle evidence remain healthy.
Official sources
- PostgreSQL 18: Table Partitioning — methods, pruning, indexes, attach/detach, and best practices; accessed 2026-08-04.
- PostgreSQL 18: CREATE TABLE — partition bounds and constraint restrictions; accessed 2026-08-04.
- PostgreSQL 18: ALTER TABLE — partition lifecycle and locking behavior; accessed 2026-08-04.
- PostgreSQL 18: CREATE INDEX — partitioned-table and concurrent-build limits; accessed 2026-08-04.
- PostgreSQL 18: Using EXPLAIN — pruned subplans and execution evidence; accessed 2026-08-04.
Continue the learning path
Evaluate row work with PostgreSQL EXPLAIN and Query Plans, then stage hierarchy changes through Zero-Downtime Database Schema Migrations. Database Sharding explains the separate cross-node problem, while Database Query Performance for Backend Systems keeps pruning tied to user impact. Continue through System Design or Topics.