4 ms·
Postgres Audit Tables Saved Us from Taking Down Production (2021)
- pyuser583 3y agoDon’t databases natively use audit tables to prevent transaction errors? Shouldn’t there be a native way to persist them?
- deleted 3y ago[deleted]
- adrr 3y agoTransaction 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.
- paulryanrogers 3y agoSome 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.
- tetha 3y agoBut 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.
- time4tea 3y agoaka, we don't do any input validation. Oops.
- DevKoala 3y agoEssentially.
- matdehaast 3y agoHeap used to bring out so many great engineering deep dives. I do miss them publishing more
- hinkley 3y agoChanging priorities or did someone leave who was pushing them to publish?
- phyzome 3y agoFrom 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.
- jtchang 3y ago> 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?
- jackweirdy 3y agoIf 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
- koolba 3y agoOr worse if you blindly deserialize JSON and cast it as whatever you want it to be without validation…
- ianlevesque 3y agoe.g. the norm for typescript use.
- awoimbee 3y agoTypescript 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.
- geraldwhen 3y agoThat’s what’s class-validator is for. You can ensure your apis are doing sensible things.
- hinkley 3y agoFor 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.
- pixel_tracing 3y agoI’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?
- habitue 3y ago2. 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.
- malisper 3y agoI 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? No
- simonw 3y agoThere'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/8cbe373e5c06c76fe65b9781f97ad075 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;
- simonw 3y agoI 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 https://github.com/simonw/sqlite-history
- itpragmatik 3y agoWhen 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
- magden 3y agoAnother 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.
- jbellew 3y agoFirstly, 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.
- aprao 3y agoI 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 https://www.citusdata.com/customers/heap