Technology Insights

Create Ruby Migration To Add An Index To A PostgreSQL Table

The other day I did some optimization work on a PostgreSQL database and at the end needed to create migrations so the changes I was going to make could be deployed on multiple servers. It was my first shot at migrations so I wanted to note for myself and anyone else that may find it useful how to create Ruby migrations for dropping and adding Indexes on PostgreSQL tables. Below you will find the basic concept for creating a migration that adds a Postgres table index and information on removing a table index which is just the reverse of adding.

Technology Insights

PostgreSQL 8.3: postgres wont start with stats_start_collector = on

PostgreSQL 8.3 changed the way the Query and Index Statistics are configured from previous versions of postgreSQL including 8.2 and below. Things are technically easier now as the stats collecting is turned on by default and there are less commands to enter into the configuration to get all of the stats you will need. I had to look into this problem as I wanted to install some cacti postgres templates and the configuration stated to add the following lines to the postgresql.conf configuration file.