Re: Why is my database so big?

On Mon, Feb 22, 2016 at 3:20 PM, Andrew Smith <laconical(at)gmail(dot)com> wrote:

> Hello,
>
> 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?
>

PostgreSQL version please ?

What is the output of below query ?

=# select pg_size_pretty(pg_relation_size('TestSize'));

Do you see any pg_toast tables in the database ? How are you calculating
the database size ?

Each field size could vary depending upon the number of characters you
entered. If you wish to understand the table size stats and its tuple size
"pgstattuple" contrib module is the way to go.

There are other components in the postgresql's data-directory which occupy
the disk space - postgresql logs (if enabled), pg_xlog etc..

What is size of each directory in the data-directory on the disk ?

Regards,
Venkata B N

Fujitsu Australia