Do you need to identify newly inserted records in some table?

Of course you can add some special column of yours which you will update.
But this solution is not much useful when you have really big amount of data because PostgreSQL deletes all old records and inserts new ones with updated value.
So if you would need to process table with several millions of records you will create serious load on disk just with this update.

Fortunately PostgreSQL has one system column in every row in every table which you can use in such extreme cases – it is called “xmin”.

It is ID of transaction which inserted record. If you will run “select max(xmin) from …table…” in ceration moment and store result it in some log table you can easily identify changes and process them.

To select new records use “select * from table where xmin> …your_stored_last_max_xmin…”

The same mechanism you can also use to just identify which tables changed.

With count(*) you can find how many records were inserted. But use it with caution – count can be really slow…

 

Only problem with this mechanism can occur if PostgreSQL will rotate transaction ID. Because xmin is 32 byte long integer. This mean max number is 2147483648 (=2GB).

If it will happen new xmin will start with number 3. Number 1 is not used, number 2 means “frozen transaction ID” used by vacuum process.

So in that case you will get “last xmin in table” some small number. Lower than previously stored “last xmin in table”

But I did not see this situation in reality yet. Therefore I only presume how it will look like according to documentation.