PostgreSQL database size is not reasonable - Mailing list pgsql-performance

From Mariel Cherkassky
Subject PostgreSQL database size is not reasonable
Date
Msg-id CA+t6e1=OQANSQ+vxUW8tR4hBPvnA=y4ThUToACejAoguex5dFA@mail.gmail.com
Whole thread Raw
Responses Re: PostgreSQL database size is not reasonable
List 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 kBcombit    | 285 GBpostgres  | 7959 kBtemplate1 | 7983 kBrepmgr    | 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 GBrep.nap_inter_x5         | 46 GBrep.man_x5               | 16 GBrep.tc_fint_x5           | 9695 MBrep.nap_ip_debit_x5      | 7645 MBrep.ip__billing      | 5458 MBrep.ps_rd            | 3417 MBrep.nap_ip_discount      | 3147 MBrep.custo_x5             | 2154 MBrep.ip_service_discou_x5 | 1836 MBrep.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 -n
7284    13322
7868    13323
7892    1
8156    166694
298713364       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 ?

pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: Bitmap scan is undercosted? - overestimated correlation and cost_index
Next
From: "David G. Johnston"
Date:
Subject: Re: PostgreSQL database size is not reasonable