Immutable Data in Postgres
summary
Builds immutable audit by writing shadow tables with $ suffix and lightweight triggers that copy inserts updates deletes, records the Postgres transaction id from Postgres plus date author and message, and generates stored‑procedure code to offer blame and undo operations and a time‑travel view that stitches effective intervals together. Audit size ranges from about 2× to 50× main tables in production. Undo and time queries reconstruct deltas and work in practice, but require careful indexing and test data to run efficiently.
AI-written summary · about
chapters (25)
- 0:00Introduction
- 1:00Netflix example
- 1:43The real problem
- 3:02Functional programming
- 4:54Git tool
- 5:42Example Netflix
- 6:17Secret Code
- 7:10Triggers
- 8:00Example
- 8:17Commit Hash
- 10:55Audit trails
- 13:31Generating code
- 15:55Example query
- 18:30Data correction
- 20:03Undo operations
- 21:51Production Data
- 23:35Netflix
- 24:35Licenses and Movies
- 25:28Effective Intervals
- 26:35License
- 27:22Filter
- 30:23Audit Tables
- 33:42High White Space
- 36:38Further Resources
- 37:45Any Questions
related talks
50mPastgreSQL: Dead data tells all tales!
38mUsing database triggers to reliably track model history
50mSolving PostgreSQL Wicked Problems
46mDon't do that!
32mAnonymizing Data in Postgres Without Losing Your Mind (or Your
15mScaling PostgreSQL Change Data Capture Using Open Source Airbyte
33mPostgres Storytelling: Support in the Darkest Hour
49mConcurrency Deep-Dive