Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The Ultimate Guide to PostgreSQL Data Change Tracking

exaspark.medium.com

Post reply on HN
Print view View original

11–20 of 27 posts

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#11

Post by dventimi »

DandyDev wrote: post #10
crucialfelix wrote: post #3

Earlier quoted context omitted.

I would avoid using database triggers for business logic. In the app (Django or whatever) after inserting news item, call a service (async) that matches users, sends alerts

Agreed. Triggers always feel really clever and powerful, but they live outside your regular application code and are therefor more or less invisible. This will eventually break the law of least surprise. On top of that, triggers are hard to test.

Not if your regular application code is in the database

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#13

Post by klysm »

hbarka wrote: post #12

Is write-ahead log something new in PostgreSQL? I recall SQL Server being able to do log shipping since the 90s.

The WAL itself isn't new, but the capabilities to use it externally in a robust manner have improved in recent years via Logical Replication.

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#14

Post by klysm »

caerwy wrote: post #9

Another technique not discussed is to create an md5 hash of each record and to keep track of added and deleted hashes. The d hash would exist as an additional column on each table being tracked.

I'm not sure I understand what you mean. Where are the hashes tracked?

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#15

Post by dkubb »

Has anyone ever implemented a schema where you define a view to return the latest instance of each resource, and it maps INSERT, UPDATE and DELETE to an append only table underneath?

I’ve done some of this with an event sourcing system, but not the query rewriting. All my reads use the “latest view”, but the app uses functions to write to the underlying scheme rather than doing “simple” DML.

I wondered if keeping the storage as append only under the hood would keep the app layer simple.

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#16

Post by agarren »

caerwy wrote: post #9

Another technique not discussed is to create an md5 hash of each record and to keep track of added and deleted hashes. The d hash would exist as an additional column on each table being tracked.

If I understand you right, this seems like a very course-grained way to track changes. You can record that a change was made, but not the specific change. It seems like it'd help facilitate something like, say, an etag, but I don't think could get any auditable data using this alone, could you? Keeping track of added/deleted hashes would probably be best handled by a trigger, unless you really want that in your app code, so this seems a lot like an audit trigger, with very little audit data. Have I misunderstood?

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#17

Post by rrr_oh_man »

dkubb wrote: post #15

Has anyone ever implemented a schema where you define a view to return the latest instance of each resource, and it maps INSERT, UPDATE and DELETE to an append only table underneath? I’ve done some of this with an event sourcing system, but not the query rewriting. All my reads use the “latest view”, but the app uses functions to write to the underlying scheme rather than doing “simple” DML. I wondered if keeping the…

Why would it keep the app layer simple? As in: Insert only? (Create = Insert, Update = new Insert, Delete = new empty Insert with deleted flag)

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#19

Post by benpacker »

dkubb wrote: post #15

Has anyone ever implemented a schema where you define a view to return the latest instance of each resource, and it maps INSERT, UPDATE and DELETE to an append only table underneath? I’ve done some of this with an event sourcing system, but not the query rewriting. All my reads use the “latest view”, but the app uses functions to write to the underlying scheme rather than doing “simple” DML. I wondered if keeping the…

The end result is almost the same as an audit table except there's much more work to do at read time, and some queries simply cannot scale as they need to with this approach.

In general for most apps you'd rather have 2 writes (write to table and write to audit table) than 1 write to the event log and hard to scale reads.

Post reply on HN