System administration functions about relation size ignore changes in the table structure

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.