CODEWITHSUNDEEP
postgresql
Maddy
Maddy

Reputation: 1379

Delayed indexing in postgres

We have a system which stores data in a postgres database. In some cases, the size of the database has grown to several GBs.

When this system is upgraded, the data in the said database is backed up, and finally it's restored in the database. Owing to the huge amounts of data, the indexing takes a long time to complete (~30 minutes) during restoration, thereby delaying the upgrade process.

Is there a way where the data copy and indexing can be split into two steps, where the data is copied first to complete the upgrade, followed by indexing which can be done at a later time in the background?

Thanks!

Upvotes: 5

Views: 3013

Answers (2)

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

There is an option to separate the data and creating index in postgresql while taking pg_dump. Here pre-data refers to Schema, post-data refers to index and triggers.

From the docs,

--section=sectionname Only dump the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to dump all sections.

The data section contains actual table data, large-object contents, and sequence values. Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items.

May be this would help :)

Upvotes: 5

Craig Ringer
Craig Ringer

Reputation: 324471

There's no built-in way to do it with pg_dump and pg_restore. But pg_restore's -j option helps a lot.

There is CREATE INDEX CONCURRENTLY. But pg_restore doesn't use it.

It would be quite nice to be able to restore everything except secondary indexes not depended on by FK constraints. Then restore those as a separate phase using CREATE INDEX CONCURRENTLY. But no such support currently exists, you'd have to write it yourself.

You can, however, filter the table-of-contents used by pg_restore, so you could possibly do some hacky scripting to do the needed work.

Upvotes: 7

Related Questions