Как ускорить создание индекса в PosgGreSQL?

Question

[BestJS]

В общем вопрос в заголовке.
Вот некоторые особенности которые я заметил:
1) PSQL создает индекс в одно-поточном режиме. Это 10% от всей производительности cpu.
2) Как я понял он считывает базу перерабатывает и сжимает. (В одно-поточном режиме это происходит оч медленно.)
3) Из-за того что он медленно перерабатывает данные, он не использует всю скорость ссд на котором расположена база.

Текстовый столбец к которому я хочу сделать индекс будет весить более 600GB.
делать индекс по моим расчетам он будет от 3-4 дней

Есть несколько идей как ускорить, но не нашел как их реализовать
Сделать много-поточность на cpu. Если загрузить проц на 100% то получаем увеличение производительности в 10раз
Сделать много-поточность на gpu но это уже из ряда фантастики.

Буду благодарен любым советам

Answer 1

[Melkij]

Какого индекса? Возможно сюрприз, но они сильно разные.

1) PSQL создает индекс в одно-поточном режиме. Это 10% от всей производительности cpu.

psql - это клиент, он вообще не создаёт индексы.
postgresql - начиная с 11 версии умеет многопоточное создание btree. Хотя, наверное, вернее будет сказать сортировку.

2) Как я понял он считывает базу перерабатывает и сжимает. (В одно-поточном режиме это происходит оч медленно.)

Не базу, а таблицу. Один раз полный seqscan для create index (с блокировкой записи) или два seqscan для create index concurrently (без блокировки таблицы).
Ничего не сжимает.

3) Из-за того что он медленно перерабатывает данные, он не использует всю скорость ссд на котором расположена база.

Ну смотря какие диски. Дешёвые SSD вполне можно озадачить временными файлами в большом количестве и одним ядром CPU, видел и такое.

Для ускорения построения большого индекса нужна память maintenance_work_mem. Сколько не жалко. 10, 20, 30, 100гб.
На postgresql 11 - плюс настроить число воркеров max_parallel_maintenance_workers, max_worker_processes, max_parallel_workers

но не нашел как их реализовать

Если хотите поучаствовать в разработке самого postgresql - welcome в список рассылок pgsql-hackers. Как делали parallel create index можно почитать по ссылкам с коммита.

Answer 2

[ky0]

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

И да, постгрес умеет строить индексы многопоточно:

PostgreSQL can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as parallel index build. For index methods that support building indexes in parallel (currently, only B-tree), maintenance_work_mem specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. Generally, a cost model automatically determines how many worker processes should be requested, if any.

https://www.postgresql.org/docs/11/sql-createindex.html

З.Ы. - ещё могу добавить, что с такими здоровыми индексами в целом неудобно работать, так что построение - это только первая проблема. Чем больше индекс, тем затратнее будет каждый инсерт/апдейт по этой таблице из-за его перестройки. Ну и, конечно, на определённом этапе индекс может просто перестать помещаться в память - это тоже не ок.