Postgres Audit Tables Saved Us from Taking Down Production (2021) | Hacker News Reader

Postgres Audit Tables Saved Us from Taking Down Production (2021)

heap.io

96 pointsthunderbong28 comments

There's a bug on that page - the text "The SQL to restore the deleted metadata looked a little like this:" is followed by a blank space.

I dug around in the HTML source and figured out it's supposed to show content from this Gist: https://gist.github.com/matt-heap/8cbe373e5c06c76fe65b9781f9...

    INSERT INTO pg_dist_shard
    SELECT logicalrelid, shardid, nodename FROM dist_shard_audit
    WHERE action = 'DELETE' AND log_time > now() - '1 minutes'::interval;

When I read the heading I thought these are out of the box audit tables provided by Postgres. But the article then explains these are custom tables created by author - so this could very well be applicable to MySQL or any other databases

> Although we use Typescript to mitigate these types of bugs, when we're working with external systems, it's easy to find yourself in a situation where the types are lying to you.

What does this mean? If you use typescript doesn't it prevent these types of bugs?

If a property is a string in your typescript interface but a number in your external service (api, DB, whatever) the typescript compiler would not know

Or worse if you blindly deserialize JSON and cast it as whatever you want it to be without validation…

e.g. the norm for typescript use.

Typescript does compile time checking only. Almost all TypeScript programs I've seen just decode JSON received and slap an `as SomethingType`. TypeScript is not strict, so it's easy to write but you can still get type errors.

That’s what’s class-validator is for. You can ensure your apis are doing sensible things.

For instance I am currently trying to file a patch for a TS project. It is advertised as an npm module, which means if you pass a string to a function expecting a number it will happily do string concatenation on it instead of addition. There are no internal checks because TS, but that only works if you call it from TS. Otherwise authors have a false sense of security and make bad decisions.

This is why I use codec libraries like zod or io-ts (purify has them built in also) at my boundaries.

They were just accepting incoming data as-is and typescript won’t do a thing for you.

Ideally you validate incoming data using jsonschema or whatnot.

In the static analysis space this is a fundamental issue. We can't really usefully reason about the actual system, the state space simply becomes too large. So you have to reduce your problems to some axiomatic abstraction. In type systems we mostly cut those axioms at the system boundary. This is mostly because type systems are often incompatible and therefore can't automatically be analyzed across the boundary.

Imagine some pipe. You can put in some bytes and get some bytes out again. As far as you know, you have to put in 16 bit integers, and you get 16 bit integers out. You put that in your type system:

    int16_t communicate(int16_t in);

Now what you don't know is that if you provide the remote system with the integer 0, it will send back a 0, followed by the string "hello world". Your type system can't catch that because you've never told it that it can happen. The axioms the static analysis was built on didn't match reality, and therefore the analysis itself isn't sound.

I implemented a similar audit table pattern to the one described in this post for SQLite as a Python library here:

sqlite-history: https://github.com/simonw/sqlite-history

aka, we don't do any input validation. Oops.

Essentially.

Firstly, what a way to extrapolate an entire article from the fact that a your engineering team is incapable of doing runtime validation.

Also brushing over the data validation side of things when this is the most important part about all of this and making out this is some clever engineering feat because you managed to have the equivalent of a write ahead log for your tables/cluster.

"when we're working with external systems, it's easy to find yourself in a situation where the types are lying to you"

No..this is your system...you show know the types of your own data..just wow.

I believe Heap uses Citus for distributed Postgres[1] - wonder if this issue could have been caught at the Citus level? Changing metadata about shard information can be a powerful foot gun, so the more defenses, the better.

1: https://www.citusdata.com/customers/heap

Heap used to bring out so many great engineering deep dives. I do miss them publishing more

Changing priorities or did someone leave who was pushing them to publish?

From experience at other companies, I feel like it's usually just this One Person who goes around and bugs people to actually sit down and write up the interesting things they have to say. When they leave, the posts stop.

Another reason why sharding is not always the best for use cases when you need to re-shard frequently. They should take a look at distributed Postgres dbs like Yugabyte.

Don’t databases natively use audit tables to prevent transaction errors? Shouldn’t there be a native way to persist them?

Transaction Log isn’t easy to reverse to raw SQL, it’s a binary log. I also don’t think it has meta information like timestamp or source.

Some can use logical logs which can be decoded by 3P software. Though I agree it would be noisy and require some tooling/filtering to be manageable.

But what is a transaction error? <UPDATE user_profiles SET first_name = 'REDACTED';> will execute very successfully from the databases point of view.. And from a business point of view, it might be either a horrible thing (in production), or a thing that happens every saturday (on dev).

But, beyond that, our Postgres + PGBackrest setup could do point in time recovery. It would be a pain, because you'd have to dig through lots and lots of WAL archives with some pg-wal-viewer to figure out the exact transaction that started to cause damage. And then you could reset the database to the exact transaction before that.

Except, this is weaker than the audit logs presented in the article. If there was a mix of poison changes and valid transactions after the identified point, we'd lose the valid changes after the first poison transaction. That may have some amount of impact which has to be evaluated from a business sense.

But, keeping these detailed audit logs around for busy tables isn't an option. If you dump a couple hundred records per second into a table, it's not really a good idea to double the write load with trigger inserts into a second table. That can incur a pretty significant increase in hardware cost to host the dang thing.

But eh, I'd much rather implement this as an append table with a bit of smart indexing. Annotate the shard routing with a <valid_since> and select <ORDER BY valid_since DESC LIMIT 1>. This way, you wouldn't need two tables, and you could rollback a bad update either with a <DELETE FROM routing WHERE valid_since BETWEEN ...>, or updating them into the past.

I’d like to push back on these suggestions a bit.

1. Why use Postgres distributed cluster vs say an incremental store that supports real time data like Materialize? A streaming database sounds like the right use case for your requirements no? Under 1 min real time latency? Is Postgres distributed able to do it efficiently? (Never tried Postgres dist.)

2. Why use typescript at all? Pick a language that actually enforces class validation and enforces type validation baked into the language itself (like Rust or Go)? Sounds like programmer error is root cause of issue that should be looked into as a mitigation step (add real class validation at the minimum)

3. Regarding audit tables, are you also keeping audit tables for user and events tables too? That would seem… excessive and now duplicated data (especially billions of rows). Doesn’t the database come with audit tables baked into it?

2. Typescript is the language they're using. Generally people don't change their entire codebase to a different language because they run across a bug that would have been hypothetically solved by a different language. Additionally, this is a validation / coercion issue. It is a risk at the boundary of any two systems, you have to translate network bytes into a type your type system understands. If you translate it wrong, the type system is helpless to save you.

1. A streaming database isn't necessarily what they need here, postgres is plenty fast for most use cases. I'd move to a specialized tool like materialize if they've squeezed all of the juice out of postgres.

3. Postgres doesn't have audit tables built in, though there are multiple tools and plugins for postgres that can hook into things and do it for you. They went with a custom trigger solution, maybe that was sufficient for their use case.

I used to work at Heap, although I left 4 years ago

> Why use Postgres distributed cluster vs say an incremental store that supports real time data like Materialize

Materialize didn't exist when Heap was founded 10 years ago. Also, Materialize is dependent on knowing what queries you are running up front. Not to mention Heap is dealing with petabytes of data. Materialize only recently introduced multi-node support, so I would be surprised if it's being used at that kind of scale.

> Why use typescript at all?

Heap was originally written in CoffeeScript. It was the decision the semi-technical CEO made. Migrating to Typescript was the best option that allowed Heap to keep their existing codebase.

> Regarding audit tables, are you also keeping audit tables for user and events tables too?

No. Only the distributed metadata had audit logging when I was there

> Doesn’t the database come with audit tables baked into it?

Reply on news.ycombinator.com