Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/theonetrade/backtest-kit-redis-postgres-pgpool-docker/llms.txt

Use this file to discover all available pages before exploring further.

backtest-kit ships with a hard write durability contract: after writeXData(...) returns, the very next readXData(...) call for the same context key must see the just-written value. The default file-based persistence satisfies this trivially — fs.writeFile + fs.readFile on a local filesystem are serialized by the OS. Replacing that with a PostgreSQL database introduces subtleties that, if ignored, silently corrupt strategy state in production.

​
Why Naïve SQL Fails

The obvious SQL translation of “write if not exists, update if exists” is a two-step read-then-write: 
// ❌ WRONG — race condition under concurrent access
const existing = await repo.findOne({ where: { symbol, strategyName, exchangeName } });
if (existing) {
  await repo.update(existing.id, { payload });
} else {
  await repo.save(repo.create({ symbol, strategyName, exchangeName, payload }));
}
Under concurrent access, two parallel writers can both see “no existing row” (both read null), both attempt INSERT, and the second one crashes with a unique-constraint violation. The backtest-kit framework catches that error, re-fetches from the exchange, and retries the write — but it may loop forever or silently record a stale value, depending on the retry logic. Even without concurrency, a follow-up SELECT after a committed INSERT can be routed to a read replica that has not yet received the commit, returning a stale row.

​
The Solution: One Atomic Round-Trip

Every upsert in this project executes a single INSERT … ON CONFLICT … DO UPDATE … RETURNING * statement. No read, no separate update — one statement, one write transaction, one primary. 
// src/lib/services/db/SignalDbService.ts
public upsert = async (
  symbol: string,
  strategyName: string,
  exchangeName: string,
  payload: ISignalRow | null,
): Promise<void> => {
  const repo = await this.repo<ISignalRowDoc>();
  const { raw } = await repo
    .createQueryBuilder()
    .insert()
    .values({ symbol, strategyName, exchangeName, payload })
    .orUpdate(["payload"], ["symbol", "strategyName", "exchangeName"])  // conflict target = unique index
    .returning("*")
    .execute();
  const result = raw[0] as ISignalRowDoc;
  await this.signalCacheService.setSignalId(result);  // Redis: ctx-key → id
};

Conflict target = unique index

Every table has a unique compound index whose columns exactly match the adapter’s context key fields (e.g. signal_items_uq on (symbol, strategyName, exchangeName)). PostgreSQL serializes concurrent inserts on that key at the storage-engine level — the loser of the race takes the DO UPDATE branch instead of throwing, so no unique-constraint violation ever reaches the application.

RETURNING * seeds Redis immediately

The just-written row is returned by the same statement. Its id is fed to the Redis cache synchronously, so the next findByContext call is O(1) — and the cache is seeded from the primary’s committed data, never from a follow-up SELECT that could land on a lagging replica.

DO UPDATE SET payload = EXCLUDED.payload

Subsequent writes to the same context key are real updates. The EXCLUDED pseudo-table holds the values from the attempted insert, so payload = EXCLUDED.payload overwrites the stored value with the new one. No lost-update window exists.

uuid PKs via gen_random_uuid()

TypeORM generates UUIDs server-side (generated: "uuid") and createDate / updatedDate columns are applied automatically on insert. No application-side ID or timestamp bookkeeping is needed.

​
Candle Immutability Exception

OHLCV candles are write-once data. Once a candle for a given (symbol, interval, timestamp) triplet exists, its price and volume columns must never be overwritten — market data is immutable. The CandleDbService therefore uses a no-op DO UPDATE that rewrites only the natural-key column to its own EXCLUDED value: 
// src/lib/services/db/CandleDbService.ts
const { raw } = await repo
  .createQueryBuilder()
  .insert()
  .values({
    symbol: dto.symbol,
    interval: dto.interval,
    timestamp: dto.timestamp,
    exchangeName: EXCHANGE_NAME,
    open: dto.open,
    high: dto.high,
    low: dto.low,
    close: dto.close,
    volume: dto.volume,
  })
  .orUpdate(["symbol"], ["symbol", "interval", "timestamp"])  // no-op: OHLCV columns untouched
  .returning("*")
  .execute();
The DO UPDATE SET symbol = EXCLUDED.symbol writes are effectively no-ops on the OHLCV data, but they still produce a row via RETURNING *. This is critical: ON CONFLICT DO NOTHING cannot RETURNING the conflicting row without a separate SELECT, and that separate SELECT might be routed to an async replica with replication lag — breaking the read-after-write invariant. The no-op update keeps everything in one primary-side statement.
The pattern DO UPDATE SET symbol = EXCLUDED.symbol is a PostgreSQL idiom for “upsert that always returns the row but never changes data columns.” It satisfies RETURNING without touching OHLCV values.

​
Soft-Delete Atomic Pattern

For adapters that support logical deletion — Measure (LLM response cache), Interval (once-per-interval markers), and Memory (per-signal memory store) — rows are never physically deleted. Instead, a removed: boolean column is flipped and a jsonb_set call embeds the flag inside the payload document simultaneously. Both happen in a single server-side UPDATE: 
// src/lib/services/db/IntervalDbService.ts
public softRemove = async (bucket: string, entryKey: string): Promise<void> => {
  const repo = await this.repo<IIntervalRow>();
  const { raw } = await repo
    .createQueryBuilder()
    .update()
    .set({
      removed: true,
      payload: () => `jsonb_set("payload", '{removed}', 'true')`,  // server-side, in-place
    })
    .where({ bucket, entryKey })
    .returning("*")
    .execute();
  const saved = raw[0] as IIntervalRow | undefined;
  if (!saved) {
    return;  // row did not exist — early return, no error
  }
  await this.intervalCacheService.setIntervalId(saved);  // cache updated to reflect tombstone
};
The value is computed on the server (jsonb_set) inside one UPDATE statement. There is no SELECT-then-save window where a concurrent upsert could overwrite the removed flag or lose the payload update. listKeys filters tombstones with removed = false so they are invisible to reads after deletion.
MeasureDbService.softRemove follows the same pattern as IntervalDbService.softRemove — the only difference is the table and cache service names. MemoryDbService.softRemove is identical in structure.

​
The Single-Node Atomicity Illusion

There is a subtle trap that only surfaces on a cluster. A lone PostgreSQL instance is one process: all concurrency is arbitrated internally by row locks and MVCC — effectively “atomicity through one global mutex”. On such a node, even a sloppy write followed by a separate SELECT appears correct, because that SELECT hits the very same process that just committed. Add read replicas and the illusion breaks. Writes go to the primary, but reads are load-balanced onto asynchronous replicas that lag behind by milliseconds. A write + follow-up SELECT can be routed to a replica that has not yet received the commit, returning a stale value or even relation does not exist right after schema creation — silently violating the read-after-write contract. Code that passed every test on a single node corrupts state in production.
This is the most common mistake when porting a single-node app to a replicated cluster. If your DbService does anything like await upsert(...) followed by await findByContext(...) as two separate statements, you have this bug. The patterns on this page eliminate it entirely.
This is precisely why the two patterns above never issue a follow-up read: the written row comes back in the same statement via RETURNING, and the Redis cache is seeded from it. It is also why the dev environment (docker/pgpool) runs a real cluster with two replicas rather than a single Postgres container — so any accidental read-after-write dependency is caught in development, not in production.
1

Write committed on primary

INSERT … ON CONFLICT … DO UPDATE executes on the primary. The row is committed and returned by RETURNING * in the same round-trip.
2

Redis seeded from primary result

cacheService.setXId(result) stores context-key → uuid in Redis immediately. No replica involved at any point in the write path.
3

Read resolves from Redis + PK lookup

findByContext reads the UUID from Redis (O(1)), then fetches the full row by primary key. Even if the PK lookup lands on a replica, UUIDs are stable — the row exists on all nodes within milliseconds.

Build docs developers (and LLMs) love

Get started for freeTalk to us