4 ms·

I make heavy use of this technique. I use it to display an audit log to users that tells them what’s changed on the entity and who did it. I set the application

I make heavy use of this technique. I use it to display an audit log to users that tells them what’s changed on the entity and who did it. I set the application_name to the currently logged in user id so I can display it back. I find this lets me punt on a lot of fine-grained permissions for different roles within an organisation. With the audit log, it becomes reasonable to say “just ask them not to do that. If they do, you’ll easily be able to see what happened and we can reverse it, then tell them to knock it off” I’ve also gotten pretty wild using it to reconstruct state in migrations. Like adding a “created_at” field to a table, then mining the audit log for creation events to set the value on existing rows. Or changing a table to a soft delete instead of an actual delete, then repopulating past entries from the audit log.

bambataa 5y ago
Do you add audit tables for basically everything, or just the most critical tables? I’m wondering whether the DB eventually becomes dominated by audit entries, though I guess you could prune them periodically if that is a problem.
- davidbanham 5y ago
  In my case I've added them everywhere, all tables. The critical tables are also the highly trafficked ones in my case. So leaving the audit off the less-critical ones wouldn't really buy me anything. It does _feel_ like the kind of thing that's likely to build an unreasonable amount of cruft around the place, but it just hasn't happened over the last few years. The way I run it all the audit logs are actually in a single table. Querying that table isn't exactly quick but it doesn't need to be. It all just trucks along.
  - BatteryMountain 5y ago
    I have all mine in a separate schema, with prefixes: "audit_schemaname" with "audit_customer", "audit_invoice" etc. And then I partition them based on the hot ones. Than you for sharing, will play around with putting it all in one table.
- BatteryMountain 5y ago
  I add them to everything now, but automate it. If you have any "hot" tables, it is worthwhile to partition those audit tables, again, automate those parts. I've only had a single case where we had to switch it off (the auditing) because we started getting deadlocks (main table was read & write heavy + our reporting engine was fed off of it at the time). So for most cases it works perfectly well. Just keep an eye on your hot tables, memory consumption and how fast your main table grows (as the audit tables can sometimes take up more disk space than your main table (had a 20Gb table with a 50Gb audit table...)). If you are on postgres, pg_partman + pg_cron are your friends. You can also automate it from you application side (which is what I mostly do, makes permission issues easier to manage and I have strong typing in my language - I run this code after deployments/migrations are done, automatically - make sure it is idempotent so you don't accidentally wipe your audit tables).

bambataa 5y ago

Do you add audit tables for basically everything, or just the most critical tables? I’m wondering whether the DB eventually becomes dominated by audit entries, though I guess you could prune them periodically if that is a problem.

davidbanham 5y ago
In my case I've added them everywhere, all tables. The critical tables are also the highly trafficked ones in my case. So leaving the audit off the less-critical ones wouldn't really buy me anything. It does _feel_ like the kind of thing that's likely to build an unreasonable amount of cruft around the place, but it just hasn't happened over the last few years. The way I run it all the audit logs are actually in a single table. Querying that table isn't exactly quick but it doesn't need to be. It all just trucks along.
- BatteryMountain 5y ago
  I have all mine in a separate schema, with prefixes: "audit_schemaname" with "audit_customer", "audit_invoice" etc. And then I partition them based on the hot ones. Than you for sharing, will play around with putting it all in one table.
BatteryMountain 5y ago
I add them to everything now, but automate it. If you have any "hot" tables, it is worthwhile to partition those audit tables, again, automate those parts. I've only had a single case where we had to switch it off (the auditing) because we started getting deadlocks (main table was read & write heavy + our reporting engine was fed off of it at the time). So for most cases it works perfectly well. Just keep an eye on your hot tables, memory consumption and how fast your main table grows (as the audit tables can sometimes take up more disk space than your main table (had a 20Gb table with a 50Gb audit table...)). If you are on postgres, pg_partman + pg_cron are your friends. You can also automate it from you application side (which is what I mostly do, makes permission issues easier to manage and I have strong typing in my language - I run this code after deployments/migrations are done, automatically - make sure it is idempotent so you don't accidentally wipe your audit tables).