1

I'm a rather new to Postgres and the issue I see seems a bit strange. Maybe you as experts know something about it.

I have a table (around 3 million rows and 200 columns). In order to populate fresh data this table gets dropped and fully reloaded on a regular basis. I didn't like this approach with full reload so I changed the process a little bit: now the records that require recalculation are deleted and loaded from scratch - other data stays intact. This approach is significantly faster than the full reload, however after deletion and insertion the table bloats horribly (3-5 times depending on the number of records deleted-inserted).

I do understand that the data is not physically removed when DELETE statement is executed. I know that dead tupples remain. My expectation was that if I dropped all the indexes before deleting data, partially deleted data, did VACUUM ANALYZE, reloaded the data and built indexes once again (exactly in this sequence) neither the table itself nor indexes would bloat. But apparently, I was wrong.

Is there anything substantial that I am missing here?

UPDATE: Funny thing is that "SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'table_name' " shows that there are no dead tuples. Now I'm puzzled even more.

UPDATE 2: I figured why the reason behind the bloating. There's another step after the "delta" reload that updates a couple of columns for the whole table (because they can't be loaded in delta mode). This update makes the table grow in size.

0

I don't think that VACUUM ANALYZE helps with free space reclaiming. It will only update index statistic. You could run VACUUM without ANALYZE, I think it will reclaim space immediately if the table is empty (otherwise it will just update the free space map).

But if you completely remove old rows, TRUNCATE TABLE will be much better: it will reclaim disk space immediately.

  • Thanks for the response, Federico. In Postgres 10.3 documentation on VACUUM I see the following: "VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table." It seems to me that ANALYZE is a useful addition to VACUUM - it doesn't cancel the main part (vacuuming). As for the TRUNCATE TABLE, it doesn't really fit my purpose because only the part of data is removed and reloaded. So I need a DELETE ... WHERE [some condition goes here]. – vvzadvor May 10 '18 at 11:58
  • Understood. Sorry for the wrong info. But then, the mistery is explained: usually VACUUM doesn't reclaim space immediately. It only updates the free space map. The only exception is when the last part of the file is free space, because then it can be reclaimed easily. Anyway, free space will be reused when rows are inserted or updated - doesn't it happen in your case? – Federico Razzoli May 10 '18 at 15:44
  • That's what I expected to happen - newly inserted rows ocupying the space that has just been freed by VACUUM. However, that, for some reason, doesn't happen in my case. – vvzadvor May 11 '18 at 5:37
  • Do you run VACUUM immediately after the DELETE? If some transactions are in progress, the deleted rows are not old enough to be considered "cleanable" by VACUUM. – Federico Razzoli May 11 '18 at 12:36
  • Yes, I do. But I'm sure there are no transactions running against this table while VACUUM is running. i actually figured out the reason behind bloating (please see update 2). – vvzadvor May 11 '18 at 14:34
0

VACUUM will only shrink the data file if the free space happens to be at the end. What you probably want is a VACUUM FULL. However be aware of a couple of caveats:

  1. It will lock the table while it runs (which may be a while)
  2. It will require equivalent temporary space to the table

But when it is finished, the final space used will be just enough for the actual data you have.

  • Thank you for the response. In your opinion what the best sequence of actions? Option 1: 1) Drop indexes 2) Delete the data that requires reloading 3) Perform VACUUM FULL 4) Insert new data 5) Create indexes OR Option 2: 1) Drop indexes 2) Delete the data that requires reloading 3) Insert new data 4) Perform VACUUM FULL 5) Create indexes – vvzadvor May 10 '18 at 14:24
  • TBH the way I would do this is to create a new table with the rows you want to keep from the first one, load the new data into the new table, then rename both the original and the new table, then drop the original. That minimises the time the data would be unavailable for queries. – Gaius May 10 '18 at 14:35
  • The thing is that table availability for queries is not an issue in this case. The tables are usually reloaded when no one is using them. There are 2 things I want to change: loading time and size of the resulting table. To decrease the loading time I introduced this "delta" approach (removing the rows that need to be recalculated and reloading them while leaving the rest of the data intact). However, I have no idea why inserted records don't ocupy the space that has been marked as free by VACUUM. – vvzadvor May 11 '18 at 5:41
  • Do you think it's a good idea to use COPY STDIN for copying the table (if i follow the approach you suggested)? I'm using Pentaho and there's a step called PostgreSQL Bulk Load that is basically doing COPY STDIN - I want to use it because it's fast. – vvzadvor May 11 '18 at 14:36

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.