Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"?

share|improve this question
    
+1 Have you tried to dump-restore the database? Maybe this can cleanup things a little. –  tscho Feb 23 '12 at 23:22

1 Answer 1

up vote 1 down vote accepted

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 be pg_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: use autovacuum.

For starters, you can run in a Debian shell:

vacuumdb -afz
reindexdb -a
reindexdb -s

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 and ANALYZE (among many others). In particular VACUUM FULL has been rewritten for 9.0 and does a much better job now.

share|improve this answer
    
Thank you. We are using pgAdmin3 from ubuntugis-unstable, which is currently in version 1.14.0. -- Which query should I check for performance? Selecting from data tables doesn't seem to be that slow. -- I will need root rights on the server machine in order to edit the configuration for enabling autovacuum, right? In which file will I find the relevant entries? Do I need to fiddle with cron, too? -- For vacuumdb and reindexdb to fix the system catalog, I run it as postgres database user, right? Is it advisable to enable password access for that user? –  krlmlr Feb 24 '12 at 7:58
    
I have found out that -a selects all databases. In our case, we have only one database and use -d dbname. Most of these operations can be done as standard user. I also found the autovacuum settings in postgresql.conf. So, my questions in the comment above are more or less solved :-) –  krlmlr Feb 24 '12 at 11:05
    
@user946850: You shouldn't need the root user, the postgres user should be able to do almost everything. Some (non-invasive) things can be done with any user. You already found postgresql.conf, that's the one. –  Erwin Brandstetter Feb 24 '12 at 15:06
    
Thanks again. Indeed, it seems that pgAdmin3 issues an expensive query when opening a schema node. Clearly a bug, it's even in the dev version. -- Concerning autovacuum -- isn't it supposed to be on by default? –  krlmlr Feb 24 '12 at 20:22
    
@user946850: In recent versions autovacuum is on by default. Should be the case for 8.4. But since you mentioned manual vacuuming, I wanted to cover all bases. With autovacuum on (and appropriate settings), manual vacuuming is largely irrelevant. There have been a couple of improvements and fixes since 8.4, though. –  Erwin Brandstetter Feb 24 '12 at 20:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.