Sample Header Ad - 728x90

Querying whole PostgreSQL table in replica on CTID

0 votes
1 answer
113 views
I noticed it is quite fast to query a whole PostgreSQL table in chunks on ctid as explained for example in this Airbyte blog post . I need this for a syncing task that might take from hours to a few days, can’t tell yet as it’s related to syncing data to another service, but it also might be paused and resumed on occasion. But I’m wondering about the issue of ctid values changing after vacuuming. And more precisely how this affects reading from a read replica. I checked the pg_stat_all_tables last_vacuum and vacuum_count for the table in question in the read replica and the vacuum count is 0 and there is no last vacuum time. I notice the order of rows returned is not exactly chronological, the rows with the first ctid values are about a year older than the chronologically first ones, but that’s not an issue for me if it wouldn’t change in the span of a few days and I know I can iterate over all rows in chunks on ctid. My question is - does this mean that the read replica table has actually not been vacuumed, and therefore the ctids have never changed? And is there something else that might lead to a change in ctid in a read replica?
Asked by Nikolay D (101 rep)
Aug 1, 2024, 08:30 AM
Last activity: Aug 1, 2024, 02:00 PM