If you want to rerun a quantitative research job a year from now, what exactly must be recoverable?

You may still have the strategy in GitHub and a run_id in the research database. But your market-data vendor may have revised historical bars, and the original API response may already be gone. A successful rerun can then produce a different answer. For a small Python/SQL research system, I care about preserving the actual combination of inputs, code, and information availability behind a decision.

My early design kept signals, indexes, and outcomes in PostgreSQL, moved large raw payloads into Cloudflare R2, and eventually pruned older raw_json. The difficult questions were elsewhere: when does this extra system earn its cost, why write an archive manifest before uploading the object, and what would prove that a restored research run still sees the same past?

I ran a small Python standard-library experiment with in-memory SQLite and a simulated MemoryObjectStore. It exercised some safeguards against unsafe pruning. It did not test real PostgreSQL/R2 failure modes or multi-worker concurrency. The implementation and limitations below are deliberately separate from the architecture decisions supported by the vendor documentation.

When PostgreSQL alone is enough

For a personal research project with modest data growth and few historical replays, I would start with PostgreSQL, reliable backups, actual restore drills, and recorded code/configuration versions. Maintaining PG, R2, a manifest protocol, and a reconciliation worker can be needless complexity when all the inputs still fit comfortably in the database.

The obvious alternative deserves serious attention: PostgreSQL point-in-time recovery (PITR). A base backup plus retained WAL can restore a database cluster to a covered point in time. If a research run’s inputs were inside PG and backup retention spans that period, you can recover them into a separate instance.

But PITR mostly recovers a database cluster, while I want to ask for the raw input associated with run_id=35. Restoring an old cluster for one experiment, maintaining years of WAL, and reconciling an older database with today’s research code can be much heavier than retrieving one immutable artifact. PITR also does not capture Python dependencies, exchange calendars, or external API responses that were never stored in PG.

I treat these as complementary jobs: PITR is for database disaster recovery; a versioned per-run archive is for research provenance. If the application has no need to restore individual historical inputs, it may not need the second one at all.

I would measure the growth curve before picking a threshold such as “use R2 after 100 GB.” PostgreSQL has built-in relation-size functions. This is a proposed read-only query; it has not been executed against the project’s PostgreSQL database:

SELECT
  pg_size_pretty(pg_relation_size('quant_research.signal_snapshot')) AS heap,
  pg_size_pretty(pg_table_size('quant_research.signal_snapshot')) AS table_including_toast,
  pg_size_pretty(pg_total_relation_size('quant_research.signal_snapshot')) AS total_with_indexes;

Combine these measurements with incoming raw-payload bytes per day, required retention, actual query frequency, backup/WAL growth, and restore-time requirements. Cold storage becomes worth considering when it meaningfully reduces database maintenance and individual-run recovery is a real requirement—not because a diagram looks more scalable.

TOAST and MVCC change the pruning decision

PostgreSQL already has TOAST for large values. When a tuple becomes too wide—typically around a 2 KB threshold—it may compress eligible values and, when needed, move them out of line. The on-disk external pointer is described as 18 bytes.

That does not mean every JSONB value above 2 KB is automatically stored outside the heap. It depends on the complete tuple, compression, and the column’s storage strategy. If regular queries do not load raw_json, TOAST may already be doing much of the heavy lifting. Field size alone is a weak reason to split a table.

My concern with the original design was the update path. Replacing raw_json with a summary is still a PostgreSQL UPDATE: MVCC creates a new row version, WAL is written, and old row versions—including possibly associated TOAST data—need eventual cleanup. Doing this across many historical rows can increase write pressure and bloat. It is not correct to say every update necessarily causes severe bloat or that a physical byte can never be reclaimed.

The VACUUM documentation distinguishes reusable space inside PostgreSQL from space returned to the operating system. Ordinary VACUUM generally enables reuse; VACUUM FULL rewrites the relation and can return more space, but requires stronger locks and more disruptive maintenance. Some tail truncation is possible, so “disk usage never changes” is equally inaccurate.

For a new system, I would consider vertical separation from day one:

signal_snapshot
  snapshot_id | run_id | signal_time | score | raw_hash | archive_key

signal_raw_payload
  snapshot_id | payload_version | raw_json | captured_at

Keep frequently queried signal fields in signal_snapshot. Store full raw data in signal_raw_payload and archive or clean only that table. Splitting the table isolates write pressure; it does not make mass DELETE exempt from MVCC or VACUUM.

If payload retention follows whole months or research batches, partitions could provide a stronger cleanup boundary. PostgreSQL’s partitioning guide explains why removing an entire expired partition is generally preferable to row-by-row deletion. But the entire partition must first qualify for deletion: every included run needs verified archival, the appropriate retention period, and deletion authorization. A single ineligible row blocks a whole-partition drop.

For an existing large table, I would not migrate immediately. First inspect heap/TOAST growth, WAL, lock behavior, and query plans; then choose between leaving TOAST alone, vertical partitioning, or partitioning by deletion lifecycle.

Why I chose manifest-first rather than upload-first

Both sequences are defensible:

Upload-first:
package → PUT object → GET/verify → commit PG manifest

Manifest-first:
commit PENDING manifest + expected digest → PUT object
→ GET/verify → mark VERIFIED in PG

Upload-first avoids a PENDING record pointing to a missing object. Its failure window is different: the upload succeeds, the process crashes before the PG insert, and the object becomes an orphan. Recovering it requires object naming, ownership, and garbage-collection rules.

For the offline prototype I chose manifest-first to persist an identifiable, searchable archive intention before any remote work. run_id / revision / expected_sha256 is fixed first. If uploading fails, reconciliation can find the intended content and track failures. It is a tradeoff, not a distributed transaction. A PENDING row by itself is not a distributed mutex.

The choice produced a real gap in the synthetic code. If an upload was denied, the manifest remained PENDING and the object did not exist. My original reconciliation path checked only for an existing remote object, so it could never recover that case. I changed it to repackage only intact original data and re-upload only if its byte length and SHA-256 still match the initial manifest:

recovered = pack(self._source_rows(manifest["run_id"]))
if digest(recovered) != manifest["sha256"] or len(recovered) != manifest["byte_size"]:
    raise ArchiveError("pending manifest source changed; cannot re-upload")
self.store.put_if_absent(manifest["object_key"], recovered)

That fix was executed in the synthetic experiment. It is not a claim about a production outage.

Real multi-worker deployments need another mechanism. A conditional PG UPDATE ... RETURNING can assign a lease, but an expired worker may wake after a successor has taken over. So the state also needs an increasing lease_epoch, a fencing token. The following SQL is a production design sketch, not code I tested in PostgreSQL:

UPDATE archive_manifest
SET lease_owner = $2,
    lease_epoch = lease_epoch + 1,
    locked_until = clock_timestamp() + interval '5 minutes',
    status = 'RECONCILING'
WHERE identity = $1
  AND status IN ('PENDING', 'RECONCILING')
  AND (locked_until IS NULL OR locked_until < clock_timestamp())
RETURNING lease_epoch, expected_sha256, object_key;

Do not hold a PG transaction open during a remote PUT/GET. After validation, a worker must complete its state transition using the exact lease_owner + lease_epoch it acquired. A stale epoch cannot commit. Five minutes is an illustrative lease duration, not a recommended production default.

The lease protects PG state, not remote writes already in flight. You still need content-addressed immutable keys, conditional creation that prevents different bytes from overwriting the same identity, and reconciliation after conflicts. If the source changed or was pruned before successful recovery, the safest answer may be manual intervention.

R2 object identity, ETag, and metadata

Cloudflare’s current R2 S3 compatibility table supports common object operations such as PutObject, HeadObject, and GetObject, but lists GetBucketVersioning and PutBucketVersioning as unimplemented. Assuming native S3 bucket version history exists here would be a design mistake.

I would therefore use explicit immutable keys such as raw/v1/run=35/revision=2/sha256=...jsonl.gz. A new payload gets a new revision and hash, never a silent overwrite. Coordinate Bucket Locks and object lifecycle rules with research retention; neither makes R2 fully equivalent to AWS S3 Object Lock.

Custom metadata can make cheap reconciliation easier. Cloudflare’s R2 S3 extensions describe the mapping of x-amz-meta-* to object metadata. This is an illustrative single-object boto3 call, not a tested live R2 integration:

# gzip_bytes and expected_sha256 must refer to the same immutable input
s3.put_object(
    Bucket=bucket,
    Key=object_key,
    Body=gzip_bytes,
    Metadata={
        "sha256": expected_sha256,
        "row-count": str(row_count),
        "schema-version": "1",
    },
    IfNoneMatch="*",
)
head = s3.head_object(Bucket=bucket, Key=object_key)
assert head["Metadata"]["sha256"] == expected_sha256

A successful HEAD metadata comparison means only that the declared metadata matches the manifest. It does not independently verify the object bytes. Before permitting source pruning, download the actual compressed object, recompute SHA-256, decompress it, and verify schema, row count, and record identity. The synthetic fixture intentionally returned a successful HEAD with corrupt GET data; its pruning guard refused the operation.

Multipart uploads need their own acceptance tests. Multipart ETags are not full-object SHA-256 values, and it would be unsafe to assume single-PutObject conditional semantics automatically apply to CompleteMultipartUpload. Test the actual SDK transfer thresholds, abort/retry behavior, and overwrite rules on an authorized R2 sandbox before enabling cleanup.

Market-data corrections are a temporal database problem

Storing old bytes is necessary; it does not tell a backtest which bytes were already available when the decision was made.

This is part of bitemporal modeling. Event/valid time identifies the market session or bar. observed_at and ingested_at identify when the research system actually saw a particular revision. Preserve the vendor’s own source_published_at separately; a vendor publishing a revision is not proof your service had received it.

The synthetic fixture stored two immutable versions for the same trading date: close 100 first observed on October 2, then a corrected 104 observed on October 5. The resulting point-in-time reads were:

as_of (UTC)	Visible close
2026-10-01 09:00	No observation yet
2026-10-03 00:00	100
2026-10-06 00:00	104

This confirmed a narrow property of the offline selector: a later correction did not leak into an earlier read. It says nothing about vendor dispute resolution, retractions, corporate actions, delisted securities, or historical index membership.

In an actual research API, a historical replay should require an explicit as_of_utc, dataset_revision, or immutable snapshot_id. A request missing the cutoff should be rejected, not quietly routed to today’s newest data. A separate latest view is useful for live dashboards. When two revisions conflict at the same event and observation time, apply vendor sequence/authority rules or quarantine them rather than treating the last inserted row as truth.

UTC storage alone does not solve trading-session cutoffs. US regular-session close is 16:00 in the exchange’s local clock; America/New_York switches between EDT and EST. A constant UTC close time therefore fails part of the year. Construct the actual trading_session and decision cutoff from a versioned exchange calendar and IANA time zone, then convert to an aware UTC timestamp. Use the actual ingestion watermark to determine what information had arrived by that cutoff.

If a closing-bar decision is made at 16:00 but the official close arrives at 16:00:02, the model at 16:00:00 must not borrow it. Early closes, trading halts, and post-market corrections make this distinction practical rather than academic. Split adjustment-factor, constituent-history, corporate-action, and price revisions into their own versioned inputs where needed. The earlier factor validation article addresses the same signal-time snapshot boundary from the evaluation side.

The offline tests are not a one-year replay

I ran 28 offline synthetic regression cases; all 28 passed. They cover duplicate archives, injected upload failure, refusal to prune when an object is missing or corrupt, PENDING repair, retention/confirmation guards, and simple as_of corrections. They test the branches I implemented and the errors I injected—not network partitions, live PostgreSQL locking, worker races, R2 multipart aborts, or a real research service.

The two-row 13.0 → 13.0 → 13.0 calculation is just a gzip/hash/recovery smoke test. Values 10.5 and 15.5 yield the same arithmetic mean before simulated pruning, from the archived object, and after restoration. That neither demonstrates sophisticated backtest reproducibility nor measures disk savings.

The original test log reported:

$ python3 verify.py --output results
Ran 28 tests in 0.017s

OK
CASE_SUMMARY {"ran": 28, "passes": 28, "failures": 0, "errors": 0}

The underlying fixture is in a private Content Lab research repository, not an open public project. I will not present an inaccessible private URL as a reproducibility link.

More important, the experiment’s reproduction_manifest is incomplete for real research: it records the toy object digest, schema, example function, parameters, Python version, and UTC, but it did not check out an old strategy commit on a new machine, rebuild dependencies, recover a historical PostgreSQL dataset, load a pinned trading calendar, and replay a real factor/outcome pipeline. The opening one-year replay promise remains unproven.

A proper integration acceptance run would do four things:

1. Freeze the raw data version, archive SHA-256, vendor revision/as_of, and the original signal snapshot.
2. Pin the strategy Git commit, configuration hash, dependency lock or container digest, exchange calendar, time zone, and outcome definition. Missing inputs define a narrower replay guarantee.
3. Use an isolated PostgreSQL instance to exercise actual transactions, TOAST, concurrent leases, and controlled deletion; use an authorized R2 sandbox for single-part and multipart uploads, interruptions, conditional writes, and full GET integrity checks.
4. Restore an archived completed run into a clean environment, rerun the frozen research code, and compare signals, decisions, and outcomes—not merely process exit codes. Investigate differences as data, code, or environment drift.

That is the meaningful acceptance criterion. Until the real PG/R2 restore path and application reads have passed it, I would leave automatic production raw_json pruning disabled. The painful failure is not storing some extra bytes today. It is discovering months after a successful cleanup that the inputs behind an old decision can no longer be explained.