I'm very new to Postgres so my math could be off here ...
This is my table:
CREATE TABLE audit ( id BIGSERIAL PRIMARY KEY, content_id VARCHAR (50) NULL, type VARCHAR (100) NOT NULL, size bigint NOT NULL, timestamp1 timestamp NOT NULL DEFAULT NOW(), timestamp2 timestamp NOT NULL DEFAULT NOW() );
I want to make some estimations on how much space 1 row would occupy. After some reading I've come up with this, is it correct?
1 row = 23 (heaptupleheader) + 1 (padding) + 8 (id) + 50 (content_id) + 6 (padding) + 100 (type) + 4 (padding) + 8 (size) + 8 (timestamp) + 8 (timestamp) = 216 bytes
I also created this same table in my local Postgres DB but the numbers don't seem to match:
INSERT INTO public.audit(content_id, type, size) VALUES ('aaa', 'bbb', 100); SELECT pg_size_pretty( pg_total_relation_size('audit') ); -- returns 24 kb INSERT INTO public.audit(content_id, type, size) VALUES ('aaaaaaaaaaaaa', 'bbbbbbbbbbbbbb', 100000000000); SELECT pg_size_pretty( pg_total_relation_size('audit') ); -- still returns 24 kb
Which brings me to think that Postgres reserves a space of 24 kb to start with and as I put in more data it will get incremented by 132 bytes once I go beyond 24 kb? But something inside me says that can't be right.
I want to see how much space 1 row would occupy in Postgres db so I can analyze how much data I can potentially store in it. Maybe I'm missing something very obvious.