MEmerqurio·Sep 3, 2025Submission
3 points · Open discussionarkhipov
temporal_tables
C2016–2025
arkhipov / temporal_tables
temporal_tables
Temporal Tables PostgreSQL Extension
13Distinct people
18Mentions
0Explicit recommenders
1.1KGitHub stars
What to know before exploring
What it is
Temporal Tables PostgreSQL Extension
GitHub
Where it is used
No documented product use was verified in the last source check.
What people are saying
No recommendation phrase was found in the indexed comments.
No classified warning is attached to this page. Read the discussions before treating silence as approval.
Also in these discussions
- scalegenius/pg_bitemporal · linked in 2 of the same discussions
Mentioned in the same Hacker News threads. That is not proof they are substitutes.
This week on Hacker News
Nov 7–Nov 14 UTC · Direct repository links and separately labeled related project stories. Indexed through 11/14/2026, 3:56:07 PM UTC.
No direct repository links were found in the indexed HN discussions over the past seven days. Older discussions are available below.
Mentions over time
HN mentions by year18 dated mentions
Observed mentions: 2016-03-18 14:26:41 UTC to 2025-09-03 20:46:02 UTC.
Counts come from the repository’s dated source evidence. Index cutoff: 2026-11-02 08:16:59 UTC.
View mention counts and date ranges
| Period | Start (UTC) | End (exclusive, UTC) | Mentions | Submissions | Comments |
|---|---|---|---|---|---|
| 2016 | 2016-01-01 00:00:00 UTC | 2017-01-01 00:00:00 UTC | 4 | 0 | 4 |
| 2017 | 2017-01-01 00:00:00 UTC | 2018-01-01 00:00:00 UTC | 3 | 1 | 2 |
| 2018 | 2018-01-01 00:00:00 UTC | 2019-01-01 00:00:00 UTC | 0 | 0 | 0 |
| 2019 | 2019-01-01 00:00:00 UTC | 2020-01-01 00:00:00 UTC | 2 | 0 | 2 |
| 2020 | 2020-01-01 00:00:00 UTC | 2021-01-01 00:00:00 UTC | 1 | 0 | 1 |
| 2021 | 2021-01-01 00:00:00 UTC | 2022-01-01 00:00:00 UTC | 5 | 1 | 4 |
| 2022 | 2022-01-01 00:00:00 UTC | 2023-01-01 00:00:00 UTC | 1 | 0 | 1 |
| 2023 | 2023-01-01 00:00:00 UTC | 2024-01-01 00:00:00 UTC | 1 | 0 | 1 |
| 2024 | 2024-01-01 00:00:00 UTC | 2025-01-01 00:00:00 UTC | 0 | 0 | 0 |
| 2025 | 2025-01-01 00:00:00 UTC | 2026-01-01 00:00:00 UTC | 1 | 1 | 0 |
What Hacker News says
18 sourcesThere is also the temporal_tables extension [0], which was already discussed in HN. [1]
[0] https://github.com/arkhipov/temporal_tables
[1] https://news.ycombinator.com/item?id=26748096
In Ways to capture changes in PostgresOne of these Postgres-based implementations of SQL:2011's temporal versioning might get you close enough:
- https://github.com/nearform/temporal_tables
- https://github.com/xocolatl/periods
- https://github.com/scalegenius/pg_bitemporal
- https://github.com/arkhipov/temporal_tables
I haven't used any of them but I work on https://xtdb.com which is also implementing SQL:2011's temporal features :)
In Show HN: I made a CMS that uses Git to store your dataThis extension simplifies things a bit:
https://github.com/arkhipov/temporal_tables
In Postgres audit tables saved us from taking down productionOne of postgres's most underrated features. RLS is amazing, can be unseen/basically work silently if your programming language-side tools are good enough, and is documented well (like everything else):
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
But the power of PG is that it doesn't stop there, if you combine this with a plugin like temporal_tables and you can segment by user and time:
https://github.com/arkhipov/temporal_tables
All of this mostly unknown to the thing that's accessing the DB. If that's not enough for you, why not add some auditing with pgaudit:
https://www.pgaudit.org/#section_three
All this value is just out there. There's even more if you can stomach browsing "ugly" sites like PGXN. Most of it works out of the box, though you may need to tinker for performance and some edge cases but it's there.
I thin
Read the rest
k it might not actually be hyperbole to say that Postgres is the greatest RDBMS database that has ever existed.
I haven't used `temporal_tables` but I have built similar temporal systems on Postgres in the past. (Judging by https://github.com/arkhipov/temporal_tables#usage at least.)
The trouble with these sorts of approaches is that they solve temporality the same way we did in the 90s: Add an `entity_history` table, timestamp your tx-time and valid-time, and add a trigger to version your entities. This must be done for each entity you want to version across your temporal plane.
It would appear that `temporal_tables` doesn't support bitemporality yet. It only has tx-time (system time). But even if it did, this approach doesn't help you with live data. Because the `entity` table corresponding to the `entity_history` table still permits destructive updates, temporal queries are always in the realm of audits and can't answer questions about the appli
Read the rest
cation data directly. Add to that a completely manual system of querying the temporal information, and the resulting systems tend to get quite hairy, which is why Martin recommends avoiding bitemporality whenever you can. Unfortunately, that recommendation (while sound, for relational databases) means that bitemporality is expensive and manual if and when it's implemented.
A bitemporal database like Crux encodes the temporal plane into all the data stored in it, making it transparent to the user. There's no up-front setup cost to bitemporality and a query's default time on both time axes is "now", allowing the user to ignore temporality entirely except in those few instances where it is required -- but when it is required, it is global.
Related to bitemporality, I'd be curious to hear whether anyone has experience with the Temporal Tables[1] extension in PostgreSQL?
[1] - https://github.com/arkhipov/temporal_tables
In Bitemporal HistoryTemporal Tables PostgreSQL Extension
153 points · Open discussionCheck out https://github.com/arkhipov/temporal_tables or https://github.com/scalegenius/pg_bitemporal
In Oracle vs. PostgreSQL – A CommentThis is one of my favourite topics. Most large DBs I've seen in the wild use some form of temporal tables, but it's often a hacky hand-rolled solution. I'd really like to see more support for bitemporal tables from vendors.
As noted above, the SQL:2011 standard supports bitemporality; here's a good summary from SIGMOD Record: https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfea...
This is a good summary of vendor support for temporal tables: https://www2.cs.arizona.edu/~rts/sql3.html
Oracle and DB2 both support bitemporal tables, SQL Server only supports system-versioned tables. There is an extension for Postgres that supports system-versioned tables, but it hasn't been updated in 2 years: https://github.com/arkhipov/temporal_tables
In the past, I've manually implemented system-versioning for audit purposes in SQL Server using tri
Read the rest
ggers, T4 templates and the CONTEXT_INFO variable to store user IDs. It worked OK, but schema changes became much more difficult.
> I'd love to see someone translate the concepts in both books to a useful postgres package.
This is some work on this. [1] is an amazing patch by some researchers in Europe that has already had several rounds of review. Their first published work was against Postgres 9.1! Their solution is way better than SQL:2011, but could be used behind-the-scenes to implement the features from the standard. Essentially they implement a temporal variant for every operator in the relational algebra (very elegantly btw, by combining standard operators with just two new functions). That approach solves some of the problems that led to Snodgrass's original temporal proposal getting rejected in the late 90s (and which are still holding back SQL:2011 IMO). Whereas the standard only gives temporal INNER JOIN, their work gives every join type, as well as aggr
Read the rest
egates (including a way to scale the inputs if the group only includes part of their time interval). It also lets queries compose better through subqueries, views, CTEs, set-returning functions, etc. In my opinion their work would make Postgres the most advanced temporal database in the world. I hope it gets the attention it needs!
I also have a very modest WIP patch myself at [2], which adds temporal-aware primary and foreign keys.
This is all based on the excellent work on ranges and exclusion constraints by Jeff Davis and others. Postgres has an excellent foundation, but needs the higher-level concepts so that users can more easily set up temporal tables and use them to answer queries and record changes.
If you'd like to learn more about temporal databases, I wrote a summary of the research at [3]. I'm also giving a talk at the Postgres conference in Ottawa next month.... :-)
EDIT: Btw, if all you need is transaction time (usually included for auditing and compliance), then the temporal_tables extension [4] should be all you need. Maybe also consider the pgaudit extension too [5].
[1] https://commitfest.postgresql.org/22/2045/
[2] https://commitfest.postgresql.org/22/2048/
[3] https://illuminatedcomputing.com/posts/2017/12/temporal-data...
[4] https://github.com/arkhipov/temporal_tables
[5] https://github.com/pgaudit/pgaudit
See also the Temporal Tables extension to PostgreSQL
https://github.com/arkhipov/temporal_tables
In Migration from Postgres to Datomichttps://github.com/arkhipov/temporal_tables seems like a good option?
In PostgreSQL 10 Beta 1 ReleasedTemporal Tables PostgreSQL Extension
5 points · Open discussionThere is https://github.com/arkhipov/temporal_tables
which seems okay.
Of course, it would be nice if Postgres natively supported the full SQL:2011 standard...
In Major features of PostgreSQL 9.6 [pdf]Showing the 15 most recent of 18 sources. Enable JavaScript to search and browse every indexed mention.