Ways to capture changes in Postgres
I once consulted at a company with a very large monolithic SQL Server. It actually wasn't Postgres but let's pretend it was.<p>It had been around for decades and over time it had ended up being used for all sorts of things within the company. In fact, it was more or less true that every application and business process within the whole company stored its data within this database.<p>A key issue we had was that because this database had many different applications that queried it, and there were a huge number of processes and procedures that inserted or updated data within it, sometimes queries would break due to upstream insert/update processes being amended or new ones added that broke application-level invariants -- or when a normal process operated differently when there was bad data.<p>It was very difficult to work out what had happened because often everything that you looked at was written a decade before you and the employees had long since left the company.<p>Would it be possible to capture changes from a Postgres database in some kind of DAG in order that you could find out things like:<p>- What processes are inserting, updating or deleting data and historically how are they behaving? For example, do they operate differently ever?<p>- How are different applications' querying this data? Are there any statistics about their queries which are generally true? Historically how are these statistics changing?<p>I don't know if there is prior art here, or what kind of approach might allow a tool like this to be made?<p>(I've thought of making something like this before but I think this is an area in which you'd want to be a core Postgres engineer to make good choices.)