Partitioning the Sales Mart

Partitioning and Materializing BookNest's Sales Mart

mart.sales_m (Declarative Partitioning), loaded in date order, becomes the fact table. 458-sales-mart.sql adds a BRIN index declared once on the parent, a procedure that creates next month's partition ahead of its orders, and a daily summary for dashboards.

458-sales-mart.sql: maintaining the partitioned, materialized sales martSQL

CREATE INDEX sales_m_date_brin ON mart.sales_m USING brin (order_date);   -- every partition
CREATE OR REPLACE PROCEDURE mart.add_sales_month(m date) LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE format('CREATE TABLE IF NOT EXISTS mart.sales_%s PARTITION OF mart.sales_m '
                 'FOR VALUES FROM (%L) TO (%L)', to_char(m, 'YYYY_MM'), m,
                 (m + interval '1 month')::date);
END $$;
CALL mart.add_sales_month('2026-07-01');          -- next month, before its first order
CREATE MATERIALIZED VIEW mart.mv_daily_sales AS
SELECT order_date, genre, channel, count(DISTINCT order_id) AS orders,
       sum(qty) AS units, sum(gross_amount) AS gross
FROM mart.sales_m WHERE status <> 'cancelled' GROUP BY 1, 2, 3;
CREATE UNIQUE INDEX ON mart.mv_daily_sales (order_date, genre, channel);
\timing on
SELECT genre, sum(gross) FILTER (WHERE NOT d.is_weekend) AS weekday,
       sum(gross) FILTER (WHERE d.is_weekend) AS weekend
FROM mart.mv_daily_sales m JOIN mart.dim_date d ON d.full_date = m.order_date
WHERE d.year = 2026 AND d.quarter = 2 GROUP BY genre ORDER BY weekday DESC;

Output

      genre      |  weekday  | weekend
-----------------+-----------+----------
 Technology      | 102344.50 | 39895.00
 Cooking         |  78696.00 | 31128.00
...
Time: 2.367 ms

To archive, the script runs ALTER TABLE mart.sales_m DETACH PARTITION mart.sales_2025_01 CONCURRENTLY (PostgreSQL 14 1,289 , outside a transaction block), which locks the parent only in SHARE UPDATE EXCLUSIVE mode; the detached month is a plain table to export to Parquet 129 (Exporting to Parquet) and drop (here it is reattached). The 8,723-row daily view answered in 2 to 10 ms over five runs; the same question on mart.sales_m, filtered through dim_date's year and quarter, pruned nothing and took 62 to 128 ms; filtering order_date directly pruned to three partitions and took 31 to 48 ms. Filter on the key, and point dashboards at summaries refreshed CONCURRENTLY after each load.

MENU