0

Recently I configured netflow for monitoring traffic of my company. I use pmacct to collect netflow packets and PostgreSQL for storing them.

I only keep netflow records for a week and after that every day I delete all records that older than 7 days.

this script runs every day with cronjob:

date >> /root/log/cronlog

du -h /var/lib/postgresql/data/base/ >> /root/log/cronlog

psql -U pmacct -d pmacct_switch -c "DELETE FROM acct_v9 WHERE stamp_updated < NOW() - INTERVAL '7 days';" >> /root/log/cronlog
psql -U pmacct -d pmacct_switch -c "VACUUM;" >> /root/log/cronlog

du -h /var/lib/postgresql/data/base/ >> /root/log/cronlog

date >> /root/log/cronlog

and this is the cronlog file:

Wed Jul 8 01:00:02 +0430 20207.4M

/var/lib/postgresql/data/base/17.5M

/var/lib/postgresql/data/base/1299480G

/var/lib/postgresql/data/base/167654.0K

/var/lib/postgresql/data/base/pgsql_tmp7.4M

/var/lib/postgresql/data/base/129936.5M

/var/lib/postgresql/data/base/1638480G

/var/lib/postgresql/data/base/

DELETE 100424891
VACUUM

7.4M

/var/lib/postgresql/data/base/17.5M

/var/lib/postgresql/data/base/1299481G

/var/lib/postgresql/data/base/167654.0K

/var/lib/postgresql/data/base/pgsql_tmp7.4M

/var/lib/postgresql/data/base/129936.5M

/var/lib/postgresql/data/base/1638481G 
/var/lib/postgresql/data/base/Wed Jul 8 02:29:33 +0430 2020

Ss you see after deleting 100 millions of records free space doesn't increase!

So, what's the solution?

2
  • 1
    What version of PostgreSQL are you using? Jul 8, 2020 at 13:48
  • The solution is to use partitioning - then you can simply drop the partitions with no longer needed rows.
    – user1822
    Jul 10, 2020 at 11:28

1 Answer 1

2

The documentation describes that well:

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

For an application where you have to delete lots of rows regularly, you might consider partitioning. If you can partition the table in a way so that you can drop partitions rather than running DELETE, you won't have no bloat problem any more.

4
  • vacuum full need a lot of extra disk space. in my case, my database is almost 150G. and i only have 200G in server. whenever i run vacuum full command after 1 minute there is almost no free space left and i have no choice bu to terminate vacuum full process. so obviously i can't use vacuum full.
    – ali.n
    Jul 10, 2020 at 11:12
  • Treat one table after the other. Jul 10, 2020 at 12:30
  • i only have one table in database!
    – ali.n
    Jul 11, 2020 at 3:53
  • You could partition the table, see my updated answer. Jul 13, 2020 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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