0

I have two tables, A(a1 serial, a2 varchar(8)) and B(b2 varchar(8));

My query looks like,

alter table B add column b1 integer;
update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2;

Here's my problem:

B was originally around 46 GB and had 6*10^7 entries. According to the documentation integers take up 8 bytes. Thus, 4 * 6 * 10^7 = 24 * 10^7 < 1 GB. The query is still executing. When I log in from another console and do

select pg_size_pretty(pg_relation_size('B'));

I see 60 GB. I'm expecting the size to be more than this when the query ends.

I'm confused by this hike in size. Is it because of the temporary data structures that it creates? If so could I expect the size to go down if i use VACUUM?

I'm using postgres 9.1.

  • you may need a VACUUM ANALYZE B – w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴ Oct 15 '14 at 9:12
  • @WingedPanther that won't reclaim anything immediately, it'll just free space to be used for subsequent updates / inserts. VACUUM FULL or CLUSTER is the only way to do this, as Josh says below. It's often more efficient to create an entirely new table (CREATE TABLE AS) then rename it into place in a single transaction. That won't generate anywhere near as much WAL if your wal_level is minimal. – hbn Oct 15 '14 at 11:19
2

Well, you are running this giant UPDATE statement of all rows in table B:

update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2;

which will more-or-less double the size of the table, since the UPDATE has to keep old row versions around. You may want to read up a bit on how PostgreSQL implements MVCC and how vacuuming works, but to answer this question:

If so could I expect the size to go down if i use VACUUM?

The space utilized by the table should go down if you run a VACUUM FULL or CLUSTER; probably a VACUUM alone will not be sufficient to immediately reclaim space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.