Obtain insertion time of a tuple in an existing table in PostgreSQL 9.3

Question

I am running PostgreSQL 9.3 (in a hot standby setup) on Ubuntu 14.04 LTS.

I would like to display the growth (e.g. amount of tuples) of a table over time. The table does not contain a "timestamp" (or equivalent out of historical reasons) column.

Is their a way in PostgreSQL 9.3 to query information about the insertion time of a tuple in a table?

Answer 1

Doing a workaround might give you the statistics on growth of a table over time. In Postgres 9.3 there is a feature called The statistics collector. This might do the trick:

1. enable Run-Time Statistics
2. create the desired analysis function using pg_stat_all_tables
   1. query how many new tuples have been inserted with n_tup_ins (see Table 27-5 pg_stat_all_tables View)
   2. calculate the difference to an older value to get the actual number of inserted tuples in a defined time-frame

Or you can take a look at the Zalando PGObserver.

Hope this helps.