When dictionaries for fast lookups in ClickHouse beat a join
A ClickHouse dictionary is an in-memory key-value representation of data from internal or external sources, used to speed up queries with JOINs and to enrich data at ingestion, according to the ClickHouse dictionaries documentation. Teams usually reach for one when a large event table, such as network sessions, API calls or device telemetry, is joined on every query to slowly changing reference data like accounts, device registries or geo tables. The reference side is small enough to hold in memory, changes far less often than the events, and is read by key. In that situation, a dictionary lookup per row replaces a hash join that rebuilds its right-hand side for each query.
A dictionary is not automatically the right tool. Keep a join when the reference table is large relative to available memory, when queries need several columns from it that change in ways a refresh cannot keep up with, or when you need exact transactional freshness from the source. Denormalizing the attribute into the event table is often better when the value is stable and known at insert time, because it costs storage but no lookup at query time. Before choosing, confirm three things with your own data: the number of distinct keys, the expected refresh interval the business can tolerate, and whether the enrichment is needed at insert time or only at query time. A small pilot against a copy of production data will show which of these your workload actually needs.
Dictionary sources and layouts, and when each fits
This table compares the common dictionary source types and in-memory layouts against the lookup pattern they fit. Read each row for the main trade-off, then confirm the exact layout and source options against the ClickHouse dictionaries documentation for your server version before you design around them.
| Option | Fit | Main trade-off | Confirm with vendor docs |
|---|---|---|---|
| ClickHouse table source | Lookup data already stored in a ClickHouse table on the same cluster | Dictionary contents must be reloaded on the refresh schedule, so changes appear with a delay | Supported layouts and the lifetime settings for ClickHouse-backed sources |
| PostgreSQL source | Reference data that lives in an operational PostgreSQL database you already run | Load queries hit the source database, and refresh timing depends on its availability | Connection settings, update field options and driver version requirements |
| MySQL source | Reference tables maintained in an existing MySQL system | Same refresh and availability concerns as PostgreSQL, plus driver and version differences | Supported MySQL versions, connection parameters and invalidation query behavior |
| HTTP source | Lookup data served by an internal API or export endpoint you control | Depends on endpoint latency and format, and a failed fetch can leave stale data in place | Supported formats, authentication options and retry behavior on failure |
| File source | Small, slow-changing reference sets such as country codes or product categories | Files must be distributed to every node and reloaded consistently, or nodes diverge | Supported file formats and how the file path and refresh are configured |
| Flat or hashed layout | Numeric keys with a dense or sparse key range held entirely in memory | Keys must be integers, and memory grows with the number of entries | Key type limits and memory accounting for each layout |
| Hashed or sparse hashed layout | Large dictionaries with mostly unused key ranges | Lookup and memory behavior differ from flat layouts, so measure on representative data | Which key types are supported and how sparse variants store values |
| Cache or complex key layouts | Dictionaries too large to hold fully in memory, or keys made of several columns | Misses fetch from the source at query time, so latency and source load become variable | Cache sizing options, complex key syntax and behavior on missing keys |
How to set up a dictionary and verify it in a test cluster
This sequence takes one join-heavy lookup from a reference table to a dictionary that is loaded, checked against the original join output, and measured for memory before it reaches production. Each step ends with a check the team can record.
Pick a representative reference table
Choose the lookup table your slowest enrichment query joins against, such as a device, account or product catalog, and confirm its row count, key columns and how often it changes. The owning data team should confirm which keys are unique, because duplicate keys in a dictionary source cause unpredictable lookup results. Done means you have a written note of the table, its key, its expected size and its update pattern, and a copy of the join query that currently uses it.
Define the source and credentials
Decide where the dictionary reads from: a ClickHouse table, PostgreSQL, MySQL, an HTTP endpoint or a file. Create a read-only database user for the source, scoped to the reference table only, and store its password through your normal secrets process rather than in the dictionary DDL. Check the connection from the test cluster with a plain query against the source before going further, so network or permission failures surface here rather than during the dictionary load.
Choose layout and lifetime
Select a layout that fits the key type and table size, such as a flat or hashed layout for simple numeric keys, and set a LIFETIME that matches how often the reference data changes. Decide whether a short refresh window or a periodic reload suits the business need, and document the staleness you accept for enrichment. The output is a short decision record naming the layout, the lifetime and the staleness window, reviewed by the data owner.
Create the dictionary
Run CREATE DICTIONARY with the chosen source, PRIMARY KEY, attributes, layout and LIFETIME, in a test database rather than the production one. Keep the DDL in version control so the same definition can be replayed on production. Done means the statement completes without error and the dictionary appears in the system dictionaries table.
Confirm the load with system tables
Query system.dictionaries to check that the status is LOADED, the element count matches the source row count and the bytes allocated are reported. If the status shows a failed load, read the last_exception column and fix the source, credentials or network path before continuing. The check passes when status, element count and source count agree.
Compare dictGet results with the join output
Run the original JOIN query and an equivalent query that uses dictGet or dictGetOrDefault on the same sample of rows, then compare the results column by column. Include keys that are missing from the reference table, because the default value behavior must match what the business expects. The step closes when the two result sets are identical for the agreed sample, and any differences are explained and either fixed or accepted in writing.
Measure memory before production
Read bytes_allocated and element_count for the dictionary in system.dictionaries, then compare against the memory limits and other workloads on the production-sized nodes. Ask the platform team to confirm headroom and to check how the reference table grows over time. The step is done when the measured footprint, the growth estimate and the headroom have been recorded and approved for the production change.
Enrichment at insert time versus lookups at query time
Insert-time enrichment looks up dictionary values while rows are written and stores the result alongside the raw columns. A materialized view that calls dictGet on each incoming block, or an ingest pipeline that performs the same lookup before the insert, produces rows that already carry the enriched attribute. The value then stays fixed for that row, which suits fields that describe the event as it happened, such as the account tier or sales region in force at the time of a transaction.
- What it freezes: the looked-up value is written into the stored row, so later changes to the dictionary source do not alter historical rows unless you backfill them.
- Where it runs: a materialized view keeps the logic in the table definition, while an ingest pipeline keeps it in application code. Confirm which path every writer uses.
- Storage cost: the enriched column adds bytes to every row written, and that cost recurs with write volume.
- Backfill: rows written before the view or dictionary existed need a deliberate reprocessing step that must be planned and tested.
Check whether the business question needs the value as it was at event time, and confirm that every write path passes through the same enrichment logic.
Query-time lookups call dictGet in a SELECT or WHERE clause, or use a dictionary in place of a join, so the value is read from the dictionary when the query runs. Nothing is frozen in storage. The ClickHouse dictionaries documentation describes how dictionaries are loaded and queried, and the same mechanism serves both patterns.
- What it updates: every query sees the dictionary's current contents, so corrections to reference data appear as soon as the dictionary reloads.
- Lean stored data: raw events are stored once with only the key column, which keeps table size down.
- Query cost: each query performs its own lookups, so heavy use across large result sets adds CPU to every query.
- Reproducibility: rerunning a report months later may return different attributes, which matters for audit trails and regulatory reporting.
Ask whether reports must reproduce past results exactly, and measure lookup cost on representative queries before committing to this pattern.
Both patterns serve stale values in different ways. An insert-time value reflects the dictionary as loaded at the moment the row was written, so a key that changed a moment earlier can produce rows with the old value. A query-time value lags only by the dictionary's reload interval, but it is recalculated each time the query runs. Refresh behavior is controlled in the dictionary definition, so it should be reviewed together with the enrichment design.
- Lifetime controls reload: the
LIFETIMEsetting determines how often ClickHouse checks the source for changes. Choose it from how quickly reference data changes and how much load the source can absorb. - Insert-time lag: rows enriched during ingestion keep whichever value was current at write time, even after the source changes.
- Missing keys: the default argument in
dictGetdecides what an unknown key returns. Define it explicitly so unmatched keys stay visible instead of appearing valid. - Reconciliation: decide whether enriched columns are ever recomputed, how often, and who approves a recomputation.
Confirm the reload interval, the default value policy and whether the business accepts the lag for each enriched field.
Insert-time enrichment moves lookup work onto the write path, which changes how ingestion behaves under load. Query-time lookups keep the write path simple but move cost to reads. In either case the dictionary itself occupies memory and depends on its source during each reload, so the operational footprint needs to be measured in the cluster that will run it.
- Per-block work: a materialized view that calls
dictGetruns for every insert block, so its cost grows with write volume and batch count. - Dictionary memory: the dictionary is held in memory on each node that serves it, which adds to the memory budget of every replica.
- Source dependency: a slow or unavailable source during reload can leave the dictionary stale or unable to load, and the behavior of the cluster in that case should be tested in advance.
- Replica consistency: each replica loads its own copy, so confirm that replicas converge after a reload and that dictionary definitions are deployed identically on every node.
Load-test the insert path with production-like batch sizes and a dictionary of representative size before the enrichment view goes live.
The choice usually follows from one question: should a row keep the reference data it had when the event happened, or should every report reflect today's reference data? Many deployments use both, with fields that must stay fixed enriched at insert time and fields that change often looked up at query time. Writing the decision down per field keeps the design from drifting as new reports are added.
- Fixed attributes: fields tied to the event, such as a status or classification recorded at the time, usually belong at insert time.
- Changing attributes: fields such as current owner names or display labels usually belong at query time, where they stay accurate without backfills.
- Mixed designs: a single table can hold both kinds, but each column's behavior should be documented so analysts know which values to trust.
- Change control: adding or changing an enrichment view affects historical and future data differently, so it should go through the same review as any schema change.
Before building, list every enriched field and record whether it must be frozen, whether it can be recalculated, and who owns that decision.
Memory, refresh lifetime and stale data risks to plan for
A dictionary lives in memory on every node that serves it, so its cost, freshness and failure behavior multiply across the cluster. The six cards below cover the decisions that shape those risks, from footprint and refresh timing to startup, duplicates and replica agreement.
Memory footprint on each replica
Each replica holds its own copy of every loaded dictionary, so memory use multiplies with the replica count, not the shard count alone. Before you load a large source, estimate the row count, key size and attribute width, then check the in-memory size on one node in a pre-production environment. Confirm the layout you choose, since some layouts trade memory for lookup speed.
Lifetime trade-offs between freshness and load
The lifetime setting controls how often ClickHouse reloads a dictionary from its source. A short lifetime keeps enrichment current but adds query load on PostgreSQL, MySQL or an HTTP endpoint, and it multiplies across replicas. A long lifetime reduces that load but leaves enriched rows wrong for longer, so set it from the business tolerance for stale attributes.
Behavior when the source is unavailable
If the source becomes unreachable during a refresh, the dictionary generally keeps serving the data it already holds, so lookups continue but grow stale. Decide how long stale enrichment is acceptable and add monitoring that alerts when the last successful load falls behind. Test this path on purpose by blocking the source in a staging environment.
Duplicate keys in the source
A dictionary expects one row per key, and duplicate keys in the source can produce unpredictable values for lookups. Check uniqueness in the source query before loading, and deduplicate upstream when the source is a log or event table. Add a validation query that counts keys returning more than one row, and run it after each schema change in the source.
Load time at startup
Dictionaries load when the server starts or when first queried, depending on configuration, so a large source can delay readiness and cause slow first queries. Measure load time for each dictionary on a copy of production data. Plan restarts, rolling upgrades and node recovery around that duration, and check whether queries depending on the dictionary should wait or fail.
Consistency across replicas
Each replica loads its dictionaries independently from the source, so replicas can hold different snapshots if refreshes happen at different times. Two replicas may then return different enrichment for the same key during a window. Decide whether that window is acceptable for your reports, and compare dictionary contents across replicas during recovery and after a restart.
Credentials, network paths and access for dictionary sources
Treat every dictionary source as a database connection that needs its own identity, secret handling and network rule. A dictionary that loads from PostgreSQL or MySQL stores the connection details in its definition, so the account used there should be a dedicated read-only user that can only select from the specific tables or views the dictionary needs. Do not reuse an application account or an administrator account. Keep the password out of plain SQL files in version control, and confirm with your secrets platform how the dictionary DDL will be generated and applied in each environment. Use the ClickHouse access control documentation as the starting point for users, roles and grants, and restrict the default user, which starts with all permissions, before any dictionary is created.
Control who can create or alter dictionaries, because a dictionary definition decides which external system the cluster connects to and what it pulls into memory. Grant dictionary creation only to a small set of administrative roles, keep application users limited to dictGet style lookups on existing dictionaries, and review DDL changes the same way you review schema changes. Confirm the exact privileges your ClickHouse version requires for dictionary management and for reading the source tables, since these grants vary across releases. Network paths need the same discipline: allow outbound connections only from the ClickHouse nodes that load the dictionary to the specific database port or HTTP endpoint, and check whether a firewall, proxy or private endpoint sits in the path, including for TLS settings on the source side.
Monitor dictionary sources as production dependencies. A failed reload usually leaves the old data in memory, so the failure can stay quiet until enrichment results drift or a lookup starts returning defaults. Check the system.dictionaries table for load status, last successful update and error messages, and alert on sustained failures from your monitoring stack. Test the failure path before go-live: revoke the source user's permission in a staging environment, block the network route, and confirm the alert fires and the team knows whether stale enrichment is acceptable for the workload.
Limits to confirm in your environment before relying on dictionaries
Dictionaries keep lookup data in memory, so their limits depend on your ClickHouse release, your server sizing and how often the source changes. The cards below list the checks to run on a test cluster before any production query or insert path depends on a dictionary.
Supported source types and versions
Dictionary sources differ in what they can load and how they refresh, such as ClickHouse tables, PostgreSQL, MySQL, HTTP endpoints and files. Confirm that your ClickHouse release supports the exact source you plan to use, and that your database driver version and network path to the source work from every server that will load the dictionary. Use the source type matrix in your own release as the acceptance criterion, not an assumption carried over from another version.
Memory sizing against your servers
A dictionary is held in memory on each server that loads it, so the footprint multiplies across a cluster. Load a representative copy of the source on a server with the same memory and settings as production, then measure resident memory before and after the load. Confirm that the result leaves headroom for queries, merges and other dictionaries.
Refresh behavior under your lifetime settings
The lifetime setting controls how often ClickHouse reloads a dictionary from its source, which sets how stale enrichment data can become. Test what happens when the source is slow or unavailable, and whether queries keep using the last loaded version. Agree with the data owners on the acceptable staleness window before you choose a lifetime value.
Key type constraints
Dictionary lookups depend on the key type and layout you choose, and some layouts accept only specific key shapes. Confirm that your join keys in production match the declared type exactly, including nullable values, string encodings and composite keys. Mismatches produce empty results or errors that can look like missing data, so test with real key values.
Dictionary definitions across replicas
Dictionary definitions and their sources must exist on every replica that serves queries, because ClickHouse does not guarantee that a definition created on one node appears on another. Confirm how your team deploys DDL, whether the definition is stored in configuration files or created with SQL, and how a rebuilt or new replica gets its dictionaries. Check this during a replica recovery test, not only during initial setup.
Questions teams ask before using dictionaries in ClickHouse
What does it cost to build a ClickHouse dictionary setup?
Cost comes from the people hours to design and test the dictionary, plus the memory and source-system load it adds. Build work is one-time: choosing the layout, writing the DDL, setting the refresh lifetime and testing enrichment on a copy of your data. Recurring cost comes from the hosts that hold the dictionary in memory and from the load on the source database each time it reloads. Cost rises with the number of dictionaries, the size of the source tables, how often the data changes, and how many source systems you must connect. When you request a quote, ask for the work split between design, build, testing and handover, and for a list of what runs after go-live.
What do we need to provide before a dictionary project starts?
You provide access, data and decisions. Access means a read-only account on each source (ClickHouse, PostgreSQL, MySQL, an HTTP endpoint or a file location) and network paths from the ClickHouse hosts. Data means a sample of the lookup tables with real key cardinality, the expected update pattern, and the join queries that the dictionary should replace. Decisions cover how stale enrichment may be, who owns the source table, and which environment hosts the pilot. Collect the query logs for the slow joins first, because they show which lookups matter. Ask the vendor to list these inputs in the written scope before work begins.
Can our own team build and run ClickHouse dictionaries without a partner?
Yes, if your team already runs ClickHouse and can read system.dictionaries and the server logs. Dictionary DDL is standard SQL, and the layout and lifetime settings are documented in the dictionaries guide. The skills that take longer to build are source-side: knowing how the source database handles reads under load, how to detect missing keys, and how to change a reload without breaking enrichment. Teams without that experience often underestimate memory growth and reload timing. A partner is most useful for a fixed-scope pilot that sets the pattern, tests failure cases and leaves a runbook your team can operate.
How long does a dictionary pilot take, and what changes the duration?
The duration depends on how many sources and lookups are in scope, how clean the keys are, and how much testing the enrichment needs. A single lookup against one well-understood source is short to test. Duration grows when the source has poor key quality, when several teams must agree on refresh behavior, when the source requires new network or credential approvals, or when results must match an existing join exactly. No fixed figure applies to every environment, so ask for a timeline in the written scope that names these drivers and the acceptance tests that close the pilot.
When is a dictionary the wrong choice compared with a join or materialized view?
A dictionary fits small to medium lookup tables that change slowly and are read by many queries, such as country codes, product catalogs or account attributes. It is the wrong choice when the lookup table is large relative to the memory you can spare, when the data must be exact at the moment of each read, or when the enrichment depends on values that arrive in the same batch. Joins suit one-off analysis against changing data. A materialized view suits cases where you want enriched rows stored once at insert time and the source changes rarely. Test each option on a copy with production-like data and compare memory, latency and freshness before you commit.
How do we handle updates to the source data in a dictionary?
Dictionaries refresh on a schedule set by the LIFETIME clause, either a fixed interval or a minimum and maximum range that spreads reloads. Updates appear after the next reload, so enrichment can lag the source by up to that interval. For sources that support it, update_field lets ClickHouse fetch only changed rows, which reduces load on the source. Plan for three things: how much lag the business accepts, how a failed reload is detected and alerted, and what happens to rows enriched during the lag. Rows already written keep their original values unless you reprocess them, so decide whether historical enrichment must be corrected. Confirm these settings against the dictionaries documentation for your ClickHouse version before you set them in production.
How DeployOpen scopes and delivers ClickHouse dictionaries
DeployOpen starts with the lookups your queries and ingestion pipelines actually need, then decides which dictionaries to build, from which sources, and how they refresh. The work runs inside your infrastructure, on a self-hosted ClickHouse deployment, and ends with documentation and a handover your team can operate.
How we scope
- Inventory the joins and enrichment lookups that slow queries or ingestion today, with their key columns and row counts.
- Record each source system for the dictionary, its owner, its update pattern and the freshness your reports require.
- Agree in writing the dictionary layouts, refresh lifetimes, memory budget and the insert-time enrichment paths in scope.
How we build
- Create dictionaries in a pre-production ClickHouse environment with representative data, then compare results against the original joins.
- Configure source credentials and network paths for PostgreSQL, MySQL, HTTP or file sources using least-privilege accounts.
- Measure memory use and load time for each dictionary, then adjust layout and lifetime settings before anything reaches production.
How we hand over
- Document each dictionary's source, key, layout, lifetime, owner and the queries or materialized views that depend on it.
- Provide runbooks for forced reloads, failed source loads and dictionary changes, tested with your operators during handover.
- Walk your team through the monitoring checks for dictionary status and memory so issues surface before users notice them.
How we support
- Fixed-scope engagements have written deliverables, so any later changes to dictionaries are scoped and agreed separately.
- Ongoing support, upgrades and continuous testing are available as a retainer if your team wants DeployOpen to keep dictionaries current.
- Ask any provider to confirm how dictionary definitions are versioned and reviewed before changes reach production clusters.
