History Tables and Audit Trails
Explore how to manage data history and audit trails in PostgreSQL by using dedicated history tables and advanced data types like jsonb and hstore. Understand strategies for preserving previous versions of rows, handling schema evolution, and efficiently archiving changes to support business and application needs.
Use case: Maintaining previous versions or an archive
Some business cases require a full history of changes available for audit trails. What’s usually done is to maintain live data in the main table, modeled with the rules we already saw, and model a specific history table covering where to maintain previous versions of the rows or an archive.
History table and denormalization
A history table itself isn’t a denormalized version of the main table but rather another version of the model entirely, with a different primary key to begin with.
What parts might require denormalization for history tables?
-
Foreign key references to other tables won’t be possible when those reference changes, and you want to keep a history that, by definition, doesn’t change.
-
The schema ...