Re: BUG #13837: Files in pg_committs not deleted

On Tue, Dec 29, 2015 at 6:32 AM,  <alain.laporte12345@gmail.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      13837
> Logged by:          Alain Laporte
> Email address:      alain.laporte12345@gmail.com
> PostgreSQL version: 9.4.5
> Operating system:   Linux
> Description:
>
> Hi,
>
> I use PostgreSQL 9.4.5 and I have activated the parameter
> track_commit_timestamp to use BDR (0.9.3) and to be able to replicate two
> databases. A directory pg_committs was created (this directory is named
> pg_commit_ts in PostgreSQL 9.5 =>
> http://www.postgresql.org/docs/9.5/static/storage-file-layout.html), but,
> his content is not periodically deleted (all files stays after 2 weeks of
> running).

In stock PostgreSQL, pg_commit_ts is truncated when the cluster-wide
oldest xid is advanced by autovacuum (or manual vacuum freeze of all
databases including templates).  With the default
autovacuum_freeze_max_age setting, that'll take more than a couple of
weeks unless you're doing somewhere around 15 million transactions per
day.  (I guess that BDR-patched 9.4 is the same, but I don't know.)

The documentation describes how autovacuum_freeze_max_age affects the
space occupied by pg_clog[1], but the same thing applies to
pg_commit_ts.  pg_clog uses 2 bits per xid, and it looks like
pg_commit_ts uses 10 bytes per xid, so the default
autovacuum_freeze_max_age gives you ~50MB of pg_clog and ~2GB of
pg_commit_ts.

I wonder if doc/src/sgml/maintenance.sgml should be updated to reflect
this, maybe with something along the lines of the attached patch.

(Isn't it a bit strange that we say that the *sole* disadvantage of
setting autovacuum_freeze_max_age to a higher number is disk space
usage?  Freezing later also has consequences for whether you'll
actually be able to complete the freeze before wraparound, especially
if you set it to 2 billion as recommended.)

[1] http://www.postgresql.org/docs/devel/static/routine-vacuuming.html

--
Thomas Munro
http://www.enterprisedb.com