I need to expose my data through Rest API for a mobile offline experience.
The idea is basically that a mobile client downloads all data to his device. after a week he wants to download only the changes into his device. each mobile client synced to a different point of time.
My problem is how to create a deterministic cursor for the client which they will send it on their next sync request and I'll be able to figure out the changes since their last sync.


  1. Clients tolerate data duplication.
  2. My database is insert-update only (never delete).

Possible solutions:

  1. Logical Decoding
    It's relatively (to other solutions we have in mind) hard to create a plugin and it doesn't fit our needs because our permission model, each client needs to get only rows he is permitted to see.
  2. txid_current + cmin value.
    Save the transaction id on each row to create incremental value, cmin is needed to create a more precise cursor (many rows may have the same txid, but only one row has a specific txid + cmin pair).
  3. Use xmin only, and re-sync (sync from scratch) user's upon wraparound. (according to the epoch counter).
  4. Add SERIAL column/sequence, and bump it up manually or with triggers, it won't make us have to serialize our transaction if we are using Overlapping Sync as describe below.
  5. Serialize all transactions that act on the most top resource in my model (database), which is least preferred because of the performance and complexity it introduces to the application code.

Currently, I really want to use option 2, because it seems like the simplest solution.

How I'm going to handle Consistent Ordering:

The problem:
If the client syncs up to the latest visible (committed) change there may be active transactions that include earlier changes that are not yet visible.

Consistent Ordering problem

The solution:
Overlapping Sync the clients, Sync up to the latest visible change but keep track of what the latest safe change was. On the next sync, ask for changes since the lastest safe change rather than the latest visible change.

Is option 2 valid? (under the assumption above and the Overlapping Sync solution), if not, what is the next simplest solution? there is another solution which I didn't think about?

2 Answers 2


I would tag each inserted row with the current transaction ID (txid_current() or, from v13 on, pg_current_xact_id()).

The “latest safe change” is the right before the xmin of the “current snapshot”, which can be found with txid_snapshot_xmin(txid_current_snapshot()) (or pg_snapshot_xmin(pg_current_snapshot()) from v13 on). That xmin is the transaction ID of the oldest still active concurrent transaction, that is, everything older than that is done and visible.

This should make it fairly easy to build a replication system.

  • Thanks, after more, thinking, txid is not enough because we may (theoretically) have very big transaction (think about import API for an example) which the cause to write operation be bigger than the sync read operation (import is limited to 500 rows per transaction, and sync limited to 100 at a time, theoretically of course), I think about to add cmin value to the txid cursor to create a more precise cursor. WDYT?
    – Michael
    Jun 21, 2020 at 21:45
  • I don't know the "import" API you're talking about, but a single statement can also add arbitrarily many rows (think COPY). Perhaps it is good enough for your special case, though. But I don't think there is a function that will give you the current command counter, so I wouldn't know how to add that column to the table. You are aware that you cannot index system columns, right? Jun 22, 2020 at 3:47
  • 1
    Actually, it is simpler: if you want a more granular ordering than the transaction ID, use the combination of transaction ID and primary key. Then with the transaction ID you can make sure to get the rows you need, as detailed in my answer, and with the primary key you can distinguish between rows. Jun 22, 2020 at 4:58

The Overlapping Sync solution is a must. It is better to have data you don't need (and not use it), then to not have the data you do need.

The Send me data since... value should be a parameter for the REST API. This will allow the Client to grab changes of data since:

  • last sync time - 1 (Client keeps track of last sync time)
  • last Friday
  • last 30 days

It seems that if you define your "top most resource" as having SCD Type 4 data, your Point 5 would be a non-issue. Some databases support this natively. If not, it is usually implemented via TRIGGER. The REST API just needs to pull the "what has changed?" information from this set of information (history table). Maybe there is something about your model I'm missing, but this (to me) seems to be the easiest.

Row Level Security (RLS) should probably be enabled so that the REST API doesn't accidentally pull the wrong rows.

IMHO - Using an easy-to-guess-the-next-value column to sync with the Client would be a potential Security Problem. I recommend using a Natural Key (eg UUID) instead of the transaction ID/PK. The REST API really only needs to send "These UUIDs have changed since input parameter, here is what they are right now". The Client does a MERGE with its own data.

Oh, you'll probably want another REST API that represent a "Give me all of my data as it exists right now". Things do happen to the Client computer requiring the end-user to rebuild the Client-side data from scratch. (eg phone got dropped in a pool)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.