И снова здравствуйте! Уже в следующий вторник стартует новый поток по курсу «Реляционные СУБД», поэтому мы продолжаем публиковать полезный материал по теме. Поехали.
На прошлой неделе я писал о конкурентном доступе в Postgres, какие команды блокируют друг друга, и как вы можете диагностировать заблокированные команды. Конечно, после постановки диагноза вам может потребоваться и лечение. С Postgres можно выстрелить себе в ногу, но Postgres также предлагает вам способы не сбить наводку. Вот некоторые из важных советов о том, как стоит и как не стоит делать, которые мы сочли полезными при работе с пользователями по переходу с их единой базы данных Postgres на Citus или при создании новых приложений аналитики в реальном времени.
-- блокировка чтения и записи таблицы, пока она не будет полностью переписана (часы?)
ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now();
-- select, update, insert и delete заблокированы, пока каталог не будет обновлен (миллисекунды)
ALTER TABLE items ADD COLUMN last_update timestamptz;
-- select и insert проходят, некоторые update и delete заблокированы, пока таблица переписывается
UPDATE items SET last_update = now();
update
и delete
на долгое время, обновляя небольшими порциями, например:do {
numRowsUpdated = executeUpdate(
"UPDATE items SET last_update = ? " +
"WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)",
now);
} while (numRowsUpdate > 0);
SELECT
к таблице, не делайте так:ALTER TABLE items ADD COLUMN last_update timestamptz;
SET lock_timeout TO '2s'
ALTER TABLE items ADD COLUMN last_update timestamptz;
lock_timeout
DDL-команда не будет выполнена, если она окажется в ожидании блокировки и, таким образом, заблокирует запросы более чем на 2 секунды. Недостатком является то, что ваш ALTER TABLE
может быть не выполнен, но вы можете повторить попытку позже. Вы можете запросить pg_stat_activity, чтобы увидеть, есть ли у вас длительные запросы перед запуском DDL-команды.CREATE INDEX
блокирует все записи на время выполнения команды. Несмотря на то, что она не блокирует SELECT
-ы, это все же довольно плохо, и есть лучший способ:CREATE INDEX CONCURRENTLY
.-- блокирует все записи
CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops);
-- блокирует только другие DDL
CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops);
DROP INDEX CONCURRENTLY items_value_idx
и попробуйте создать его снова.BEGIN;
-- чтение и запись заблокированы отсюда:
TRUNCATE items;
- длительная операция:
\COPY items FROM 'newdata.csv' WITH CSV
COMMIT;
BEGIN;
CREATE TABLE items_new (LIKE items INCLUDING ALL);
-- длительная операция:
\COPY items_new FROM 'newdata.csv' WITH CSV
-- чтение и запись заблокированы отсюда:
DROP TABLE items;
ALTER TABLE items_new RENAME TO items;
COMMIT;
BEGIN;
LOCK items IN EXCLUSIVE MODE;
...
ALTER TABLE
, но пока создается индекс для первичного ключа, что может занять много времени, если таблица большая, все запросы будут заблокированы.ALTER TABLE items ADD PRIMARY KEY (id); -- блокирует запросы на длительное время
CREATE UNIQUE INDEX CONCURRENTLY
, а затем использовать уникальный индекс в качестве первичного ключа, что является быстрой операцией.CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); -- занимает много времени, но не блокирует запросы
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk; -- блокирует запросы, но ненадолго
VACUUM FULL
звучит как то, что вы бы хотели сделать, чтобы вычистить “пыль” вашей базы данных, более подходящей командой была бы:PLEASE FREEZE MY DATABASE FOR HOURS;
VACUUM FULL
перезаписывает всю таблицу на диск, что может занять часы или дни, и при этом блокирует все запросы. Хотя для VACUUM FULL
есть несколько допустимых вариантов использования, таких как таблица, которая раньше была большой, но теперь она маленькая и все еще занимает много места, но это, вероятно, не ваш вариант.VACUUM
, но НЕ VACUUM FULL
.ERROR: deadlock detected
DETAIL: Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483.
Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661.
BEGIN;
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- захватывает блокировку на hello
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- блокировка в ожидании world
END;
BEGIN
UPDATE items SET counter = counter + 1 WHERE key = 'world'; -- захватывает блокировку на world
UPDATE items SET counter = counter + 1 WHERE key = 'hello'; -- блокировка в ожидании hello
END;
hello
, затем world
, то первая транзакция заблокирует вторую на hello
, прежде чем она сможет захватить любые другие блокировки.К сожалению, не доступен сервер mySQL