How do I know when data was added to Postgres?

Question

I inherited an existing Postgres database full of data. Most of the data has a create_date column value. Some of the earlier data was inserted before it was tracked.

Is there a hidden Postgres metadata table somewhere that tracks the execution of INSERT requests?

Answer 1

Postgres 9.5 or later

You can enable track_commit_timestamp in postgresql.conf (and restart) to start tracking commit timestamps. Then you can get the timestamp for your xmin . Related answer:

• Atomically set SERIAL value during transaction

Postgres 9.4 or later

PostgreSQL has no such metadata unless you yourself wrote it down.

You can get some information from the row headers (HeapTupleHeaderData) , in particular from the insert transaction id xmin . It contains the identifier of the transaction into which the row was inserted (required to determine visibility in the PostgreSQL MVCC model). Try (for any table):

 SELECT xmin, * FROM tbl LIMIT 10; 

Some restrictions apply:

• If the database was unloaded and restored, then, obviously, the information disappeared - all rows are inserted into one transaction.
• If the database is huge / very old / very heavily written, then perhaps it went through a transaction identifier bypass , and the order of numbers in xmin ambiguous.

But for most databases you should be able to get:

• chronological order of the INSERT
• which rows were inserted together
• when (probably) there was a long period of time between the inserts

However, there is no time.

Answer 2

track_commit_timestamp (boolean)

It is mainly used during replication server setup. Record transaction commit time. This parameter can only be set in the postgresql.conf file or on the server command line. The default value is off .

Answer 3

The short answer is no.

If that were the case, everyone would complain that it was a waste of space on all the tables that they did not want to track.