Hstore index row size maximum error

Question

Using Rails 4 and trying to do an index on my hstore column.

My migration file:

class AddIndexToProfiles < ActiveRecord::Migration
  def up
    execute "CREATE INDEX profiles_metadata_gin_data ON profiles USING GIN(meta_data)"
  end

  def down
    execute "DROP INDEX profiles_metadata_gin_data"
  end
end

And the error I am receiving:

StandardError: An error has occurred, this and all later migrations canceled:

PG::ProgramLimitExceeded: ERROR:  index row size 7936 exceeds maximum 2712 for index "profiles_metadata_gin_data"

Any idea what to do about this?

Answer 1

The different index types have various restrictions on the size of items which may be indexed; in the case of your GIN index, it looks like you are well past the "GinMaxItemSize" limit, which is defined like so:

#define GinMaxItemSize \
    MAXALIGN_DOWN(((BLCKSZ - SizeOfPageHeaderData - \
            MAXALIGN(sizeof(GinPageOpaqueData))) / 3 - sizeof(ItemIdData)))

I think your workaround options are:

• Try a GIST index instead, I believe the limits there should be higher.
• Recompile Postgres with a larger block size, perhaps BLCKSZ=16384 would work.
• Use a partial GIN index skipping those troublesome large "meta_data" columns, e.g. CREATE INDEX profiles_metadata_gin_data ON profiles USING GIN(meta_data) WHERE length(meta_data::text) < 2000. You would have to include that same WHERE clause in queries in order for the index to get used though, which may be more trouble than it's worth.
• Find a way to trim the meta_data columns for those troublesome row(s) so that you can squeak under your 2712 byte limit for GIN.