Batch inserts fail with ORA-14400 when LIST partition lacks DEFAULT for unexpected values.
Range Partitioning — The Production Gold Standard
Range partitioning is the go-to strategy for time-series data. It maps rows to partitions based on a continuous range of values. This is incredibly powerful for data lifecycle management; instead of running expensive DELETE queries that bloat the transaction log and fragment indexes, you simply drop the oldest partition.
By using the PARTITION BY RANGE clause, the database engine gains the intelligence to perform 'Partition Pruning'—ignoring every file on disk that doesn't contain the requested date range.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- io.thecodeforge.db.schema
-- PostgreSQL range partitioning by year
CREATE TABLE orders (
id BIGINT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total DECIMAL(12,2)
) PARTITION BY RANGE (order_date);
-- Creating the child tables (partitions)
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- Add indexes on children automatically via the parent
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Partition pruning in action
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date >= '2025-06-01' AND order_date <= '2025-06-30';Output
Seq Scan on orders_2025 (cost=0.00..15.50 rows=5 width=48)
Filter: ((order_date >= '2025-06-01'::date) AND (order_date <= '2025-06-30'::date))
Production Insight
Time-series data often sees a 'hot partition' — the most recent range receives all writes.
Range partitioning makes this visible: the latest partition can become a bottleneck under high write load.
Rule: for high-volume ingest, consider sub-partitioning the hot range by hash to spread writes.
Always schedule partition maintenance (detach/drop) during low traffic to avoid lock contention.
Key Takeaway
Range partitioning is ideal for time-series.
Dropping a partition is instant; deleting rows is not.
Plan for hot partitions: sub-partition or use smaller range intervals.
Hash Partitioning — Solving Hotspot Issues
When your data doesn't have a natural 'range' (like a timestamp) or when all your writes hit the 'current' range creating a bottleneck, Hash Partitioning is the solution. It uses a hash function on the partition key to distribute rows evenly across a fixed number of partitions.
This ensures that even if 10,000 users are signing up at the same second, their data is spread across multiple physical files, reducing I/O contention.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- io.thecodeforge.db.schema
-- Hash partitioning for even load distribution
CREATE TABLE user_sessions (
session_id UUID PRIMARY KEY,
user_id BIGINT NOT NULL,
data JSONB,
last_seen TIMESTAMP
) PARTITION BY HASH (user_id);
-- Create 4 partitions using the modulus/remainder logic
CREATE TABLE user_sessions_p0 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_p1 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_p2 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_p3 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 3);Output
-- Rows are distributed: hash(user_id) % 4
Production Insight
Hash partitioning guarantees even distribution only if the hash function provides uniform output.
Changing the modulus later (e.g., from 4 to 8) requires dropping and recreating all partitions.
Queries with range conditions on the partition key (e.g., user_id between 1000 and 2000) scan ALL partitions.
Rule: use hash only when the primary access pattern is point lookups by the partition key.
Key Takeaway
Hash partitioning eliminates write hotspots.
Trade-off: range queries can't use pruning — full scan of all partitions.
Choose modulus based on expected data growth; changing it is an expensive migration.
List Partitioning — Categorical Data Isolation
List partitioning groups rows by discrete values such as country, status, or category. Each partition holds rows where the partition key matches a predefined list of values. It's perfect for multi-tenant systems where each tenant has a separate partition, or for data that naturally splits by region.
A critical consideration is the 'default' partition — a catch-all for values that don't match any defined list. Without it, inserts with unexpected values fail.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- io.thecodeforge.db.schema
-- List partitioning by region
CREATE TABLE customer_data (
customer_id BIGINT,
region TEXT NOT NULL,
signup_date DATE,
lifetime_value DECIMAL(12,2)
) PARTITION BY LIST (region);
-- Partitions for major regions
CREATE TABLE customer_na PARTITION OF customer_data
FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE customer_eu PARTITION OF customer_data
FOR VALUES IN ('UK', 'DE', 'FR', 'IT', 'ES');
CREATE TABLE customer_apac PARTITION OF customer_data
FOR VALUES IN ('JP', 'CN', 'IN', 'AU');
-- Required: default partition for safety
CREATE TABLE customer_default PARTITION OF customer_data DEFAULT;Output
Rows with region 'US' go to customer_na; region 'BR' goes to customer_default
Production Insight
Without a DEFAULT partition, adding a new region requires an ALTER TABLE to add a new partition — which can lock the table.
The DEFAULT partition can grow large if many unmapped values arrive; monitor its size.
For global apps with many regions, consider using hash or composite partitioning instead.
Rule: always include a DEFAULT partition and set up an alert when it receives rows.
Key Takeaway
List partitioning enforces data categorization.
Always add a DEFAULT partition to avoid insertion failures.
Monitor the default partition: its size indicates missing partition definitions.
Composite Partitioning — Combining Strategies
Composite (or sub) partitioning combines two partitioning methods, typically range + hash or range + list. The table is first partitioned by a range, and then each range partition is further divided into sub-partitions using hash or list. This is useful for massive tables where you need both pruning on a time dimension and distribution across storage for parallelism.
Example: partition by month, then sub-partition by hash on customer_id. Queries on a single month only scan one range partition, and writes are spread across sub-partitions within that month.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- io.thecodeforge.db.schema
-- Composite range-hash partitioning
CREATE TABLE audit_logs (
log_id BIGSERIAL,
event_time TIMESTAMPTZ NOT NULL,
user_id BIGINT NOT NULL,
action TEXT
) PARTITION BY RANGE (event_time);
-- Monthly partitions, each sub-partitioned by hash on user_id
CREATE TABLE audit_logs_2026_01 PARTITION OF audit_logs
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
PARTITION BY HASH (user_id);
-- Sub-partitions within the month
CREATE TABLE audit_logs_2026_01_p0 PARTITION OF audit_logs_2026_01
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE audit_logs_2026_01_p1 PARTITION OF audit_logs_2026_01
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ... p2, p3
Output
Query: WHERE event_time BETWEEN '2026-01-10' AND '2026-01-15' AND user_id = 12345
Prunes to audit_logs_2026_01_p1 (month + hash)
Production Insight
Composite partitioning increases query optimizer complexity — execution plans become harder to read.
Planned maintenance (e.g., dropping old months) must consider sub-partitions; dropping the top-level partition drops all sub-partitions.
Be careful with partition key order: place the most selective key first for maximum pruning.
Rule: only use composite partitioning when a single strategy cannot handle your data volume and access patterns.
Key Takeaway
Composite partitioning layers two strategies for flexibility.
Query optimizer overhead increases — test execution plans thoroughly.
Drop top-level partition to remove all sub-partition data instantly.
Partition Pruning — The Engine That Makes Partitioning Fast
Partition pruning is the query optimizer's ability to skip irrelevant partitions based on the WHERE clause. Without pruning, partitioning can actually degrade performance because the database must check metadata for every partition. Pruning occurs only when the partition key is used in a sargable predicate (e.g., equality, range, IN list).
Common pitfalls: wrapping the partition key in a function (e.g., DATE(order_date) = '2025-01-01') prevents pruning. Ensure the column is used directly.
-- io.thecodeforge.db.schema
-- Example where pruning works (direct column comparison)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE order_date >= '2025-06-01' AND order_date < '2025-07-01';
-- Example where pruning fails (function on column)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE extract(year from order_date) = 2025;
-- The second query will scan all partitions because the expression is not matched to partition bounds.
Output
-- First query: 'Subplans Removed: n' shows pruning occurred.
-- Second query: 'Subplans Removed: 0' — full scan.
Production Insight
I've seen production queries run 100x slower because a migration script used CAST(order_date AS DATE) instead of order_date directly.
Function-based indexes don't help; the partition key must be the raw column.
Rule: always test with EXPLAIN after creating or modifying queries on partitioned tables.
If pruning isn't happening, check for function wrappers, implicit type casting, or missing partition key in WHERE.
Key Takeaway
Partition pruning is the performance magic behind partitioning.
Never wrap the partition key in a function in WHERE clauses.
Always verify with EXPLAIN — you can't assume pruning works.
Containerized Database Management
To test partitioning strategies locally without polluting your system, use Docker to spin up a pre-configured instance. This ensures your staging and production environments use the exact same partitioning logic.
# io.thecodeforge.infrastructure
FROM postgres:16-alpine
# Optimized for large-scale partitioning tests
ENV POSTGRES_DB=forge_warehouse
ENV POSTGRES_USER=admin
ENV POSTGRES_PASSWORD=secure_password
# Copy initialization scripts that create partitions
COPY init-partitioning.sql /docker-entrypoint-initdb.d/
EXPOSE 5432
Output
Step 4/5 : COPY init-partitioning.sql /docker-entrypoint-initdb.d/
Production Insight
Docker is ideal for testing partitioning strategies, but remember: production hardware differences affect partition scan performance.
Use the same PostgreSQL major version locally as in production to avoid optimizer differences.
Volume mount a data directory to persist test partitions across container restarts.
Rule: automate partition creation in init scripts — never manually create partitions in production.
Key Takeaway
Docker containers provide reproducible partitioning test environments.
Match PostgreSQL version to production for accurate pruning behavior.
Automate partition creation as part of deployment scripts.