We have a Postgresql 8.4.8 database server on a Debian system with a database that has 500+ schemas with roughly 300+ tables each. The schemas and tables are created by scripts, we cannot influence the process much short of deleting unnecessary schemas every now and then. Perhaps there were 5000+ schemas during the lifetime of this database, of which 4500- are gone now.
Opening the database using pgAdmin3 takes ages. Opening a schema takes ages. Autocompletion in psql
is horribly slow. This does not happen with "small" databases. I have tried VACUUM ANALYZE pg_tables
, but this is not supported.
Are there some cleanup tasks that need to be carried out to handle this situation? Or perhaps there is a tuning parameter that can be set? An index on a system table missing or outdated?
As a side question: Is it possible to group several schemas so that there is yet another hierarchy level between "database" and "table"?
Best Answer
This can also be a shortcoming of pgAdmin. There have been a number of improvements for recent versions. Which one do you use? 1.14.2 is the current version (as of 2012-05-21).
Are queries from the command line interface
psql
slow, too?Apart from that, you need to keep vacuuming all your tables in the database, not just
pg_tables
. I would strongly advice to enable the auto vacuuming daemon.Also,
pg_tables
is not relevant at all. It is a system view to implement the SQL standard information schema. The relevant system tables would bepg_namespace
,pg_class
,pg_attribute
and others. But it is pointless to target just one of them. For your kind of setup you need to be vacuuming the whole catalog. And it is very advisable to vacuum all of your database on a regular basis anyway. So, let me repeat: useautovacuum
.For starters, you can run in a Debian shell:
The last line targets system tables for possible bloat of indexes in the catalog in particular. See
man reindexdb
or the fine manual.Finally, another option would be to upgrade to PostgreSQL 9.1. There have been a number of improvements for
VACUUM
andANALYZE
(among many others). In particularVACUUM FULL
has been rewritten for 9.0 and does a much better job now.