Internal tooling · 2024

Quarry

Archived
  • Data
  • Backend
  • Postgres
RoleEngineer
Duration3 months
TeamSolo
StackPostgres, TypeScript, Node, Grafana

01 — The problemA reporting layer that replaced a nightly spreadsheet export, built entirely inside Postgres because the data was never big enough to justify anything else.

Ops rebuilt the same four reports by hand every morning from a CSV export. It took about ninety minutes, it was error-prone, and the numbers quietly disagreed with the dashboard because the two used different definitions of "active".

02 — ConstraintsWhat I had to work inside

  • About 40 million rows total. Real, but nowhere near warehouse scale.
  • No budget for new infrastructure.
  • The definition of every metric had to live in exactly one place and be readable by a non-engineer.

03 — ApproachMaterialised views, refreshed concurrently

Each report became a materialised view with a unique index, refreshed on a schedule with `CONCURRENTLY` so readers are never blocked. The whole pipeline is SQL files in the repo, applied by a migration runner — reviewable in a pull request, diffable, and revertible.

sql
create materialized view mv_active_accounts as
select date_trunc('day', e.at) as day,
       count(distinct e.account_id) as active
from   activity_event e
where  e.at >= now() - interval '18 months'
group  by 1;

-- CONCURRENTLY is the point, and it *requires* a unique index
create unique index on mv_active_accounts (day);

refresh materialized view concurrently mv_active_accounts;

04 — ApproachOne definition, checked into git

The disagreement between ops and the dashboard was never a bug — they were two correct answers to two different questions nobody had written down. Every metric got a SQL file with a plain-English comment at the top explaining what counts and what does not. Arguments about numbers turned into pull requests against a definition.

05 — DecisionsWhat I chose, and what it cost

Postgres over a warehouse

ChoseMaterialised views in the existing database
OverBigQuery or Snowflake with an ETL tool
Because40M rows fits comfortably. The warehouse would have added cost, a sync pipeline, and a second source of truth.
What it costRefreshes compete with production traffic. They run at 04:00 and would need a read replica to grow much further.

Scheduled refresh over streaming

ChoseHourly batch
OverIncremental / real-time aggregation
BecauseNobody makes an hourly decision from these reports. Daily would have been fine; hourly was free.
What it costThe dashboard is stale by up to an hour, which had to be labelled on screen so people stopped reporting it as a bug.

06 — OutcomesWhere it landed

90 min → 0Daily manual workFully removed from the ops routine.
4Reports automated
1Definition of "active"Down from three mutually contradictory ones.

07 — RetrospectiveWhat I would tell myself at the start

  • Reach for a warehouse when Postgres actually stops coping, not when the row count starts sounding impressive.
  • Most data disputes are definition disputes. Writing the definition down resolved more than any query optimisation.
  • Archived is a legitimate status. The company changed reporting tools in 2025 and this was retired on purpose.
PreviousOrchard StorefrontNextThis Site
All case studies