1

I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well.

I am using PostgreSQL 9.3 64Bit on a windows 2012 server. I have tweaked the postgresql.conf using advice on the wiki. So it is as optimised as I can make it.

The table is large (>150million rows) which I need to add an extra column and populate it based on the contents of 3 other columns. The size of this table is 53Gb without indexes.

Having tested lots of approaches I am using a CASE statement. Two of the columns are arrays so I have used GIN indexes and a standard index.

A sample of my CASE statement looks like this, the rules go up to 38

UPDATE osmm.topographicarea
    SET fcode = (CASE
        --building CASE statements
        WHEN (descriptivegroup @> '{Building}' and descriptiveterm @> '{Archway}') then 1
        WHEN (descriptivegroup @> '{Building}') then 2

        WHEN (descriptiveterm @> '{Step}') then 3
        WHEN (descriptivegroup @> '{Glasshouse}') then 4
        WHEN (descriptivegroup @> '{Historic Interest}') then 5
        WHEN (descriptivegroup @> '{Inland Water}') then 6
      ELSE 99             
   END);
COMMIT;

The process takes over 5hrs but adds a huge 180Gb to the table!!

Adding an integer column to this table surely should not do this?

How can I fix this please?

  • 2
    The indexes won't help at all here as you are updating all rows in the table. To improve the speed I would drop all indexes that include the fcode column. – a_horse_with_no_name Nov 27 '14 at 9:43
  • @a_horse_with_no_name ok that is really useful, any ideas why it would use up so much disk space? – tjmgis Nov 27 '14 at 9:47
  • 2
    That's caused by Postgres' MVCC implementation (wiki.postgresql.org/wiki/MVCC) which essentially means that the update creates a new copy of each row. One way to alleviate this might be to do the update in batches (e.g. of 5 million) and then run a vacuum analyze after each batch. Autovacuum will not kick in for that table until the update has been committed – a_horse_with_no_name Nov 27 '14 at 9:51
  • Can you add the result of select * from pg_stat_all_tables where relid='osmm.topographicarea'::regclass; ? – Daniel Vérité Nov 27 '14 at 14:14
  • if PostgreSQL would not copy those rows ROLLBACK would not work. where would you take those old rows from after all? – Hans-Jürgen Schönig Nov 27 '14 at 21:09
1

Size of the table is 53Gb without indexes. Obviously you have indexes, one of them a GIN index, which is typically multiple times the size of a plain table storing only the indexed column. I expect you get around 180 GB for

SELECT pg_total_relation_size(osmm.topographicarea);

Details on measuring sizes:

As has been commented, due to the MVCC model of Postgres, an UPDATE in place effectively writes a new row version of every changed row (and also for affected indexes), which roughly duplicates the size of the table in your operation.

If you don't have to worry about concurrent access, you could go a different route:

60 GB (size of table, rounded up) will be too much for a temp table in memory. It would spill to disk, which voids the intended effect. You could just create a new regular table, drop the old one and rename the new - if you don't have concurrent access or depending objects.

To reclaim space you need

VACUUM FULL osmm.topographicarea;

While being at it, make that:

VACUUM FULL ANALYZE osmm.topographicarea;

Or run CLUSTER or use pg_repack or pg_squeeze if you cannot afford an exclusive lock on the big table.

Details:

Optimize UPDATE

Indexes are not helping for this UPDATE, on the contrary. Since you are updating every row and all involved columns are in the same row, indexes are of no use whatsoever here. They still have to be kept up to date at all times, though. It will be much cheaper to delete all indexes and add them back later - especially the comparatively expensive GIN index. And more ... Details:

Also, you are updating every row unconditionally. If some of the rows already have the right value in fcode, it would be cheaper not to touch those at all. Details (last chapter):

But it sounds like you are adding a new column. In this case there is nothing to gain here.

Of course, all the usual advice for performance optimization applies as well.

  • so it was VACUUM FULL then ended up releasing all the space that had been used up during the UPDATE process. Thanks for your help – tjmgis Dec 2 '14 at 9:29

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.