IPv4 and IPv6 Types in ClickHouse

ClickHouse provides dedicated IPv4 and IPv6 data types for storing network addresses compactly and querying them efficiently. Compared to storing addresses as String, these types use a fixed binary layout, sort correctly, and unlock IP-specific functions for CIDR and netmask operations. This page explains how the types are stored, how to model CIDR blocks, and how to pick the right type for network data.

IPv4 and IPv6 Storage

The IPv4 type is stored in 4 bytes as a UInt32, and the IPv6 type is stored in 16 bytes as a UInt128 big-endian value. Both accept standard string notation on insert and display addresses in their familiar human-readable form on SELECT, while keeping the compact binary representation on disk.

CREATE TABLE access_log
(
    ts   DateTime,
    src  IPv4,
    dst  IPv6
)
ENGINE = MergeTree
ORDER BY (src, ts);

INSERT INTO access_log VALUES
    (now(), '116.253.40.133', '2a02:aa08:e000:3100::2');

SELECT src, hex(src) FROM access_log;
-- 116.253.40.133    74FD2885

Because the binary form is fixed-width and ordered, IPv4/IPv6 columns work well as part of the ORDER BY key. See CREATE TABLE for the full DDL syntax.

IPv4 vs IPv6: Choosing a Type

If a single column must hold both IPv4 and IPv6 addresses, use IPv6 and rely on IPv4-mapped addresses. ClickHouse compares the two families directly, so toIPv4('127.0.0.1') = toIPv6('::ffff:127.0.0.1') evaluates to true. Prefer IPv4 only when you are certain the data is IPv4-only and want the smaller footprint.

CIDR Blocks and Netmasks

ClickHouse does not have a separate "CIDR" column type. The common pattern is to store the network address in an IPv4/IPv6 column and the prefix length in a small integer column, then use IP functions to expand or test ranges at query time.

CREATE TABLE subnets
(
    network IPv4,
    prefix  UInt8
)
ENGINE = MergeTree
ORDER BY network;

INSERT INTO subnets VALUES ('192.168.5.0', 24);

-- Expand a CIDR block into its first/last address
SELECT IPv4CIDRToRange(network, prefix) AS range
FROM subnets;
-- ('192.168.5.0','192.168.5.255')

IPv4CIDRToRange(ipv4, cidr) and IPv6CIDRToRange(ipv6, cidr) return a tuple of the lowest and highest address in the subnet, which is useful for range joins or computing broadcast addresses.

To test membership directly against CIDR notation, use isIPAddressInRange(address, prefix), which returns 1 when the address falls inside the network and 0 otherwise:

SELECT
    isIPAddressInRange('127.0.0.1', '127.0.0.0/8'),  -- 1
    isIPAddressInRange('127.0.0.1', 'ffff::/16');    -- 0

For privacy masking, cutIPv6(x, bytesToCutForIPv6, bytesToCutForIPv4) zeroes out the trailing bytes of an address, letting you anonymize hosts while keeping the network portion.

Conversion and Parsing Functions

Use toIPv4() and toIPv6() to convert strings or integers into the native types. For untrusted input, the OrNull and OrDefault variants avoid exceptions on malformed data:

SELECT toIPv4OrNull('999.999.999.999');    -- NULL
SELECT toIPv4OrDefault('999.999.999.999'); -- 0.0.0.0 (default)

If you need the raw numeric form, IPv4StringToNum/IPv4NumToString convert between text and a UInt32 integer, while IPv6StringToNum/IPv6NumToString convert between text and a FixedString(16) binary representation. When inserts fail with parsing errors, see Cannot parse IPv4 and Cannot parse IPv6 for the common causes.

Best Practices

1. Use IPv4/IPv6 instead of String for address columns to cut storage and enable IP functions and correct ordering.
2. Store CIDR blocks as a network IPv4/IPv6 column plus a UInt8 prefix rather than a free-text string.
3. For dual-stack data, standardize on IPv6 with IPv4-mapped addresses so all rows live in one comparable column.
4. Validate untrusted input with toIPv4OrNull/toIPv4OrDefault to keep bad rows from failing a whole batch.
5. Put the address column early in the ORDER BY key when most queries filter or group by source/destination IP.

Common Issues

1. Storing IPs as String: doubles or triples storage, sorts lexicographically (so 10.0.0.2 sorts after 100.0.0.1), and blocks IP functions.
2. Mixing families in an IPv4 column: inserting an IPv6 address into an IPv4 column raises a parse error. Use IPv6 for mixed data.
3. Expecting a CIDR type: ClickHouse has none. Model the network and prefix separately and use isIPAddressInRange or IPv4CIDRToRange.
4. Forgetting big-endian ordering: IPv6 is stored as UInt128 big-endian, so manual byte manipulation must account for byte order.

How Pulse Helps

Pulse continuously monitors ClickHouse clusters and surfaces schema and query patterns that affect performance, including columns that would benefit from native IPv4/IPv6 types or better ORDER BY keys for IP-heavy workloads. With automated health checks and AI-powered recommendations, Pulse helps you keep network-analytics tables compact and fast as data grows.

Frequently Asked Questions

Q: How much space do IPv4 and IPv6 types use in ClickHouse?
A: IPv4 is stored in 4 bytes as a UInt32, and IPv6 is stored in 16 bytes as a UInt128 big-endian value, before column compression.

Q: Can one column store both IPv4 and IPv6 addresses?
A: Yes. Use the IPv6 type and insert IPv4 addresses as IPv4-mapped addresses (::ffff:192.0.2.1). ClickHouse compares IPv4 and IPv6 values directly.

Q: Is there a CIDR or subnet data type?
A: No. Store the network address in an IPv4/IPv6 column with a separate prefix-length integer, and use IPv4CIDRToRange, IPv6CIDRToRange, or isIPAddressInRange for range work.

Q: How do I check whether an IP belongs to a subnet?
A: Use isIPAddressInRange(address, 'network/prefix'), which returns 1 if the address is inside the CIDR range and 0 otherwise.

Q: Should I use IPv4/IPv6 types or just store IPs as strings?
A: Prefer the native types. They are smaller, sort numerically rather than lexicographically, and unlock IP-specific functions that string columns cannot use.

Q: How can I anonymize stored IP addresses?
A: Use cutIPv6(x, bytesToCutForIPv6, bytesToCutForIPv4) to zero out the host portion of an address while preserving the network prefix.