Postgres latest sequence id and uncommitted writes

Question

Hoping someone could clarify a section of this article "scalable-incremental-data-aggregation" and the corresponding question.

Assume Postgres 10 and up.

In Postgres 10, you can use the pg_sequence_last_value function to check the most recently issued sequence number. However, it would not be safe to simply aggregate all events up to the most recent sequence value. There might still be in-progress writes to the events table that were assigned lower sequence values, but are not yet visible when the aggregation runs.

Are there alternatives to pg_sequence_last_value that guarantee the correct seq id?

---

In a table (call it events) with a BIGSERIAL id, is there a preferred way to fetch the latest seq id for aggregation purposes? It's fine if there are pending inserts in a different transaction (they'll have higher sequence ids) and be picked up on subsequent runs.

The goal is to use a seq id to run aggregation (incremental rollups) on already committed inserts.

Are the following three methods guaranteed to return the latest seq id for committed inserts and avoid the pitfall mentioned in the article relating to pg_sequence_last_value?

Are there other techniques one might suggest?

SELECT max(id) FROM events;

SELECT last_value FROM pg_sequences WHERE sequencename='events_id_seq';

SELECT id FROM events ORDER BY id DESC LIMIT 1;

Assume read committed isolation level

Answer 1

select max(id) is the most reliable way.

It's also quite fast if you have a b-tree index on id (performance is virtually independent of the size of the table).

Answer 2

One of ways to do this is to associate updates with a transaction id. In this case all updates below xmin can be considered committed or rolled back. See the documentation:

txid_current() bigint get current transaction ID, assigning a new one if the current transaction does not have one

txid_current_snapshot() txid_snapshot get current snapshot

txid_snapshot_xmin(txid_snapshot) bigint get xmin of snapshot

xmin Earliest transaction ID (txid) that is still active. All earlier transactions will either be committed and visible, or rolled back and dead.