Using Declarative Partitioning in PostgreSQL to Scale a Sales Table

The problem: a monolithic sales table slowing down queries and maintenance

Imagine a sales table that stores every transaction for a growing e‑commerce platform. Over time it reaches hundreds of millions of rows. Simple reports that filter by date start to scan the whole table, causing high I/O and long response times. Routine maintenance commands like VACUUM and REINDEX take out exclusive locks on the entire table, blocking writes during peak hours.

The goal is to keep the table logically intact while giving the planner the ability to skip irrelevant data and letting maintenance work run on smaller pieces with less contention.

Thesis: declarative range partitioning solves both query and maintenance pains

PostgreSQL 10 introduced declarative partitioning, which lets you define a parent table and let the system automatically route rows to child partitions based on a partition key. When queries filter on that key, the planner can prune whole partitions, reducing the amount of data read. Maintenance operations are still issued on the parent but are executed in parallel on each child, shortening lock windows.

Designing the partitioning scheme

For the sales example the natural partition key is sale_date (a date or timestamp column). We choose range partitioning by month because:

• Most reports filter by a month or quarter, aligning with partition boundaries.
• Monthly granularity yields a manageable number of partitions (e.g., 12 per year) without excessive metadata overhead.

The parent table will hold no data; all rows live in the child partitions.

Creating the partitioned table and indexes

Run the following as a role that owns the schema (or a superuser). Adjust your_schema and table names to your environment.

-- 1. Create the parent table partitioned by range on sale_date
CREATE TABLE your_schema.sales (
    sale_id      bigserial PRIMARY KEY,
    product_id   integer NOT NULL,
    sale_date    date    NOT NULL,
    amount       numeric(10,2) NOT NULL,
    -- other columns...
) PARTITION BY RANGE (sale_date);

-- 2. Create a monthly partition for January 2023
CREATE TABLE your_schema.sales_2023_01 PARTITION OF your_schema.sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- 3. Repeat for other months as needed, or automate with a script.

-- 4. Indexes on the parent propagate to children
CREATE INDEX idx_sales_product_id ON your_schema.sales (product_id);
CREATE INDEX idx_sales_sale_date ON your_schema.sales (sale_date);

Where to run: In psql connected to the target database. Permissions: You need CREATE on the schema and ownership of the parent table (or superuser). Risks: Creating the parent table takes a brief exclusive lock; schedule during low‑traffic windows. Adding partitions later also takes a short lock on the parent but not on existing child tables.

Expected check: After creating a partition, verify it appears in pg_inherits: SELECT inhrelid::regclass AS child FROM pg_inherits WHERE inhparent = 'your_schema.sales'::regclass;

Query performance and maintenance benefits

With the partitioning in place, a query that filters by month can skip all unrelated partitions.

EXPLAIN ANALYZE
SELECT sum(amount)
FROM your_schema.sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-02-01';

You should see the planner mention only the relevant child, e.g., Index Scan using idx_sales_sale_date on sales_2023_01 and a note like Partitionwise aggregate or Append showing a single partition scanned. Compare this to the same query on the unpartitioned table where you would see a Seq Scan on the whole sales relation.

Maintenance commands work similarly:

VACUUM (ANALYZE) your_schema.sales;
REINDEX TABLE your_schema.sales;

Internally PostgreSQL issues VACUUM on each child partition in parallel, which reduces the time the exclusive lock is held on any single table. You can observe the effect by checking lock durations in pg_locks or by timing the commands before and after partitioning.

Trade‑offs and limitations

Partitioning is not a free lunch:

• Partition pruning requires the predicate to reference the partition key. Queries that filter only on product_id will scan all partitions unless you also create an index that the planner can use.
• Too many small partitions increase metadata overhead. If you partition by day for years of data, the planner may spend more time managing partition routes than saving I/O. Monitor pg_total_relation_size and query planning time; if planning time grows noticeably, consider coarser granularity.
• Foreign keys and unique constraints have restrictions. A foreign key that references the partitioned table must be defined on the parent, and uniqueness cannot be enforced across partitions without additional work (e.g., using exclusion constraints or application‑level checks).

Practical verification: After loading a representative data set, run EXPLAIN ANALYZE for a typical report and for a report that does not filter on sale_date. Compare the Rows Removed by Filter and execution time. Also query pg_stat_user_tables to see the seq_scan and idx_scan counts per child; a well‑pruned workload will show low seq_scan on irrelevant children.

Actionable closing

Start small: pick one month of recent sales, create a partition for it, and move existing data into that partition with INSERT INTO ... SELECT ... or ALTER TABLE ... ATTACH PARTITION. Verify the plan shows pruning, then repeat for additional months. Automate partition creation with a cron‑triggered script that adds the next month’s partition a few days before the boundary arrives. Monitor planning time and lock duration to ensure the trade‑off stays favorable for your workload.