# Choosing the Right Database: Why There is No Best Database Szymon Kopyciński · 12 October 2026 Three databases, one task, which one do you use, and why? ## Introduction Your firm collects trades, quotes and order-book updates. Three researchers want to query several years of them. Which database should you choose? The first suggestion is to benchmark a query against DuckDB, ClickHouse and kdb+, then pick the fastest. But one query on a quiet machine leaves several questions unanswered. What happens while new data arrives? Can three researchers run expensive queries together? Who restores the system after a disk fails? Can you reproduce last month's backtest after correcting a feed error? The database needs to fit that whole workload. ## How much data are we talking about? Assume the firm retains trades, quotes and order-book updates for a selected instrument universe across several venues. It collects 100 million events per trading day, each containing approximately 200 bytes of logical data. These are illustrative numbers, not measured storage sizes or benchmarks. They describe the selected dataset, rather than complete market-wide coverage. An event here is a normalised stored record; 200 bytes is not its exchange wire-message size. $$ 100{,}000{,}000 \times 200 = 20{,}000{,}000{,}000\text{ bytes} $$ That is 20 GB per day in decimal units. At an assumed 250 trading days per year, it is 5 TB annually, or 15 TB over three years, before compression, indexes, replication and backups. A continuously traded market needs a different calendar. Over an illustrative eight-hour collection window, the average arrival rate is about 3,472 events per second. That is a modest average ingestion rate. It alone provides little justification for a complicated ingestion architecture. The retained history is substantial, however: three years contain 75 billion records, and a query may touch months of them. For this design exercise, also require capture to absorb a one-second burst at 100,000 events per second without losing data. That represents 20 MB per second of logical event data during the burst. This is an illustrative stress case, not a claimed exchange rate; representative feed traces should establish the actual burst sizes and durations. A system sized only for the daily average could accumulate a backlog precisely when the market becomes busy. The scope matters at each stage. If a feed handler receives a whole-venue feed before filtering to our selected instruments, its input requirement can be much larger than the archive's ingestion requirement. For context, [NYSE's capacity sheet](https://www.nyse.com/publicdocs/nyse/data/NYSE_Proprietary_Market_Data_Capacity.pdf), generated on 13 January 2026 for the preceding 30 days, reports NYSE Equities Integrated Feed bursts of 1.19 Gb/s over one second and 2.84 Gb/s over one millisecond. It includes refresh and retransmission streams and only the A-side feeds. These are bandwidth measurements, not daily event counts. Our assumed 200-byte record cannot turn them into exchange message rates, and our selected-data burst assumption does not size a full-feed receiver. The researchers mainly scan time ranges, filter instruments, aggregate trades and calculate features. They rarely edit individual market events. The infrastructure budget is modest, but volumes and query concurrency are expected to grow. This describes historical research infrastructure. It does not imply that a database query sits on the critical path of an ultra-low-latency strategy. ## Requirements before products Functionally, we need to capture events, identify their source, retrieve selected histories and reproduce datasets. Non-functional requirements concern freshness, query response time, concurrent access, recovery and cost. Some remain unspecified. Must researchers query the current second, or is a dataset published every few minutes sufficient? Does a full-year feature calculation need to finish in seconds, or can it run overnight? How much captured data may be lost after a machine failure? For an initial recommendation, assume five-minute research freshness is acceptable, long jobs may take minutes, and there is no production service depending on live analytical queries. Require acknowledged capture to survive a process restart; tolerate temporary research unavailability while restoring a failed machine from a separate copy. These are design assumptions to agree with the team, not consequences of “100 million events”. Correctness matters too. Preserve exchange sequence identifiers, receive timestamps, event timestamps and schema versions. Event time alone may not establish feed order. As [From Market Data to Order Book](/blog/quant-foundations-2627/how-market-data-becomes-an-orderbook) explains, missing or misapplied deltas can invalidate reconstructed state. Storing those errors efficiently does not fix them. ## Transactions and analysis do different work An order-management database might atomically record an order and reserve risk capacity. A researcher might calculate average traded volume across a billion rows. Both use tables, but they ask the storage system to do different things. *Transactional* workloads usually involve small reads and writes, constraints and coordinated state changes. *Analytical* workloads often read many rows but only a few columns, then reduce them into a smaller result. These are workload tendencies, not mutually exclusive database capabilities. Imagine each event has 20 fields. A volume query needs only instrument, timestamp and quantity. A row-oriented layout stores an event's fields together; a column-oriented layout groups values of the same field. Reading three columns can avoid reading the other seventeen, provided the engine and file layout support that access. Columnar execution can also operate on batches of values, reducing repeated per-row interpretation. Similar values often compress well: repeated instrument identifiers, or differences between nearby timestamps. Less data movement can outweigh decompression costs. The benefit depends on the columns, codecs and query; it is not a promised compression ratio. Fetching and updating one complete record is a different problem. Index maintenance, transaction coordination and write patterns may matter more than scan efficiency. ## Layout can matter more than the engine Suppose a query asks for one instrument between 09:00 and 09:30. Partitioning splits a dataset into separately manageable pieces, perhaps by date. It helps the engine exclude irrelevant pieces and makes retention easier. Sorting within pieces by instrument and time puts relevant values near one another. Metadata or indexes can then identify regions worth reading. Those mechanisms are related but distinct. An index is a way to locate or rule out data; a partition is a physical division; sorting determines locality. Sorting by instrument then time favours instrument-specific histories. Sorting primarily by time may better suit market-wide intervals. Too many small partitions or files introduce their own overhead. Reading thousands of tiny objects involves listing, opening and scheduling work before useful scanning begins. A layout with one file per message is a poor starting point, regardless of engine. Continuous collection also need not mean one database transaction per event. We can capture events continuously and write bounded batches. The batch interval trades freshness against ingestion overhead. Keep capture recoverable while batches are being formed; an in-memory buffer alone does not meet the restart requirement. ## Local analysis or a shared service? A central database gives researchers one query endpoint and a common view of incoming data. It also puts their queries in competition for CPU, memory and storage bandwidth. ```mermaid flowchart TD accTitle: Central analytical service accDescr: Capture is buffered durably before database insertion. Research queries and background merges compete for the service's resources. F[Feed handlers] --> C[Validated durable capture] C --> I[Batch inserter] I --> D[Analytical database] R[Three researchers] -->|Concurrent queries| D D --> M[Background storage work] D --> B[Separate backup storage] ``` A different design publishes immutable files and lets each researcher query them independently. Compute then lives with the researcher, though network access and shared storage can still become bottlenecks. ```mermaid flowchart TD accTitle: Published datasets with independent query engines accDescr: Finished Parquet objects become visible through a versioned manifest. Researchers pin that manifest and query without opening a shared writable database file. C[Durable event capture] --> W[Validate and write Parquet] W --> O[Immutable objects] W -->|Publish after objects complete| M[Versioned dataset manifest] M --> A[Researcher A: DuckDB] M --> B[Researcher B: DuckDB] M --> D[Researcher C: DuckDB] O --> A O --> B O --> D ``` The manifest is a list of completed objects and their dataset version. A reader pins one version rather than discovering an arbitrary mixture of files while publication is in progress. On a local filesystem, atomic replacement can publish that list; object storage needs its own safe publication mechanism. Neither design gets dataset consistency merely from using Parquet. ## DuckDB: where should the query run? DuckDB's embedded model lets a Python process run analytical SQL without operating a conventional database server. Researchers can query Parquet directly and use the results in their existing workflow. DuckDB supports pushing column selection and eligible filters into Parquet scans, so it need not load every field into Python first. File and row-group layout still influence how much it reads. See its [Parquet documentation](https://duckdb.org/docs/current/data/parquet/overview). For three researchers working on published histories, this is attractive. Each can choose their own compute resources. A large join can still exhaust memory or spill to storage, and downloading years of data to every laptop may be impractical. “Embedded” describes deployment, not unlimited capacity. Shared writes need a more precise discussion. In the direct native-file model, multiple writer threads can operate within one process; independent processes are not simply free to open that file for concurrent writing. All-read-only access is another supported mode. The [current concurrency documentation](https://duckdb.org/docs/current/connect/concurrency) also describes Quack, which mediates access through a server, and DuckLake, which coordinates shared data through a transactional catalogue such as PostgreSQL. As checked on 12 October 2026, that page labels Quack beta as of DuckDB 1.5.2 and describes DuckLake 1.0 as intended for production use. A forecast for Quack's maturity is not a release guarantee. Check the actual deployed version. These options change the architecture; the blanket statement “DuckDB cannot support multiple processes writing” is no longer adequate. A catalogue or server adds authentication, resource management and recovery responsibilities. DuckDB's local simplicity is a reason to consider it, not a reason to ignore those responsibilities when using it centrally. ## ClickHouse: make analytics a service ClickHouse is a column-oriented analytical database suited to central ingestion and queries. Its MergeTree family stores sorted data parts and merges them in the background. A sparse primary index identifies candidate granules, or groups of rows, rather than pointing to every matching row individually. It does not enforce uniqueness like a PostgreSQL primary key. These mechanics are documented in the [MergeTree reference](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree). For our instrument-and-time queries, `(instrument, event_time)` is a plausible sorting key to test. It is less helpful for some market-wide scans. Start with coarse date partitions for retention rather than a partition for every instrument. Skipping data requires the query to align with the layout. Small frequent inserts create parts and merge work. Client batching, or server-side asynchronous inserts, can reduce that pressure. With `wait_for_async_insert=1`, an asynchronous insert is acknowledged after a successful flush; with `0`, acknowledgement can precede storage and flush errors can be hidden. Those modes have different failure behaviour. See [asynchronous inserts](https://clickhouse.com/docs/concepts/features/operations/insert/asyncinserts). An acknowledgement still needs interpretation against the deployment's disk and replication settings. A service can admit concurrent queries, but it cannot give every researcher the whole machine simultaneously. Set memory limits, query timeouts and concurrency controls, and leave resources for ingestion and merges. Measure mixed workloads; a fast isolated query may become a slow shared query. Self-management means upgrades, monitoring, backups and recovery drills. Replication adds availability options and coordination, not immunity from accidental deletion. Managed hosting transfers some operating work into a service bill. ClickHouse and DuckDB's core licences are respectively [Apache 2.0](https://github.com/ClickHouse/ClickHouse/blob/master/LICENSE) and [MIT](https://github.com/duckdb/duckdb/blob/main/LICENSE); hosted products have separate terms. ## kdb+: useful operations, specialised expertise kdb+ combines a database with q, a language built around array and table operations. Its columnar historical layouts and time-series operations are relevant to market data. For example, an [as-of join](https://code.kx.com/q/ref/aj/) can associate each trade with the latest preceding quote for its instrument. That is useful, but the timestamp choice must still avoid including information unavailable at the simulated decision time. KX documents a [kdb+tick architecture](https://code.kx.com/q/architecture/) with a tickerplant logging and publishing updates, a real-time database holding current data, and a historical database serving partitioned history. A gateway can route queries across them. These are building blocks, not an architecture every firm must copy. The real-time memory requirement, end-of-day persistence and query routing all need design. q has [parallel processing facilities](https://code.kx.com/q/basics/peach/), so “kdb+ is single-threaded” is too broad; process layout, supported parallel operations and available resources determine concurrency. A team already fluent in q may build and maintain this efficiently. A Python-and-SQL team must include training, code review and support coverage in the cost. Compact syntax is not automatically a small maintenance burden. [Commercial kdb+ use](https://code.kx.com/q/learn/licensing/) is governed by the agreement with KX. Current documentation also offers KDB-X Community Edition for personal and commercial use, subject to [usage restrictions](https://code.kx.com/licensing/usage-restrictions.html), including RAM and deployment limits. Do not assume either that all commercial experimentation requires a paid edition or that a community licence permits the proposed production deployment. Obtain terms for that deployment; this article makes no price claim. ## PostgreSQL and Parquet still have jobs PostgreSQL is a reasonable choice when the dataset is smaller, queries are selective, or transactional updates and constraints dominate. Familiar operations can outweigh a scan-performance disadvantage. [B-tree indexes and BRIN summaries](https://www.postgresql.org/docs/18/indexes-types.html), alongside [partitioning](https://www.postgresql.org/docs/18/ddl-partitioning.html), offer useful ways to narrow reads. BRIN is particularly relevant when values correlate with physical row order. For years of event-wide analytical scans, a row-oriented store deserves careful measurement before becoming the default. It can still hold instrument metadata, access permissions and dataset catalogues without holding every tick. [Parquet](https://parquet.apache.org/docs/overview/) is a columnar file format, not a query engine or a complete transactional database. Several engines can read the same files. That separates durable historical storage from the compute used today. Someone must still manage schemas, publication, access control, corrections and retention. | Approach | Requirement which favours it | Responsibility to accept | | --- | --- | --- | | DuckDB over published Parquet | Independent research; modest freshness needs | Dataset publication and each reader's resources | | Shared DuckDB architecture | DuckDB workflows with coordinated access | Server or catalogue operations; version-specific maturity | | ClickHouse service | Fresh shared analytics alongside continuous ingestion | Layout, batching, resource limits and service recovery | | kdb+ architecture | Time-series workflows and existing q expertise | Process design, specialist support and licence terms | | PostgreSQL | Selective queries and transactional state | Index cost and scan suitability at the intended scale | ## What I would build first Under the five-minute freshness assumption, start with recoverable capture, validated Parquet publication and DuckDB for research. Keep a separate recoverable copy of capture and published datasets. Version corrected datasets rather than silently replacing the inputs of an old backtest. Partition by date initially, sort for the common instrument-and-time access pattern, and compact small files. The event count alone does not justify a dedicated analytical service. This recommendation buys independent research compute and fewer service operations, while sacrificing a single live query endpoint and accepting responsibility for dataset publication. Before committing, replay a representative busy session on the proposed hardware. Run an instrument-hour query, a multi-month aggregation and a substantial feature join, both alone and with all three researchers active. Record versions, layout, compression, hardware, cache state, bytes scanned, elapsed time, peak memory, ingestion lag and cost. Restart the writer mid-publication and verify readers still see a complete version. These are proposed tests, not benchmark results. If shared fresh queries become necessary, or duplicated scans and remote reads prove expensive, test ClickHouse next. It can serve fresh data while DuckDB reads versioned Parquet exports for reproducible research. That hybrid creates another pipeline: record export source ranges and versions, reconcile counts, and expose freshness. Two stores create disagreement opportunities as well as flexibility. ## Your turn The firm now collects one billion events daily. Twenty researchers query concurrently, and production services require fresh analytical data. Propose a revised architecture. Which assumptions changed: storage volume, freshness, resource isolation, or recovery requirements? Which component would you reconsider first? Explain whether you would introduce a shared query service, change the file layout, separate production compute, or do several of these. Name a measurement which would justify each change, and explain how you would keep historical research reproducible.