How to fix PostgreSQL error on index row size

The Issue

If you're seeing this error, you're probably using JWT tokens, and are certainly using Postgres.

When a user creates an oauth token, the following error is generated:

ERROR: index row size [NUMBER] exceeds maximum [NUMBER] for index "index_oauth_access_tokens_on_refresh_token" HINT: Values larger than [NUMBER]/[NUMBER] of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. 

The error with index index_oauth_access_tokens_on_token is also possible. Replace the column name accordingly in below solution.

The Cause

The error seems to occur due to the btree index used on token, as btree indexes can't handle data with more that 2713 bytes.

The Solution

I've resolved this through running the following SQL, which uses an MD5 hashing function to create an index which is, to all extents and purposes, unique. There is a small chance of a collision, so I have not created this index as UNIQUE.

~/P/D/identity-provider ❯❯❯ psql identity-provider_development;

psql (9.4.4)
Type "help" for help.

identity-provider_development=# DROP INDEX index_oauth_access_tokens_on_refresh_token;
identity-provider_development=# CREATE INDEX index_oauth_access_tokens_on_refresh_token ON oauth_access_tokens((md5(refresh_token)));

This results in the following schema:

identity-provider_development=# \d oauth_access_tokens
                                         Table "public.oauth_access_tokens"
      Column       |            Type             |                            Modifiers
-------------------+-----------------------------+------------------------------------------------------------------
 id                | integer                     | not null default nextval('oauth_access_tokens_id_seq'::regclass)
 resource_owner_id | integer                     |
 application_id    | integer                     |
 token             | character varying           | not null
 refresh_token     | character varying           |
 expires_in        | integer                     |
 revoked_at        | timestamp without time zone |
 created_at        | timestamp without time zone | not null
 scopes            | character varying           |
Indexes:
    "oauth_access_tokens_pkey" PRIMARY KEY, btree (id)
    "index_oauth_access_tokens_on_token" UNIQUE, btree (token)
    "index_oauth_access_tokens_on_refresh_token" btree (md5(refresh_token::text))
    "index_oauth_access_tokens_on_resource_owner_id" btree (resource_owner_id)

This solution 'works for me' - any feedback is appreciated.