Re: Poor overall performance unless regular VACUUM FULL - Mailing list pgsql-performance

> Ouch hurts my eyes :)  Can you see something like table_len,
> dead_tuple_percent, free_percent order by dead_tuple_percent desc
> limit 10 or something like that maybe?

Sorry about the pain.  Didn't know what you needed to see.

Ordering by dead_tuple_percent:

db.production=> select table_name, table_len, dead_tuple_percent,
free_percent from temp_tuplestats order by dead_tuple_percent desc
limit 10;
              table_name              | table_len | dead_tuple_percent | free_percent
-------------------------------------+-----------+--------------------+--------------
  scheduler_info                      |      8192 |              43.95 |           46
  inserter_maintenance_logs           |     16384 |              25.13 |            9
  merchants                           |      8192 |              24.19 |           64
  scheduler_in_progress               |     32768 |              16.47 |           75
  guilds_hosts                        |      8192 |              13.28 |           67
  work_types                          |      8192 |              12.18 |           78
  production_printer_maintenance_logs |     16384 |              11.18 |           11
  guilds_work_types                   |      8192 |              10.94 |           71
  config                              |      8192 |              10.47 |           83
  work_in_progress                    |    131072 |               8.47 |           85
(10 rows)

These are our smallest, and in terms of performance, least significant
tables.  Except for work_in_progress, they play little part in overall
system performace.  work_in_progress gets dozens of insertions and
deletions per second, and as many queries.

Ordering by table size, because I had the questions of where the bloat
is, in terms of disk space used (since I brought up before that the
physical size of the database is growing at about 50% per quarter):

db.production=> select table_name, table_len, dead_tuple_percent, free_percent from temp_tuplestats order by table_len
desclimit 10; 
                  table_name                 |  table_len  | dead_tuple_percent | free_percent
--------------------------------------------+-------------+--------------------+--------------
  documents                                  | 28510109696 |               1.05 |           21
  document_address                           | 23458062336 |               2.14 |           10
  latest_document_address_links              |  4953735168 |               3.71 |           21
  documents_ps_page                          |  4927676416 |               1.19 |            6
  injectd_log                                |  4233355264 |               0.74 |           17
  ps_page                                    |  3544350720 |               0.81 |            4
  temp_bak_documents_invoice_amount_for_near |  3358351360 |                  0 |            0
  statements                                 |  1832091648 |                4.4 |            2
  documents_old_addresses                    |  1612947456 |                  0 |            1
  cron_logs                                  |   791240704 |                  0 |            1
(10 rows)

Am I seeing in the above queries evidence that my bloat is mostly in
free space, and not in dead tuples?