Re: Why is my database so big?

Tom, thanks for your unbiased detailed response.

Interesting post.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 22 February 2016 05:06
To: Andrew Smith
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why is my database so big?

Andrew Smith <laconical@gmail.com> writes:
> I am setting up a proof of concept database to store some historical data.
> Whilst I've used PostgreSQL a bit in the past this is the first time
> I've looked into disk usage due to the amount of data that could
> potentially be stored. I've done a quick test and I'm a little
> confused as to why it is occupying so much space on disk. Here is my table
definition:

> CREATE TABLE "TestSize"
> (
>   "Id" integer NOT NULL,
>   "Time" timestamp without time zone NOT NULL,
>   "Value" real NOT NULL,
>   "Status" smallint NOT NULL,
>   PRIMARY KEY ("Id", "Time")
> );

> CREATE INDEX test_index ON "TestSize" ("Id");

> With a completely empty table the database is 7 MB. After I insert 1
> million records into the table the database is 121 MB. My
> understanding is that each of the fields is sized as follows:

> integer - 4 bytes
> timestamp without time zone - 8 bytes
> real - 4 bytes
> smallint - 2 bytes

> So for 1 million records, it needs at least 18 million bytes, or ~17
> MB to store the data. Now I'm sure there is extra space required for
> managing the primary key fields, the index and other misc overhead
> involved in getting this data into the internal storage format used by
> PostgreSQL. But even if I triple the number of bytes stored for each
> record, I only end up with 51 MB or so. Am I missing something obvious?

It doesn't sound that far out of line.  Postgres is not great with narrow
tables like this one :-(.  The factors you're not accounting for include:

* Alignment padding.  On a 64-bit machine those fields would occupy 24
bytes, not 18, because row widths are always going to be multiples of 8.

* Row header overhead, which is 28 bytes per row (24-byte row header plus
4-byte row pointer).

* That primary key index is going to need 16 data bytes per entry (alignment
again), plus an 8-byte index tuple header, plus a 4-byte row pointer.

* The other index similarly requires 8+8+4 bytes per row.

* Indexes tend not to be packed completely full.  If you load a PG btree in
exactly sequential order, the leaf pages should get packed to about the
index fillfactor (90% by default).  If you load in random order, ancient
wisdom is that the steady-state load factor for a btree is about 66%.

* There's some other inefficiencies from upper-level btree pages, page
header overhead, inability to split rows across pages, etc; though these
tend to not amount to much unless you have wide rows or wide index entries.

Given the above considerations, the *minimum* size of this table plus
indexes is 100 bytes/row.  Your observed result of 114 bytes/row suggests
you're getting an index load factor of around 80%, if I counted on my
fingers correctly.  That's not awful, but I'd guess that at least one of the
indexes is getting loaded nonsequentially.

You could REINDEX the indexes (possibly after playing with their fillfactor
settings) to improve their density.  But you're not going to be able to move
the needle by more than about 10% overall, so personally I wouldn't bother.

The long and the short of it is that Postgres is more oriented to OLTP-style
applications where access to and update of individual rows is the key
performance metric.  If you're dealing with historical data and mainly want
aggregated query results, it's possible you'd get better performance and
more-compact storage from a column-store database.

There's ongoing investigation into extending Postgres to support
column-style storage for better support of applications like that; but any
such feature is probably several years away, and it will not come without
performance compromises of its own.

            regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general