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

21–27 of 27 posts

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#21

Post by caerwy »

klysm wrote: post #14
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?

here's the article I learned this from. https://www.mssqltips.com/sqlservertip/2543/using-hashbytes-...

you'd add a hash column to the table you want to track. this would be used in a data warehouse where you want to track what has changed after a truncate and load. you'd keep additional tables on the side to track added/and delete hashes for a delta copy to downstream application tables.

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#22

Post by namekyd »

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…

[dead]

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#23

Post by Incipient »

For tables that don't need to scale infinitely, and to trade off in simplicity and keeping logic in the application, I use a version column.

It's the "brute force" type approach. It doesn't scale, has performance penalties, but it's incredibly easy to use and understand, and time walking is trivial.

Relies heavily on DISTINCT ON.

On truly old tables I've added "maintenance tasks" that push old rows into an archive/audit table.

Edit/note - I realise if you suggest this at any of the big players, you'll probably be fired on the spot.

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#24

Post by vivzkestrel »

cpursley wrote: post #7
vivzkestrel wrote: post #2

This article brings a very good question, let us say I have a stock news app and want to issue an alert everytime a new news item is added to the database for AAPL. There could be a 1000 symbols. What is the best way to implement this alerting system in postgres?

The WAL CDC approach: https://github.com/cpursley/walex?tab=readme-ov-file#publica... CREATE PUBLICATION news_item FOR TABLE news WHERE (topic IS "AAPL");

any suggestions for node.js on this? This one seems specific to elixir

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#25

Post by cpursley »

vivzkestrel wrote: post #24
cpursley wrote: post #7

Earlier quoted context omitted.

The WAL CDC approach: https://github.com/cpursley/walex?tab=readme-ov-file#publica... CREATE PUBLICATION news_item FOR TABLE news WHERE (topic IS "AAPL");

any suggestions for node.js on this? This one seems specific to elixir

Node is not appropriate for this sort of workload. You can use WalEx and forward the events to your Node app via webhook: https://github.com/cpursley/walex?tab=readme-ov-file#webhook...

Re: The Ultimate Guide to PostgreSQL Data Change Tracking

#26

Post by arjunlol »

vivzkestrel wrote: post #2

This article brings a very good question, let us say I have a stock news app and want to issue an alert everytime a new news item is added to the database for AAPL. There could be a 1000 symbols. What is the best way to implement this alerting system in postgres?

It depends on your reliability and performance constraints. I'd recommend just doing this via hooks on the application layer if it's a simple application. If you're building a production scalable application that required reliability guarantees - you can do this with https://github.com/BemiHQ/bemi too (I'm one of the maintainers from the article). I saw you mentioned you're using Node.js, check out the Bemi github there's a few Node.js compatible libraries too. Feel free to ping me any q's!
Post reply on HN