4

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?

3

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

returns:

int_size | array_size
---------+-----------
       4 |         28

So the overhead is substantial for just a single value.

| improve this answer | |
  • 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? – dvtan 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" – a_horse_with_no_name Nov 27 '16 at 10:52
  • 1
    @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
2

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.

CREATE TABLE test AS (
  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);
VACUUM ANALYZE test;

So now these work as expected

EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 10000;
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.

| improve this answer | |
  • 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? – dvtan 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. – Evan Carroll 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.