Всё, что кладёт прод, делает тебя умнее. © программист, положивший прод

Задачи: 

1. Добавить констрейнт на ключ партиции.

2. Добавить новый индекс contracts_status_index.

3. Удалить существующий индекс contracts_id_index.

Исходные данные: 

1. Очень большое количество данных в БД.

2. Постоянная высокая нагрузка: нельзя лочить таблицы более, чем на несколько секунд.

3. Партиционированная таблица - contracts (схема представлена ниже).

4. Сами партиции, создающиеся ежедневно (ключом партиций будет created_at, то есть все данные попадающие в дату 01.01.1993 будем сохранять в таблицу contracts_01_01_1993, и так далее).

5. PostgreSQL 11.

6. Rails 4.1.6.

Примечание: Партиционированные таблицы — partitioned tables (в psql-документации на русский они переведены как секционированные таблицы, но мы остановимся на названии, принятом у нас в компании Каруна).

План-капкан: идём по описанным выше задачам, попутно рассматривая потенциальные ошибки. В конце каждого пункта приводим финальное рабочее решение (допускаем существование альтернатив).

ADD CONSTRAINT

Хотелось бы рассмотреть создание constraint до того, как мы приступим к добавлению индекса. Эта важно по следующей причине: при создании индексов мы будем использовать команду ATTACH PARTITION. Если констрейнты для ключа партиций будут отсутствовать, то перед прикреплением таблиц будут происходить сканирование и валидация строки с ACCESS EXCLUSIVE LOCK, что для нас непозволительно.

Ошибка №1

Использование команды VALIDATE CONSTRAINT без команды NOT VALID. Из документации psql следует, что команда VALIDATE проходит по всем указанным полям, проверяя их валидность.

Использование команды NOT VALID до команды VALIDATE позволяет последней накладывать на таблицу ограничение SHARE UPDATE EXCLUSIVE, а не EXCLUSIVE LOCK (простыми словами, без NOT VALID мы получим долгий и тотальный лок на таблицы).

Ошибка №2

Использование команд NOT VALID и  VALIDATE CONSTRAINT в одной транзакции. Возьмем пример, положивший нам прод:

execute <<~SQL
  ALTER TABLE contracts_01_01_1993 ADD CONSTRAINT contracts_created_at_check 
    CHECK (created_at >= DATE '1993-01-01 00:00:00' AND created_at < DATE '1993-01-02 00:00:00') NOT VALID;

  ALTER TABLE contracts_01_01_1993 VALIDATE CONSTRAINT contracts_created_at_check;
SQL
# скорость выполнения такого запроса на одну партицию
# всего лишь с 20 млн записями будет выполняться примерно за 5 секунд

Рельса под капотом оборачивает всё, что внутри одного execute в единую транзакцию. В данном случае это критично, так как сочетание NOT VALID  и VALIDATE CONSTRAINT непозволительно.

Сама по себе операция NOT VALID проходит достаточно быстро и обладает EXCLUSIVE LOCK (то есть блочит всю таблицу на время исполнения операции). Но находясь в одной транзакции с более медленной (хоть и конкурентной) операцией VALIDATE CONSTRAINT, лок на всю таблицу не снимется, пока не завершится VALIDATE CONSTRAINT.

Решение

Для начала нам нужно проверить, работает ли вакуум на родительской и дочерних таблицах. Один из вариантов использовать проверку типа:

def vacuum_in_progress?(table_name)
  select_value("SELECT count(*)::int > 0 FROM pg_stat_progress_vacuum
               WHERE relid::regclass = '#{table_name}'::regclass;")
end

И разделяем две транзакции из ошибки №2:

def add_constraint_for_partitions
  execute <<~SQL
    ALTER TABLE contracts_01_01_1993 ADD CONSTRAINT contracts_01_01_1993_check 
      CHECK constraint_condition NOT VALID;
  SQL 
  # теперь операция занимает на те же 20 млн записей всего лишь 3.1 ms

  execute <<~SQL
    ALTER TABLE contracts_01_01_1993 VALIDATE CONSTRAINT contracts_01_01_1993_check;
  SQL
  # тут же мы остаёмся с теми же 5 секундами,
  # но больше таблица не лочится на это время
end

Итого

class CreateConstraintForContracts
  def up
    raise if vacuum_in_progress?(:contracts)
    # проверка родительской таблицы не проверяет дочерние
    raise if vacuum_in_progress?(:contracts_01_01_1993)
    # поэтому все партиции проверяем отдельно

    add_constraint_for_partitions # также для всех партиций
  end
end

CREATE INDEX

Ошибки

Тут кажется всё довольно просто на фоне других случаев, поэтому достаточно привести один из возможных рабочих примеров. Мы не встретили каких-либо подводных камней, но из важного стоит отметить использование флага CONCURRENTLY при создании индексов на партициях (чуть ниже мы ещё вернемся к этому флагу).

Решение

Создаём в конкурентном режиме индекс для каждой партиции. Наличие CONCURRENTLY является более предпочтительным, так как включаемый при этом лок защищает от выполнения вакуума и одновременных изменений схемы БД.

def create_index_for_partitions
  execute <<~SQL
    CREATE INDEX CONCURRENTLY IF NOT EXISTS contracts_01_01_1993_status_index
      ON contracts_01_01_1993 USING BTREE (status);
  SQL
  # повторяем для всех партиций
end

Далее создаём такой же индекс для родительской таблицы. Для этого используем флаг ONLY. Если его не использовать, то постгрес будет пытаться создать этот индекс не только для родительской таблицы, но и для всех дочерних, что займёт слишком много времени (и плюс создаст дубликаты уже существующих индексов на дочерних таблицах).

def create_index_for_partitioned_table
  execute <<~SQL
    CREATE INDEX IF NOT EXISTS contracts_status_index ON ONLY contracts (status);
  SQL
  # psql позволяет не писать USING BTREE
end

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

def attach_partitions
  execute <<~SQL
    ALTER INDEX contracts_status_index ATTACH PARTITION contracts_01_01_1993_status_index;
  SQL 
  # повторяем для всех партиций
  # constraint выше мы рассматривали именно для этого случая
end

# ATTACH просто прикрепляет дочерний индекс к индексу родителя.

Итого:

class CreateStatusIndexForContracts
  disable_ddl_transaction! 
  # рельсовый метод, который позволяет запускать миграцию вне транзакции      
  # его реализацию мы не будем рассматривать

  def up
    return if vacuum_in_progress?(:contracts)
    return if vacuum_in_progress?(:contracts_01_01_1993)
    # повторить для всех партиций

    create_index_for_partitions
    create_index_for_partitioned_table
    attach_partitions
  end
end

DROP INDEX

Ошибка №1:

execute <<~SQL
  ALTER TABLE contracts DETACH PARTITION contracts_01_01_1993;
  DROP INDEX IF EXISTS contracts_01_01_1993_status_index;
  ALTER TABLE contracts ATTACH PARTITION contracts_01_01_1993;
SQL

В чём соль: psql не позволяет дропнуть индекс на дочерней таблице. Решение пришло незамедлительно. Открепить партицию, дропнуть индекс, прикрепить партицию назад. Звучит достаточно разумно, но есть один нюанс (тут могла быть ваша шутка про нюанс). А именно: при прикреплении таблицы-A, к таблице-B psql автоматически проходит по индексам таблицы-A и сравнивает их с индексами таблицы-B. Недостающие индексы создаются автоматически для партиции (т.е. таблица-A).

В итоге  после удаления индексов командой DROP эти индексы создаются заново при выполнении ATTACH PARTITION. По факту, имеем провисание БД на непростительное время и горящие клиентские попы.

Ошибка №2

Так как мы подразумеваем, что у нас высокая нагрузка на БД, то любые взаимодействия с текущей партицией (то есть той, в которую конкретно сегодня происходит активная запись) мы пытаемся свести к минимуму. По возможности, все операции с ней оставляем на самый последний момент.

Решение

Это не единственно верное решение. Если у вас нет огромной нагрузки на БД, и вы можете себе позволить провисание оной продолжительностью от нескольких минут и более, то можно смело дропать индексы на родительской (партиционированной) таблице и по умолчанию, индексы на партициях тоже удалятся. 

Также, если дропаются индексы уникальные для самих партиций (то есть индексы, которые есть у партиции, но нет у родительской таблицы), то будет достаточно запустить дроп индексов для нужной партиции с флагом CONCURRENTLY.

Снимаем триггеры с таблицы, также не забываем про сиквенсы, если таковые имеются. Всё это нужно будет удалить/пересоздать/перевесить на новую таблицу (на том, как это делается, не будем останавливаться, так как больший интерес для нас представляет DROP — он показался сложнее).

1. Отключаем ddl транзакции для миграции (иначе некоторые операции, например, DROP INDEX CONCURRENTLY не выполнятся).

   disable_ddl_transaction!

2. Создаём новую партиционированную таблицу, полностью идентичную старой, с теми же индексами, констрейнтами, блекджэком и правами. За исключением того, что индекс, который мы хотим удалить, не создаём.

   def create_new_table_contracts
     execute <<-SQL
       CREATE TABLE contracts_copy(id, amount, other_columns);
       
       ALTER TABLE contracts_copy ADD CONSTRAINT your_name CHECK (your_condition);
         
       CREATE INDEX your_index ON contracts_copy (your_condition); 
   
       GRANT INSERT ON contracts_copy TO your_role;
       # и делаем всё то, что может ещё потребоваться для таблицы
     SQL
   end
   
   def reattach_partitions_from_new_to_old_table
     transaction do
       execute('LOCK TABLE contracts IN ACCESS EXCLUSIVE MODE;')
       # лочим таблицу и, как следствие, все партиции
       
       execute <<~SQL 
         ALTER TABLE contracts  DETACH PARTITION contracts_01_01_1993;
         ALTER TABLE contracts_copy ATTACH PARTITION contracts_01_01_1993 
           FOR VALUES FROM ('01-01-1993') TO ('31-01-1993');
       SQL
       # Детачим из старой таблицы и аттачим к новой
       # Крайне быстрая операция, так как данные никуда не перетекают
       # и не изменяются
     end
   end

   WARNING

   Индексы партиций должны иметь ВСЕ индексы новой партиционированной таблицы. В противном случае при аттаче будут создаваться отсутствующие индексы, что болезненно увеличит время лока и полученных люлей...  

3. После того, как все партиции получили нового хозяина, нужно переименовать таблицы.

   def rename_tables
     execute <<~SQL
       ALTER TABLE contracts     RENAME TO contracts_old;
       ALTER TABLE contracts_new RENAME TO contracts;
     SQL
   end

   Тут лок заканчивается, и всё должно продолжить функционировать в прежнем режиме. Что мы получаем именно таким способом? Теперь у нас есть новая таблица без индекса, который мы собирались удалять. Но этот индекс всё еще есть у самих партиций.

4. Наконец-то мы можем, не беспокоясь о времени выполнения команды, дропнуть оставшийся индекс на всех партициях, а старую таблицу — со спокойной душой удалить.

   def drop_indexes_and_old_table
     raise if vacuum_in_progress?(contracts_01_01_1993) # снова для всех партиций
     
     execute("DROP INDEX CONCURRENTLY IF EXISTS contracts_01_01_1993_status_index;") # так же для всех       
     execute("DROP TABLE IF EXISTS contracts_old;")
   end

Итого

class DropStatusIndexForcontracts
  disable_ddl_transaction! # помним: CONCURRENTLY vs DDL 
  
  def up
    raise if vacuum_in_progress?(:contracts)
    raise if vacuum_in_progress?(:contracts_01_01_1993) # ну, вы поняли
    
    create_new_table_contracts
    reattach_partitions_from_new_to_old_table
    sleep(1) # чтобы немного передохнуть и разгрузить БД
	  rename_tables
    drop_indexes_and_old_table
  end
end

МЕГА итого

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

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

UPD

Thanks to @Melkij за замечания по поводу некорректностей с DROP/CREATE INDEX