Queries that filter only on user_id must scan all 500GB of data across all monthly partitions — partition pruning cannot help because the time filter is absent. The query does a full table scan across all partitions. The fix is either to add a time range constraint to the query when possible, or to create a secondary index on user_id that propagates to all partitions. For very high-volume user_id queries, consider hash partitioning by user_id instead of range partitioning by time, or use a composite partition key that includes both user_id and a time component.

Dropping a table acquires an ACCESS EXCLUSIVE lock that blocks all concurrent operations on the table. Even though the partition is separate from the parent table, the metadata operation still generates WAL and can block the replication slot from advancing. If you are using replication slots, the dropped partition's WAL cannot be removed until the replica acknowledges it. The fix is to use ALTER TABLE DETACH PARTITION instead of DROP TABLE — detaching is less aggressive on the replication slot, and you can drop the detached table during a low-traffic window.

Static partition pruning happens at query planning time — the planner determines which partitions match before execution based on constants in the query. If your query has WHERE created_at = '2024-06-15', the planner knows exactly which partition to scan and excludes all others before executing. Dynamic partition pruning evaluates at execution time — used when the partition key depends on a subquery, parameter, or function result that is not known at planning time. Dynamic pruning has overhead because the executor must determine which partitions to scan during execution. Queries using dynamic pruning are typically slower than those using static pruning.

Hash partitioning when you cannot predict data distribution and need even row counts across partitions. If your data has natural ordering that aligns with your query patterns (like time-series data), range partitioning is better because it groups related data together. Hash partitioning is also better when you have high-concurrency inserts on the same partition key — hash distribution spreads inserts across partitions and reduces lock contention. The tradeoff is that range queries without the hash key become full-table scans across all hash partitions.

PostgreSQL supports multi-column partition keys for both range and list partitioning, specified as a tuple in parentheses: PARTITION BY RANGE (created_at, user_id). The partition bounds are compared tuple-wise — meaning (2024-01-01, 100) to (2024-01-01, 200) covers dates of 2024-01-01 with user_ids between 100 and 200. The limitation is that the planner can only prune partitions when the query includes all leading columns of the partition key. If you partition by (created_at, user_id) and query only on user_id, no pruning occurs because the planner needs the leading column (created_at) to determine which partitions to skip. Always put the most frequently queried column first in a composite partition key.

The insert fails with a no partition of relation error. PostgreSQL will not insert the row into a default partition or fall back to the parent table — the row must match an explicit partition bound or an attached default partition. To handle out-of-range data gracefully, attach a DEFAULT PARTITION using FOR VALUES DEFAULT. Without a default partition, applications must handle the insertion error and route data appropriately. For time-based partitions, a default partition acts as a catchall for dates beyond your planned ranges, but you should monitor it closely — a growing default partition indicates your partition bounds are stale.

The safest approach uses a two-phase migration. First, create the partitioned table structure alongside the original table with no data, then use a batched INSERT INTO ... SELECT FROM to copy data in manageable chunks (e.g., 100,000 rows per transaction) to avoid locking and replication lag. Second, use ALTER TABLE ... ATTACH PARTITION to attach partitions as they fill. For very large tables, use the pg_partman extension which automates incremental data migration and partition management. The key is to migrate data while the original table continues accepting writes, then do a final cutover where you rename the old table, rename the partitioned table to the original name, and replay any writes that arrived during migration using a logical replication slot or change-data-capture pipeline.

PARTITION BY RANGE defines partitions as non-overlapping value ranges — FROM ('2024-01-01') TO ('2024-04-01') covers everything between those two boundaries. Ranges must be contiguous and non-overlapping, which makes them ideal for time-series data where partitions represent periods. PARTITION BY LIST defines partitions as specific discrete values — FOR VALUES IN ('warehouse_a', 'warehouse_b') covers only those exact values. List partitioning is better when data naturally groups into categories (regions, departments, account tiers) where you want queries for one category to hit only that partition. List partitions can be non-contiguous unlike range partitions. The choice depends on your access patterns: time-based queries favor range partitioning; categorical or geographic queries favor list partitioning.

Each partition runs its own autovacuum workers independently, meaning a partitioned table with 36 monthly partitions can have 36 concurrent vacuum workers. This is generally beneficial — active partitions get frequent vacuum while historical partitions rarely need it. However, index maintenance must be considered: indexes on the parent table propagate to all partitions, but building a large index on the parent blocks writes across all partitions. Use CREATE INDEX CONCURRENTLY on the parent to build indexes without blocking, but be aware this takes longer and cannot be run inside a transaction. For very large partitions, consider building indexes on individual partitions rather than the parent — this lets you stagger index builds and avoid a single massive operation. Partition-specific indexes also allow optimization based on each partition's data characteristics.

Yes, PostgreSQL allows partitioning by an expression: PARTITION BY RANGE (EXTRACT(YEAR FROM created_at)) or PARTITION BY RANGE (LOWER(category)). However, partition pruning only works when the query's filter matches the expression exactly. If you partition by EXTRACT(YEAR FROM created_at), a query with WHERE created_at >= '2024-01-01' will NOT prune — PostgreSQL cannot derive the year expression from a raw timestamp comparison. The query must use WHERE EXTRACT(YEAR FROM created_at) = 2024 to enable pruning. This creates an impedance mismatch: application queries naturally use range comparisons on timestamps, not year extractions. To avoid forcing application changes, partition by the column directly and let the planner handle range pruning naturally. Expressions in partition keys are most useful for list partitioning where you want to partition by a transformed value.

Subpartitioning creates a hierarchy where the planner must evaluate both levels to determine which partitions to scan. For a query to prune at both levels, it must include filters for both the top-level and second-level partition keys. If you have RANGE by year then RANGE by month within each year, a query filtering only on month without year cannot prune the top-level year partitions — the planner must scan all year partitions and evaluate month subpartitions within each. The planner overhead increases with more levels because it must evaluate more bounds. PostgreSQL supports exactly two levels of partitioning (parent + one level of subpartitions), which is a deliberate design choice to keep planning time reasonable. Best practice is to limit subpartitioning to two levels with clear purposes for each: year for archival boundaries, month for query granularity.

Partition maintenance operations acquire locks of varying severity: ALTER TABLE ATTACH/DETACH takes a SHARE UPDATE EXCLUSIVE lock which allows reads and updates to continue but blocks other maintenance operations; DROP TABLE takes an ACCESS EXCLUSIVE lock which blocks all queries until the drop completes. For production safety, detach partitions before dropping — ALTER TABLE orders DETACH PARTITION orders_2023_q1 is lightweight, then DROP TABLE orders_2023_q1 can run during low-traffic windows. For index creation on partitioned tables, always use CREATE INDEX CONCURRENTLY which takes a SHARE UPDATE EXCLUSIVE lock instead of blocking — but it cannot run inside a transaction, so plan accordingly. Partition splits are the most dangerous operations because they require acquiring locks on both the parent and new partition while redistributing rows.

Indexes on partitioned tables inherit the cardinality of their underlying data. If one partition contains 80% of rows (hot partition), indexes on that partition are larger and more expensive to scan than indexes on smaller partitions. When you create an index on the parent table (CREATE INDEX ON orders(user_id)), PostgreSQL creates indexes on each partition automatically — but each partition's index reflects that partition's data distribution. Partition-specific indexes let you optimize for each partition's characteristics: the hot partition might need a covering index with include columns, while cold partitions might need no index at all. The tradeoff is management overhead — you must maintain partition-specific indexes manually, while parent-propagated indexes are managed automatically. For time-range partitioned tables, keep indexes on the parent for queries that span partitions, and add partition-specific indexes only for the most active partitions where you can add optimization tricks like partial indexes or different index types.

Partition-wise joins occur when a query joins two partitioned tables on the partition key, allowing PostgreSQL to join only corresponding partitions instead of all partitions. If you have orders partitioned by customer_id and payments also partitioned by customer_id, a join on customer_id can be executed as independent joins between matching partition pairs. This reduces the volume of data moved through the join algorithm. Partition-wise aggregation works similarly — when you aggregate data grouped by the partition key, PostgreSQL can aggregate within each partition in parallel and then combine results. The benefit is proportional to the number of partitions and the data volume within each partition. For small partition counts (under 8), the overhead of coordinating partition-wise operations often exceeds the benefit. For larger partition counts with substantial data in each partition, partition-wise operations can reduce memory usage and execution time significantly.

PostgreSQL's partition estimation uses the number of partitions and partition bounds to estimate row counts, but it does not have sophisticated partition-level statistics like Oracle's partition-wise statistics. In Oracle, each partition can have its own statistics, allowing precise cardinality estimates for queries that target specific partitions. PostgreSQL relies on the parent table's statistics combined with partition bound information to estimate partition cardinalities — this estimation degrades when partitions have significantly different data distributions (skewed partitions). PostgreSQL 14+ improved partition estimation for certain query patterns, but Oracle still has an advantage in multi-partition queries where partition-level statistics allow more accurate join ordering and join method selection. The implication is that PostgreSQL partitioned queries may choose suboptimal join orders more often than Oracle when partitions have unequal sizes, and you should verify EXPLAIN output for large partitioned tables to ensure join decisions are reasonable.

PostgreSQL 11 added partition-wise joins and aggregation which dramatically improve performance for partitioned table queries. PostgreSQL 13 added ALTER TABLE ... ATTACH PARTITION CONCURRENTLY which prevents locking during partition attachment. PostgreSQL 14 improved partition statistics collection which helps the planner make better decisions for skewed partitions. PostgreSQL 15 added asynchronous partition creation. The main upgrade caveat is that some partition-related query plans may change — a query that ran acceptably slow on PG10 may now run faster but with different memory usage patterns. Test partition-heavy workloads before upgrading. Also verify that any partitioning extensions like pg_partman are compatible with the target version.

pg_partman automates partition creation, retention, and data movement through background workers. It pre-creates partitions ahead of time based on a configured interval (daily, monthly, etc.) and can automatically drop partitions older than a retention period. This eliminates the risk of missing a partition creation window that would cause inserts to fail. The operational risks: pg_partman runs as a background worker that consumes resources — configure its schedule carefully to avoid contention with peak traffic. It also creates a part_config table in the target schema that must be backed up. If pg_partman is misconfigured (wrong interval, wrong retention), it can create partitions too aggressively or drop partitions with live data. Always test the configuration on a non-production system first.

PostgreSQL caches the execution plan for prepared statements. When the prepared statement was first created, PostgreSQL compiled a plan based on the partitions that existed at that time. The cached plan does not automatically adapt when partitions are added or removed. The solution is to use EXECUTE with a parameterized query instead of PREPARE — each execution generates a fresh plan that accounts for the current partition configuration. Alternatively, call DEALLOCATE and re-prepare periodically, or use SET plan_cache_mode = force_custom_plan to always use custom plans for prepared statements. For dynamic partition scenarios, custom plans are almost always better because partition changes invalidate cached generic plans.

If pruning shows only one partition is scanned but the query is still slow, the bottleneck is likely the index on that partition. Even a single-partition scan can be slow if the partition's index is large or fragmented. Check pg_relation_size('partition_index') to see index size. Check pg_stat_user_indexes for high index scan counts versus actual row fetches — a low ratio indicates index inefficiency. Also check for missing indexes: a query with WHERE user_id = X AND status = 'active' may be scanning the partition index on user_id but still filtering on status row-by-row. Run ANALYZE partition_name to ensure statistics are up to date. Check pg_statio_user_indexes for index buffer cache hit rates — if the index does not fit in memory, disk I/O becomes the bottleneck.

A default partition acts as a catchall for rows that do not match any explicit partition bound. The risk is that the default partition can accumulate significant data, and queries that should prune to specific partitions still touch the default partition because PostgreSQL must check it for non-matching rows. Queries that join or aggregate across the partitioned table also include the default partition. Monitoring: set an alert on the default partition size — any growth above a small threshold (e.g., 1GB) indicates partition bounds are stale and need updating. Query pg_stat_user_tables for the default partition to track row count growth over time. Ideally, a healthy partitioned table has a zero-row default partition because all data falls within explicit bounds. If the default partition is growing, that is a signal to extend your partition range before the next maintenance window.