Historical Data Tracking in PostgreSQL - Part 1: Historical Table and Triggers

Introduction to Historical Data Tracking in PostgreSQL

Historical data tracking, also known as #historification, plays a crucial role in maintaining a comprehensive record of changes made to data over time. This process allows us to access previous versions of data and analyze its evolution, providing valuable insights for various business scenarios. For instance, consider a scenario where a company needs to monitor how product characteristics, such as quantity, category distribution, average price, and inventory availability, have evolved over time.

While some relational database systems offer built-in support for historification, #PostgreSQL does not natively include such a feature. However, PostgreSQL's versatility and support for stored procedures enable us to implement a custom historical data tracking process using triggers and functions.

There may be other alternatives or approaches to achieving historification in PostgreSQL, such as using extensions or external tools, but these aspects will not be mentioned here.

Defining the Historical Table

In PostgreSQL, we create a historical table to store the historical versions of our original table. The historical table's name is the same as the corresponding table, with the addition of the "_hist" suffix. For instance, if our original table is called 'products' and is included in the 'stg' schema, the historical table will be named 'stg.products_hist'.

The structure of the historical table closely mirrors that of the original table, with one crucial difference: it contains three additional columns to capture historical data for each record:

• start_date: This column records the date when a record becomes active.
• end_date: This column stores the date when a record ceases to be active.
• deleted: This column helps identify whether a record has been deleted.

Pro Tip: To simplify maintenance and avoid modifications in multiple parts of the code when adding new fields, it is advisable to define these three additional columns as the first fields of the historical table.

Defining Triggers

#Triggers in PostgreSQL allow us to define automatic actions in response to specific events occurring on a table. They are powerful tools for enforcing business rules, maintaining data integrity, or triggering additional tasks whenever certain operations are performed on the associated table.

In our context, triggers are employed to automatically capture and record changes made to data in the table, facilitating the creation of version history. We have defined three triggers associated with the 'products' table in the 'stg' schema:

CREATE TRIGGER trg_products_insert
AFTER INSERT ON stg.products?
FOR EACH ROW?
EXECUTE FUNCTION dwh.trg_fnc_products_insert();

CREATE TRIGGER trg_products_update?
AFTER UPDATE ON stg.products?
FOR EACH ROW?
EXECUTE FUNCTION dwh.trg_fnc_products_update();

CREATE TRIGGER trg_products_delete?
BEFORE DELETE ON stg.products?
FOR EACH ROW?
EXECUTE FUNCTION dwh.trg_fnc_products_delete();        

Each set of triggers follows a similar structure, explained below:

1. Each trigger is associated with a table, in this case, 'stg.products'.
2. The trigger is activated after an INSERT, UPDATE, or DELETE operation on the table, depending on its purpose.
3. For each inserted, updated, or deleted row, the corresponding trigger function is executed: 'stg.trg_fnc_products_insert()', 'stg.trg_fnc_products_update()', and 'stg.trg_fnc_products_delete()'.
4. The trigger's purpose is to perform certain actions or validations either before or after a new row is inserted, updated, or deleted in the table.

The triggers defined are responsible for maintaining the historical record using the mentioned trigger functions whose operation is summarized below:

• When an insertion occurs, the corresponding trigger captures the new data and inserts it into the historical table. It sets the 'start_date = now()' for the record (indicating when it became active) and sets the 'end_date' to NULL as the record is still active.
• Similarly, when an update is made to any record, the corresponding trigger marks the previous version as completed in the historical table by assigning 'end_date = now()' which indicates that the old record has expired. The trigger then inserts a new entry with the updated data, following the same steps as explained above.
• In the case of a deletion in the table, the associated trigger logs this action in the historical table by setting the 'deleted' value to true and assigning an 'end_date = now()' to the corresponding record. This indicates that the record has expired.

Stay tuned for the next article where we will delve deeper into the trigger functions 'stg.trg_fnc_products_insert()', 'stg.trg_fnc_products_update()', and 'stg.trg_fnc_products_delete()', providing an in-depth explanation of each.

Keep learning and exploring the fascinating world of historical data tracking in PostgreSQL! #DataTracking #Historification #PostgreSQLTriggers #PostgreSQL #HistoricalData #DataManagement #Triggers