CODEWITHSUNDEEP
postgresql
Vladimir Akkerman
Vladimir Akkerman

Reputation: 131

Which way is better to optimize postgresql db?

Reindex or backup/restore to optimize database? Do indexes rebuild while restoring db from backup?

Upvotes: 0

Views: 56

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22952

If practical, a full backup and restore is always better than a simple reindex simply because you also get an extra backup file.

The restore process will (1) create tables, then (2) copy data in and finally (3) create indexes, apply constraints etc.

This is not the same as using CLUSTER of course, which physically re-orders a table based on one of its indexes. In some cases that can be useful.

If you are going to do this though, make sure you have good measurements before and after your "optimization" because many factors affect overall database performance and this may prove pointless.

Upvotes: 1

Related Questions