PostgreSQL Partitioning in Production: A 2026 Guide with Real-World Configs and Mistakes

-- Parent table
CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    user_id BIGINT NOT NULL,
    amount NUMERIC(10,2),
    status VARCHAR(20)
) PARTITION BY RANGE (created_at);

-- Partitions
CREATE TABLE orders_2026_q1 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE orders_2026_q2 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

-- Indexes on parent table apply to all partitions automatically
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3
);

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months',
    retention_keep_table = false
WHERE parent_table = 'public.orders';

-- Good: prunes to single partition
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01';

-- Bad: scans ALL partitions
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE user_id = 12345;

CREATE TABLE orders_2026_q2 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01')
    PARTITION BY HASH (user_id);

CREATE TABLE orders_2026_q2_h0 PARTITION OF orders_2026_q2
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- Create h1, h2, h3

SELECT
    parent.relname AS parent_table,
    child.relname AS partition_name,
    pg_size_pretty(pg_total_relation_size(child.oid)) AS size
FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

#!/bin/bash
# Runs daily at 2 AM
psql -d mydb -c "CALL partman.partition_data_proc('public.orders', p_interval := '1 month', p_source_table := 'public.orders_old');"
psql -d mydb -c "DELETE FROM public.orders WHERE created_at < NOW() - INTERVAL '90 days';"

<script type="application/ld+json">
{
  "@context": "https://schema.org",
  "@type": "FAQPage",
  "mainEntity": [
    {
      "@type": "Question",
      "name": "Can I change the partition key after creation?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "No. Once set, it's immutable. You'd need to rebuild the entire table. Choose carefully."
      }
    },
    {
      "@type": "Question",
      "name": "Will adding new partitions cause downtime?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "No. Adding a partition is a DDL operation, but PostgreSQL 16+ supports CREATE TABLE IF NOT EXISTS — no table locks."
      }
    },
    {
      "@type": "Question",
      "name": "Do foreign keys work with partitioned tables?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "Yes, but with a catch: the foreign key must include the partition key. It's a frustrating limitation."
      }
    },
    {
      "@type": "Question",
      "name": "How do I migrate existing data to a partitioned table?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "Use pg_dump to export, create the partitioned table, then import. Or use INSERT INTO ... SELECT in batches. There's no in-place conversion."
      }
    }
  ]
}
</script>




## References & Community Insights
The following authoritative resources were referenced for architectural best practices and specifications:

- [PostgreSQL Official Documentation](https://www.postgresql.org/docs/)
- [Stack Overflow Engineering Blog](https://stackoverflow.blog/engineering/)
- [Redis Enterprise Architecture](https://redis.com/redis-enterprise/)