# Cómo archivar datos de investigación cuantitativa: PostgreSQL PITR y R2

> Canonical: https://vxi.me/es/posts/quant-research-data-archive-postgres-r2/
> Locale: es-es
> Published: 2026-10-08
> Updated: 2026-10-08
> Markdown: https://vxi.me/es/posts/quant-research-data-archive-postgres-r2.md

Si dentro de un año quieres volver a ejecutar una investigación cuantitativa hecha hoy, ¿qué necesitas poder recuperar exactamente?

Quizá el código de la estrategia siga en GitHub y la base de datos aún conserve un `run_id`. Pero el proveedor puede haber corregido las cotizaciones históricas y la respuesta original de su API puede haberse eliminado. Que el programa vuelva a ejecutarse no garantiza el mismo resultado. Para un sistema de investigación con Python y SQL, lo importante es poder reconstruir los datos, el código y la información que **realmente estaban disponibles cuando se tomó la decisión**.

Mi primera propuesta era sencilla: PostgreSQL almacena índices, señales y outcomes; los `raw_json` voluminosos pasan a Cloudflare R2; cuando el archivo se verifica, se reduce el contenido histórico de la base de datos. La parte complicada apareció después: ¿cuándo compensa mantener otro sistema?, ¿por qué crear un manifest antes de subir el objeto?, ¿y cómo demostrar que un estudio antiguo sigue siendo recuperable después de borrar sus datos originales?

Hice un pequeño experimento con la biblioteca estándar de Python, SQLite en memoria y un `MemoryObjectStore` simulado. Probé algunas condiciones que impiden borrar datos demasiado pronto. **No conecté PostgreSQL ni R2 reales, ni probé la concurrencia entre múltiples workers**. Conviene separar esos resultados de las decisiones de arquitectura basadas en documentación.

## Antes de añadir R2, decide si PostgreSQL basta

En un proyecto personal con un volumen moderado y pocas recuperaciones históricas, no empezaría gestionando PostgreSQL, R2, manifests y un worker de reconciliación al mismo tiempo. Primero tendría copias de seguridad, ejercicios de restauración reales y versiones registradas del código y los parámetros.

La alternativa que hay que comparar es [PostgreSQL PITR (Point-in-Time Recovery)](https://www.postgresql.org/docs/current/continuous-archiving.html). Un base backup y los WAL archivados permiten recuperar un clúster hasta un instante cubierto por la retención. Si la entrada de una investigación estaba en PG y los respaldos abarcan ese período, se puede localizar en un entorno restaurado e independiente.

El problema es la granularidad: PITR restaura principalmente un **clúster de base de datos**, mientras que el investigador pide «dame exactamente lo que leyó `run_id=35`». Restaurar una base antigua por un solo run, retener años de WAL y acoplarla con el código actual puede resultar más caro que recuperar un objeto inmutable. PITR tampoco conserva las dependencias de Python, los calendarios bursátiles ni las respuestas externas que nunca llegaron a PostgreSQL.

Las dos técnicas tienen tareas distintas: PITR protege ante desastres de base de datos; el archivo versionado por run aporta trazabilidad a una investigación concreta. Pueden convivir, pero el segundo no es obligatorio.

No usaría la regla arbitraria de «a partir de 100 GB hay que usar R2». Primero mediría heap, TOAST, índices, bytes diarios del payload, crecimiento de backups/WAL, frecuencia de consultas y objetivos de recuperación. Esta **consulta de solo lectura es una propuesta; no se ejecutó en el PostgreSQL real del proyecto**:

~~~sql
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;
~~~

Añadiría R2 solo cuando los datos fríos afecten de forma apreciable al mantenimiento y exista una necesidad real de restaurar runs individuales.

## TOAST y MVCC cambian el coste de recortar raw_json

PostgreSQL ya ofrece [TOAST](https://www.postgresql.org/docs/current/storage-toast.html). Cuando una fila resulta demasiado ancha —normalmente alrededor de un umbral de 2 KB— puede comprimir valores de longitud variable y moverlos fuera de la fila si hace falta. La documentación describe un puntero externo de 18 bytes en disco.

Eso **no significa** que todo campo JSONB mayor de 2 KB vaya automáticamente a una tabla TOAST. Depende del tamaño de la fila completa, de cuánto pueda comprimirse y de la estrategia de almacenamiento. Si las consultas habituales no recuperan `raw_json`, quizá TOAST ya esté haciendo parte del trabajo.

Lo que me hizo replantear el diseño anterior fue la escritura necesaria para eliminar payloads. Sustituir `raw_json` por un resumen sigue siendo un `UPDATE`. Con MVCC se crea una nueva versión de la fila, se genera WAL y habrá que limpiar versiones antiguas y, posiblemente, los datos TOAST correspondientes. Miles de actualizaciones históricas pueden causar presión de escritura, bloat y bloqueos. Tampoco sería correcto afirmar que cada UPDATE provoca necesariamente un bloat grave.

La [documentación de VACUUM](https://www.postgresql.org/docs/current/sql-vacuum.html) distingue espacio reutilizable **dentro de PostgreSQL** de espacio que vuelve al sistema operativo. `VACUUM` normal suele habilitar la reutilización; `VACUUM FULL` reescribe la tabla y puede devolver más espacio físico, pero exige bloqueos más fuertes. A veces también puede truncarse el extremo libre de un archivo. Ni «siempre se recupera disco» ni «nunca se recupera» son reglas válidas.

Si diseñara desde cero, evaluaría separar verticalmente los payloads:

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

signal_raw_payload
  snapshot_id | payload_version | raw_json | captured_at
~~~

Las consultas frecuentes trabajan con `signal_snapshot`; los originales permanecen en `signal_raw_payload`. Tras una verificación de archivo se limpia esta segunda tabla, evitando actualizaciones masivas en la tabla principal. Separarla no elimina los efectos MVCC de un `DELETE` masivo.

Si la retención coincide con meses o lotes completos, el particionado puede ser mejor. La [guía de PostgreSQL](https://www.postgresql.org/docs/current/ddl-partitioning.html) explica por qué retirar una partición vencida suele ser más eficiente que borrar sus filas una por una. Pero **todas las filas** de esa partición deben estar archivadas, verificadas y autorizadas para eliminación. No se puede hacer `DROP` de un mes entero porque la mayoría de sus registros ya haya expirado.

Para una base existente, primero mediría heap/TOAST, WAL, coste de consultas y bloqueos. Después decidiría entre aprovechar TOAST, dividir verticalmente o crear particiones alineadas con el ciclo de retención.

## Por qué manifest-first y no upload-first

Hay al menos dos secuencias legítimas:

~~~text
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
~~~

Con upload-first no aparece un registro `PENDING` apuntando a un objeto que todavía no existe. El riesgo se mueve: si el PUT tiene éxito y el proceso falla antes del commit de PG, queda un objeto huérfano. Para recuperarlo hacen falta convenciones de claves, propiedad y reglas de limpieza.

En el prototipo offline elegí **manifest-first** porque quería registrar una intención de archivo consultable antes de empezar. `run_id / revision / expected_sha256` se fija al principio. Ante un fallo, el reconciliador sabe qué contenido debía existir. Es una decisión de diseño, no una transacción distribuida, y un registro `PENDING` no actúa automáticamente como mutex.

La decisión provocó incluso un fallo real en el código simulado. Cuando el upload se denegaba, el manifest quedaba `PENDING` y el objeto no existía. La primera reconciliación solo intentaba verificar objetos ya presentes y no podía reparar esa situación.

La corregí para reconstruir los bytes exclusivamente desde la fuente intacta, y repetir el upload solo si el SHA-256 y la longitud coincidían con el manifest original:

~~~python
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)
~~~

Ese camino se ejecutó en la prueba sintética. **No describe un incidente en producción.**

En un sistema real con varios workers hacen falta leases y fencing tokens. Imagina que A obtiene el lease y queda bloqueado en la red; el lease expira y B toma el control. Si A despierta, ya no puede confirmar un estado con su permiso antiguo. Un `UPDATE ... RETURNING` condicional en PG puede adquirir el trabajo mientras aumenta `lease_epoch`. Este SQL es **una propuesta todavía no ejecutada en PostgreSQL**:

~~~sql
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;
~~~

No mantendría abierta una transacción PG durante PUT/GET remotos. Tras validar, el worker solo podrá actualizar su estado si coinciden `lease_owner + lease_epoch`. Los cinco minutos son ilustrativos, no una recomendación universal.

El lease de PG tampoco detiene un PUT remoto que el worker antiguo ya envió. Siguen haciendo falta claves inmutables basadas en contenido, escritura condicional que impida sobrescribir una identidad con bytes distintos y reconciliación ante conflictos. No se ha validado este comportamiento con workers concurrentes o particiones de red reales.

### Versiones en R2, ETag y metadatos

La [tabla de compatibilidad S3 de R2](https://developers.cloudflare.com/r2/api/s3/api/) incluye `PutObject`, `HeadObject` y `GetObject`, pero marca `GetBucketVersioning` y `PutBucketVersioning` como no implementados. No conviene suponer que R2 tiene el historial de versiones nativo de los buckets de AWS S3.

Utilizaría claves inmutables explícitas como `raw/v1/run=35/revision=2/sha256=...jsonl.gz`. Una revisión genera una clave nueva; no sobrescribe en silencio la anterior. Hay que coordinar [Bucket Locks](https://developers.cloudflare.com/r2/buckets/bucket-locks/) y [reglas de ciclo de vida](https://developers.cloudflare.com/r2/buckets/object-lifecycles/) con el período de retención. Bucket Locks de R2 no equivale a compatibilidad completa con la API S3 Object Lock.

Las [extensiones S3 de R2](https://developers.cloudflare.com/r2/api/s3/extensions/) explican el mapeo de `x-amz-meta-*` a metadatos personalizados. Guardar allí el SHA-256 del archivo comprimido, el número de filas y la versión del schema permite una comparación barata mediante `HEAD`. El siguiente fragmento es **un ejemplo de boto3 para PutObject sencillo, no probado contra un R2 real**:

~~~python
# 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
~~~

El SHA devuelto por `HEAD` es una declaración de quien subió el archivo. **No verifica por sí mismo su contenido**. Antes de autorizar el pruning hace falta descargarlo con `GET`, recalcular el SHA-256 de los bytes comprimidos, descomprimir, validar schema, filas e identidades. En el experimento offline provoqué un `HEAD` aparentemente correcto con datos corruptos en `GET`; la eliminación fue rechazada.

Un ETag multipart tampoco equivale al SHA-256 del objeto completo. No hay que extrapolar sin pruebas las condiciones de un `PutObject` sencillo a `CompleteMultipartUpload`. Las decisiones del SDK sobre división, cancelación, reintentos y sobrescritura se deben ensayar en un bucket R2 autorizado.

## Una corrección de cotizaciones introduce dos tiempos diferentes

Conservar bytes antiguos no basta: un backtest necesita saber **qué versión estaba disponible en el momento exacto de decidir**.

Es parte del modelado bitemporal. El event/valid time relaciona la cotización con una sesión del mercado. `observed_at` o `ingested_at` indica cuándo el sistema de investigación recibió realmente una revisión. Conviene guardar además `source_published_at` del proveedor: que el proveedor haya publicado un dato no significa que tu sistema lo hubiera recibido.

En el ejemplo sintético, el cierre 100 de una fecha de mercado se observó por primera vez el 2 de octubre; la corrección a 104 llegó el 5 de octubre:

| as_of (UTC) | Cierre visible |
| --- | ---: |
| 2026-10-01 09:00 | Aún no había datos |
| 2026-10-03 00:00 | 100 |
| 2026-10-06 00:00 | 104 |

La prueba comprueba únicamente que el selector offline no filtra la corrección futura a una lectura anterior. No resuelve revocaciones de proveedores, prioridad de correcciones, ajustes por splits, valores excluidos del mercado ni cambios de componentes de índices.

Una API histórica debería exigir `as_of_utc`, `dataset_revision` o un `snapshot_id` inmutable. Sin cutoff, mejor rechazar el replay que recurrir silenciosamente a la versión más reciente. Para pantallas en tiempo real puede existir una vista latest separada. Si dos versiones compiten por el mismo evento y tiempo de observación, hay que aplicar secuencias y reglas del proveedor o ponerlas en cuarentena; «el último INSERT gana» no es un criterio suficiente.

Guardar todo en UTC tampoco resuelve por sí solo el horario bursátil. La sesión ordinaria de EE. UU. termina a las 16:00 hora local, pero `America/New_York` alterna EDT y EST. Un cierre UTC fijo sería incorrecto durante parte del año. Es mejor derivar cada `trading_session` y cada cutoff de decisión de un calendario bursátil versionado y una zona IANA, convertir después a UTC y comprobar el watermark real de ingestión.

Si el modelo decide a las 16:00:00 y el cierre oficial llega a las 16:00:02, ese dato aún no debería estar disponible para la primera decisión. Los cierres anticipados, suspensiones y correcciones fuera de horario también importan. Ajustes corporativos, factores de ajuste y composición histórica de índices requieren versiones propias. La anterior guía de [validación de factores](/posts/quant-factor-validation-bucket-spread-rank-ic/) trataba esta frontera desde los snapshots de señal.

## Pasar 28 tests offline no equivale a reproducir un año de investigación

Ejecuté **28 casos sintéticos de regresión offline y los 28 pasaron**. Cubren archivos duplicados, errores de upload inyectados, rechazo del pruning por objetos ausentes o corruptos, recuperación de `PENDING`, guardas de retención/confirmación y pequeñas correcciones `as_of`. Lo que prueban son las ramas que yo implementé y los fallos que yo introduje, no particiones de red, locks reales de PostgreSQL, carreras entre workers ni abortos multipart de R2.

La comparación `13.0 → 13.0 → 13.0` usando dos valores ficticios, 10.5 y 15.5, es solo un smoke test de gzip, hash y recuperación sencilla. No demuestra la reproducibilidad de un backtest complejo ni ahorro de almacenamiento.

Este fue el final del registro de ejecución original:

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

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

El fixture completo sigue en un repositorio privado de Content Lab. No voy a enlazarlo como si fuera un proyecto público y accesible.

Falta algo más importante: un `reproduction_manifest` completo. El ejemplo registra el digest del objeto, schema, función de prueba, parámetros, versión de Python y UTC. **No** he restaurado datos reales de PostgreSQL en otra máquina, reconstruido dependencias, recuperado un commit histórico del código, fijado el calendario bursátil y ejecutado toda la cadena de factor/outcome. La promesa del inicio —volver a ejecutar exactamente el mismo estudio dentro de un año— sigue sin demostrarse.

Un criterio de aceptación serio debería:

1. Congelar la versión de los datos originales, SHA-256 del objeto, revisión del proveedor/`as_of` y snapshot de señal de aquella ejecución.
2. Fijar el commit Git de la estrategia, hash de configuración, lockfile o digest del contenedor, versión del calendario, zona horaria y definición de outcome.
3. Probar en PostgreSQL aislado transacciones reales, TOAST, leases concurrentes y borrado controlado; usar un bucket R2 de pruebas autorizado para PUT sencillo/multipart, interrupciones, escrituras condicionales y `GET` completo.
4. Restaurar el run en un entorno limpio, volver a ejecutar el código congelado y comparar señales, decisiones y outcomes de forma explícita. Un exit code 0 no basta.

Hasta verificar de verdad el camino de restauración PG/R2 y la lectura desde la aplicación, mantendría desactivado el pruning automático de `raw_json` en producción. Es mucho más fácil pagar hoy por algunos bytes adicionales que descubrir meses después que ya no puedes explicar con qué datos tomaste una decisión antigua.

## Verification context

- Level: first-party
- Verified: 2026-10-08T00:00:00.000Z
- Environment: Python 3.13.5, sqlite3 in-memory
- Environment: Synthetic MemoryObjectStore
- Environment: 28 offline state-machine regression cases
- Evidence mode: mixed
- Evidence receipt: https://vxi.me/evidence/quant-research-data-archive-postgres-r2.json