I have seen people, DBA questioning whether there any way to check when the table was created in Postgres.
Till 9.5 there was NO appropriate answer for the question but after 9.5 we can say yes we can get create time of table because of new feature i.e Commit timestamps.
track_commit_timestamp
Record commit time of transactions. This parameter can only be set in PostgreSQL.conf file or on the server command line.
The default value is off. This value is a boolean value.
Change in value needs postmaster restart.
Test Case:
Without enabling track_commit_timestamp
SELECT pg_xact_commit_timestamp(xmin), * FROM pg_class where relname= 'tracking';
ERROR: could not get commit timestamp data
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
Enabling track_commit_timestamp
alter system set track_commit_timestamp='on';
Then restart the Postgres service(postmaster spinup).
create table tracking(i int);
To query that information, use the function pg_xact_commit_timestamp(transaction_id) to find out when rows were
INSERTed/UPDATEed, call the pg_xact_commit_timestamp function passing in the xmin system column
When a new table was created the new entry will be added to pg_class.
From pg_class we can take the last commit time for a table in our case its "tracking"(table name).
postgres=#
SELECT pg_xact_commit_timestamp(xmin), * FROM pg_class where relname= 'tracking';
-[ RECORD 1 ]------------+---------------------------------
relnamespace | 2200
pg_xact_commit_timestamp | 2016-10-08 00:41:57.824771+05:30
relname | tracking
reltype | 16508
Insert and update
postgres=# insert into tracking values (1);
INSERT 0 1
INSERT 0 1
postgres=# insert into tracking values (2);
postgres=#
SELECT pg_xact_commit_timestamp(xmin), * FROM tracking ;
pg_xact_commit_timestamp | i
----------------------------------+---
2016-10-08 12:32:11.223785+05:30 | 1
2016-10-08 12:32:13.862489+05:30 | 2
postgres=# update tracking set i=10 where i=2;
UPDATE 1
postgres=#
SELECT pg_xact_commit_timestamp(xmin), * FROM tracking ;
pg_xact_commit_timestamp | i
----------------------------------+----
2016-10-08 12:32:11.223785+05:30 | 1
2016-10-08 12:34:21.379262+05:30 | 10
Basically track_commit_timestamp records the committed row values(insert,update).
Note: Make sure that user must enable the track_commit_timestamp if he wants to record row's committed time.
The advantage of enabling this parameter can be used in multimaster(BDR) systems or pg_logical(replication setup) to over
comer from conflict resolution
Performance Impact:
This GUC added by the community to track the last committed timestamp of rows in tables.
Once enabled, commit timestamps are tracked for all transactions in the DB cluster, which causes a performance hit.If you only need it for one or a few tables, rather add timestamps to involved tables manually creating a trigger on tables and capturing the timestamp would be preferable .
