Managing db indexes on heroku

Question

How do I go about managing my database indexes on Heroku? I know about taps but that seems to be for pushing/pulling data.

How do I view, update, delete my indexes? My dev db is sqlite3 while on Heroku it's postgres.

Answer 1

You should manage your indices via your migrations, so that they're in sync across your environments.

Answer 2

Looks like you're using a shared rather than dedicated database so you have to do it the hard way. If you had a dedicated database then you could heroku pg:psql and then \di and assorted other psql commands to get what you're looking for.

There's always the hard way though and that involves the internal catalog tables. There are few chunks of SQL that you'll need, you can wrap them in ActiveRecord::Base.connection.select_rows calls and access the results from your Rails console.

You can get a list of your tables and their indexes with this:

select c2.relname as table, c2.oid as table_oid, c.relname as name, c.oid as index_oid
from pg_catalog.pg_class c
join pg_catalog.pg_index i on i.indexrelid = c.oid
join pg_catalog.pg_class c2 on i.indrelid = c2.oid
left join pg_catalog.pg_user u on u.usesysid = c.relowner
left join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relkind  = 'i'
  and n.nspname <> 'pg_catalog'
  and pg_catalog.pg_table_is_visible(c.oid)
order by c2.relname, c.relname

Then you can use the index_oid to get a description of the index in question with this:

select c.relname, c.oid, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c.reltablespace
from pg_catalog.pg_class c
join pg_catalog.pg_index i on c.oid = i.indexrelid
where c.oid = '#{index_oid}'

Or you could use the table_oid to get a list of indexes for that table with this:

select ci.relname, ci.oid, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), ci.reltablespace
from pg_catalog.pg_index i 
join pg_catalog.pg_class ci on i.indexrelid = ci.oid
where i.indrelid = '#{table_oid}'
order by ci.relname  

You'd probably want to wrap all this stuff in a utility class for easy access:

class PGInfo
    def self.list_indexes
        data = ActiveRecord::Base.connection.select_rows(%Q{
            select c2.relname as table, c.relname as name, c.oid as oid
            ...
        })
        # do something pretty with the array of arrays that is in data
    end
    # etc.
end

I haven't tried these with a shared database at Heroku (sorry, I only have a dedicated database to play with). There might be easier ways but these should get the job done and they'll be easy to use if you wrap them up in a PGInfo class.

All that gives you the index information you need, then you can use normal migrations to add, remove, or modify your indexes.