Lo que estoy tratando de hacer:

Estoy tratando de mover unos 2 millones de registros de una tabla a otra. Para hacer esto, estoy haciendo una declaración de insert que se alimenta de una consulta de select .

 insert into my_table ( select a, b, c from my_other_table where (condition) )

Sin embargo, mientras ejecuto esto, sigo quedándome sin memoria.

Lo que esperaba (y por qué estoy confundido):

Si el conjunto de trabajo era más grande de lo que podía caber en la memoria, pensé totalmente que Postgres almacenaría las páginas en el disco y haría la escritura de forma iterativa entre bastidores.

Sin embargo, lo que sucede es que aparentemente intenta leer todo el contenido select en la memoria antes de colocarlo en la otra tabla.

Incluso en nuestra instancia robusta r5.2xl, consume toda la memoria hasta que finalmente se dispara el OOM Killer y Aurora reinicia la instancia.

Este gráfico muestra que la memoria libre se reduce a cero cada vez que ejecuto la consulta. La copia de seguridad de la memoria se debe a que la instancia se elimina y reinicia automáticamente debido a OOM.

Mi pregunta principal:

1. ¿Por qué Postgresql no es más inteligente y hace su propia paginación entre bastidores?
2. ¿Hay alguna configuración que deba habilitar para que sea consciente de sus limitaciones de memoria?

Lo que he probado:

Ajuste de los parámetros shared_buffers y work_mem .

El valor de shared_buffer predeterminado de Aurora asigna 20 gb a nuestra instancia. Intenté reducir esto a 10 gb y luego a 6.5 gb (reiniciando cada vez) pero fue en vano. El único efecto fue hacer que la consulta tomara años y, en última instancia, consumiera toda la memoria disponible después de ejecutarse durante aproximadamente 30 minutos.

De manera similar, intenté configurar work_mem hasta el mínimo permitido, pero aparentemente esto tampoco tuvo ningún efecto en el resultado final.

Lo que puedo hacer como solución alternativa:

Podría, por supuesto, hacer la paginación / procesamiento por lotes desde el cliente:

 computeBatchOffsets(context).forEach(batchOffset -> context.insertInto(BLAH) .select(DSL.asterisk()) .from(FOO) .limit(batchOffset) .offset(batchOffset) .execute()

Pero, además de ser más lento que simplemente dejar que la base de datos lo haga, "se siente" como algo que la base de datos seguramente debería poder hacer internamente. Entonces, estoy confundido por qué tendría que manejarlo a nivel de cliente.