I have a simple Postgres setup (one machine, one disk) and would like to record a changelog for some of the tables. As I would like to access that changelog from the same web app that uses the database, it would be ideal to store the row-by-row changes in Postgres as well.
It is critical that the logging doesn't slow the machine down too much during times of high load. I would like to avoid setting up triggers (unless you believe they are the best option here).
I know about Debezium and Kafka, but setting all of that up seems like a dreadful amount of complexity for what I'm trying to achieve. What is the easiest way to accomplish what I'm trying to do? I'm open to other options too.
Edit for clarification: I don't need the log entry to be written alongside the changes in one atomic transaction – I'd rather it's queued up to happen at some unspecified time later. If some timestamps are wrong or (worst case) a log entry is missing, it's not the end of the world in my situation; write performance is more important.
Best Answer
setup a trigger on UPDATE DELETE and INSERT that writes the info you want to a logging table.