Export a consistent PostgreSQL catalogue while updates continue

Define what a consistent catalogue means

Start by naming the exported unit: product, variant or offer per channel. One row per product may not suit a recipient expecting one row per variant and currency. Also document eligibility filters, mandatory relationships and the meaning of an absent record. Should an absent product be removed, retained or flagged? This decision determines the risk posed by a partial export.

Then distinguish consistency from freshness. A catalogue representing an agreed state may remain useful even when changes arrive afterwards. Conversely, a collection of recently read values can contain incompatible relationships. Ask the business team to specify the acceptable delay and the rules that make a collection suitable for publication.

• Identify the tables, joins and unique key of each delivered row.
• Specify filters, currencies, units and deletion rules.
• Set a duration limit and an explicit condition for abandoning the run.
• Name the person who accepts discrepancies and authorises publication.

Reproduce a missing record with four products

Consider an entirely hypothetical scenario. The eligible products have identifiers 10, 20, 30 and 40. The export reads two rows per page, ordered by identifier. The first page contains 10 and 20. Another transaction then deletes 10 and commits that deletion. The second query skips the first two rows of the collection now visible: 20 and 30. It returns 40. Product 30 is missing from the file.

The process can nevertheless report three written rows and no SQL errors. To reproduce this during acceptance testing, use two sessions and a pause between pages. Retain the expected and actual identifiers. This small dataset demonstrates why checking the job status alone is insufficient.

Add a second scenario: a product and its variants are updated together between two reads. Ask which combination of values the export should contain. The answer establishes a precise expectation for the concurrency tests.

Separate traversal order from the read snapshot

PostgreSQL documentation explains that Read Committed can expose a different state to each query. Repeatable Read keeps a common view for successive reads within a transaction, established by its first relevant statement. Deterministic pagination also requires a unique ordering. These properties address different problems.

For this proposed case, start with a read-only extraction inside a Repeatable Read transaction, using a unique, stable traversal key. Verify that all relevant reads actually use that transaction. An orchestrator opening a separate connection for each step does not automatically meet this condition.

Also establish a scope boundary: this approach covers data within one database. A PostgreSQL read subsequently enriched by an external pricing API needs an additional reconciliation rule. A local isolation setting alone cannot define the shared state of those two systems.

Choose pagination that can be verified

For a paginated extraction, consider key-based traversal: after the last delivered key, read the following keys in the same order. If the catalogue is defined by product and variant, the position marker must represent that combination, with a unique tie-breaker. Document how null values are handled and prevent changes to the sorting rules during a batch.

This proposal makes page boundaries easier to identify, but it does not replace the consistency decision. Test ordering and returned values separately. A progress key describes a position in the traversal; it does not prove that every page belongs to the same state.

LIMIT/OFFSET remains an option for a frozen collection. PostgreSQL notes, however, that skipped rows still need to be computed. Compare the timings of the first and last pages on a representative volume before choosing this approach. Determine page size through measurement rather than imposing a universal value.

Freeze values before a slow delivery

If the recipient processes data slowly, consider separating extraction from delivery. The first process produces a complete intermediate batch; the second transmits it. The design record must specify which fields are frozen. Keeping identifiers alone and rereading prices during transmission does not meet the objective of stable content.

Associate the batch with an identifier, its scope, the transformation version, a row count and the checks performed. Use explicit states such as preparing, validated, published and abandoned. Moving to validated must depend on content checks, rather than merely reaching the end of the write operation.

This choice adds storage and an operational step. Direct extraction may be simpler when the processing window is short and controlled. To decide, measure extraction duration, intermediate volume, delivery time and the cost of starting again. Have the database team review long transactions and resource limits.

Plan for interruption before the first run

Decide what happens when a connection disappears halfway through traversal. Under the proposed method, a batch whose extraction has not finished remains ineligible for publication. If its read snapshot is no longer available, restart extraction under a new identifier. Do not present resuming from the last key as certain continuation of the same snapshot.

Once the complete batch is frozen and validated, delivery can have its own recovery procedure. Keep transmission and receipt evidence associated with the same content. Also agree how earlier attempts are handled, so an operator or automatic retry cannot later publish an abandoned batch.

Mistakes to look for during review

A count taken from the source after export is not necessarily a useful reference when that source continues to change. Prepare the reference within the chosen read scope. Likewise, an identical row count can conceal one missing product and another repeated product: compare keys as well.

A snapshot preserves a state; it does not repair business data that is already contradictory. Retain relationship and eligibility checks. Finally, do not infer a deletion at the destination from a row missing in an incomplete batch. First require evidence that the entire extraction has been validated.

Acceptance checklist and publication decision

Run acceptance tests with concurrent changes triggered at known points. For each test, archive inputs, the sequence of events, the batch identifier, expected results and observed results. The following criteria are a proposal to adapt to the recipient's contract.

• Compare all exported keys with the reference, with no omissions or repetitions.
• Insert, update and delete between pages; check the expected state.
• Test variants and their parent products within the same scenario.
• Check a partial final page, an empty catalogue and null values.
• Disconnect before completion; verify that publication remains prohibited.
• Interrupt delivery of a validated batch; verify that its content is preserved.
• Measure first and last pages, total duration and resource consumption.
• Verify that only a complete, accepted batch can trigger deletions.

Prepare a focused assessment

To scope an engagement, gather the relevant schema, current queries, approximate volume, recipient rules and an example discrepancy. The expected outcome is a documented decision covering reads, traversal, batch retention and publication. These inputs allow Sculpture Binaire to discuss a concrete scope without assuming that the entire integration flow needs to be rebuilt.