Calculating and saving space in Postgresql

Posted by punkish on Stack Overflow See other posts from Stack Overflow or by punkish
Published on 2010-06-03T13:44:57Z Indexed on 2010/06/03 14:04 UTC
Read the original article Hit count: 245

Filed under:

I have a table in Pg like so

CREATE TABLE t (
    a BIGSERIAL NOT NULL,               -- 8 b
    b SMALLINT,                         -- 2 b
    c SMALLINT,                         -- 2 b
    d REAL,                             -- 4 b
    e REAL,                             -- 4 b
    f REAL,                             -- 4 b
    g INTEGER,                          -- 4 b
    h REAL,                             -- 4 b
    i REAL,                             -- 4 b
    j SMALLINT,                         -- 2 b
    k INTEGER,                          -- 4 b
    l INTEGER,                          -- 4 b
    m REAL,                             -- 4 b
    CONSTRAINT a_pkey PRIMARY KEY (a)
)

The above adds up to 50 bytes per row. My experience is that I need another 40% to 50% for system overhead, without even any user-created indexes to the above. So, about 75 bytes per row. I will have many, many rows in the table, potentially upward of 145 billion rows, so the table is going to be pushing 13-14 Terabytes. What tricks, if any, could I use to compact this table? My possible ideas below --

Convert the REAL values to INTEGERs. If they can stored as SMALLINT, that is a saving of 2 bytes per field.

Convert the columns b .. m into an array. I don't need to search on those columns, but I do need to be able to return one column's value at a time. So, if I need column g, I could do something like

SELECT a, arr[5] FROM t;

Would I save space with the array option? Would there be a speed penalty?

Any other ideas?

© Stack Overflow or respective owner

Related posts about postgresql