X
X
x672017-09-05 17:58:46
PostgreSQL
x67, 2017-09-05 17:58:46

Why can postgres DB be bloated?

There is a database, recently I realized that it would be time to get rid of old data from it, I set daily cleaning of old data for the largest tables, and then a complete vacuum (then I turned off its daily execution). The database began to grow in size, despite the fact that the tables do not grow. From the manuals it is clear that VACUUM FULL deliver us from the evil one, but he does not deliver! For all tables shows

INFO:  vacuuming "tablename"
INFO:  "tablename": found 0  removable, X nonremovable row versions in Z pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed Y.YY sec., то есть вакуум не нужен, ибо все ненужное уже удалено

REINDEX also did just in case, although he never created indexes. And yet, the tables are now 12 GB, and the database is inflated to 24. What to do about it?
P.S. More bugs are showing up.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.
connection to server was lost
psql: FATAL:  the database system is in recovery mode
on a few heavy queries, or when doing a vacuum at night (which I disabled). Heavy requests are rare, but with them I unload ~ 500 mb of data generated from several subqueries, they last a long time (tens of minutes), but there are no requirements for speed
pps autovacuum settings and default postgres

Answer the question

In order to leave comments, you need to log in

1 answer(s)
T
terrier, 2017-09-05
@x67

Is it the same size after restart?
What are the largest directories in $PGDATA? ( du -sh ( --max-depth=N ) | sort -hr if anything ) Is n't the
disk actually leaking?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question