Adding new notes takes a ton of space (Vacuum database pgAdmin!!)

[polishukdenis]

Hi,

here's the problem. I have a big database. It takes about 64Gb of disk space. Now when I create a new definition and then run a task to create notes for it the DB becomes about 73Gb which is very much.

If I reset notes and recreate them then it's ok. The space taken becomes reasonable.

Is this a bug? If so then are you going to fix it? If not then how do I fix it?

[sreticentv]

If you have a huge database (64 gb) then you should consider hiring a database administrator. Big databases require a large amount of work to maintain properly. Big companies have 64 gb databases and have teams of people to work on them

[polishukdenis]

Unfortunately I don't have such an option, but thanks for advice.

Did I understand correctly that you're not considering such a behavior as a bug?

[karstenkloss]

In normal PostgreSQL operation, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (see Chapter 12): the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must be reclaimed for reuse by new rows, to avoid infinite growth of disk space requirements. This is done by running VACUUM.

Hope this explains to you why this is not a bug.......

PostgreSQL: Documentation: 8.1: Routine Database Maintenance Tasks (http://www.postgresql.org/docs/8.1/static/maintenance.html)

[polishukdenis]

Hmm, I don't think it explains much because 10GB for one definition is quite a lot. I have 430 definitions that take 64gb and then just a single new one takes about ten.

Also. From what is written in your post I make a conclusion that running vacuum should help. Is this a correct conclusion?

I will run it right now. But I'm pretty sure that I tried it before and it didn't help.

[karstenkloss]

It's the explanation for the growth of your database.

Make sure to run first "Vacuum (full)" and then "Reindex" as this order gives the best results.

[polishukdenis]

Thank you.
Here's what I'm running right now:

QIP Shot - (http://shot.qip.ru/00oGf3-5WuKdMO0d/)

Is this the correct one?

[karstenkloss]

I always make it within PGAdminIII.....

[polishukdenis]

Well, it didn't work. It freed up about 1gb of space...

[polishukdenis]

Okay, I'll try PGAdmin

[polishukdenis]

karstenkloss, wow, looks like you were right! Currently my PGadmin is still working but it reduced the disk space usage significantly already. So looks like that was the problem.

Although I hope that it will not kill my DB :)))

[karstenkloss]

I use it since years and never had any problems. Nice that it helps you :)