Setup
Enterprise customers are asking for an audit log: who did what, and when, in their account. Sales has promised it for next quarter.
Requirements:
- Record actions like “user X changed setting Y from A to B” across the product.
- Keep events for 1 year, then delete them.
- Customers can search in the UI by person, action type, and date range, and export results as CSV.
- Later, customers will want to stream events into their own security tools.
Numbers:
- About 30 million events a month across all customers. The largest customer produces about 15% of them.
- Writes are constant. Reads are rare: a few hundred searches a day, mostly over the last 30 days.
Constraints: a team of 3, 6 weeks, an existing Postgres primary that is already the busiest part of the system.
Options on the table:
- A plain table in the existing Postgres database.
- A separate Postgres database with the table partitioned by month.
- Write events to object storage and query them with a serverless query engine.
- Buy a third-party audit log service.
Outcome
A team with the same requirements chose option 2. Monthly partitions made retention easy (drop a partition instead of deleting 30 million rows), and putting it on its own database kept audit writes away from the busy primary. Searches over 30 days came back in under a second with an index on (account, time).
The decision that turned out hardest to reverse wasn’t storage. It was the event schema. Customers built scripts on the CSV export, and later on the streaming feed, so every field name and the shape of “actor” and “target” became a public contract. When the team needed to model actions taken by API keys instead of people, they had to version the schema and support both shapes for a year.
At 10× scale, searches on free-form event details became slow. The team added a narrowly scoped index on the few detail fields customers actually filtered on, rather than indexing everything.
Senior commentary
“Storage choices in this prompt are fairly reversible. Events are append-only and you can copy them anywhere later. What isn’t reversible is anything a customer builds against. The moment the export ships, your field names are an API. So I’d spend real design time on the event shape: who acted (a person, an API key, the system), what they acted on, and a version number from day one.
“For storage I’d keep it boring: a separate Postgres database, partitioned by month. You get SQL for search, cheap retention, and isolation from the primary, and the team already knows how to run it. Object storage is cheaper per gigabyte but makes the ‘search in the UI’ requirement awkward. Buying is reasonable if the budget exists, but then the vendor’s event model becomes your contract with customers.
“What would change my mind: if the streaming requirement moves up to this quarter, I’d design around a log or stream first and make Postgres one consumer of it.”
Discussion questions
- What did you treat as hard to reverse? Did the outcome agree?
- How does the read/write mix change which option looks best?
- What would you put in version 1 of the event schema, and what would you deliberately leave out?
- How would you handle a customer who asks to have their audit data deleted before the year is up?