This is the commit message of commit c87ff71f374652936a6089215a30998492b14d52 in the PostgreSQL git repository. This new feature is really interesting. It was written by Mark Kirkwood, reviewed by Laurenz Albe, and commited by Magnus Hagander.

It allows one to see when the autovacuum will fire an ANALYZE on a table. This is extremely useful. At least, to me, when I do a training course. Before, you only had dead_tuples in pg_stat_all_tuples to guess when autovacuum will fire a VACUUM. You had nothing to guess when it will fire an ANALYZE. As the commit message says, this information is available in PostgreSQL but not publicly available. This new function makes it available for the next PostgreSQL release. The PostgreSQL project only adds new features in the development branch of PostgreSQL. So the older releases won't have it. Unless you have an extension which will do the same thing.

That's what I did today: add the function in an extension, and add the extension on pgxn.org so that anyone can install it.

It's actually quite simple to install. The easier way is to use pgxnclient:

[guillaume@laptop ~]$ pgxnclient install mods_since_analyze
INFO: best version: mods_since_analyze 1.0.0
INFO: saving /tmp/tmpTHjC3c/mods_since_analyze-1.0.0.zip
INFO: unpacking: /tmp/tmpTHjC3c/mods_since_analyze-1.0.0.zip
INFO: building extension
cp mods_since_analyze.sql mods_since_analyze--1.0.sql
sed 's/DROP /ALTER EXTENSION mods_since_analyze ADD /' uninstall_mods_since_analyze.sql > mods_since_analyze--unpackaged--1.0.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mods_since_analyze.o mods_since_analyze.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  -shared -o mods_since_analyze.so mods_since_analyze.o
sed 's/EXTVERSION/1.0/;s/EXTENSION/mods_since_analyze/;s/EXTCOMMENT/Expose the estimation of number of changed tuples since last analyze/' mods_since_analyze.control.in > mods_since_analyze.control
rm mods_since_analyze.o
INFO: installing extension
[sudo] password for guillaume: 
/usr/bin/mkdir -p '/usr/share/pgsql/extension'
/usr/bin/mkdir -p '/usr/share/pgsql/mods_since_analyze'
/usr/bin/mkdir -p '/usr/lib64/pgsql'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze.control '/usr/share/pgsql/extension/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze--unpackaged--1.0.sql ./mods_since_analyze--1.0.sql  '/usr/share/pgsql/mods_since_analyze/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 755  mods_since_analyze.so '/usr/lib64/pgsql/'

Done. Easy enough, right? :)

If you don't have pgxnclient, this is still quite easy but you need to do it manually:

  1. Download mods_since_analyze (mods_since_analyze-1.0.0.zip)
  2. Unzip the downloaded file
  3. Compile it
  4. Install it

In other words:

[guillaume@laptop ~]$ wget http://api.pgxn.org/dist/mods_since_analyze/1.0.0/mods_since_analyze-1.0.0.zip
--2013-07-07 18:34:09--  http://api.pgxn.org/dist/mods_since_analyze/1.0.0/mods_since_analyze-1.0.0.zip
Resolving api.pgxn.org (api.pgxn.org)... 88.198.47.126
Connecting to api.pgxn.org (api.pgxn.org)|88.198.47.126|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5686 (5.6K) [application/zip]
Saving to: ‘mods_since_analyze-1.0.0.zip’

100%[========================================================================================>] 5,686       --.-K/s   in 0.02s   

2013-07-07 18:34:10 (271 KB/s) - ‘mods_since_analyze-1.0.0.zip’ saved [5686/5686]

[guillaume@laptop ~]$ unzip mods_since_analyze-1.0.0.zip 
Archive:  mods_since_analyze-1.0.0.zip
cfe2bbb6ea83d02d402024821696aef7de01d7be
   creating: mods_since_analyze-1.0.0/
  inflating: mods_since_analyze-1.0.0/.gitignore  
  inflating: mods_since_analyze-1.0.0/AUTHORS  
  inflating: mods_since_analyze-1.0.0/COPYING  
  inflating: mods_since_analyze-1.0.0/INSTALL  
  inflating: mods_since_analyze-1.0.0/META.json  
  inflating: mods_since_analyze-1.0.0/Makefile  
  inflating: mods_since_analyze-1.0.0/README.md  
 extracting: mods_since_analyze-1.0.0/TODO  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.c  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.control.in  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.sql  
  inflating: mods_since_analyze-1.0.0/uninstall_mods_since_analyze.sql  
[guillaume@laptop ~]$ cd mods_since_analyze-1.0.0/
[guillaume@laptop mods_since_analyze-1.0.0]$ make && make install
cp mods_since_analyze.sql mods_since_analyze--1.0.sql
sed 's/DROP /ALTER EXTENSION mods_since_analyze ADD /' uninstall_mods_since_analyze.sql > mods_since_analyze--unpackaged--1.0.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mods_since_analyze.o mods_since_analyze.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  -shared -o mods_since_analyze.so mods_since_analyze.o
sed 's/EXTVERSION/1.0/;s/EXTENSION/mods_since_analyze/;s/EXTCOMMENT/Expose the estimation of number of changed tuples since last analyze/' mods_since_analyze.control.in > mods_since_analyze.control
rm mods_since_analyze.o
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/extension'
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/mods_since_analyze'
/usr/bin/mkdir -p '/opt/postgresql-9.2/lib'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze.control '/opt/postgresql-9.2/share/extension/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze--unpackaged--1.0.sql ./mods_since_analyze--1.0.sql  '/opt/postgresql-9.2/share/mods_since_analyze/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  mods_since_analyze.so '/opt/postgresql-9.2/lib/'

Then you'll have to activate it on the database where you want to use it. You simply need to execute CREATE EXTENSION:

tests=# CREATE EXTENSION mods_since_analyze;
CREATE EXTENSION

It will add a single function:

tests=# \dx+ mods_since_analyze
  Objects in extension "mods_since_analyze"
             Object Description              
---------------------------------------------
 function pg_stat_get_mod_since_analyze(oid)
(1 row)

This function returns the number of changed tuples since the last ANALYZE (manual or automatic).

tests=# CREATE TABLE t1(id integer);
CREATE TABLE
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+-----------------------------
now                               | 2013-07-07 22:32:41.25594+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 0

tests=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:00.408815+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 1000000

tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:04.569035+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 1000000

tests=# ANALYZE;
ANALYZE
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:09.00094+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 0

tests=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
tests=# DELETE FROM t1 WHERE id<100000;
DELETE 199998
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:32.42597+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 1199998

tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:34:52.344178+02
pg_stat_get_last_autoanalyze_time | 2013-07-07 22:34:15.050458+02
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 0

It works from the 9.0 release to the 9.3 release.

Hope you'll enjoy it.