Migrating to a Temporal Schema

Please note different location this month at UpStart Collective at the U.S. Bancorp Tower (a.k.a. Big Pink).

Coinciding with devopsdays Portland, OR, Sept 8-10, 2026.

In v18, Postgres got temporal primary keys, unique constraints, and foreign keys (with `NO ACTION`). Hopefully in v19 we'll have `UPDATE/DELETE FOR PORTION OF`. So it's a good time to start thinking about migrating your schema to a temporal structure. Some advantages include:

- Easier queries and joins to reconstruct historical data.
- A better way to do soft-deletes (preserving referential integrity).
- No bugs from foreign key references to since-updated data.
- A less ad hoc way of representing historical data.

This talk will explore how to migrate your schema to include application-time `daterange` and `tstzrange` columns. I'll use an existing schema for a time-tracking and invoicing application (used by me for over 12 years), showing the pain points of the old structure, not sparing my pride at some bad decisions, and give an approach to bring it all into a nicer temporal structure.

We will also talk about some remaining pain points in using temporal tables, and suggestions to mitigate them.
If you are contemplating a move to temporal tables, this talk will give you an overview of the landscape.