Re: Detailed questions about pg_xact_commit_timestamp

Hi,

On 7/9/19 12:22 AM, Morris de Oryx wrote:
> I have some specific questions about pg_xact_commit_timestamp, and am hoping
> that this is the right place to ask. I read a lot of the commentary about the
> original patch, and the contributors seem to be here. If I'm asking in the wrong
> place, just let me know.
>
> I'm working on a design for a concurrency-safe incremental aggregate rollup
> system,and pg_xact_commit_timestamp sounds perfect. But I've found very little
> commentary on it generally, and couldn't figure out how it works in detail from
> the source code.
>
> Hopefully, someone knows the answers to a few questions:
>
> * Is it possible for pg_xact_commit_timestamp to produce times out of order?
> What I'm after is a way to identify records that have been chagned since a
> specific time so that I can get any later changes for processing. I don't need
> them in commit order, so overlapping timestamps aren't a problem.

I think yes. For example, you can have a session "A" xid 34386826 that commit
after session "B" xid 34386827:
postgres=# select pg_xact_commit_timestamp('34386827'::xid);
pg_xact_commit_timestamp
-------------------------------
2019-07-11 09:32:29.806183+00
(1 row)

postgres=# select pg_xact_commit_timestamp('34386826'::xid);
pg_xact_commit_timestamp
------------------------------
2019-07-11 09:32:38.99444+00
(1 row)

>
> * How many bytes are added to each row in the final implementation? The
> discussions I saw seemed to be ranging from 12-24 bytes. There was discussion of
> adding in extra bytes for "just in case." This is pre 9.5, so a world ago.

src/backend/access/transam/commit_ts.c says 8+4 bytes per xact.

Note it is not per row but per xact: We only have to store the timestamp for one
xid.

>
> * Are the timestamps indexed internally? With a B-tree? I ask for
> capacity-planning reasons.

I think no.

>
> * I've seen on StackOverflow and the design discussions that the timestamps are
> not kept indefinitely, but can't find the details on exactly how long they are
> stored.
>

Yes timestamp are stored in pg_commit_ts directory. Old timestamp are removed
after freeze has explained in
https://www.postgresql.org/docs/current/routine-vacuuming.html :

> The sole disadvantage of increasing autovacuum_freeze_max_age (and
vacuum_freeze_table_age along with it) is that the pg_xact and pg_commit_ts
subdirectories of the database cluster will take more space, because it must
store the commit status and (if track_commit_timestamp is enabled) timestamp of
all transactions back to the autovacuum_freeze_max_age horizon. The commit
status uses two bits per transaction, so if autovacuum_freeze_max_age is set to
its maximum allowed value of two billion, pg_xact can be expected to grow to
about half a gigabyte and pg_commit_ts to about 20GB. If this is trivial
compared to your total database size, setting autovacuum_freeze_max_age to its
maximum allowed value is recommended. Otherwise, set it depending on what you
are willing to allow for pg_xact and pg_commit_ts storage. (The default, 200
million transactions, translates to about 50MB of pg_xact storage and about 2GB
of pg_commit_ts storage.)

> * Any rules of thumb on the performance impact of enabling
> pg_xact_commit_timestamp? I don't need the data on all tables but, where I do,
> it sounds like it might work perfectly.
>
> Many thanks for any assistance!

I didn't notice any performance impact, but I didn't do any extensive testing.

Regards,