DEV Community

Cover image for ClickHouse IP Geolocation for Billions of Log Rows
ABDULLAH AFZAL
ABDULLAH AFZAL

Posted on

ClickHouse IP Geolocation for Billions of Log Rows

Most ClickHouse IP geolocation tutorials turn each IP range into a CIDR block with a one-line XOR trick. On a commercial city database, that trick sizes 3 in 4 multi-address ranges wrong and claims almost 10x the addresses those ranges hold. Nothing throws. Your country and city columns just quietly pick up neighbours' answers.

This walkthrough builds the exact version in plain ClickHouse SQL: one function that splits IPv4 and IPv6 ranges into CIDR blocks, an ip_trie dictionary that matched the vendor's own MMDB network for network, enrichment at insert time, and a daily refresh that swaps releases without a gap.

TL;DR

  • Use the ip_trie dictionary layout for IP ranges. It is the only layout that does range lookups on IPv6, and it answers one in-memory lookup per row.
  • Do not convert ranges with the XOR one-liner. Split each range into exact CIDR blocks; the SQL function below does it for IPv4 and IPv6 with integer-only math.
  • Keep only the columns you query in the trie. On a 1M-range sample, a trie holding just the country id used 49 MiB; one holding every shipped column used 370 MiB.
  • Enrich at insert time with MATERIALIZED columns, so last month's rows keep last month's location.
  • Refresh by building _next tables, checking row counts, then EXCHANGE TABLES and SYSTEM RELOAD DICTIONARY. The old dictionary serves until the new one is ready.

The short version: get the CIDR split right, keep the dictionary thin enough to fit in RAM on every replica, and pay for lookups once at insert instead of on every query. Everything below was run on ClickHouse 26.11 against data in a real vendor CSV layout, with the measurements and hardware stated.

Pick the lookup layout before you write SQL

ClickHouse gives you three ways to answer "which range contains this IP": a range join, a range_hashed dictionary, or an ip_trie dictionary.

A BETWEEN join is fine for a one-off question on a small table. For billions of rows you want one in-memory probe per row, which is what dictionaries give you. I measured the two dictionary layouts on a 1M-range sample, each holding only a row id so the comparison is fair:

Layout IPv6 Memory Load time 19.2M random IPv4 lookups
ip_trie (both families) Yes 53.18 MiB 5.7 s 12.6 s
range_hashed, constant key (IPv4 part) No 71.53 MiB 1.1 s 10.9 s

The range_hashed trick is legitimate and almost nobody documents it: give every row the same key (0) and let RANGE(MIN start MAX end) do the work, and you skip CIDR conversion entirely. It returned identical answers to the trie. The catch is that the lookup value has to convert to Int64, so a UInt128 IPv6 address fails with a type error. If your file has a single IPv6 row, and real geolocation files do, you are on ip_trie. So is the rest of this article.

ip_trie wants CIDR prefixes as keys, not start and end addresses. That conversion is where the popular recipe goes wrong.

The range-to-CIDR bug in the popular recipe

This is the conversion at the heart of most ClickHouse GeoIP setups, from ClickHouse's IP geolocation blog post. The observability schema guide still uses it:

bitXor(ip_range_start, ip_range_end) AS xor,
if(xor != 0, ceil(log2(xor)), 0) AS unmatched,
32 - unmatched AS cidr_suffix,
toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) AS cidr_address
Enter fullscreen mode Exit fullscreen mode

It finds the smallest single CIDR block that contains the range. That is only correct when the range already is one block. Take 59.44.215.15 to 59.44.215.17, three addresses. The one-liner turns it into 59.44.215.0/27, which covers 32. The exact answer is two blocks: 59.44.215.15/32 and 59.44.215.16/31. A 144-address range from 202.101.230.144 to 202.101.231.31 becomes a /23 with 512 addresses instead of four exact blocks.

ip_trie returns the most specific matching prefix, so an oversized block only shows up when a neighbour's block is more specific, or when there is no neighbour and the lookup should have returned nothing. That is why it survives code review.

I measured it on a 1M-range sample of ipgeolocation.io's Advanced IP to City database. Nine in ten ranges there are single addresses, which the one-liner handles fine. Of the 104,601 IPv4 ranges that hold more than one address:

  • 79,565 (76%) got the wrong block size. Those ranges hold 91,977,222 addresses; the one-liner claimed 898,986,820.
  • 980 of the 1,901 two-address ranges shrank to a /32, because log2(1) is 0. Half of each range disappears.
  • One random address sampled from every IPv4 range resolved to another range's record 2.4% of the time, and to another city 0.5% of the time. That last number is a floor. The sample is a sparse slice of the full file, so most oversized blocks have no neighbour to collide with. On DB-IP's free city file, which is complete and contiguous, the same test sent 2.06% of sampled addresses to the wrong city.

Step 1: Load the files the way they ship

I'm using ipgeolocation.io's database CSVs for the examples, because every file stores integer ids that point at small reference files (country_id, city_place_id), which is the shape you want in RAM, and the same database also ships as an MMDB you can check the result against. DB-IP Lite publishes start/end ranges too, so the same SQL works for it once you rename the columns. MaxMind's GeoLite2 CSVs already ship CIDR networks, so with those you can skip the split.

The IP to City archive has three files you need: db-ip-location.csv.gz (IPv4 and IPv6 ranges in one file, with a header row), db-place.csv.gz (place names in 12 languages), and db-country.csv.gz. Copy them into an ipgeo/ folder under the server's user_files directory (/var/lib/clickhouse/user_files on a default install), because file() only reads from there.

CREATE DATABASE IF NOT EXISTS geo;

-- One row per exact CIDR block. Keep only the columns you will query:
-- every column here is held in RAM on every replica once the dictionary loads.
CREATE TABLE IF NOT EXISTS geo.ip_cidr
(
    cidr            String,
    country_id      UInt16,
    state_place_id  UInt32,
    city_place_id   UInt32,
    latitude        Float32,
    longitude       Float32,
    time_zone_name  String,
    connection_type String
)
ENGINE = MergeTree
ORDER BY cidr;

-- Small reference tables: names are resolved by id, not copied into every block.
CREATE TABLE IF NOT EXISTS geo.place
(
    id            UInt32,
    place_english String
)
ENGINE = MergeTree
ORDER BY id;

CREATE TABLE IF NOT EXISTS geo.country
(
    id                    UInt16,
    country_code2         String,
    country_name_place_id UInt32
)
ENGINE = MergeTree
ORDER BY id;
Enter fullscreen mode Exit fullscreen mode

Every address in this pipeline is an IPv6 value. IPv4 rows become IPv4-mapped addresses (::ffff:59.44.215.15), so one trie and one code path cover both families.

Step 2: Convert IP ranges to exact CIDRs in SQL

Three parts: a function that does the bit math, one INSERT that uses it, and a check that proves the output is exact.

One function for IPv4 and IPv6

The algorithm is the classic one: start at the range's first address, emit the largest block that is aligned at that address and does not run past the end, jump past it, repeat. In ClickHouse that is an arrayFold over a fixed number of steps, wrapped in SQL UDFs so the load query stays readable. This implementation targets ordinary database ranges, not a range spanning the entire IPv6 address space (::/0), which requires special handling because its address count exceeds UInt128.

-- Integer-only helpers on UInt128, so IPv4 (stored as IPv4-mapped IPv6) and IPv6 share one code path.
-- Float64 log2() is fine for 32-bit values but rounds 128-bit ones, so it is avoided on purpose.

-- Largest power of two that is <= n (bin() trims leading zero bytes, position() finds the top bit).
CREATE OR REPLACE FUNCTION ip_floor_pow2 AS (n) ->
    if(n = 0, toUInt128(0),
       bitShiftLeft(toUInt128(1), length(bin(n)) - position(bin(n), '1')));

-- Largest block that can start at x: the value of x's lowest set bit.
CREATE OR REPLACE FUNCTION ip_lowbit AS (x) ->
    if(x = 0, bitShiftLeft(toUInt128(1), 127),
       toUInt128(bitAnd(x, toUInt128(bitNot(x) + 1))));

-- Next block when walking from x towards e: aligned at x and not past e.
CREATE OR REPLACE FUNCTION ip_next_block AS (x, e) ->
    least(ip_lowbit(x), ip_floor_pow2(toUInt128(e - x + 1)));

-- Split the inclusive range [s, e] into exact CIDRs, returned as (start, prefix_length) pairs.
-- An IPv4 range needs at most 62 blocks and an IPv6 range at most 254, hence 64 or 256 steps.
CREATE OR REPLACE FUNCTION ip_range_to_cidrs AS (s, e) ->
    arrayFold(
        (acc, i) -> if(acc.1 > e OR acc.1 < s,   -- done, or wrapped past the top of the space
                       acc,
                       (toUInt128(acc.1 + ip_next_block(acc.1, e)),
                        arrayPushBack(acc.2, (acc.1, toUInt8(128 - bitCount(ip_next_block(acc.1, e) - 1)))))),
        range(if(e <= toUInt128(toIPv6('::ffff:255.255.255.255')), 64, 256)),
        (s, CAST([], 'Array(Tuple(UInt128, UInt8))'))
    ).2;
Enter fullscreen mode Exit fullscreen mode

Two details matter more than they look. The toUInt128() wrappers stop ClickHouse from promoting e - x + 1 to Int256, which arrayFold would reject because the accumulator type has to stay fixed. And the step count switches to 64 for IPv4-mapped ranges, which keeps the fold cheap for the 96% of rows that are IPv4.

Load and split in one pass

Now read the CSV, split every range, and write one row per block. The file() schema lists every column because the files carry a header row and ClickHouse matches columns by name. Empty id fields, such as a range with no city, load as 0.

INSERT INTO geo.ip_cidr
SELECT
    concat(toString(toIPv6(c.1)), '/', toString(c.2)) AS cidr,
    country_id, state_place_id, city_place_id,
    latitude, longitude, time_zone_name, connection_type
FROM file('ipgeo/db-ip-location.csv.gz', 'CSVWithNames',
    'start_ip String, end_ip String, country_id UInt16, state_place_id UInt32, state_code String,
     district_place_id UInt32, city_place_id UInt32, accuracy_radius Float32, confidence String,
     dma_code String, zip_code String, latitude Float32, longitude Float32, geo_name_id UInt32,
     time_zone_name String, connection_type String')
ARRAY JOIN ip_range_to_cidrs(toUInt128(toIPv6(start_ip)), toUInt128(toIPv6(end_ip))) AS c;

INSERT INTO geo.place
SELECT id, place_english
FROM file('ipgeo/db-place.csv.gz', 'CSVWithNames',
    'id UInt32, place_english String, place_german String, place_russian String, place_korean String,
     place_portuguese String, place_japanese String, place_persian String, place_french String,
     place_chinese String, place_spanish String, place_czech String, place_italian String');

INSERT INTO geo.country
SELECT id, country_code2, country_name_place_id
FROM file('ipgeo/db-country.csv.gz', 'CSVWithNames',
    'id UInt16, continent_code String, continent_name_place_id UInt32, country_code2 String,
     country_code3 String, country_code_ioc String, country_name_place_id UInt32,
     country_name_official_place_id UInt32, country_capital_place_id UInt32, currency_code String,
     currency_name String, currency_symbol String, calling_code String, tld String, languages String');
Enter fullscreen mode Exit fullscreen mode

On the 1M-range sample, the split produced 1,435,478 blocks (1.44 per range) and took about 18 seconds on 2 vCPUs. Want names in German or Japanese instead of English? Select a different place_* column into geo.place.

Check the result against the MMDB

This is the check that convinced me. The same database is also available as an MMDB file, which stores IP mappings in a network tree. Comparing the generated CIDRs with those in the MMDB provides a strong structural regression check when both formats preserve the same network boundaries. Equivalent lookup data can sometimes have different CIDR partitions, so a mismatch should be investigated rather than automatically treated as incorrect conversion. This script dumps the MMDB's networks in the same text form ClickHouse produces:

"""Dump every network in an MMDB file as one CIDR per line, in the same text form ClickHouse produces."""
import sys

import maxminddb  # pip install maxminddb


def main(mmdb_path: str, out_path: str) -> int:
    try:
        reader = maxminddb.open_database(mmdb_path)
    except (OSError, maxminddb.InvalidDatabaseError) as exc:
        print(f"cannot open {mmdb_path}: {exc}", file=sys.stderr)
        return 1
    count = 0
    with reader, open(out_path, "w", encoding="utf-8") as out:
        for network, _record in reader:  # iterates every network that has data
            if network.version == 4:
                # ClickHouse keeps IPv4 as IPv4-mapped IPv6, so /n becomes /(96 + n)
                out.write(f"::ffff:{network.network_address}/{96 + network.prefixlen}\n")
            else:
                out.write(f"{network.network_address}/{network.prefixlen}\n")
            count += 1
    print(f"{count} networks written to {out_path}")
    return 0


if __name__ == "__main__":
    if len(sys.argv) != 3:
        sys.exit("usage: mmdb_networks.py <file.mmdb> <out.txt>")
    sys.exit(main(sys.argv[1], sys.argv[2]))
Enter fullscreen mode Exit fullscreen mode

Put the output in user_files/ipgeo/ and compare. If the counts are equal and nothing is missing, the sets are identical:

SELECT
    (SELECT count() FROM geo.ip_cidr) AS clickhouse_blocks,
    (SELECT count() FROM file('ipgeo/mmdb_networks.txt', 'LineAsString', 'cidr String')) AS mmdb_blocks,
    (SELECT count() FROM file('ipgeo/mmdb_networks.txt', 'LineAsString', 'cidr String')
      WHERE cidr NOT IN (SELECT cidr FROM geo.ip_cidr)) AS in_mmdb_not_in_clickhouse;
Enter fullscreen mode Exit fullscreen mode

On the sample: 1,435,478 blocks on both sides, zero missing. The XOR one-liner cannot pass this check, which makes it a useful regression test after every refresh.

Step 3: Build the dictionaries

Three ClickHouse dictionaries: the trie, plus two hashed_array lookups for names. Give the source a dedicated read-only user. The ClickHouse docs ask for one, and a dictionary that reads as default is a password reset waiting to break your inserts.

-- A read-only user for the dictionary source. Pick your own password; do not reuse "default".
CREATE USER IF NOT EXISTS geo_dict IDENTIFIED WITH sha256_password BY 'replace-with-a-long-random-secret' HOST LOCAL;
GRANT SELECT ON geo.* TO geo_dict;

CREATE DICTIONARY IF NOT EXISTS geo.ip_trie
(
    cidr            String,
    country_id      UInt16,
    state_place_id  UInt32,
    city_place_id   UInt32,
    latitude        Float32,
    longitude       Float32,
    time_zone_name  String,
    connection_type String
)
PRIMARY KEY cidr
SOURCE(CLICKHOUSE(USER 'geo_dict' PASSWORD 'replace-with-a-long-random-secret' DB 'geo' TABLE 'ip_cidr'))
LAYOUT(IP_TRIE)
LIFETIME(0);   -- reload on purpose after a refresh, never on a timer

CREATE DICTIONARY IF NOT EXISTS geo.places
(
    id            UInt64,
    place_english String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(USER 'geo_dict' PASSWORD 'replace-with-a-long-random-secret' DB 'geo' TABLE 'place'))
LAYOUT(HASHED_ARRAY())
LIFETIME(0);

CREATE DICTIONARY IF NOT EXISTS geo.countries
(
    id                    UInt64,
    country_code2         String,
    country_name_place_id UInt32
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(USER 'geo_dict' PASSWORD 'replace-with-a-long-random-secret' DB 'geo' TABLE 'country'))
LAYOUT(HASHED_ARRAY())
LIFETIME(0);
Enter fullscreen mode Exit fullscreen mode

LIFETIME(0) is deliberate. A timer reload would pick up a half-built table; the refresh in Step 6 reloads explicitly once the new data is in place. SHOW CREATE DICTIONARY prints the password as [HIDDEN], so it does not leak through the catalog, though anyone who can read your migration files still sees it.

Step 4: Look up IPs, distinct ones first

Two small wrappers keep queries and column definitions readable:

-- Wrappers so queries and column definitions stay readable. Input is an IPv6 value;
-- IPv4 addresses work as IPv4-mapped IPv6 (toIPv6('8.8.8.8') = ::ffff:8.8.8.8).
-- Unknown, private, and bogon addresses fall through to '' instead of throwing.
CREATE OR REPLACE FUNCTION geo_country_code AS (ip) ->
    dictGetOrDefault('geo.countries', 'country_code2',
        toUInt64(dictGetOrDefault('geo.ip_trie', 'country_id', ip, toUInt16(0))), '');

CREATE OR REPLACE FUNCTION geo_city AS (ip) ->
    dictGetOrDefault('geo.places', 'place_english',
        toUInt64(dictGetOrDefault('geo.ip_trie', 'city_place_id', ip, toUInt32(0))), '');
Enter fullscreen mode Exit fullscreen mode

A private address like 10.0.0.1 matches no range, so the trie returns the default you pass in, and geo_country_code comes back empty instead of guessing. If your log stores IPs as strings, convert with toIPv6OrDefault(ip) so one malformed value does not fail the whole query; it becomes ::, which also matches nothing.

Query-time lookups have a cost worth knowing. On 2 vCPUs, 20M lookups over random IPs took about 19 seconds, roughly a million rows per second. The same lookups over sorted IPs took 1.7 seconds, because neighbouring keys stay in CPU cache. Logs ordered by time look random to the trie.

So when you do look up at query time, collapse to distinct IPs first. This runs against the log table from Step 5, but any table with an IPv6 client column works:

SELECT
    dictGetOrDefault('geo.ip_trie', 'time_zone_name', client_ip, '') AS time_zone,
    sum(hits) AS requests
FROM
(
    -- One lookup per distinct client instead of one per request
    SELECT client_ip, count() AS hits
    FROM logs.http_requests
    WHERE ts >= now() - INTERVAL 1 DAY
    GROUP BY client_ip
)
GROUP BY time_zone
ORDER BY requests DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

On 30M log rows over 500,000 distinct IPs, this ran in 1.77 seconds against 15.97 seconds for the per-row version. Same totals, 9x faster.

Step 5: Enrich at insert time

This is the part I'd push back on in most setups: don't geolocate historical logs at query time. IP blocks get reassigned, sold, and re-announced. A row written last March describes a request from last March, and it should keep the country that address had then. Query-time dictGet quietly rewrites history every time you refresh the database.

MATERIALIZED columns compute the value once, when the row is inserted, from whatever dictionary release is live at that moment:

CREATE DATABASE IF NOT EXISTS logs;

CREATE TABLE IF NOT EXISTS logs.http_requests
(
    ts              DateTime,
    client_ip       IPv6,              -- IPv4 clients land here as ::ffff:a.b.c.d
    method          LowCardinality(String),
    path            String,
    status          UInt16,
    bytes           UInt64,

    -- Computed once, at insert, from the dictionary version that was live at that moment.
    country_code    LowCardinality(String) MATERIALIZED geo_country_code(client_ip),
    city            String MATERIALIZED geo_city(client_ip),   -- tens of thousands of values: plain String
    connection_type LowCardinality(String) MATERIALIZED dictGetOrDefault('geo.ip_trie', 'connection_type', client_ip, '')
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (ts, client_ip);
Enter fullscreen mode Exit fullscreen mode

Three behaviours to know. Inserts never mention these columns; ClickHouse fills them. SELECT * does not return them, so name them explicitly. And ClickHouse expands the UDFs into the column definition when the table is created (check SHOW CREATE TABLE), so replacing a function later does not change existing columns.

Store finished values like a country code, never a dictionary row id or a place id you plan to resolve later. Ids belong to a release; the string you store today should mean the same thing in a year.

Adding enrichment to a table that already holds data takes two statements:

ALTER TABLE logs.http_requests_archive
    ADD COLUMN IF NOT EXISTS country_code LowCardinality(String) MATERIALIZED geo_country_code(client_ip);

-- Write the values into one partition at a time; runs as a background mutation.
ALTER TABLE logs.http_requests_archive MATERIALIZE COLUMN country_code IN PARTITION 202610;
Enter fullscreen mode Exit fullscreen mode

Until a partition is materialized, ClickHouse computes the column on read, from today's dictionary. After MATERIALIZE COLUMN, the value is written into the part and stops moving. Be honest about what a backfill gives you: today's answer for an old row, not the historical one. If you need true point-in-time geography for old data, keep the old database releases and backfill each month from its matching release. Track progress in system.mutations.

Step 6: Refresh daily without a gap

The refresh builds the next release beside the live one, refuses to swap if the build looks truncated, then swaps tables and reloads dictionaries. Per the ClickHouse docs on dictionary updates, the old copy keeps answering queries while the new one loads and is then replaced atomically. Budget memory for both copies during that window.

Save this as refresh.sql. The three INSERT ... SELECT statements are the ones from Step 2, pointed at the _next tables and the ipgeo/next/ folder:

-- Build the next release beside the live one. The live dictionary keeps answering the whole time.
CREATE TABLE IF NOT EXISTS geo.ip_cidr_next AS geo.ip_cidr;
CREATE TABLE IF NOT EXISTS geo.place_next   AS geo.place;
CREATE TABLE IF NOT EXISTS geo.country_next AS geo.country;
TRUNCATE TABLE geo.ip_cidr_next;
TRUNCATE TABLE geo.place_next;
TRUNCATE TABLE geo.country_next;

-- INSERT INTO geo.ip_cidr_next ... FROM file('ipgeo/next/db-ip-location.csv.gz', ...)  (Step 2, first statement)
-- INSERT INTO geo.place_next   ... FROM file('ipgeo/next/db-place.csv.gz', ...)        (Step 2, second statement)
-- INSERT INTO geo.country_next ... FROM file('ipgeo/next/db-country.csv.gz', ...)      (Step 2, third statement)

-- Refuse to swap in a build that looks truncated. throwIf stops the script here.
SELECT throwIf(count() < {min_blocks:UInt64}, 'ip_cidr_next looks truncated; keeping the live release')
FROM geo.ip_cidr_next
FORMAT Null;

-- Each EXCHANGE is atomic on an Atomic database; each reload swaps the in-memory copy atomically.
EXCHANGE TABLES geo.place AND geo.place_next;
EXCHANGE TABLES geo.country AND geo.country_next;
EXCHANGE TABLES geo.ip_cidr AND geo.ip_cidr_next;
SYSTEM RELOAD DICTIONARY geo.places;
SYSTEM RELOAD DICTIONARY geo.countries;
SYSTEM RELOAD DICTIONARY geo.ip_trie;
Enter fullscreen mode Exit fullscreen mode

If you forget to paste the inserts, the guard catches it: an empty _next table fails the row-count check and nothing gets swapped. That is exactly the failure mode the guard is for.

One limitation matters: the three table exchanges and dictionary reloads are separate operations, not one transaction. While a refresh runs, lookups can briefly combine data from different releases if place or country identifiers change. A failed reload can also leave only part of the update active.

For strict consistency, pause writes that depend on these dictionaries during the refresh, verify that all three dictionaries have loaded successfully, and resume writes only after validation. If queries must continue with no cross-release inconsistency, use a versioned lookup design and switch the complete release as one unit.

The loader needs its own user, with just enough rights. EXCHANGE TABLES requires DROP TABLE on both tables, and the reload grant only exists at the global level:

CREATE USER IF NOT EXISTS geo_loader IDENTIFIED WITH sha256_password BY 'replace-with-another-secret' HOST LOCAL;
GRANT SELECT, INSERT, CREATE TABLE, DROP TABLE, TRUNCATE ON geo.* TO geo_loader;
GRANT SYSTEM RELOAD DICTIONARY ON *.* TO geo_loader;
GRANT READ ON FILE TO geo_loader;
Enter fullscreen mode Exit fullscreen mode

The download side verifies the archive before anything touches ClickHouse. Your subscription gives you a static archive link, a signature link, and a public key; keep the links in environment variables, since they identify your account.

#!/usr/bin/env bash
# Download, verify, and swap in a new IP database release without a lookup gap.
set -euo pipefail

: "${IPGEO_DB_URL:?set to the archive link from your database subscription}"
: "${IPGEO_SIG_URL:?set to the matching signature link}"
: "${IPGEO_PUBKEY:?set to the path of public-key.pem}"
: "${CLICKHOUSE_PASSWORD:?set to the geo_loader password}"   # read by clickhouse-client
export CLICKHOUSE_USER="${CLICKHOUSE_USER:-geo_loader}"
USER_FILES="${USER_FILES:-/var/lib/clickhouse/user_files}"
MIN_BLOCKS="${MIN_BLOCKS:-1000000}"   # set to roughly 90% of your current row count
REFRESH_SQL="${REFRESH_SQL:-$(dirname "$0")/refresh.sql}"

work="$(mktemp -d)"
trap 'rm -rf "$work"' EXIT

fetch() {  # retries transient network errors, fails loudly on HTTP errors
  curl --fail --silent --show-error --location \
       --retry 3 --retry-delay 15 --connect-timeout 15 --max-time 3600 \
       -o "$2" "$1"
}

fetch "$IPGEO_DB_URL"  "$work/db.zip"
fetch "$IPGEO_SIG_URL" "$work/db.zip.sig"

# The signature proves the archive came from the vendor; checksum.txt only proves it arrived intact.
openssl dgst -sha256 -verify "$IPGEO_PUBKEY" -signature "$work/db.zip.sig" "$work/db.zip"
unzip -q "$work/db.zip" -d "$work/x"
( cd "$work/x" && sha256sum --check --quiet checksum.txt )

install -d "$USER_FILES/ipgeo/next"
cp "$work"/x/db-ip-location.csv.gz "$work"/x/db-place.csv.gz "$work"/x/db-country.csv.gz \
   "$USER_FILES/ipgeo/next/"

# SQL failures stop the script. Dictionary reload status needs a separate check.
clickhouse-client --param_min_blocks="$MIN_BLOCKS" --queries-file "$REFRESH_SQL"

bad=$(clickhouse-client --query "
    SELECT count()
    FROM system.dictionaries
    WHERE database = 'geo'
      AND name IN ('ip_trie', 'places', 'countries')
      AND status != 'LOADED'
")

total=$(clickhouse-client --query "
    SELECT count()
    FROM system.dictionaries
    WHERE database = 'geo'
      AND name IN ('ip_trie', 'places', 'countries')
")

if [[ "$bad" != "0" || "$total" != "3" ]]; then
    echo "Dictionary refresh incomplete; investigate before resuming writes" >&2
    exit 1
fi

echo "geo dictionaries refreshed at $(date -u +%FT%TZ)"
Enter fullscreen mode Exit fullscreen mode

I tested three scenarios: a clean release, a guard set too high, and an archive with one byte appended. The guard stops a truncated build before the swap, while signature verification prevents a modified archive from reaching ClickHouse. The script also checks dictionary load status after reloading. Run it from cron after the vendor's daily or weekly release, and alert on a non-zero exit. For strict cross-release consistency, validate the loaded release before resuming writes.

What ClickHouse IP geolocation costs in RAM

A dictionary loads fully into RAM on every server that queries it. That makes the column list in geo.ip_cidr a capacity decision, not a tidy-up. Measured on the 1M-range sample (1,435,478 blocks):

What the trie holds Memory, 1M ranges Full 41.4M-range file (linear estimate)
Country id only 49 MiB about 2.0 GiB
Row id only (for a two-dictionary layout) 53 MiB about 2.2 GiB
Every shipped column, as ids 370 MiB about 15 GiB
Every column with English names copied in 489 MiB about 20 GiB

The right-hand column is a straight multiplication, so treat it as a planning estimate, not a measurement. The pattern holds either way: names cost the most, so resolve them through the small place and country dictionaries (22 MiB for all 12 languages of place names on the sample) instead of copying them into every block.

You can go thinner. A trie that stores only a row id, plus a hashed_array of the row attributes keyed by that id, used 275 MiB for every column and 99 MiB for country, city, latitude, and longitude, with no measurable slowdown on random lookups. I didn't use it here because row ids change with every release, so a refresh has to swap two dictionaries that must agree on them, and two reloads are not one atomic step. In the gap between them, lookups can return another range's attributes. If memory is your binding constraint, that layout is still worth it; just design the swap so both halves change together before anything reads them.

A few extra notes

Every replica in a cluster loads its own copy and reloads on its own schedule, so for a few seconds after a refresh two replicas can disagree about a freshly reassigned range. For analytics that is noise. For anything that makes a decision per request, store the answer at insert as in Step 5, so the row records what was decided.

The same pattern carries more than location. An ASN database becomes a second trie keyed the same way, and a security database with VPN, proxy, and hosting flags becomes a third. Keep them as separate dictionaries unless you have checked that their ranges line up exactly.

If you use DB-IP Lite instead, its licence requires attribution to DB-IP on pages that show its data. And whichever source you use, an IP location is an estimate of where a network is registered or observed, not where a device is. The accuracy_radius and confidence columns are in the file for a reason; materialize them too if decisions depend on the city.

If your events pass through Kafka before they reach ClickHouse, enriching at the Kafka layer moves this work upstream. If Elasticsearch is the other half of your stack, the GeoIP ingest pipeline walkthrough covers the equivalent there, and the Grafana Geomap post puts the result on a map.

Start with the MMDB comparison query, even if you keep your current pipeline: point it at the table behind your existing dictionary and an MMDB from the same release, and in a minute you know whether that dictionary is exact. If it is not, the split function and the refresh script are the two pieces to copy, and the guard's MIN_BLOCKS is the one setting to tune for your file.

Top comments (0)