Choosing a Database by Workload: SQLite, PostgreSQL, ClickHouse, and CrateDB

Rows of servers in a data center
Photo by imgix on Unsplash. Visual editorial reference, not a verified screenshot of the software discussed. · View original image

A system starts with a product table and ends with processing orders, events, metrics and search queries. It’s tempting to solve growth by changing “storage engine,” as if SQLite, PostgreSQL, ClickHouse and CrateDB were four interchangeable implementations of the same piece. They are not: database management systems (DBMS) with architecture, matching models, guarantees and different operation costs. ClickHouse also calls * table engines * dedicated components of its own product, for example MergeTree.

The useful choice starts with a less viscous question: what operations dominate, how invariants cannot be broken and who will operate the system? An optimal index for a PostgreSQL consultation does not automatically transform that DBMS into a columnar analytic base. Conversely, that a system quickly processes aggregations doesn’t mean that it’s the right place to confirm a transfer that requires several atomic textures.

This comparison uses revised official documentation in November 2026: PostgreSQL 18, SQLite documentation, ClickHouse MergeTree and CrateDB 6.5. Performance figures that can be found in manufacturers’ marketing are not moved here as independent benchmarks: the proposed comparative laboratory did not run.

Original conceptual illustration accompanying Choosing a Database by Workload: SQLite, PostgreSQL, ClickHouse, and CrateDB; not a screenshot.
Original conceptual illustration of this article's topic; not a screenshot or measured result.

First defines how work works

For an application with products, orders, users and browsing events, three families with access should be separated:

  • Transactional operations: update stocks, create an order and register their lines, resolve a conflict of concurrence, enforce foreign and unique keys.
  • Analytical reading: add million events, compute trends by hour and dimension, scans about few columns and long periods.
  • Local or embedded access: persisting status with a device or process, run consultations without managing an independent server, explicitly synchronize with other copies as appropriate.

They’re profiles, and they’re not mutually exclusive. PostgreSQL can run aggregations and ClickHouse can get updates; the distinction is what architecture prioritised and what commitments appear when the load changes. The size also doesn’t decide by itself: ten gigabytes with many concurrent transactions and ten gigabytes of events consulted in batches require different strategies.

Before selecting a DBMS write down a small contract: write frequency, number of competing writers, required consistency, dominant wants, data distribution, tolerable latency, malfunction recovery, regions, operation costs and equipment experience. “Millions of rows” does not suffice as a specification.

SQLite: A library and a file are not an incomplete prototype

SQLite integrates into the application process and keeps its base in a file - without a SQL network server as a required component. For mobile and desktop applications, utilities, persistent caches or services with suitable topologies is an important architectural property: parts to be deployed and maintained are reduced. It’s wrong to put her down as “just for testing.”

The central boundary is the writing concurrence: several reader transactions can coexist but there’s only one writer at a time. The WAL mode allows readers and a writer to move more independently than in the classic scheme but does not create parallel writers about the same file. Checkpoints and WAL location matter especially if a reader process keeps operations open for a long time.

-- Ejemplo local SQLite: modos y estado de journal.
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO notes (title, updated_at)
VALUES ('Prueba local', '2026-11-16T00:00:00Z');

That example propose a standby timeout at lockdown, doesn’t remove a writer’s limit. Nor does it convert a shared network path or several servers into a distributed base. Reactions and backup policy should be designed from the process that the file knows. The Online Backup API is preferable to assuming that copying an open file will always produce consistent backup.

SQLite also has FTS5: textual search with MATCH, prefixes, phrases, proximity and ranking. Do not require another search engine simply because you have text. The question is what relevance, indexation, facets and user experience you need.

SQLite makes sense when topology really supports a local file / instance and write profile is compatible and requires more design when there are numerous distributed processes that wish to coordinate scriptures about the same status.

PostgreSQL: protecting invariants while growing system

PostgreSQL is a DBMS clientele-relative server with MVCC, multidecision transactions, constraints, joins, indexes and a large area of administration. Its strength is not a generic score “OLTP 10 / 10,” but allows to express rules that have to be met even with actual concurrently: a unique key, a valid reference, a CHECK, a transaction that changes several tables atomically.

Imagine an order that reduces inventory and inserts an order. To do so within a transaction has a semantics different from sending two independent sentences to a system that confirms each statement without BEGIN / COMMIT.

-- PostgreSQL: ejemplo de control transaccional, no receta de inventario.
BEGIN;

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 42 AND stock > 0
RETURNING stock;

-- La aplicación debe verificar que el UPDATE afectó una fila;
-- si no, debe abortar y no crear la orden.
INSERT INTO orders (product_id, quantity)
VALUES (42, 1);

COMMIT;

That block presupposes examples and requires application logic that valids the result of UPDATE: if there are no stocks, the INSERT should not be run and a real code should also protect business challenges with constraints and errors. Here’s an illustration of the transactional unit, not a complete run.

PostgreSQL can also indexe JSONB, textual data and geometries using appropriate structures and operator classes. The indexes are designed for inquiries: an indexed column does not secure lower latency and an Seq Scan may be the right choice. That level of detail belongs to the artículo sobre índices y planner en PostgreSQL.

The complexity appears with increased expectations: matching and VACUUM, backups and verified restoration, replication, connections, migration and storage growth. The documentación de administración de PostgreSQL separates these issues from the SQL semantics.

PostgreSQL makes sense when the application needs to protect relational invariants and accept multiple readers / writers on an administered central basis. It’s reasonable that this remains the true source of an analytic projection or an index of search.

ClickHouse: Optimizing analytics requires another physical organisation

The MergeTree family organizes data in parts and uses a physical classification order according to ORDER BY. The primary index scattering helps to skip reading ranges. It’s not a restriction of oneness equivalent to a transactional PostgreSQL ZXHOLDER2ZXQ.

A deliberately small example shows an event table designed for time and tenant:

-- ClickHouse: ejemplo ilustrativo de esquema MergeTree.
CREATE TABLE events_demo (
    tenant_id UInt32,
    event_time DateTime,
    event_name LowCardinality(String),
    amount Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time);

SELECT tenant_id, event_name, count(), sum(amount)
FROM events_demo
WHERE event_time >= '2026-11-01 00:00:00'
GROUP BY tenant_id, event_name;

The physical order favours some patterns but not all claims will filter tenant_id. The monthly partition is also a decision that should be supported by volume, retention and pattern of consultations and to select too much can increase the number of parts and maintenance work. The table is illustrative and has not been measured.

ClickHouse should be described as * append-only *. His current documentation includes lightweight updates and delights, including * patch parts *-based routes. That doesn’t turn his update costs into those of an OLTP row-oriented table. A light update, more expensive mutations, change visibility and operations have to be distinguished under different conditions.

Also, write “ACID: yes / no” in a matrix. The documented guarantees depend on the operation, the engine and the configuration and we do not have to move them without nuances Multi- judgment from PostgreSQL to MergeTree. For an order system, the primary decision often goes to another true source, that projects events to ClickHouse when it compensates.

ClickHouse makes sense when the weight of the consultations is in aggregations, scans and series of events, and the computer can assume ingestion, merges, retention, projections consistency and analytics operation.

CrateDB: SQL distributed with different transactional boundaries

CrateDB 6.5 divides tables or partitions into * shards * distributed between nodes. Primary shards and replicas allow to distribute consultations and to hold some node faults according to topology and configuration. That doesn’t mean an automatic backup or a universal guarantee of availability: quorum, partitions, replicas and restoration matter.

CrateDB uses SQL but its documentación de transacciones does not offer the BEGIN, COMMIT and PostgreSQL ZXQ control to group several sentences and each statement is confirmed separately. That radically modifies how to express invariants that cover several operations. Optimic competition and version controls can help in concrete cases but do not replace all guarantees of a multi-decision transaction.

For events, telemetry and consultations distributed about a volume spread across nodes, CrateDB puts forward a different model to the traditional relational Row@-@ store. The decision should include share costs, re-balancing, reference, replication and cluster management requirements, and should not be limited to how “SQL also speaks.”

The guía de snapshots requires thinking about the consistent status of the cluster. Copying separate data directories or using independent disk snapshots does not show an complete restoration of all shareand metadata. Replication, high availability and backup are not synonymous.

Compare mechanisms, nonlogos

The matrix shows regular architectural commitments and does not propose to assign an absolute capacity to all settings or editions.

DimensionSQLitePostgreSQLClickHouse (MergeTree)CrateDB
Performance modelRetirementRelational SQL serverServer oriented to columnar analyticsSQL cluster distributed about shareS
Writing coincidenceA simultaneously based writerConcurrently with MVCCIngestion and Updatas with motor / operation semanticsReparations and guarantees by statement
Several atomic sentencesYeah, inside his transactionYesDo not match without checking operation / configurationNo BEGIN / COMMIT multi-statement equivalent
Regular organizationOptional local file, B-tree and FTSTables / heap and specialized indicesOrden parts, columns and dispersed primary indexShads and copies distributed
Strong consultationLocal application and rich SQLJoins, transactions and invariantsAddition and analyticsSQL about distributed data
OperationBackup / file integrityService, VACUUM, replica, backupParts, merges, cluster and retentionShards, replicas, snapshot and cluster
RecoveryBackup API + Proven RestorationBackups and recovery point-in-time when setBackups and procedures as per deploymentConsistent cluster snapshot

For each real decision, ask also about license / editing, security, access controls, migration, diagnostic tools and equipment skills. The “operation” line isn’t deleted because the software’s free.

The same application may require two warehouses, not four

Let’s think about a trade with inventory, orders, text search and navigation metrics. A incremental architecture could follow these phases:

  1. Choose SQLite for the local client if they have been drunk, or PostgreSQL for the central transactional system if there are several shared actors and rules.
  2. Adjust inquiries and PostgreSQL indices before adding new infrastructure.
  3. If textual relevance and interactive search requirements appear that the current solution does not cover, create an indexed projection with a specialized search engine, while keeping DBMS as a source of truth.
  4. If analytic consultations really compete with OLTP or require scale cylindrical processing, ClickHouse evaluates and if the distribution and reference model favor CrateDB, evaluate them for their concrete guarantees.

A projection should not inadvertently become a second true source: it needs initial load, synchronization, purging, reconstruction and retard metrics. The following article from the block, “MeilisResearch vs. Tipesense,” explores that border and there’s no internal link as soon as it’s not published.

How to compare without manufacturing benchmarking

Using the same logical model doesn’t mean that all bases have to use the same physical scheme. For an honest lab, it defines products, orders and events with a reproducible seed. He then implements indexes / management / partitioning idiomatic for each engine, documenting his choice. Otherwise you would end up comparing the penalty of bad choosing the configuration of one of the participants.

Before any figure, register exact versions, machine, storage, memory, cache, volume and distribution, concurrence and demand profiles. Distinguish initial ingestion of hot updates and cold consultations and separate p50 / p95 latences from mudput. For distributed systems you also have to measure recovery of nodes and lag of replicas, and do not infer resilience from a local demand.

The lab did not run. There are no TPS number, milliseconds and percentages of superiority in this article. Any graph showing them would be fictitious illustration, and no evidence.

Final decision: workload is contract

No proper response without describing what should continue to be true after an operation, what consultations dominate and what mistakes are expected to endure. SQLite Simplifies local display with an explicit matching model. PostgreSQL brings together operations and an extensive relational ecosystem. ClickHouse changes the layout to favor analysis. CrateDB distribute data and SQL with different transactional guarantees. They are alternatives or supplements depending on the system, and have been required to avoid a technological development.

Main documentation: SQLite WAL, SQLite FTS5, PostgreSQL 18, ClickHouse MergeTree, CrateDB 6.5.