Data Engineering

Storage shaped
by the question

A table's physical layout should be dictated by how it is read, not by how it happens to be written. Once storage mirrors the shape of the questions the business actually asks, both the database and the people maintaining it get their work cut down for free.

System: Passenger profiling platform
Scale: 535M rows across 16 airports
Role: Sole architect and engineer
Stack: PostgreSQL / Partitioning / SQL

The challenge

Analytical questions over half a billion rows were reading far more data than the customer was entitled to see, and pushing past the request timeout.

The approach

Split the table along the two axes that matter — who is asking, and how writes arrive — then rewrite the read path to match.

The result

Customer dashboards answer in seconds instead of timing out, upkeep became a bounded operation, and tenancy is enforced by the layout itself.

01The questionEvery customer-facing dashboard asks a variation of the same thing: what does the audience look like at my airport, over these days? The layout below exists to make that one question cheap.FastAPI / Redis cache 02Partition by airportThe table is split into separate physical pieces, one per airport. A customer only ever asks about their own airports, so the database can discard every other piece before it reads a single row. It also means a customer's data is kept apart by the storage itself, not by a filter someone might forget.PostgreSQL / LIST partitioning 03Sub-partition by passengerEach airport's piece is split again into buckets, spread evenly by passenger. Writes arrive in bursts, and without this they would all pile onto the same spot; spreading them means many can land at once without queueing behind each other.PostgreSQL / HASH sub-partitioning 04What actually gets readThe result is that a question about one airport touches one column and ignores the rest. The same split makes routine upkeep cheap: statistics, cleanup and retiring old data all happen one small piece at a time instead of as a single operation over everything.GROUPING SETS / partition pruning

Fig. 02 — Every customer question is answered inside one lit column

Hover any step to read what it doesTap any step to read what it does

Storage shapedby the question

The challenge

The approach

The result

Storage shaped
by the question