Catalog Bloat in Development - Frequently dropping/adding schemas and objects
In my current development environment I often drop some or all of the
schemas in the database and then re-create them schemas and the objects they
contain. When I go to bring up the database in my GUI it takes a
considerable amount of time to initialize. I suspect this is because the
catalog tables are becoming bloated. What is the recommended course of
action to de-bloat them? Running an unqualified vacuum does not seem to
help. Is it better to just periodically drop and recreate the database
itself or would a vacuum with specific tables listed be sufficient - and if
so which tables?
Thanks!
David J.
David,
the VACUUM FULL VERBOSE command might overcome this - I believe it works by effectively doing what you are proposing with a drop database and recreate. It does however lock the tables during the process (not a problem in a dev environ one assumes) but may not be ideal on a live database.
cheers
Ben
On 30/06/2012, at 4:45 AM, David Johnston wrote:
Show quoted text
In my current development environment I often drop some or all of the schemas in the database and then re-create them schemas and the objects they contain. When I go to bring up the database in my GUI it takes a considerable amount of time to initialize. I suspect this is because the catalog tables are becoming bloated. What is the recommended course of action to de-bloat them? Running an unqualified vacuum does not seem to help. Is it better to just periodically drop and recreate the database itself or would a vacuum with specific tables listed be sufficient – and if so which tables?
Thanks!
David J.