0

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.

4
  • "For some reason my instance only lets me raise it to just below 2GB," - are you using Windows?
    – user1822
    Oct 29, 2022 at 21:44
  • Yes, Windows 10 Home. Is that the reason?
    – Jaejatae
    Oct 30, 2022 at 1:12
  • What were max_parallel_workers and max_worker_processes set to?
    – jjanes
    Oct 30, 2022 at 16:35
  • max_parallel_workers = 6, I didn't change max_worker_processes value, so it was 8
    – Jaejatae
    Oct 31, 2022 at 10:28

1 Answer 1

3

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.

4
  • If you are really IO bound (how do you know?). I suspect that because of the heavy traffic on the HDD containing the database, but otherwise I have no way of knowing at the moment. Resource management showed a constant queue to access the disk (around 30 processes in queue most of the time).
    – Jaejatae
    Oct 30, 2022 at 15:03
  • I don't know how to interpret the disk queue from resource management (or even what "resource management" is). A problem with using Windows for heavy duty processing in PostgreSQL is that there isn't nearly as many people with personal experience in doing it for you to draw on. Can you tell how busy it is in something easier to interpret, like MB/s?
    – jjanes
    Oct 30, 2022 at 17:55
  • Sorry, it is called "Resource monitor", my bad. Yes there are other info, but they relate to individual processes. I noticed at the time that a lot of postgresql processes were writing (and reading) different files to disk (named something like /pgsql/tmp/sharedfilexxxx.xxxx/x.xx) at varying speeds. One process for each psql worker. The added speeds didn't look like they would fill the disk capacity though. "Active Time" never left 100% for the three days.
    – Jaejatae
    Oct 30, 2022 at 18:15
  • I got to try the new SSD today, with only one index being created, as suggested. It finished within an hour or two. Thank you @jjanes
    – Jaejatae
    Nov 16, 2022 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.