Re: BUG #13837: Files in pg_committs not deleted

Finally, pg_committs is well truncated according to your explanation.

So, there are no bug in PostgreSQL or BDR.

Thanks!


Alain.

On Tue, Dec 29, 2015 at 4:24 AM, Thomas Munro <thomas.munro@enterprisedb.com
> wrote:

> 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
>