Hashfuncs DuckDB Extension

The Hashfuncs extension, developed by Query.Farm, adds non-cryptographic hash functions to DuckDB.

DuckDB extensions are plugins that expand the core DuckDB engine with new capabilities.

Installation

hashfuncs is a DuckDB Community Extension.

You can now use this by using this SQL:

install hashfuncs from community;
load hashfuncs;

What is hashing?

Hashing is a process that transforms input data of any size into a fixed-size output, which is typically a hash value or hash code. Hash functions are designed to be fast, deterministic (same input always produces the same output), and distribute hash values uniformly across the output space.

Non cryptographic hash functions are commonly used for:

  • Database indexing: Creating efficient lookup structures

  • Partitioning data: Distributing data across multiple nodes or buckets

  • Caching: Creating cache keys

  • Bloom filters: Probabilistic data structures for membership testing

Available Hash Functions

This extension provides multiple high-performance non-cryptographic hash algorithms, each optimized for different use cases:

xxHash Family

xxHash is an extremely fast non-cryptographic hash algorithm, reaching speeds close to RAM limits.

xxh32(data [, seed])

  • Returns: UINTEGER (32-bit unsigned integer)
  • Seed type: UINTEGER (optional)
  • Description: 32-bit version of xxHash, good for hash tables and checksums
SELECT xxh32('hello world');
┌──────────────────────┐
│ xxh32('hello world') │
│        uint32        │
├──────────────────────┤
│      3468387874      │
│    (3.47 billion)    │
└──────────────────────┘

SELECT xxh32('hello world', 42);
┌──────────────────────────┐
│ xxh32('hello world', 42) │
│          uint32          │
├──────────────────────────┤
│        4225033588        │
│      (4.23 billion)      │
└──────────────────────────┘

xxh64(data [, seed])

  • Returns: UBIGINT (64-bit unsigned integer)
  • Seed type: UBIGINT (optional)
  • Description: 64-bit version of xxHash, recommended for most applications
SELECT xxh64('hello world');
┌──────────────────────┐
│ xxh64('hello world') │
│        uint64        │
├──────────────────────┤
│ 5020219685658847592  │
│  (5.02 quintillion)  │
└──────────────────────┘

SELECT xxh64('hello world', 12345);
┌─────────────────────────────┐
│ xxh64('hello world', 12345) │
│           uint64            │
├─────────────────────────────┤
│    15771590491225725957     │
└─────────────────────────────┘

xxh3_64(data [, seed])

  • Returns: UBIGINT (64-bit unsigned integer)
  • Seed type: UBIGINT (optional)
  • Description: Latest xxHash algorithm, optimized for modern CPUs
SELECT xxh3_64('hello world');
┌────────────────────────┐
│ xxh3_64('hello world') │
│         uint64         │
├────────────────────────┤
│  15296390279056496779  │
└────────────────────────┘

SELECT xxh3_64('hello world', 999);
┌─────────────────────────────┐
│ xxh3_64('hello world', 999) │
│           uint64            │
├─────────────────────────────┤
│     3002856137354040482     │
│     (3.00 quintillion)      │
└─────────────────────────────┘

xxh3_128(data [, seed])

  • Returns: UHUGEINT (128-bit unsigned integer)
  • Seed type: UBIGINT (optional)
  • Description: 128-bit version providing larger hash space, reducing collision probability
SELECT xxh3_128('hello world');
┌─────────────────────────────────────────┐
│         xxh3_128('hello world')         │
│                 uint128                 │
├─────────────────────────────────────────┤
│ 225447084758876380551077147957698971904 │
└─────────────────────────────────────────┘

SELECT xxh3_128('hello world', 777);
┌─────────────────────────────────────────┐
│      xxh3_128('hello world', 777)       │
│                 uint128                 │
├─────────────────────────────────────────┤
│ 283192007746380917896797379546610829141 │
└─────────────────────────────────────────┘

RapidHash Family

RapidHash is designed for exceptional speed while maintaining good hash quality.

rapidhash(data [, seed])

  • Returns: UBIGINT (64-bit unsigned integer)
  • Seed type: UBIGINT (optional)
  • Description: Main RapidHash algorithm, optimized for speed and quality
SELECT rapidhash('hello world');
┌──────────────────────────┐
│ rapidhash('hello world') │
│          uint64          │
├──────────────────────────┤
│   3397907815814400320    │
│    (3.40 quintillion)    │
└──────────────────────────┘

SELECT rapidhash('hello world', 2023);
┌────────────────────────────────┐
│ rapidhash('hello world', 2023) │
│             uint64             │
├────────────────────────────────┤
│      11789095433300219990      │
└────────────────────────────────┘

MurmurHash3 Family

MurmurHash3 is a well-established non-cryptographic hash function known for good distribution and performance.

murmurhash3_32(data [, seed])

  • Returns: UINTEGER (32-bit unsigned integer)
  • Seed type: UINTEGER (optional)
  • Description: 32-bit MurmurHash3, widely used and tested
SELECT murmurhash3_32('hello world');
┌───────────────────────────────┐
│ murmurhash3_32('hello world') │
│            uint32             │
├───────────────────────────────┤
│          1586663183           │
│        (1.59 billion)         │
└───────────────────────────────┘

SELECT murmurhash3_32('hello world', 123);
┌────────────────────────────────────┐
│ murmurhash3_32('hello world', 123) │
│               uint32               │
├────────────────────────────────────┤
│             679062093              │
│          (679.06 million)          │
└────────────────────────────────────┘

murmurhash3_128(data [, seed])

  • Returns: UHUGEINT (128-bit unsigned integer)
  • Seed type: UINTEGER (optional)
  • Description: 128-bit MurmurHash3 for x86 platforms
SELECT murmurhash3_128('hello world');
┌─────────────────────────────────────────┐
│     murmurhash3_128('hello world')      │
│                 uint128                 │
├─────────────────────────────────────────┤
│ 206095855024402301784664199839047883400 │
└─────────────────────────────────────────┘

murmurhash3_x64_128(data [, seed])

  • Returns: UHUGEINT (128-bit unsigned integer)
  • Seed type: UINTEGER (optional)
  • Description: 128-bit MurmurHash3 optimized for x64 platforms
SELECT murmurhash3_x64_128('hello world');
┌─────────────────────────────────────────┐
│   murmurhash3_x64_128('hello world')    │
│                 uint128                 │
├─────────────────────────────────────────┤
│ 228083453807047072434243676435732455694 │
└─────────────────────────────────────────┘

Supported Data Types

All hash functions support the following DuckDB data types:

  • String types: VARCHAR, BLOB
  • Integer types: TINYINT, SMALLINT, INTEGER, BIGINT, HUGEINT
  • Unsigned integer types: UTINYINT, USMALLINT, UINTEGER, UBIGINT, UHUGEINT
  • Floating point types: FLOAT, DOUBLE
  • Date/time types: DATE, TIME

Performance Characteristics

Algorithm Speed Quality Output Size Best Use Case
xxh32 Very Fast Good 32-bit Legacy systems, hash tables
xxh64 Very Fast Very Good 64-bit General purpose hashing
xxh3_64 Fastest Excellent 64-bit Modern applications
xxh3_128 Fast Excellent 128-bit When collision resistance is critical
rapidhash Extremely Fast Good 64-bit High-throughput applications
rapidhash_micro Extremely Fast Good 64-bit Small data, high frequency
rapidhash_nano Fastest Fair 64-bit Tiny data, maximum speed
murmurhash3_32 Fast Very Good 32-bit Distributed systems, Bloom filters
murmurhash3_128 Fast Very Good 128-bit UUID generation, partitioning
murmurhash3_x64_128 Fast Very Good 128-bit 64-bit optimized partitioning

Love ❤️ this DuckDB extension? You’ll Love This.

Get the best from Query.Farm — smart tips, powerful tools, and project updates sent directly to your inbox, but only when we’ve got something great to share.

Usage Examples

Basic Hashing

-- Hash various data types
SELECT xxh64(42);                    -- Integer
SELECT xxh64('Hello, World!');       -- String
SELECT xxh64('2023-12-01'::DATE);    -- Date
SELECT xxh64(3.14159::FLOAT);        -- Float

Data Partitioning

-- Distribute data across 10 partitions
SELECT
    customer_id,
    xxh64(customer_id) % 10 as partition_id
FROM customers;

Creating Consistent Hash Keys

-- Create cache keys from multiple columns
SELECT
    user_id,
    product_id,
    xxh64(CONCAT(user_id, ':', product_id)) as cache_key
FROM user_purchases;

Data Integrity Verification

-- Create checksums for data verification
SELECT
    file_name,
    file_content,
    xxh3_128(file_content) as checksum
FROM file_storage;

Using Seeds for Different Hash Spaces

-- Create different hash values for the same data
SELECT
    data,
    xxh64(data, 0) as hash_space_0,
    xxh64(data, 1) as hash_space_1,
    xxh64(data, 2) as hash_space_2
FROM my_table;

Bloom Filter Implementation

-- Generate multiple hash values for Bloom filter
-- Learn more about Bloom filters: https://en.wikipedia.org/wiki/Bloom_filter
WITH bloom_hashes AS (
    SELECT
        item,
        murmurhash3_32(item, 0) % 1000000 as hash1,
        murmurhash3_32(item, 1) % 1000000 as hash2,
        murmurhash3_32(item, 2) % 1000000 as hash3
    FROM items
)
SELECT * FROM bloom_hashes;

Load Balancing

-- Distribute requests across servers
SELECT
    request_id,
    xxh3_64(request_id) % 5 as server_id
FROM incoming_requests;

Algorithm Selection Guide

For general-purpose hashing: Use xxh3_64 - it provides the best balance of speed and quality for modern applications.

For maximum speed: Use rapidhash or rapidhash_nano when you need the absolute fastest hashing.

For legacy compatibility: Use murmurhash3_32 if you need compatibility with existing systems using MurmurHash.

For high collision resistance: Use xxh3_128 or murmurhash3_x64_128 when you need larger hash spaces.

For small data: Use rapidhash_micro or rapidhash_nano for very small inputs.

Performance Tips

  1. Choose appropriate output size: Use 32-bit hashes only when memory is constrained; 64-bit hashes provide better collision resistance.

  2. Use seeds wisely: Seeds allow you to create independent hash functions from the same algorithm.

  3. Consider your data distribution: Some algorithms perform better with certain types of input data.

  4. Benchmark for your use case: Performance can vary based on your specific data patterns and hardware.

Limitations

  • These are non-cryptographic hash functions - do not use them for security-sensitive applications like password hashing or digital signatures
  • Hash collisions are possible (but rare with good algorithms and appropriate output sizes)
  • Performance characteristics may vary based on input data patterns and hardware architecture