What I'm trying to do:

I'm trying to move about 2m records from one table into another. To do this, I'm doing an insert statement which is fed by a select query.

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

However, while running this, I keep running out of memory.

What I expected (and why I'm confused):

If the working set was larger than could fit in memory, I totally thought Postgres would buffer pages onto the disk and do the write iteratively behind the scenes.

However, what's happening is that it apparently tries to read all of the selected content into memory prior to stuffing it into the other table.

Even on our chunky r5.2xl instance, it consumes all of the memory until eventually the OOM Killer fires and the Aurora reboots the instance.

This graph is showing freeable memory dip down to zero everytime I run the query. The memory shooting back up is due to the instance automatically being killed and rebooted due to OOM.

My main question:

1. Why is Postgresql not being smarter and doing its own pagination behind the scenes?
2. Is there some setting I need to enable to get it to be aware of its memory limitations?

What I've tried:

Adjusting shared_buffers and work_mem parameters.

Aurora's default shared_buffer value allocates 20gb to our instance. I've tried dialing this down to 10gb, and then 6.5gb (restarting each time) but to no avail. The only affect was to make the query take ages and still ultimately consume all memory available after running for about 30min.

I similarly tried setting work_mem all the way to allowable minimum, but this seemingly had no effect on the end result as well.

What I can do as a work around:

I could, of course, do the pagination / batching from the client:

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

But, in addition to it being slower than just letting the database do it, it "feels" like something the database should surely be able to do internally. So, I'm confused why I'd need to handle it at the client level.