Partitioned Analytics Warehouse

Project roadmap

1. 01

   Design the warehouse

   ~2h

   Write DESIGN.md: the events table (event id, occurred at, event type, user id, session id, page, properties as jsonb), partitioned by RANGE on occurred at, one partition per month; the retention period (for example 13 months); which reports must be fast (daily active users, events per type per day, conversion funnels); and the summary tables that serve them. Done when: DESIGN.md covers the schema, partitions, retention and reports.

2. 02

   Create the partitioned table

   ~1.5h

   Create the partitioned events table, a default partition to catch unexpected dates, and monthly partitions. Choose indexes carefully: a BRIN index on occurred at (tiny and effective for data inserted in time order) and a B-tree on (event type, occurred at). Compare their sizes. Done when: inserting events routes them to the right monthly partition.

3. 03

   Automate partition management

   ~2h

   Write PL/pgSQL functions: ensure_partitions(months_ahead) creates missing future partitions, and apply_retention() detaches and drops partitions older than the retention period, logging what it did to a maintenance log table. Make both safe to run repeatedly. Schedule them with pg_cron or document a cron job. Done when: running the functions creates next months' partitions and removes expired ones, and a second run does nothing.

4. 04

   Load data in bulk

   ~2h

   Generate a large realistic dataset (tens of millions of events across 13 months) with generate_series, and write a loading procedure that uses COPY into a staging table, validates rows (known event types, non-null times), moves good rows into events and rejected rows into a rejects table with the reason, all in one transaction. Make loading the same batch twice safe (for example with the event id as a key). Done when: the data loads, and bad rows end up in rejects.

5. 05

   Build summary tables

   ~2.5h

   Create daily rollup tables (events per type per day, daily active users, and a funnel from view to cart to purchase) and a refresh function that recalculates only the days that changed since the last run. Point reports at the rollups instead of the raw events. Done when: dashboard queries on the rollups return in milliseconds and match the same queries on raw events.

6. 06

   Measure and tune

   ~2h

   Run the main report queries with EXPLAIN (ANALYZE, BUFFERS) and confirm partition pruning (only the relevant partitions are scanned). Record the timings before and after partitioning, indexes and rollups in README.md, and explain which change helped which query. Done when: README.md shows measured improvements for every report.

7. 07

   Test it

   ~1.5h

   Write test.sql that checks: events land in the right partition, the default partition stays empty, retention drops only expired partitions, reloading a batch doesn't duplicate data, and rollups match raw counts for sample days. Done when: psql -f test.sql passes.

Resources

• DocsPostgreSQL: table partitioning
• DocsPostgreSQL: BRIN indexes
• DocsPostgreSQL: COPY
• DocsPostgreSQL: using EXPLAIN
• Docspg_cron