+++
title = "Transaction databases, tick stores and search indexes"
description = "Ledgers, tick stores and search indexes preserve different facts. Learn why one database rarely serves every financial workload."
date = 2026-09-11
draft = false
[taxonomies]
topics = ["relational-transactional-databases", "time-series-tick-stores", "document-key-value-graph-search", "core-ledgers-account-processing"]
kinds = ["explainer"]
[extra]
tier = "public"
schema_type = "Article"
article_class = "explainer"
related_concepts = ["transaction isolation", "system of record", "tick data", "search refresh"]
sources = ["https://www.postgresql.org/docs/current/transaction-iso.html", "https://www.mongodb.com/docs/manual/core/transactions/", "https://questdb.com/docs/concepts/designated-timestamp/", "https://clickhouse.com/docs/guides/clickhouse/working-with-joins", "https://www.elastic.co/docs/manage-data/data-store/near-real-time-search", "https://docs.python.org/3/library/decimal.html"]
source_details = [{ title = "Transaction Isolation", publisher = "PostgreSQL Global Development Group", url = "https://www.postgresql.org/docs/current/transaction-iso.html", checked = "2026-09-09" }, { title = "Transactions", publisher = "MongoDB", url = "https://www.mongodb.com/docs/manual/core/transactions/", checked = "2026-09-09" }, { title = "Designated timestamp", publisher = "QuestDB", url = "https://questdb.com/docs/concepts/designated-timestamp/", checked = "2026-09-09" }, { title = "Working with JOINs in ClickHouse", publisher = "ClickHouse", url = "https://clickhouse.com/docs/guides/clickhouse/working-with-joins", checked = "2026-09-09" }, { title = "Near real-time search", publisher = "Elastic", url = "https://www.elastic.co/docs/manage-data/data-store/near-real-time-search", checked = "2026-09-09" }, { title = "decimal — Decimal fixed-point and floating-point arithmetic", publisher = "Python Software Foundation", url = "https://docs.python.org/3/library/decimal.html", checked = "2026-09-09" }]
faq = [{ question = "Does a successful database transaction prove the balance is correct?", answer = "No. Atomic execution does not establish correct posting rules." }, { question = "Can one database support more than one role?", answer = "Yes. The roles describe required behavior, not a mandatory number of products." }]
evidence_as_of = "2026-09-09"
related_articles = ["exactly-once-financial-effects", "lakehouse-interoperability", "lineage-quality-reconciliation"]
category_slug = "software-models"
category_name = "Software & models"
+++

A financial database role is the kind of answer a store is responsible for returning, such as a committed account balance, a time-specific market observation or a searchable document match. Different roles can use overlapping data without having the same authority or visibility timing.

## Committed balances and transaction boundaries

A transactional database can support grouped changes and concurrency controls. The application still defines the financial operation those changes represent. Balanced postings, stable transaction identifiers, currency-aware amounts and reversal relationships belong to the ledger design.

A relational data model does not specify the complete isolation behavior. PostgreSQL supports configurable isolation levels; its documented default is Read Committed. A sequence of statements needs a deliberate transaction boundary if the business result depends on what concurrent operations can change between those statements.

Transactions are also available outside relational engines. MongoDB documents multi-document transactions. The useful comparison is the actual operation and its guarantees, not the assumption that only one database family supports transactions.

## Market observations and time-specific queries

A tick store organizes market observations for time-dependent retrieval. The schema needs to distinguish event time, receipt time and correction time. Selecting a designated timestamp makes a particular time axis available to queries; it does not make that axis correct for every financial question.

An as-of join associates a record with an observation at or before a selected timestamp. Joining a trade to the latest preceding quote answers a temporal question only if the timestamp and identifier relationship match the intended analysis.

## Search visibility and ledger authority

A search index supports retrieval over an indexed representation. Its visibility boundary can differ from the commit boundary of the source record. Elasticsearch, for example, documents a refresh boundary for search visibility.

Take a ledger posting committed at 10:00:00 and indexed at 10:00:02. During those two seconds, the authoritative balance includes the posting while search returns the earlier projection. A search miss during that interval does not establish that the posting failed. Retrying the financial instruction on that basis risks confusing delayed visibility with missing execution.

A controlled projection retains the source identifier, source version and processing position needed to compare it with authoritative state. Search freshness and ledger correctness are then separately observable properties.

## Assigning authority to database roles

One engine can serve several roles, and one financial application can use several engines. What must remain explicit is the output contract: which record is authoritative, what time the answer describes, how corrections propagate and how recovery reestablishes consistency. Duplicated data is interpretable when those contracts and reconciliations are defined.

## Questions about transaction isolation

### Does a successful database transaction prove the balance is correct?

No. Atomic execution does not establish correct posting rules.

### Can one database support more than one role?

Yes. The roles describe required behavior, not a mandatory number of products.