What is the additional overhead of an array compared to a normal column of that same datatype? In other words, if an array will almost always have one value in it, how much space would I be "wasting" by using an array instead of a normal column?


You can check that using pg_column_size():

select pg_column_size(1::integer) as int_size, 
       pg_column_size(array[1]::integer[]) as array_size


int_size | array_size
       4 |         28

So the overhead is substantial for just a single value.

  • So would it be better to use a foreign key instead? The overhead for that would be a join, and two 4-byte integers for primary key if I'm not mistaken. In other words, instead of a one-time column overhead, it'd be a (smaller?) overhead for each row. What do you think?
    – davidtgq
    Nov 26 '16 at 19:47
  • 1
    @DavidTan: I can't put it better then the manual does: "searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements" Nov 27 '16 at 10:52
  • 3
    @a_horse_with_no_name But if you think the storage overhead of an array is bad, try the storage overhead of an additional 2-column table.
    – jjanes
    Nov 27 '16 at 19:54

Indexing requirements

It's also important to consider indexes. If you create an index on an int type you'll likely use the natural comparison operators which will work on the default b-tree index. However, if you have an int[] type you have new operators. Because an int type presumably doesn't work, you likely won't want = or you would just use the int type. That is to say, you probably don't want arrayCol = array[2948], or you would just use intCol = 2948.

  SELECT x::int AS foo, array[x::int] AS bar
  FROM generate_series(1,1e6) AS x
CREATE INDEX test_foo ON test (foo);
CREATE INDEX test_bar ON test (bar);

So now these work as expected

EXPLAIN ANALYZE SELECT * FROM test WHERE bar = array[10000];

But, this does not use the index.

EXPLAIN ANALYZE SELECT * FROM test WHERE bar @> array[10000];

So you need a GIN index, and now this will work.

CREATE INDEX test_gin ON test USING GIN (bar);

So let's review now the sizes of the indexes.

              List of relations
         Name    | Type  |  Table  |  Size
 test_bar        | index | test    | 47 MB
 test_gin        | index | test    | 53 MB
 test_foo        | index | test    | 21 MB

So two points,

  1. You need a GIN index to make use of array operators.
  2. You don't need a b-tree index (don't create it).
  3. GIN indexes are substantially larger than indexes on INT columns
  4. GIN indexes are somewhat slower to update.

Just more things to consider.

  • Thanks. Perhaps another thing to consider is that if those integers refer to primary keys in another table tags_tag, using a proper through-table with a primary key and two foreign keys of its own would turn the tables (excuse the pun), wouldn't it?
    – davidtgq
    Nov 26 '16 at 21:50
  • 1
    I doubt the overheads introduced by the indexes overcome to overhead of the table join. Use the arrays if they make sense. Nothing wrong with them. Nov 27 '16 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.