How should I configure my postgresql to create an Index for a big table (655GB) as fast as possible?

Question

I'm importing a big dataset of medication usage (14 years of public health system data from Brazil) for a data science project.

When I first started, I created one of the tables with all the indexes (one int4, one int8, one bpchar(2) and one bpchar(4)), but this caused the COPY transactions to become very slow, so I dropped the indexes to speed up data ingestion.

It worked, and I finished importing the 655GB (roughly 2.5 billion rows) last week. However, when trying to create the indexes, the code ran for three days straight and never finished, and I ended up losing the progress for the indexes. I'm working on getting an UPS for this.

I tried creating all indexes at the same time, in parallel, to finish as soon as possible, but I believe the concurrency between different indexes ended up creating an IO bottleneck (using 100% of my HDDs IO capacity this whole time).

I have followed some tips I found in this link to try and speed up creation, as follows:

• I've set the table parallel_workers setting to 6, as well as pg variable max_parallel_maintenance_workers to 6.
• I have set the pg variable maintenance_work_mem to 1.5GB. For some reason my instance only lets me raise it to just below 2GB, and I haven't found out how to increase this limit yet.
• I haven't experimented with checkpoint_timeout, max_wal_size or min_wal_size, because I didn't really understand what they mean, so I decided to leave them alone for now.

I'm running this in my personal computer: 32gb RAM, Ryzen 5, a dedicated 4TB HDD for the database. On Windows 10 Home. I have the intention of acquiring one or two additional SSD to house the database, since IO has been such a bottleneck.

I believe I should probably stick to one index at a time, focusing all resources to its creation, but I am not sure, and I don't want to waste another week just to find out. This is the first time I'm creating and managing a database by myself, and most things I know are from stackexchange and postgresql.org/docs.

---

Was I right to drop the indexes before COPY?

How should I approach the creation of the indexes, what postgresql settings should I customize (and how) to do it as fast as possible?

And lastly, should I get two SSDs for partitions? Or an SSD for the data and the existing HDD for the WAL would be enough? Or something different? I really don't know how to deal with this, and would prefer to know more before buying more hardware.

Answer 1

Creating the indexes after the COPY is a good idea. However long it takes to build an index, it would be much worse to maintain existing indexes during the COPY.

It is probably not a good idea to have multiple index creations going at the same time as well as (trying) to have each one occur in parallel. If you are really IO bound (how do you know?), then it might not be a good idea to use either form of parallelization. Btree index creation should already use primarily large sequential reads and writes, and those should already be able to saturate the IO capacity without the need for parallelization. Trying to do multiple of those large sequential reads and writes at the same time might be unhelpful, or even counterproductive.

Windows does have a limit of just under 2GB for maintenance_work_mem, which Linux does not have. However, in my experience increasing maintenance_work_mem to very high values has diminishing returns for btree index creation anyway. But if you are using parallel workers, they split that memory among themselves for index creation, so 1.5GB/7 workers might get down the region where it would be better to have a higher setting. But again, if you are IO bound using parallel workers are probably a mistake anyway.

If you can buy a big enough SSD array to put the whole database on it, that would almost certainly help, and might make parallel workers useful. But if it isn't big enough and you need to try to split up the data putting some SSD and some on HDD, that will likely be a lot of futzing about for little value.