What is more efficient: arrays or single column values?

In PostgreSQL ( as well as in other rdbms ) you can define columns as arrays. What I wondered is: What is more efficient when it comes to space: Creating several columns or just creating once column as array? The result, at least for me, is rather surprising.

First, let’s create a table with columns of type int:

postgres=# create table single_values ( a int, b int, c int, d int, e int, f int, g int, h int, i int, j int );
CREATE TABLE
postgres=# \d single_values
 Table "public.single_values"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 
 c      | integer | 
 d      | integer | 
 e      | integer | 
 f      | integer | 
 g      | integer | 
 h      | integer | 
 i      | integer | 
 j      | integer | 

Next create a table with just one column of type array[int]:

postgres=# create table arr_values ( a int[] );
CREATE TABLE

Ready for populating the first table;

truncate table single_values;
\timing on
Timing is on.
DO $$DECLARE
  ln int := 1000;
BEGIN
  for i in 0..1e6 loop
    insert into single_values (a,b,c,d,e,f,g,h,i,j)
           values (ln,ln,ln,ln,ln,ln,ln,ln,ln,ln);
  end loop;
END$$;
DO
Time: 4225.313 ms
postgres=# 

And the table with the array column:

truncate table arr_values;
 \timing on
 Timing is on.
 DO $$DECLARE
 larr int[] := '{1000,1000,1000,1000,1000,1000,1000,1000,1000,1000}';
 BEGIN
 for i in 0..1e6 loop
 insert into arr_values (a)
 values (larr);
 end loop;
 END$$;

postgres=# \i populate_arr_values.sql
 TRUNCATE TABLE
 DO
 Time: 4033.523 ms
 postgres=#

Now, what about the size?

postgres=# select pg_size_pretty(pg_relation_size('single_values'));
 pg_size_pretty 
----------------
 65 MB
(1 row)

Time: 0.227 ms
postgres=# select pg_size_pretty(pg_relation_size('arr_values'));
 pg_size_pretty 
----------------
 89 MB
(1 row)

Time: 0.737 ms

I did expect the table with the array to be smaller but it is the other way around. The table with the 10 columns is smaller. In the next post I’ll do the same for oracle.