PostgreSQL database size is not reasonable

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-performance(at)postgresql(dot)org>
Subject: PostgreSQL database size is not reasonable
Date: 2017-12-12 15:15:06
Message-ID: CA+t6e1=OQANSQ+vxUW8tR4hBPvnA=y4ThUToACejAoguex5dFA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

In my postgresql 9.6 instance I have 1 production database. When I query
the size of all databases :

combit=> Select
pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname))
as size from pg_database;
datname | size -----------+---------
template0 | 7265 kB
combit | 285 GB
postgres | 7959 kB
template1 | 7983 kB
repmgr | 8135 kB(5 rows)

When I check what are the big tables in my database (includes indexes) :

combit=> SELECT nspname || '.' || relname AS "relation",
combit-> pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
combit-> FROM pg_class C
combit-> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
combit-> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
combit-> AND C.relkind <> 'i'
combit-> AND nspname !~ '^pg_toast'
combit-> ORDER BY pg_total_relation_size(C.oid) DESC
combit-> LIMIT 20;
relation | total_size -----------------------------+------------
rep.ps_rf_inst_prod | 48 GB
rep.nap_inter_x5 | 46 GB
rep.man_x5 | 16 GB
rep.tc_fint_x5 | 9695 MB
rep.nap_ip_debit_x5 | 7645 MB
rep.ip__billing | 5458 MB
rep.ps_rd | 3417 MB
rep.nap_ip_discount | 3147 MB
rep.custo_x5 | 2154 MB
rep.ip_service_discou_x5 | 1836 MB
rep.tc_sub_rate__x5 | 294 MB

The total sum is not more than 120G.

When I check the fs directly :

[/data/base] : du -sk * | sort -n7284 133227868 133237892
18156 166694298713364 16400

[/data/base] :

16400 is the oid of the combit database. As you can see the size of combit
on the fs is about 298G.

I checked for dead tuples in the biggest tables :

combit=>select relname,n_dead_tup,last_autoanalyze,last_analyze,last_autovacuum,last_vacuum
from pg_stat_user_tables order by n_live_tup desc limit4;

-[ RECORD 1 ]----+------------------------------
relname | ps_rf_inst_prod
n_dead_tup | 0
last_autoanalyze | 2017-12-04 09:00:16.585295+02
last_analyze | 2017-12-05 16:08:31.218621+02
last_autovacuum |
last_vacuum |
-[ RECORD 2 ]----+------------------------------
relname | man_x5
n_dead_tup | 0
last_autoanalyze | 2017-12-05 06:02:07.189184+02
last_analyze | 2017-12-05 16:12:58.130519+02
last_autovacuum |
last_vacuum |
-[ RECORD 3 ]----+------------------------------
relname | tc_fint_x5
n_dead_tup | 0
last_autoanalyze | 2017-12-05 06:04:06.698422+02
last_analyze |
last_autovacuum |
last_vacuum |
-[ RECORD 4 ]----+------------------------------
relname | nap_inter_x5
n_dead_tup | 0
last_autoanalyze | 2017-12-04 08:54:16.764392+02
last_analyze | 2017-12-05 16:10:23.411266+02
last_autovacuum |
last_vacuum |

I run vacuum full on all 5 top tables 2 hours ago and it didnt free alot of
space...

On this database the only operations that happen are truncate , insert and
select. So how can it be that I had dead tuples on some of my tables ? If I
only run truncate,select,insert query tuples shouldnt be created..

And the bigger question, Where are the missing 180G ?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David G. Johnston 2017-12-12 15:21:14 Re: PostgreSQL database size is not reasonable
Previous Message Jeff Janes 2017-12-12 09:29:48 Re: Bitmap scan is undercosted? - overestimated correlation and cost_index