If you have all the necessary indexes (and not more than necessary) then you can also try to modify PostgreSQL WAL parameters. wal_buffers, max_wal_size, checkpoint_timeout, checkpoint_completion_target could be significant when it comes to performance of big updates.

I also have another idea, honestly don't know if it will be faster. You have to test it yourself. Maybe try writing the update like that:

UPDATE schema.table a
SET col1 = exists(select null from schema.polygon_table b where st_intersects(a.geom, b.geom))::int,
    col2 = exists(select null from schema.another_polygon_table b where st_intersects(a.geom, b.geom))::int

This way there is only one sequential iteration of the entire 50 million record table as opposed to N iterations.

If you have all the necessary indexes (and not more than necessary) then you can also try to modify PostgreSQL WAL parameters. wal_buffers, max_wal_size, checkpoint_timeout, checkpoint_completion_target could be significant when it comes to performance of big updates.

I also have another idea, honestly don't know if it will be faster. You have to test it yourself. Maybe try writing the update like that:

UPDATE schema.table a
SET col1 = exists(select null from schema.polygon_table b where st_intersects(a.geom, b.geom))::int,
    col2 = exists(select null from schema.another_polygon_table b where st_intersects(a.geom, b.geom))::int
--etc...

This way there is only one sequential iteration of the entire 50 million record table as opposed to N iterations.

If you have all the necessary indexes (and not more than necessary) then you can also try to modify PostgreSQL WAL parameters. wal_buffers, max_wal_size, checkpoint_timeout, checkpoint_completion_target could be significant when it comes to performance of big updates.

I also have another idea, honestly don't know if it will be faster. You have to test it yourself. Maybe try writing the update like that:

UPDATE schema.table a
SET col1 = (select (count(*)>0)::int from schema.polygon_table b where st_intersects(a.geom, b.geom)),
    col2 = (select (count(*)>0)::int from schema.another_polygon_table b where st_intersects(a.geom, b.geom))

This way there is only one sequential iteration of the entire 50 million record table as opposed to N iterations.

If you have all the necessary indexes (and not more than necessary) then you can also try to modify PostgreSQL WAL parameters. wal_buffers, max_wal_size, checkpoint_timeout, checkpoint_completion_target could be significant when it comes to performance of big updates.

I also have another idea, honestly don't know if it will be faster. You have to test it yourself. Maybe try writing the update like that:

UPDATE schema.table a
SET col1 = exists(select null from schema.polygon_table b where st_intersects(a.geom, b.geom))::int,
    col2 = exists(select null from schema.another_polygon_table b where st_intersects(a.geom, b.geom))::int

This way there is only one sequential iteration of the entire 50 million record table as opposed to N iterations.