Что быстрее count(id) или count(UUID) в PostgreSQL?

Казалось бы в прошлой статье мы выяснили, что нет разницы между count(id), count(1) или count(*).

Давайте теперь узнаем, как же PostgreSQL работает с таким замечательным инструментом как UUID.

Общая идея заключается в том, что UUID предоставляет нам способ генерации уникальных идентификаторов без необходимости зависеть от конкретных контекстов или ресурсов. В PostgreSQL, для создания UUID, вы можете использовать extension uuid-ossp (для доп.функционала) или встроенную функцию gen_random_uuid().

UUID имеет несколько применений:

1. Уникальные идентификаторы для записей: Вместо использования автоинкрементных числовых идентификаторов (SERIAL или BIGSERIAL) вы можете использовать UUID для уникальной идентификации записей в таблицах. Это особенно полезно в распределенных системах, где целостность данных может быть подвергнута риску из-за конфликтов при генерации идентификаторов.
2. Распределенные системы и репликация: UUID обеспечивает гарантию уникальности идентификаторов даже в распределенных системах или при репликации данных, где отсутствует единая централизованная точка генерации идентификаторов.
3. Слияние данных: Если вам нужно объединить данные из разных источников, UUID может быть более надежным способом идентификации записей, так как вероятность коллизий (совпадения идентификаторов) очень низка.
4. Защита личных данных: Использование UUID может помочь обезличивать данные, так как UUID не содержит информацию о самой записи.
5. Публичные идентификаторы: UUID можно использовать как публичные идентификаторы для ресурсов в API или веб-приложениях.
6. Переносимость данных: Использование UUID позволяет легко перемещать и синхронизировать данные между разными базами данных и системами.
7. Замена недоступных идентификаторов: В некоторых случаях (например, при интеграции с другими системами), идентификаторы извне могут быть недоступными или неудобными для использования. UUID может быть хорошей альтернативой в таких сценариях.

Во первых, давайте сравним скорость генерации 10 млн. строк данных с UUID и без:

CREATE EXTENSION “uuid-ossp”;

\timing

create table records2 (id int8 not null, filler text);
insert into records2 SELECT id, repeat(‘ ‘, 100) FROM generate_series(1, 10000000) id;

INSERT 0 10000000
Time: 19633.695 ms (00:19.634)

А теперь то же самое, но с генерацией UUID:

create table records3 (uuid_v4 uuid not null, filler text);
insert into records3 SELECT gen_random_uuid(), repeat(‘ ‘, 100) FROM generate_series(1, 10000000) id;

INSERT 0 10000000
Time: 70658.563 ms (01:10.659)

Почти в 4 раза выше!!!

Сравним размер нагенерированных данных:

\dt+

public | records2 | table | postgres | permanent | heap | 1347 MB |
public | records3 | table | postgres | permanent | heap | 1421 MB |

А размер отличается не сильно.. Связана такая разница по скорости с долгой отработкой функции генерации UUID.

Но это не самое страшное. Давайте создадим индексы по этим полям (тоже есть разница, хоть и небольшая) и проверим работу count():

create index on records2 (id);
CREATE INDEX
Time: 6107.314 ms (00:06.107)

create index on records3 (uuid_v4);
CREATE INDEX
Time: 12160.637 ms (00:12.161)

И выполним count:

SELECT COUNT(id) FROM records2;

10000000
(1 row)

Time: 983.464 ms

SELECT COUNT(uuid_v4) FROM records3;

10000000
(1 row)

Time: 2305.091 ms (00:02.305)

Видим разницу с 2+ раза. Это связано с картой видимости данных для разных снимков – реализация MVCC. То есть находя строчку в индексе, PostgreSQL идет в visibility map (vm) и проверят, видим ли мы для данного снимка данных эту строку. Казалось бы в любом случае, он должен сходить 10 млн. раз, но есть один нюанс – в первом случае у нас данные генерируются последовательно и мы попадаем обычно в ту же страницу vm в памяти и она лежит закешированная + есть оптимизация у PostgreSQL на такой случай – а в случае UUID – данные случайны и каждый раз нужно искать в хеш таблице нужную страницу и при ее отсутствии подтягивать ее с диска.

Другими словами – в первом случае мы последовательно идем по блокам vm, а во втором случае у нас рандомное обращение. Вот и разница почти в 2 раза!!

В конце статьи, давайте еще посмотрим на размер данных и индексов:

SELECT relname, pg_size_pretty(pg_relation_size(oid)), pg_prewarm(oid) FROM pg_class WHERE relname like ‘records%’;
relname | pg_size_pretty | pg_prewarm
———————-+—————-+————
records2 | 1347 MB | 172414
records2_id_idx | 214 MB | 27422
records3 | 1420 MB | 181819
records3_uuid_v4_idx | 301 MB | 38506
(4 rows)

Time: 4015.334 ms (00:04.015)

Видим, что ожидаемо и индекс для UUID имеет больший размер. Проблему можно решить, используя генерацию UUID v7 – но пока патч не вошел в основную версию PostgreSQL.