Hello
According to the example at the end of the letter the functions pg_total_relation_size, pg_table_size and pg_indexes_size seem to ignore adding or removing columns from a table.
I first noticed it in case of stored generated columns but the same applies to regular columns as well.
I tested it in PostgreSQL 16.
Best regards
Erki Eessaar
*******************************
CREATE TABLE Invoice (invoice_code INTEGER NOT NULL,
client_id INTEGER NOT NULL,
invoice_date DATE NOT NULL,
invoice_year SMALLINT GENERATED ALWAYS AS (extract(year FROM invoice_date)) STORED NOT NULL,
invoice_month SMALLINT GENERATED ALWAYS AS (extract(month FROM invoice_date)) STORED NOT NULL,
CONSTRAINT pk_invoice PRIMARY KEY (invoice_code));
/*I generate 15000 rows.*/
INSERT INTO Invoice (invoice_code, client_id, invoice_date)
SELECT generator AS invoice_code,
floor(random() * (1_000 - 1 + 1) + 1)::int AS client_id,
'2015-01-01'::date + floor(random() * (current_date - '2015-01-01' + 1) + 1)::int AS invoice_date
FROM generate_series(1, 15_000) AS generator;
SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 1056768
SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 1056768
ALTER TABLE Invoice DROP invoice_year;
ALTER TABLE Invoice DROP invoice_month;
VACUUM ANALYZE;
SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 1056768
SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 1056768
I removed stored(!) generated columns but the size does not change.
DROP TABLE Invoice;
CREATE TABLE Invoice (invoice_code INTEGER NOT NULL,
client_id INTEGER NOT NULL,
invoice_date DATE NOT NULL);
INSERT INTO Invoice (invoice_code, client_id, invoice_date)
SELECT generator AS invoice_code,
floor(random() * (1_000 - 1 + 1) + 1)::int AS client_id,
'2015-01-01'::date + floor(random() * (current_date - '2015-01-01' + 1) + 1)::int AS invoice_date
FROM generate_series(1, 15_000) AS generator;
SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 704512
SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 704512
ALTER TABLE Invoice ADD COLUMN invoice_year SMALLINT GENERATED ALWAYS AS (extract(year FROM invoice_date)) STORED NOT NULL;
ALTER TABLE Invoice ADD COLUMN invoice_month SMALLINT GENERATED ALWAYS AS (extract(month FROM invoice_date)) STORED NOT NULL;
SELECT pg_total_relation_size('invoice'::regclass) AS reslt;
--Result: 704512
SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 704512
I added stored(!) generated columns but the size does not change.
ALTER TABLE Invoice DROP COLUMN invoice_month;
ALTER TABLE Invoice DROP COLUMN invoice_year;
ALTER TABLE Invoice DROP COLUMN invoice_date;
VACUUM ANALYZE;
SELECT pg_total_relation_size('invoice'::regclass) AS reslt;--Result: 704512
SELECT pg_table_size('invoice'::regclass)+pg_indexes_size('invoice'::regclass) AS reslt;
--Result: 704512
The size is still the same.
