5

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?

MWiesner
  • 8,868
  • 11
  • 36
  • 70
rzo1
  • 5,561
  • 3
  • 25
  • 64
  • 2
    Unfortunately not, unless you tell it to store this type of data it won't store the data. – Lucas Jan 18 '16 at 09:26
  • Thanks for comment. I thought of "some magic" regarding the hot standby setup, since the two server instance have to exchange data so that their is possibly some information about timestamps or growth over time... – rzo1 Jan 18 '16 at 09:34

1 Answers1

2

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.

semm0
  • 947
  • 7
  • 18