kopongo.com

home

Cleaning up postgres

16 Mar 2007

When you use postgres, every now and then, you need to clean out your database. Of course, as the project moves on, the tables that you need to clean change as well: some get added, some dropped. And then there's the indexes.

I usually do not get around to updating the cleaning scripts unless I notice something is wrong with the database, and then I have to deal with figuring out all the new tables and such.

This can be a lot simpler if you use the postgres metadata tables. The 'information_schema.tables' table has the list of all tables for a specific database. You can therefore get a list of tables with a simple:

    select
        table_name 
    from 
        INFORMATION_SCHEMA.TABLES 
    where 
         table_schema = 'public'

This gives you the list of tables, like this:

      table_name           
      -------------------------------
      first_table
      second_table 
      (2 rows)

So how do you get the VACUUM?

Change the select to this:

    select 
         'VACUUM ANALYZE VERBOSE ' || table_name || ';' 
    from 
         INFORMATION_SCHEMA.TABLES 
    where 
        table_schema = 'public'

The '||' does string concatenation in SQL. Not the quite the most obvious thing, but what are you gonna do?

So this that last command gives you this:

    ?column?                        
    -------------------------------------------------------
    VACUUM ANALYZE VERBOSE first_table;
    VACUUM ANALYZE VERBOSE second_table;

So now you can try this one by hand, or put it in a cron job somewhere...

    % psql -d mydb_production -f sql_script.sql -o vacuuum_tables.sql
    % psql -d mydb_production -f vacuum_tables.sql

I also like to recompute my indexes after I vacuum. I add this to the first cleaning script, a simple thing like so:

     select 
         'REINDEX INDEX ' || indexname || ';' 
     from 
         pg_indexes 
     where 
         schemaname ='public';

Now you get freshly computed indexes every time you run this. Nice!