PostgreSQL DB size larger than expected

Question

I have the next psql database:

CREATE TABLE "readings33" (
    "uniqueid" BIGSERIAL PRIMARY KEY,
    "uniqueid_sensor" INTEGER NOT NULL,
    "timestamp" TIMESTAMP NOT NULL DEFAULT NULL,
    "value" VARCHAR(15) NOT NULL,
    CONSTRAINT "FK_readings_sensors" FOREIGN KEY ("uniqueid_sensor") REFERENCES "public"."sensors" ("uniqueid") ON UPDATE NO ACTION ON DELETE CASCADE   
);

AFAIK the total size should be around:

"uniqueid" -> 8 bytes
"uniqueid_sensor" -> 4 bytes
"timestamp" -> 10 bytes
"value" VARCHAR(15) 8 bytes (because my value length for the test is a string with 8 bytes)

The sum of all is 8+4+10+8 = 30 bytes but when I write 100.000 rows to the DB this occupees 12.5 Mibs that is 125 bytes per row. I've done this text with 10.000 rows and the relation is about the same... Can anybody tell me why this increment is size??

Thanks in advance

There is another 23 bytes overhead per row and then there is [data type alignment](https://stackoverflow.com/a/7431468/330315) (btw: a timestamp only [takes 8 bytes](https://www.postgresql.org/docs/current/datatype-datetime.html)) — , Mar 17 '21 at 19:50
I get less than 9MB, and 1/4 of that is for the primary key index. — jjanes, Mar 17 '21 at 22:27
How exactly did you measure the table size? Please **[edit]** your question and add that query. — , Mar 18 '21 at 08:17

Laurenz Albe · Answer 1 · 2021-03-18T09:50:39.890

0

A timestamp occupies 8 bytes, but there will be 4 bytes of padding after the integer. An 8 byte string will occupy 9 bytes.

So the row data occupy 33 bytes. Together with 23 bytes row header and additional padding that will be around 60 bytes. Finally, there is some overhead per 8kB data block.

If you get much more than that, then either your measurements are wrong (did you include the primary key index?), or your table is bloated by data modifications or a non-standard fillfactor setting.

To measure the actual size of the table, use

SELECT pg_table_size('readings33');

To measure the size including the indexes, use

SELECT pg_total_relation_size('readings33');

edited Mar 18 '21 at 09:50

answered Mar 18 '21 at 03:31

Laurenz Albe

209,280
17
206
263

Hi Laurenz, the rows are imported from a CSV file, no modifications after the import and fillfactor is the default when creating the table. Is there any application to analyze where the problem is?? The size of the table is measured by HeidiSQL, don't know what exactly does and the index is started at 1. – Mar 18 '21 at 08:38
I have added queries to the answer. – Laurenz Albe Mar 18 '21 at 09:50

score 0 · Answer 2 · answered Mar 18 '21 at 09:00

Finally after run this script I found that the size is correct, don't know why HeidiSQL gives me a wrong size:

WITH cteTableInfo AS 
(
    SELECT 
        COUNT(1) AS ct
        ,SUM(length(t::text)) AS TextLength  
        ,'public.readings'::regclass AS TableName  
    FROM public.readings AS t  
)
,cteRowSize AS 
(
   SELECT ARRAY [pg_relation_size(TableName)
               , pg_relation_size(TableName, 'vm')
               , pg_relation_size(TableName, 'fsm')
               , pg_table_size(TableName)
               , pg_indexes_size(TableName)
               , pg_total_relation_size(TableName)
               , TextLength
             ] AS val
        , ARRAY ['Total Relation Size'
               , 'Visibility Map'
               , 'Free Space Map'
               , 'Table Included Toast Size'
               , 'Indexes Size'
               , 'Total Toast and Indexes Size'
               , 'Live Row Byte Size'
             ] AS Name
   FROM cteTableInfo
)
SELECT 
    unnest(name) AS Description
    ,unnest(val) AS Bytes
    ,pg_size_pretty(unnest(val)) AS BytesPretty
    ,unnest(val) / ct AS bytes_per_row
FROM cteTableInfo, cteRowSize
 
UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'TotalRows', ct, NULL, NULL FROM cteTableInfo
UNION ALL SELECT 'LiveTuples', pg_stat_get_live_tuples(TableName), NULL, NULL FROM cteTableInfo
UNION ALL SELECT 'DeadTuples', pg_stat_get_dead_tuples(TableName), NULL, NULL FROM cteTableInfo;

Results:

HeidiSQL just sums up `pg_table_size()` plus `pg_relation_size()`. I should probably add `pg_indexes_size()` and `pg_total_relation_size()`? — Anse, Mar 19 '21 at 06:30

PostgreSQL DB size larger than expected

2 Answers2