При сборе информации со сторонних ресурсов возникают одинаковые для всех проблемы — требуется проверить в своем хранилище, нет ли там уже собранных данных. Идентификаторов нет, источники разные, следовательно работать приходится с текстовыми ключами, поиск по которым гораздо медленней чем по числовым идентификаторам. Проблема обходится применением хэшей — ключевые строки хэшируются и хэш сохраняется в базе вместе с данными. При получении новой порции информации вычисляется хэш и производится индексированный поиск по нему, что намного быстрее поиска по строке.

ххhash очень удобен для этих целей, потому что возвращает 64-битовое число, а не строку, и может быть записан в bigint. Он быстрый, лаконичный, работает «на лету». Большая разрядность практически на нет сводит шансы коллизии, но если она и возникнет, достаточно добавить пробел к тексту для исправления ситуации. Правда есть одна проблема — этой функции нет в postgres «из коробки». На момент, когда мне понадобилась эта функция, я не нашел нужного расширения и написал свое, к тому же было интересно самому выполнить задачу.

Сначала я генерировал xxhash на клиентской стороне. Практически во всех популярных языках xxhash есть, но, например, в Питоне генерируется положительное целое в диапазоне 64-битовоого беззнакового, а bigint в postgres знаковый. И если хэш попадал в верхний диапазон, то при попытке сохранения возникало исключение. Решил просто:

MIDPOINT = 2 ** 63
safe_hash = xxhash(row['content']) - MIDPOINT

Но вставка строк с хэшем из Питона работала очень медленно, неприемлемо медленно. Поэтому я решил перенести вычисление xxhash на сервер — написал расширение с функцией xxhash на Си.

Исходный код расширения на гитхабе — https://github.com/anton‑zaharov/pg_xxhash

Результаты работы xxhash привожу ниже.

Создание таблицы с данными и заполнение ее случайными строками:

-- создаем тестовую таблицу
CREATE TABLE IF NOT EXISTS random_table (
    id SERIAL PRIMARY KEY,
    key VARCHAR(1024) NOT NULL,
    hash BIGINT NOT NULL,
    payload TEXT NOT NULL
);

-- создаем нужные функции, предварительно скачав готовый .so 
CREATE EXTENSION xxhash;


DO $$
DECLARE
    -- Настройки производительности
    total_rows INT := 10000000;       -- Сколько ВСЕГО строк нужно сгенерировать (например, 10 млн)
    batch_size INT := 100000;        -- Размер одного пакета 
    processed_rows INT := 0;
BEGIN
	-- 1: Оптимизация сессии (отключаем синхронную запись на диск для этой сессии)
    SET LOCAL synchronous_commit = OFF;

    -- 2: Если таблица уже была с индексами, их лучше временно удалить (DROP INDEX).
    -- (Для PRIMARY KEY это сделать сложнее, но если таблица пустая/новая, можно просто создать PK позже).
    DROP INDEX IF EXISTS idx_random_table_hash;
    DROP INDEX IF EXISTS idx_random_table_key;

    RAISE NOTICE 'Начало генерации % строк...', total_rows;

    -- 3: Цикл пакетной вставки
    WHILE processed_rows < total_rows LOOP
        
        INSERT INTO random_table (key, hash, payload)
        SELECT 
            generated_key,
            xxhash_bigint_varchar(generated_key), -- моя функция из расширения для входного параметра типа varchar
            'Запись номер ' || (processed_rows + i)
        FROM (
            SELECT 
                substring(repeat(md5(random()::text), 32) FROM 1 FOR (10 + (random() * 1014))::int) AS generated_key,
                i
            FROM generate_series(1, batch_size) AS i
        ) sub;

        processed_rows := processed_rows + batch_size;
        RAISE NOTICE 'Записано строк: %', processed_rows;
    END LOOP;

    RAISE NOTICE 'Данные вставлены. Создаем индексы и первичный ключ...';

    -- 4: Накатываем индексы на уже готовую и заполненную таблицу
    -- Это в разы быстрее, чем вставлять данные в таблицу, где индекс уже есть
    --ALTER TABLE my_random_table ADD CONSTRAINT my_random_table_pkey PRIMARY KEY (id);
    CREATE INDEX IF NOT EXISTS idx_random_table_hash ON random_table(hash);
	CREATE INDEX IF NOT EXISTS idx_random_table_key ON random_table(key);

    RAISE NOTICE 'Генерация успешно завершена!';
END $$;

Тест:

-- Шаг 1: Создаем временную таблицу с тестовым набором данных (например, 10 000 случайных записей из основной)
DROP TABLE IF EXISTS temp_test_search;
CREATE TEMP TABLE temp_test_search AS 
SELECT key, hash 
FROM random_table 
ORDER BY random() 
LIMIT 10000;

DO $$
DECLARE
    rec RECORD;
    start_time TIMESTAMP;
    key_start TIMESTAMP; key_end TIMESTAMP;
    hash_start TIMESTAMP; hash_end TIMESTAMP;
    join_key_start TIMESTAMP; join_key_end TIMESTAMP;
    join_hash_start TIMESTAMP; join_hash_end TIMESTAMP;
BEGIN
    -- 1. ТЕСТ: Поочередный поиск по KEY
    key_start := clock_timestamp();
    FOR rec IN SELECT key FROM temp_test_search LOOP
        PERFORM 1 FROM random_table WHERE key = rec.key;
    END LOOP;
    key_end := clock_timestamp();

    -- 2. ТЕСТ: Поочередный поиск по HASH
    hash_start := clock_timestamp();
    FOR rec IN SELECT hash FROM temp_test_search LOOP
        PERFORM 1 FROM random_table WHERE hash = rec.hash;
    END LOOP;
    hash_end := clock_timestamp();

    -- 3. ТЕСТ: Массовый INNER JOIN по KEY
    join_key_start := clock_timestamp();
    PERFORM 1 FROM random_table m INNER JOIN temp_test_search t ON m.key = t.key;
    join_key_end := clock_timestamp();

    -- 4. ТЕСТ: Массовый INNER JOIN по HASH
    join_hash_start := clock_timestamp();
    PERFORM 1 FROM random_table m INNER JOIN temp_test_search t ON m.hash = t.hash;
    join_hash_end := clock_timestamp();

    -- Сохраняем результаты в обычную временную таблицу
    DROP TABLE IF EXISTS test_results;
    CREATE TEMP TABLE test_results AS
    SELECT 
        'Поочередный поиск по строкам (Loop)'::text AS "Тип теста", 
        (key_end - key_start) AS "Поиск по KEY (Текст)", 
        (hash_end - hash_start) AS "Поиск по HASH (Число)"
    UNION ALL
    SELECT 
        'Массовое объединение (Inner Join)'::text, 
        (join_key_end - join_key_start), 
        (join_hash_end - join_hash_start);
END $$;

-- Вывод результата
SELECT * FROM test_results;

Результаты для тестовых данных в 10 000 строк по таблице разного размера:

* Во второй гистограмме на исходной таблице в 100млн строк тестовую выборку пришлось уменьшить до 100, поскольку запросы выполнялись очень долго из за ограничений железа. Но соотношение такое же — inner join быстрее в 2–3 раза

Результаты можно прокомментировать так: использование хэша обеспечивает минимум двукратное ускорение выполнения операций. Это не бог весть что, но на хороших нагрузках очень существенно.

P/S Для индексирования по полю bigint hash применялось btree. Я проверил как будет работать hash‑индекс — никакого улучшения не произошло, наоборот, время поиска увеличилось.