I'm trying to add a
INTEGER DEFAULT 0 field to a very large (partitioned) table spread across two tablespaces.
Each tablespace is on a different disc (one on C drive, the other on D). I get the following error
ERROR: could not extend file "pg_tblspc/31575/PG_10201707211/31576/1155134.27": No space left on device HINT: Check free disk space SQL state: 53100
Both C and D drive have a directory called
pg_tblspc/31575/PG_10201707211/31576 but neither has a file called 1155134.27 (I guess it's a temporary file?)
The C drive has 70 Gbyte of free space, the D drive 350 Gbyte.
Question 1 - how can I work out which of the two disks is actually short of space?
Question 2 - how can I work out how much free space is actually required? An integer requires 4 bytes storage, the table consists of monthly partitioned data, each month has about 60 million rows and the table has 4 years of data. 4 x 60 x 12 x 4 = about 12,000 Mbytes or 12 GB so according to my simple maths there should be enough space on either disc for all the extra space required. So why does postgres require more?
(postgres version 10.4)
The file structure on each disc is probably the same because each tablespace was created with very similar commands...
CREATE TABLESPACE fastDb OWNER dbOwn LOCATION 'c:/pgdata/fdb'; CREATE TABLESPACE slowDb OWNER dbOwn LOCATION 'd:/pgdata/fdb';
I have managed to answer question one simply by re-running the query (takes hours) and monitoring free-space from the OS - d: drive is the one running out of space.
Still can't work out how much space should be required. I've freed up 800Gb on d: drive and have switched to a SMALLINT field (2 bytes instead of 4) but I'm still running out of space.