One strategy we are currently implementing is using a second Postgres database synchronized with logical replication.
1. We insert the logs in the main database and periodically delete old logs. We can thus create the log in the same transaction as a record is created/updated/deleted. Yay atomicity!
2. The second database uses logical replication to receive new logs, but it only replicates INSERT operations so old logs are never deleted.
3. The application can show recent logs quickly (who updated the customer profile this week?) and if the user wants to dig deeper, we can query the slower log database but the user kind of expect that pulling the entire history of a record will take a bit longer than other operations.
1. We insert the logs in the main database and periodically delete old logs. We can thus create the log in the same transaction as a record is created/updated/deleted. Yay atomicity!
2. The second database uses logical replication to receive new logs, but it only replicates INSERT operations so old logs are never deleted.
3. The application can show recent logs quickly (who updated the customer profile this week?) and if the user wants to dig deeper, we can query the slower log database but the user kind of expect that pulling the entire history of a record will take a bit longer than other operations.