Which way is better to optimize postgresql db?

Question

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

VACUUM FULL (-f option) is something like defragmentation. Not sure what you mean by indexes optimization. However it is not the answer but just a proposal. Your question is more appropriate for the dba site — Abelisto
– Abelisto, Commented Apr 3, 2016 at 15:11

Richard Huxton · Accepted Answer · 2016-04-03 18:44:14Z

1

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.

answered Apr 3, 2016 at 18:44

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wildplasser Over a year ago

IIRC, the statistics are not recreated after a dump+restore. So you still would have to do that. Manually, or by a script.

Collectives™ on Stack Overflow

Which way is better to optimize postgresql db?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related