Why does this POSTGRES command run out of space?.....
ALTER TABLE table1 ALTER column field1 TYPE VARCHAR(250);
field1 is currently VARCHAR(200), I need to increase it to VARCHAR(250) but after running for 20 minutes it reports running low on disc space.
My initial guess was that the DB is trying to find space for another 50 characters for all the existing records
However, although the table is a large partitioned table (10s of millions of rows and several 100 Gbytes). The postgres documentation seems to suggest that VARCHAR fields only use space actually needed for data, so there should be no need to allocate an extra 50 characters for all the millions of records in the tables - the extra space should only be required by new data arriving subsequently.
So why is so much extra space required ? (I had 16 Gbyte free when I attempted to run it)
Update, the correct version is....
"PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit"
The data is partitioned by month for the last 3 years, 36 partitions in total, (spread over 2 disks, not sure if that makes a difference).
Just re-ran it again, took 35 minutes, this is the error
ERROR: could not extend file "pg_tblspc/59626/PG_13_202007201/59627/348392.2": No space left on device
HINT: Check free disk space.
select version();will tell you. – Feb 24 '22 at 12:04