Manage db indices on geroku

Question

How can I manage database indexes on Heroku? I know about taps, but this seems to be for pushing / pulling data.

How to view, update, delete my indexes? My dev db is sqlite3, and on Heroku is postgres.

Answer 1

It looks like you are using a shared rather than a dedicated database, so you need to do this in a complicated way. If you have a dedicated database, you can heroku pg:psql and then \di and sort the other psql commands to get what you are looking for.

There is always a hard way, and this is due to internal directory directories. You will need several SQL blocks, you can wrap them in calls to ActiveRecord::Base.connection.select_rows and access the results from the Rails console.

You can get a list of your tables and their indices as follows:

 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 index_oid to get a description of the index in question:

 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 can use table_oid to get a list of indexes for this 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 probably want to wrap all this 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 have not tried them with a shared database in Heroku (sorry, I only have a special database for the game). There may be simpler ways, but they should do the job, and they will be easy to use if you complete them in the PGInfo class.

Anything that gives you the necessary index information, you can use regular migrations to add, delete, or modify your indexes.

Answer 2

You must manage your indexes through your migrations so that they synchronize in your environment.