Re: BUG #13837: Files in pg_committs not deleted

On Tue, Dec 29, 2015 at 4:24 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Tue, Dec 29, 2015 at 6:32 AM, <alain(dot)laporte12345(at)gmail(dot)com> wrote:
>> 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.)

Rebased, will add to commitfest.

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