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