Временные таблицы PostgreSQL

Мне нужно выполнить запрос 2,5 миллиона раз. Этот запрос генерирует несколько строк, которые мне нужны AVG(column), а затем используйте этот AVG для фильтрации таблицы из всех значений ниже среднего. Затем мне нужно INSERT отфильтровать результаты в таблицу.

Единственный способ сделать такую вещь с разумной эффективностью - это создать TEMPORARY TABLE для каждого потока python-postmaster. Я просто надеюсь, что эти TEMPORARY TABLE не будут сохраняться на жестком диске (вообще) и будут оставаться в памяти (ОЗУ), если только они не работают из рабочей памяти, конечно.

Я хотел бы знать, будет ли TEMPORARY TABLE брать записи на диск (что помешало бы INSERTS, т.е. замедлить весь процесс)

Ответ 1

Обратите внимание, что в Postgres поведение по умолчанию для временных таблиц заключается в том, что они не удаляются автоматически и данные сохраняются при фиксации. См. ON COMMIT.

Тем не менее временная таблица удалена в конце сеанса базы данных:

Временные таблицы автоматически отбрасываются в конце сеанса или необязательно в конце текущей транзакции.

Есть несколько соображений, которые вы должны учитывать:

Если вы хотите явно указать DROP временную таблицу в конце транзакции, создайте ее с помощью синтаксиса CREATE TEMPORARY TABLE ... ON COMMIT DROP.
При наличии пула соединений сеанс базы данных может охватывать несколько клиентских сеансов; чтобы избежать столкновений в CREATE, вы должны отказаться от временных таблиц - либо до возвращения соединения к пулу (например, делая все внутри транзакции и используя синтаксис создания ON COMMIT DROP), либо по мере необходимости (предшествующий любой оператор CREATE TEMPORARY TABLE с соответствующим DROP TABLE IF EXISTS, который имеет то преимущество, что он также работает вне транзакций, например, если соединение используется в режиме автоматической фиксации.)
Пока используется временная таблица, сколько ее будет в памяти, прежде чем переходить на диск? См. temp_buffers в postgresql.conf
Что-нибудь еще, о чем я должен беспокоиться, когда часто работаю с временными таблицами? Вакуум рекомендуется после того, как у вас есть DROPped временные таблицы, чтобы очистить любые мертвые кортежи из каталога. Postgres автоматически вакуумируют каждые 3 минуты или около того для вас при использовании настроек по умолчанию (auto_vacuum).

Кроме того, не связанный с вашим вопросом (но, возможно, связанный с вашим проектом): имейте в виду, что если вам нужно запускать запросы по временной таблице после того, как вы ее заполнили, тогда неплохо создать соответствующие индексы и выпустите ANALYZE в соответствующей таблице temp после того, как вы закончите вставлять в нее. По умолчанию оптимизатор, основанный на затратах, предположит, что недавно созданная таблица temp имеет ~ 1000 строк, и это может привести к низкой производительности, если таблица temp действительно содержит миллионы строк.

Ответ 2

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

EDIT: Если вам абсолютно нужны временные таблицы только для RAM, вы можете создать табличное пространство для своей базы данных на RAM-диске (/dev/shm works). Это уменьшает количество дискового ввода-вывода, но будьте осторожны, что в настоящее время это невозможно сделать без записи физического диска; при создании временной таблицы механизм БД будет сбрасывать список таблиц в стабильное хранилище.