Is there a upper bound for an array column?

I am getting this error when inserting into the array field -

PG::Error: ERROR:  index row size 3480 exceeds maximum 2712 for index "ix_data"

Here's my table definition -

create table test_array(id varchar(50), data text[]);


CREATE INDEX ix_data ON test_array USING GIN (data);

I need an index on array field, since I am doing some lookups on it.

  • Could it be that data contains a list of tags like demonstrated in this related blog post by Scott Snyder? If that is the case, I might have a better solution for you. Commented Sep 28, 2012 at 19:10
  • user310525, I'd like to second Erwin's suggestion that this would be better on dba.se, if you are willing to create an account there and flag for a moderator to migrate? Commented Sep 29, 2012 at 6:52
  • Due to link rot: archive.org link for Scott's blog post
    – cstork
    Commented Dec 25, 2020 at 9:36

3 Answers 3


The problem

Here is a very similar case discussed on pgsql.general. It's about the limitation in a b-tree index, but it's all the same because a GIN index uses a b-tree index for keys internally and therefore runs into the same limitation for key size (instead of item size in a plain b-tree index).

I quote the manual about GIN index implementation:

Internally, a GIN index contains a B-tree index constructed over keys, where each key is an element of one or more indexed items

Either way, at least one array element in your column data is too big to be indexed. If this is just a singular freak value or some kind of accident you may be able to truncate the value and be done with it.

For the purpose of the following demo I'll assume otherwise: lots of long text values in the array.

Simple solution

You could replace elements in your array data with according hash values. And send look-up values through the same hash function. Of course, you probably want to store your originals in addition somewhere. With that, we almost arrive at my second variant ...

Advanced solution

You could create a look-up table for array elements with a serial column as surrogate primary key (effectively a radical kind of hash value) - which is all the more interesting if involved element values are not unique:

  elem_id serial NOT NULL PRIMARY KEY
, elem    text UNIQUE NOT NULL

Since we want to look up elem, we add an index - but an index on an expression this time, with only the first 10 characters of the long text. That should be enough in most cases to narrow a search down to one or a few hits. Adapt the size to your data distribution. Or use a more sophisticated hash function.

CREATE INDEX elem_elem_left10_idx ON elem(left(elem,10));

Your column data would then be of type int[]. I renamed the table to data and got rid of the ominous varchar(50) in your example:

  data_id serial PRIMARY KEY
, data int[]

Each array element in data refers to a elem.elem_id. At this point, you may consider to replace the array column with an n:m table, thereby normalizing your schema and allowing Postgres to enforce referential integrity. Indexing and general handling becomes easier ...

However, for performance reasons, the int[] column in combination with a GIN index may be superior. Storage size is a lot smaller. In this case we need the GIN index:

CREATE INDEX data_data_gin_idx ON data USING GIN (data);

Now, each key of the GIN index (= array element) is an integer instead of a longish text. The index will be smaller by several orders of magnitude, searches will consequently be much faster.

The downside: before you can actually perform a search you have to look up the elem_id from the table elem. Using my newly introduced functional index elem_elem_left10_idx, this, too, will be much faster.

You can do it all in one simple query:

SELECT d.*, e.*
FROM   elem e
JOIN   data d ON ARRAY[e.elem_id] <@ d.data
WHERE  left(e.elem, 10) = left('word1234word', 10) -- match index condition
AND    e.elem = 'word1234word';  -- need to recheck, functional index is lossy

You may be interested in the extension intarray, that supplies additional operators and operator classes.

db<>fiddle here
Old sqlfiddle


I was getting this on a PostGIS geography column. It was because I accidentally created the index incorrectly. You should include the USING GIST parameter when creating such indexes.

  • 1
    Thank you -- that was it! Wow, so far fetched. May have saved me hours. Especially since I thought GiST was used by default but I was mistaken and it tries to use b-tree.
    – Jonas
    Commented Jun 25, 2019 at 16:36

The error is with the index ix_data, not the text[] field. The maximum size for a row in that particular index type is limited to 2712 bytes. If you drop your index and try the insert again, it should work for you. If you need to index a larger field, you may want to look into the full text indexing features of Postgres.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.