[Snapshots] Precision loss on json/jsonb large integers

[eminano]

Describe the bug
When having a json/jsonb structure that contains large integers on the source database, the snapshot replicates the data to the target with precision loss.

Example:
Source: '{"number": 1234567890123456789}'
Target: '{"number": 1234567890123456800}'

This happens because the snapshot retrieves the row values via the pgx library, which uses json to unmarshal json/jsonb types under the hood. The encoding/json library unmarshals integers into float64 types, which is what's causing the loss of precision in very large integers (numbers > 2^53-1 (9,007,199,254,740,991) lose precision).

To Reproduce
Steps to reproduce the behavior:

1. CREATE TABLE test_json (id int, data jsonb);
2. INSERT INTO test_json VALUES (1, '{"num": 1234567890123456789}');
3. Run pgstream snapshot
4. Compare the value on source and target.
   SELECT data->>'num' FROM test_json WHERE id = 1;

Expected behavior
Precision should not be lost between source and target.

Setup (please complete the following information):

• pgstream version: v0.9.5
• Postgres version: 18.1
• Postgres environment: self-hosted

[dosubot]

The precision loss with large integers in json/jsonb fields during snapshot replication happens because pgstream relies on pgx's default handling, which uses Go's encoding/json package. By default, encoding/json unmarshals numbers into float64, so integers larger than 2^53-1 lose precision. This conversion happens before pgstream processes the data, as the snapshot logic fetches rows with pgx and passes the values as-is into WAL events, without special handling for json/jsonb types. There is currently no configuration or workaround in pgstream to preserve large integer precision in these fields—preserving precision would require reading JSON columns as raw bytes or customizing the JSON codec in pgx, which pgstream does not do today. A recent update to pgx (v5.8.0) included some fixes for numeric scanning and json/jsonb handling, but this does not address the core issue of float64 conversion for large integers in JSON fields citation.

If you need to avoid this precision loss, you would need to intercept the JSON data before it is unmarshalled into Go types, for example by reading json/jsonb columns as raw bytes and handling them with a custom decoder that preserves large integers as strings or json.Number. This would require changes to how pgstream or pgx handles these columns citation.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}