PostGIS- slow spatial UPDATE queries on 50mil rows

Question

PostgreSQL 11.8, AWS hosted RDS instance, SSD, 8GB RAM, 3.0 GHz Intel Scalable Processor. Plenty of storage space.

I'm finding running updates on a large table using spatial queries is taking a very long time (>24 hours). How can I speed it up? I looked into HOT updates, but I can't use that as I have an indexed column (the geometry column, which is essential for fast spatial processing)- as I understand it, HOT updates don't work if any column is indexed.

Here is my process:

I create a new table of 50 million points as a copy of another table (I don't want to alter the original).

CREATE TABLE schema.table AS
SELECT id, geom 
FROM schema.orig_table;

I add about 40 new columns which will store '1's where a spatial condition is true. This doesn't have to be a 1 though, I may need the number to be a 2,3,4 etc.

ALTER TABLE schema.table 
ADD COLUMN col1 integer DEFAULT 0,
ADD COLUMN col2 integer DEFAULT 0,
ADD COLUMN col3 integer DEFAULT 0, etc etc

I set the id column as the primary key.

ALTER TABLE schema.table ADD PRIMARY KEY (id);

I assign a geometry type to the geometry column.

ALTER TABLE schema.table 
ALTER COLUMN geom TYPE geometry(POINT,27700) USING ST_SetSRID(geom,27700);

I add a spatial index on the geometry column.

CREATE INDEX table_gix ON schema.table USING GIST(geom);

From here on, I run multiple UPDATE queries on the table, setting one of the 40 columns with the value 1 (or 2/3/4... etc I decide on a query by query basis) where the point intersects another table (I have a load of geospatial tables representing various polygons, points, line features. All spatially indexed and working correctly).

UPDATE schema.table a
SET col1 = 1
FROM schema.polygons b
WHERE ST_INTERSECTS(a.geom, b.geom);

...

UPDATE schema.table a
SET col29 = 1
FROM schema.another_polygon_table b
WHERE ST_INTERSECTS(a.geom, b.geom);

etc etc.... x200

Another 200 of these UPDATE queries occur. All the other geospatial tables used in the query are clean, fast, and without geometry errors. And most of the time, these ST_INTERSECTS queries don't find anything, so the value in the table stays at 0. The problem is, some of these queries can take up to 1 hour each. So 200 of these? Causes a headache.

The time taken for the whole script to run can take 24 hours. But putting a LIMIT 1000000 as a test, shows that it can run quickly on smaller sample sizes. It also shows that the overall script time grows exponentially with the amount of rows updated in the table.

How can I speed up this whole process? What's the most efficient way of running 100s of spatial UPDATE queries on a table of 50 million rows?

Answer 1

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.

Answer 2

Since you are not writing to any indexed column, you can allow for HOT updates, which indeed may have a significant effect: positive for UPDATE performance, negative for reserved disk space for the relation.

Rewrite your table with a modified fillfactor to leave room per page for the transition row tuples:

ALTER TABLE schema.table
  SET (fillfactor = 49)
;

VACUUM FULL;    -- physically rewrites data on disc

---

I use 49 (% of page size) here to make sure each row can be duplicated on the same page during an UPDATE; this low a value may or may not be necessary, but should allow to get HOT updates in most cases.

You can rewrite the table to the default fillfactor = 100 after that if you do not plan to UPDATE often...but a disk rewrite will take quite some time.