By JonLuca


2019-04-09 16:48:47 8 Comments

I have a 200 GB database with 26 tables that I need to create indexes on.

Currently index creation takes a long time on the biggest tables, as they have ~12gb and 400,000,000 rows in them (>4 hours).

I've set maintenance_work_mem to 100gb, and set max_parallel_workers to 30.

Are there any other parameters I should tune to improve the index creation speed?

This is on AWS Aurora, using postgres-10.6, in case that makes a difference.

There is no one else using the database and downtime/full locks are fine.

1 comments

@jjanes 2019-04-09 20:08:49

This is on AWS Aurora, using postgres-10.6, in case that makes a difference.

Yes, this matters quite a lot. Native parallel btree index builds were introduced in v11, so your "max_parallel_workers" setting won't matter for index builds under v10.

Unless you upgrade, you will have to parallelize them yourself by opening multiple sessions in parallel and building one index in each one. You will probably want to lower "maintenance_work_mem" as well if you have parallel processes (either of the manual or the v11 variety) as each one can claim that much memory.

@Erwin Brandstetter 2019-04-09 23:45:14

He can't upgrade (yet). The latest Aurora Postgres 2.2 is based on Postgres 10.6. Their storage system has its own parallelization - independent from Postgres' parallel implementation. I am not sure how that interacts with max_parallel_workers exactly, but overall it massively outperforms RDS Postgres (even v11) in writing activity - with the notable exception of building indexes, where both are on par from what I have seen so far. Unfortunate for the particular task of the OP.

@jjanes 2019-04-10 14:30:27

Ah, I didn't know the difference between Aurora and RDS on version availability. I would expect parallel workers to be beneficial only for their CPU parallelism, not IO parallelism. I was assuming he had enough IO available so that CPU would be limiting--although if it were to parallelize 30-ways, maybe that is not a good assumption.

Related Questions

Sponsored Content

1 Answered Questions

postgres performance tuning - what to try next?

3 Answered Questions

3 Answered Questions

[SOLVED] Postgres speed up index creation for large table

1 Answered Questions

2 Answered Questions

1 Answered Questions

[SOLVED] Query works locally but not on AWS RDS

1 Answered Questions

[SOLVED] MySQL Config for server with 8gb RAM and 4 cpus

  • 2015-11-17 08:51:16
  • John S.
  • 5442 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql performance

2 Answered Questions

[SOLVED] reducing migration time of a large size postgresql database

1 Answered Questions

[SOLVED] Create index on a Postgres table as fast as possible

Sponsored Content