SQL Dialects [2026]: DuckDB vs ClickHouse vs CockroachDB

As of May 13, 2026, these three engines sit in different corners of the SQL map: DuckDB 1.5.2 is local-first and PostgreSQL-leaning, ClickHouse is a fast analytical dialect with aggressive extensions, and CockroachDB v26.1 is the distributed SQL option for multi-region correctness. This cheat sheet is for engineers who need quick syntax recall, deployment fit, and tuning scope without reading three doc sets end to end.

• DuckDB is the easiest place to start if your workload begins with files, notebooks, or embedded analytics.
• ClickHouse is the strongest default for high-volume analytical scans, rollups, and real-time dashboards.
• CockroachDB is the best fit when data placement, regional latency, and transactional safety are product requirements.
• Assume low portability for engine-specific knobs: PRAGMA, query SETTINGS, and cluster settings solve different problems.

Dialect Snapshot

What actually changes between them

• DuckDB tracks PostgreSQL conventions closely, but deliberately diverges on items like floating-point division, integer division, identifier handling, and permissive equality checks.
• ClickHouse extends SQL for analytics with patterns such as query SETTINGS, output FORMAT, and storage-aware modifiers like FINAL.
• CockroachDB supports the PostgreSQL wire protocol and the majority of PostgreSQL syntax, but distributed-system constraints still make some PostgreSQL features unsupported or behaviorally different.

Version and release context

• DuckDB 1.5.2 is the current release line on the official install page, with 1.4.4 listed as LTS.
• ClickHouse describes itself as shipping monthly releases and introduced the official clickhousectl CLI in beta on April 9, 2026.
• CockroachDB stable docs currently expose the v26.1 release line, while compatibility pages already document some behavior in v26.2.

When To Choose Each

Choose DuckDB when:

• You start from Parquet, CSV, notebooks, or an embedded app process.
• You want near-zero operational work and fast local iteration.
• You need PostgreSQL-flavored SQL, but not a distributed cluster.
• You care more about developer speed than cross-region writes.

Choose ClickHouse when:

• You need fast scans, rollups, and dashboards over very large analytical data.
• You are comfortable using engine-specific SQL extensions for performance.
• You want columnar storage, strong compression, and ClickBench-style performance expectations.
• You can separate operational analytics from transactional system-of-record concerns.

Choose CockroachDB when:

• You need transactional SQL across regions, not just replicated analytics.
• Read/write locality is part of the schema design, not an afterthought.
• You want PostgreSQL driver and tool compatibility, but with distributed placement controls.
• You need first-class range visibility and historical reads for debugging or correctness.

Live Filter And Shortcuts

Commands By Purpose

duckdb
ATTACH 'file.db';
USE file;
ATTACH 'sqlite_file.db' AS sqlite_db (TYPE sqlite);

clickhouse-client --host <hostname> \
  --secure \
  --port 9440 \
  --user <username> \
  --password <password>

cockroach demo movr --nodes 3;
SHOW REGIONS FROM DATABASE movr;

PRAGMA version;
SHOW TABLES;
DESCRIBE SELECT * FROM 'events.parquet';

EXPLAIN SYNTAX
SELECT sum(number)
FROM (SELECT * FROM numbers(10000))
WHERE number <= 10;

EXPLAIN SELECT * FROM rides WHERE city = 'new york';
SHOW RANGES FROM TABLE movr.users WITH DETAILS;

SET memory_limit = '10GB';
SET threads TO 4;
EXPLAIN ANALYZE
SELECT city, count(*)
FROM read_parquet('events/*.parquet')
GROUP BY city;

SELECT count()
FROM events
SETTINGS max_threads = 4;

SELECT *
FROM events FINAL
FORMAT Null;

EXPLAIN PIPELINE
SELECT count()
FROM events
SETTINGS max_threads = 4;

EXPLAIN ANALYZE (REDACT)
SELECT *
FROM rides
WHERE revenue > 90
ORDER BY revenue ASC;

SELECT *
FROM read_parquet(['file1.parquet', 'file2.parquet']);

LOAD httpfs;
ATTACH 'encrypted.db' AS enc_db (ENCRYPTION_KEY 'quack_quack');

SELECT *
FROM actors AS a
JOIN roles AS r ON a.id = r.actor_id
FORMAT Null
SETTINGS join_algorithm = 'parallel_hash';

CREATE TABLE promo_codes (
  code STRING PRIMARY KEY,
  description STRING
) LOCALITY GLOBAL;

CREATE TABLE vehicles (
  id UUID PRIMARY KEY,
  city STRING,
  owner_id UUID
) LOCALITY REGIONAL BY ROW;

SELECT *
FROM rides AS OF SYSTEM TIME '-4h';

Configuration And Session Controls

Scope matters more than syntax

• DuckDB uses SET and PRAGMA for session-level behavior. You can inspect current values with current_setting() or duckdb_settings().
• ClickHouse commonly exposes tuning at query time through the SETTINGS clause, which keeps performance intent close to the statement.
• CockroachDB separates session variables from cluster-wide controls. Use SHOW CLUSTER SETTINGS and SET CLUSTER SETTING only when you really mean global impact.

-- DuckDB
SELECT current_setting('memory_limit') AS memlimit;

-- CockroachDB
SHOW CLUSTER SETTINGS;
SHOW CLUSTER SETTING diagnostics.reporting.enabled;
SET CLUSTER SETTING <setting> = <value>;

If you need to sanitize examples before sharing traces, plan bundles, or SQL samples externally, use TechBytes' Data Masking Tool. It fits especially well beside CockroachDB plan output and any ad hoc snippets you lift from production tickets.

Advanced Usage And Gotchas

DuckDB portability traps

• DuckDB supports == for equality, while PostgreSQL does not.
• DuckDB uses // for integer division and returns floating-point results for 1 / 2, unlike PostgreSQL integer division.
• Identifier handling is more forgiving than PostgreSQL because DuckDB preserves case while matching identifiers case-insensitively.

ClickHouse tuning traps

• FINAL is convenient, but it can be expensive because it forces extra work at query time.
• Analytical extensions such as FORMAT, SETTINGS, and join algorithm hints are useful precisely because they are not generic SQL.
• Expect the best results when you write for the engine, not when you try to preserve strict PostgreSQL portability.

CockroachDB distributed SQL traps

• EXPLAIN ANALYZE executes the target statement. It is not a dry run.
• REGIONAL BY ROW is powerful, but only when your access patterns actually align to row locality.
• AS OF SYSTEM TIME is a practical debugging and conflict-reduction primitive, but it is still bounded by the garbage-collection window.

Decision shortcut

• Need local analytics over files today: choose DuckDB.
• Need the fastest analytical warehouse path tomorrow: choose ClickHouse.
• Need globally distributed writes with SQL locality semantics: choose CockroachDB.