This is similar to a recent problem I posted where COPY command was hanging for a large data set. In that instance, it was due to a foreign key constraint. But in this case I'm creating an index, so I would think an FK wouldn't be an issue, even though I still disabled triggers on the table just in case. I'm trying to add a regular btree index on a table with 10 billion rows. The index is on two int fields. I tried running it and it was going forever, so I thought it might just be too slow, I increased max_parallel_maintenance_workers to 8 and maintenance_work_mem to 2047MB (I'm on Windows, so it's the max).

At that point, things seemed to go faster, but the same problem happened: I can see the files growing in the pgsql_tmp/pgsql_tmpxxxx.x.sharedfileset folder, until they just stop but the index creation never seems to finish.

I wondered if I'd set too many workers for whatever reason, so I tried setting it to 4, same problem. Files were last modified around 3:20am, it's 7:35am and it's still running. The files in the folder are 261GB, which looks about right compared to the table size and every time I run the process it stalls at that size, so I assume it's done with creating the index, I just have no clue what it might be doing at this point. In case it matters, the table has a foreign key on another table that has 1 billion records, but the triggers are disabled on the table, which has worked for me in loading data in the table. I checked for locks, there are none, it's not waiting on any lock, which makes sense because this is a test database with dummy data that I created to test some things, so nobody else even knows it exists or has any use for it.