sqldatabasepostgresqlindexingdatabase-migration

How do you calculate the time to create an index on a large Postgresql table?


I have a Postgres table with 26 million rows in it, and I want to add an indexed boolean column.

Is there a way to calculate how long that type of operation will take, before attempting it in production?


Solution

  • There is no way to calculate index time as it depends on many factors (hardware, software, configuration, load, etc.)

    The good news is that you may CREATE INDEX CONCURRENTLY, which takes longer but does not lock the table. So the operation has no side effects except higher load on server.

    For some caveats, check the documentation.