PostgreSQL :: partitioning table

1.txt

// 1. Create table
CREATE TABLE orders_partitioned (
    id            BIGSERIAL      NOT NULL,
    customer_id   BIGINT         NOT NULL,
    customer_name VARCHAR(255)   NOT NULL,
    status        order_status   NOT NULL DEFAULT 'new',
    total_amount  NUMERIC(12, 2) NOT NULL DEFAULT 0,
    created_at    TIMESTAMPTZ    NOT NULL DEFAULT now(),
    -- Partition key (created_at) must be part of the primary key
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

// 2. Create partition tables
CREATE TABLE orders_partitioned_2026_04 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE orders_partitioned_2026_05 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE orders_partitioned_2026_06 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE orders_partitioned_2026_07 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Create default partition for out-of-range data (optional, but prevents insert failures)
CREATE TABLE orders_partitioned_default PARTITION OF orders_partitioned
    DEFAULT;

2.txt

// ทำการ analyze query
EXPLAIN ANALYZE 
SELECT * from orders_partitioned;

// ผลการ analyze
"Append  (cost=0.00..59.75 rows=650 width=556) (actual time=0.043..0.045 rows=0.00 loops=1)"
"  ->  Seq Scan on orders_partitioned_2026_04 orders_partitioned_1  (cost=0.00..11.30 rows=130 width=556) (actual time=0.011..0.011 rows=0.00 loops=1)"
"  ->  Seq Scan on orders_partitioned_2026_05 orders_partitioned_2  (cost=0.00..11.30 rows=130 width=556) (actual time=0.012..0.012 rows=0.00 loops=1)"
"  ->  Seq Scan on orders_partitioned_2026_06 orders_partitioned_3  (cost=0.00..11.30 rows=130 width=556) (actual time=0.007..0.007 rows=0.00 loops=1)"
"  ->  Seq Scan on orders_partitioned_2026_07 orders_partitioned_4  (cost=0.00..11.30 rows=130 width=556) (actual time=0.006..0.006 rows=0.00 loops=1)"
"  ->  Seq Scan on orders_partitioned_default orders_partitioned_5  (cost=0.00..11.30 rows=130 width=556) (actual time=0.006..0.006 rows=0.00 loops=1)"
"Planning:"
"  Buffers: shared hit=5"
"Planning Time: 0.314 ms"
"Execution Time: 0.116 ms"