Почему PostgreSQL не сохраняет на диск при длительном выполнении INSERT … SELECT …?

Question

Я использую стороннюю оболочку данных, чтобы переместить большой кусок данных (с очень простым преобразованием даты в одном столбце) в локальную базу данных. Используя курсор Django (потому что мне лень извлекать учетные данные для создания необработанного курсора psycopg2), я делаю этот вид запроса (анонимный и с удалением пары соединений, но в остальном идентичный оригиналу):

cursor.executemany(
    sql.SQL(
        """
        INSERT INTO local_table (
            foreign_key_id,
            other_foreign_key_id,
            datetime,
            comment
        )
        SELECT other_local_table.id,
               %s,
               (object_date + to_timestamp(object_time, 'HH24:MI')::time) at time zone '…',
               comment
          FROM imported_schema.remote_table
          JOIN other_local_table ON other_local_table.code = remote_table.code
        """
    ),
    [(dummy_id,)],
)

Однако через некоторое время локальный сервер Postgres всегда уничтожает OOM. Я ожидал, что Postgres сбросит новые строки на диск, чтобы избежать нехватки памяти, но, насколько я могу судить, этого просто не происходит - /var/lib/docker/volumes/vagrant_postgres_data только увеличивается на несколько МБ, в то время как резидентное использование памяти превращается в ГБ. На локальном сервере недостаточно оперативной памяти для хранения всего набора результатов в памяти, поэтому мне нужно решение, которое не требует более дорогой настройки оборудования.

Нужно ли устанавливать что-то вроде wal_sync_method или же work_mem чтобы это работало?

---

Согласно документам executemany должен быть правильным инструментом для работы:

Функция в основном полезна для команд, которые обновляют базу данных: любой набор результатов, возвращаемый запросом, отбрасывается.

---

Запуск контейнеров Postgres 10.6 в Linux на обоих серверах и в Django 2.1 локально. Я не использую никаких расширений, кроме FDW.

---

Объясните план:

Insert on local_table  (cost=817872.44..818779.47 rows=25915 width=56)
  ->  Subquery Scan on "*SELECT*"  (cost=817872.44..818779.47 rows=25915 width=56)
        ->  HashAggregate  (cost=817872.44..818390.74 rows=25915 width=48)
              Group Key: other_local_table.id, 1, timezone('…'::text, (remote_table.object_date + (to_timestamp((remote_table.object_time)::text, 'HH24:MI'::text))::time without time zone)), remote_table.comment
              ->  Nested Loop  (cost=101.15..807974.88 rows=989756 width=48)
                    ->  Nested Loop  (cost=0.57..60.30 rows=73 width=12)
                          ->  Nested Loop  (cost=0.29..42.35 rows=38 width=4)
                                ->  Seq Scan on fourth_local_table  (cost=0.00..7.45 rows=1 width=4)
                                      Filter: ((code)::text = '…'::text)
                                ->  Index Scan using … on third_local_table  (cost=0.29..34.49 rows=41 width=8)
                                      Index Cond: (id = fourth_local_table.id)
                          ->  Index Scan using … on other_local_table  (cost=0.29..0.45 rows=2 width=16)
                                Index Cond: (id = third_local_table.id)
                    ->  Foreign Scan on remote_table  (cost=100.58..9421.44 rows=151030 width=20)

---

postgresqltuner предлагает мне

set vm.overcommit_memory=2 в /etc/sysctl.conf … Это отключит чрезмерную загрузку памяти и предотвратит уничтожение postgresql убийцей OOM.

Это решение?

Answer 1

Я не вижу ничего в вашем плане выполнения, кроме HashAggregate которые могут потреблять любое количество памяти, и это должно быть ограничено work_mem,

Чтобы диагностировать это, вы должны сначала настроить свою систему так, чтобы вы получали обычную ошибку OOM, а не вызывали OOM killer. Это значит установить vm.overcommit_memory = 2 с sysctl и корректировка vm_overcommit_ratio в 100 * (RAM - swap) / RAM,

Когда сервер получает ошибку OOM, он сбрасывает текущие контексты памяти и их размер в журнал PostgreSQL. Это должно дать указание, куда идет память. Добавьте это к вопросу в случае сомнения.

Используете ли вы какие-либо сторонние расширения?