How to measure the amount of disk space required for an ALTER TABLE operation?

Question

How to measure the amount of disk space required for an ALTER TABLE operation?

Navigation

#1 by (2 votes)

1

I need to make some changes to one of the tables to add new columns. The problem is that I can not at all. I get the following error:

PG: DiskFull: ERROR: could not extend file "base / 1672994 / 5557167.4": No space left on device. HINT: Check free disk space.

Well, there is no doubt that I need to increase my disk space, since not even a VACUUM FULL operation worked, presenting this same problem.

The query I tried to do is as follows:

ALTER TABLE messages ADD COLUMN readed BOOLEAN DEFAULT FALSE;

It's a simple field, and boolean does not take up much space. But reading on other forums, I saw something related to the fact that PG needs more room to do such an operation. I'm not sure how it works, but this table I'm trying to change has over 6,700,000 rows, and taking that into account, it might make sense that the current slot is not enough for the operation.

But anyway. What I want to know is: Is there a way for me to calculate on average how much additional space I'll need to buy for this operation to work? And how does this ALTER TABLE issue work? Do you really need a lot more room to work?

sql postgresql

asked by anonymous 08.04.2016 / 12:52

1 answer

Border radius on table with border collapse Reuse function on same page with Jquery (beginner) Change event?

score 2 · Accepted Answer

It's hard to give a definitive answer on this, and trial and error turns out to be an effective way to figure out where to go.

In fact it's a little strange to need so much space because the way you're doing should not change the whole table. PostgreSQL has a mechanism in which it changes the lines as it is written on it. As long as it has no writing, it takes the line without the column and considers the default value for the read query . It's very clever. But for some reason this does not seem to be working.

The basic idea is to see the size of the table. To list the tables:

SELECT pg_database.datname, pg_database_size(pg_database.datname),
    pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database ORDER BY pg_database_size DESC

This may not be enough as there may be changes in the index as well or you may need other manipulations that only look at PostgreSQL's inner workings to be sure.