1

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.

3
  • 1
    You forgot 23 bytes header overhead per row. Also varchar(16) is 16 characters not 16 bytes (and has one additional byte for the length information). Depending on your data this could be up to 48 bytes if you have values that require multi-byte characters
    – user1822
    Commented May 28, 2021 at 5:30
  • How many records are there in your table?
    – Vérace
    Commented May 28, 2021 at 12:00
  • around 100 million
    – Capacytron
    Commented May 29, 2021 at 19:32

1 Answer 1

2

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.

1
  • I have - Unique index for BigInt field and Non unique index for timez.
    – Capacytron
    Commented May 28, 2021 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.