GIST index creation too slow on PostgreSQL

Question

I have a database in PostgreSQL with the following structure:

Column    |         Type          | Collation | Nullable |                    Default                     
-------------+-----------------------+-----------+----------+------------------------------------------------
 vessel_hash | integer               |           | not null | nextval('samplecol_vessel_hash_seq'::regclass)
 status      | character varying(50) |           |          | 
 station     | character varying(50) |           |          | 
 speed       | character varying(10) |           |          | 
 longitude   | numeric(12,8)         |           |          | 
 latitude    | numeric(12,8)         |           |          | 
 course      | character varying(50) |           |          | 
 heading     | character varying(50) |           |          | 
 timestamp   | character varying(50) |           |          | 
 the_geom    | geometry              |           |          | 
Check constraints:
    "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
    "enforce_geotype_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326)

The database contains ~146.000.000 records and the size of table that contains the data is:

public | samplecol   | table    | postgres | 31 GB      | 

I try to create a GIST index on the geometry field the_geom with this command:

create index samplecol_the_geom_gist on samplecol using gist (the_geom);

but takes too long. It runs 2 hours already.

Based on this question Slow indexing of 300GB Postgis table Ask Question, before index creation I execute in psql console:

ALTER SYSTEM SET maintenance_work_mem = '1GB'; 
ALTER SYSTEM


SELCT pg_reload_conf();

pg_reload_conf 
----------------  
t 
(1 row)

But index creation takes too long. Does anyone know why? An how to fix this?

Answer 1

I am afraid you'll have to sit it out.

Apart from high maintenance_work_mem, there is not really a tuning option. Increasing max_wal_size will help somewhat, since you will get fewer checkpoints.

If you can't live with an ACCESS EXCLUSIVE lock for that long, try CREATE INDEX CONCURRENTLY, which will be even slower, but won't block concurrent database activity.