Как уникальное ограничение влияет на производительность записи в DB Postgres

Ограничение UNIQUE, указанное в столбце или группе столбцов, влияет на производительность записи Postgres DB каким-либо образом? Как это внутренне функционирует?

Я имею в виду, выполняет ли он уникальную проверку во время ввода новой записи? Если да, то как это делается, выполняет ли линейный поиск дублирующее значение, уже существующее в БД? В этом случае считается, что это влияет на производительность, то есть больше числа уникальных ограничений хуже будет производительность записи/вставки? Это правда?

Ответ 1

Создание ограничения UNIQUE или PRIMARY KEY приводит к созданию индекса UNIQUE btree. Этот индекс должен обновляться всякий раз, когда любая запись INSERT ed, UPDATE ed или DELETE d, если какой-либо индексированный столбец изменен. Если индексированные столбцы не изменены, то HOT (оптимизация только для кучи) может ударить и избежать обновления индекса, особенно если у вас есть нестандартный FILLFACTOR, чтобы сделать пространство на страницах.

Обновление индекса при вставке/обновлении требует времени, поэтому вставка в индексированную таблицу UNIQUE выполняется медленнее, чем вставка без одного уникального индекса или первичного ключа. То же самое верно для UPDATE, но если индекс используется для того, чтобы найти кортеж для обновления (и избегать seqscan), он обычно является чистым выигрышем и не имеет индекса вообще. Если для поиска кортежа используется другой индекс или если seqscan работает быстрее (как это верно на небольших таблицах), то, как и в случае INSERT, индекс не имеет преимущества и просто берет на себя стоимость записи, чтобы обновить его для этой операции. Это справедливо для всех индексов, а не только индексов UNIQUE.

Каждый INSERT или UPDATE в индексированном столбце UNIQUE требует поиска индекса, чтобы убедиться, что ключ не конфликтует с существующим ключом. Из неопределенной памяти это сочетается с процессом вставки новой записи в индекс, но я не уверен на 100% там.

AFAIK DELETE не влияет на индекс. Он просто устанавливает xmax для кортежа в куче.

Индекс обновляется, даже если вы ROLLBACK транзакция или транзакция прерывается с ошибкой после успешной установки или обновления в столбце UNIQUE с ограничениями. VACUUM Работа autovacuum очищает записи мертвого индекса позже. См. Concurrency Управление в руководстве PostgreSQL.

Все это также относится к PRIMARY KEY, который также реализуется с использованием индекса UNIQUE.

Каждый индекс, включая индексы, используемые ограничениями PRIMARY KEY и UNIQUE, накладывает штраф на производительность записи.