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?
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, 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;
But, this does not use the index.
EXPLAIN ANALYZE SELECT * FROM test WHERE bar @> array;
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,
- You need a GIN index to make use of array operators.
- You don't need a b-tree index (don't create it).
- GIN indexes are substantially larger than indexes on
- GIN indexes are somewhat slower to update.
Just more things to consider.