Do I need to Vacuum DB after massive insert?

Question

I use Postgres 12.7

I did massive insert using CopyManager.

• Input: 320GB of tab separated data

Sample row is:

2021-03-16T07:14:41.121000  588467  1900000 0   0   0   0   S   0   LITTLE_CHAR

-- around 60 bytes

Table schema is:

create table my_table
(
    time         TIMESTAMPTZ NOT NULL, -- 8 bytes
    price        integer     NOT NULL, -- 4 bytes
    size         bigint      NOT NULL, -- 8 bytes
    bid          integer     not NULL, -- 4 bytes
    ask          integer     not NULL, -- 4 bytes
    openInterest integer     not NULL, -- 4 bytes
    marketCenter integer     not NULL, -- 4 bytes
    conditions   varchar(8)  not NULL, -- 8 bytes
    side         smallint    not NULL, -- 2 bytes
    symbol       varchar(16) not NULL  -- 16 bytes
                                       -- 8*2 + 4*5 + 8 + 2 + 16 = 
                                       -- 16 +  20 +     10 + 16 = 62 bytes per row
);

It took 1TB on Postgres side. Three times more, what went wrong?

i was advised to run VACCUM. Why would I if there were only inserts? 24 hours of inserts.

Answer 1

Databases format the data for quick future reference and modification, and under ACID conditions. This involves adding various kinds of headers and dividing the data into pages, and optionally reserving some free space in each page for future use. But 3 fold seems extravagant if you really have no indexes. I get each row taking a little over 101 bytes, 81 tuples per 8192-byte page, so quite a bit less than a two fold expansion.

You can 'select ctid from my_table' to see how many tuples are generally packed into each page. It would be interesting to know if you get less than 81 tuples per page consistently, or if there are inconsistent gaps or regions of lower density. If the table fillfactor is set to 100 and your load started with an empty table and had no false starts, then you shouldn't have either issue. If you do, then perhaps a VACUUM FULL will fix it (at the expense of taking a huge amount of time, and scratch space to do the work). Aside from that, for an insert-only table VACUUM would only be needed to set hint bits, freeze tuples, or set the visibility map; not for space reasons.

If your main goal is to make your data smaller, there are excellent compression tools available. PostgreSQL is not one of them.