Постгрес. Могу ли я ожидать хорошего ускорения, переключив BIGINT на INT?

В этой базе данных Postgres есть таблицы всех размеров до 10 миллионов строк. Почти все имеют первичный ключ BIGINT, считая от 1 и выше.

Поскольку BIGINT имеет 64 бита, но 10 миллионов строк находятся в пределах 2 миллиардов максимума INT, стоит ли преобразовывать эти столбцы BIGINT в INT (32 бита) и SMALLINT (16 бит) для ускорения некоторых тяжелых SQL-запросов? Более компактное хранение индексов/таблиц должно дать нам более высокий коэффициент попаданий в кеш. На какое ускорение я могу рассчитывать, если оно вообще есть? И есть ли недостатки, если не использовать BIGINT? (при условии, что достижение максимального значения INT/SMALLINT никогда не будет проблемой)


postgresql database database-indexes
person Kjetil S.    schedule 07.04.2020    source источник
comment
предполагая, что достижение максимального значения INT/SMALLINT никогда не будет проблемой... это большое предположение. Всегда есть кто-то, кто приходит с блестящей идеей добавить x миллионов/миллиардов/что угодно в PK для хранения временных записей/записей v2/что угодно, и поскольку это сработало так хорошо в первый раз, давайте делать это регулярно.   -  person JGH    schedule 07.04.2020

Ответы (3)


arrow_upward
2
arrow_downward

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

Стандартный индекс btree, поддерживающий PK, не изменится в размере, 4 байта потеряны для заполнения выравнивания. Но если вы используете дополнительный 4-байтовый столбец в покрывающем индексе, это экономит 8 байтов вместо 4, что делает индексный кортеж 20 байтами вместо 28.

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

Если вы уверены, что не сожжете более 3^31 числа (не 2^32: Postgres использует целое число со знаком, вам придется использовать и отрицательную половину) за время жизни таблицы, и вы на самом деле экономите место в таблицах и индексах, тогда, во что бы то ни стало, переходите на простой integer. Я видел реальную разницу много раз. Но вам нужно некоторое понимание механизмов хранения Postgres, прежде чем вы возитесь с этим.

Связанный:

person Erwin Brandstetter    schedule 07.04.2020

arrow_upward
1
arrow_downward

Достоверный ответ на этот вопрос даст только нагрузочный тест, но я ожидаю, что заметного прироста производительности вы не увидите.

bigint здесь правильный тип данных. Даже если вы уверены, что не исчерпаете лимит integer, учтите следующее:

  • если вы генерируете значения, используя последовательность, вы, вероятно, не будете использовать все возможные числа, транзакции могут быть отброшены

  • когда-то жила парочка умных людей, которые думали, что 232 — это намного больше IP-адресов, чем кому-либо может понадобиться.

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

Преждевременная оптимизация — корень всех зол.

person Laurenz Albe    schedule 07.04.2020

arrow_upward
0
arrow_downward

Данные обычно хранятся на диске с выравниванием по 8-байтовым границам. Индекс одного столбца будет иметь тот же размер для bigint, что и для int. Для таблицы или многостолбцового индекса int может упаковываться более плотно, в зависимости от того, какие соседние столбцы могут быть объединены вместе, чтобы уместиться в 8 байтах.

Изменение производительности, скорее всего, будет небольшим или отсутствующим, и его трудно предсказать. Чтобы узнать ответ, вам придется пройти тест. Но, вероятно, это не стоит того времени, которое потребуется для этого.

person jjanes    schedule 07.04.2020