Re: Poor overall performance unless regular VACUUM FULL

>> On Tue, 14 Jul 2009, Scott Marlowe wrote:
> Are you guys doing anything that could be deemed pathological, like
> full table updates on big tables over and over?  Had an issue last
> year where a dev left a where clause off an update to a field in one
> of our biggest tables and in a few weeks the database was so bloated
> we had to take it offline to fix the problem.  After fixing the
> query.

I've just audited the source, looking for any updates without where
clauses.  None jumped out to bite me.

Almost everything we do happens in transactions which can occasionally
take 10-20 minutes to complete and span thousands or tens of thousands
of rows across multiple tables.  Are long-running transactions a
culprit in table bloat?

I've also used contrib/pgstattuple to try to identify which of our
large tables and indices are experiencing bloat.  Here are the
pgstattuple results for our largest tables:

table_len:          56639488
tuple_count:        655501
tuple_len:          53573112
tuple_percent:      94.59
dead_tuple_count:   0
dead_tuple_len:     0
dead_tuple_percent: 0
free_space:         251928
free_percent:       0.44
table_name:         status

table_len:          94363648
tuple_count:        342363
tuple_len:          61084340
tuple_percent:      64.73
dead_tuple_count:   10514
dead_tuple_len:     1888364
dead_tuple_percent: 2
free_space:         28332256
free_percent:       30.02
table_name:         uploads

table_len:          135675904
tuple_count:        1094803
tuple_len:          129821312
tuple_percent:      95.68
dead_tuple_count:   133
dead_tuple_len:     16048
dead_tuple_percent: 0.01
free_space:         991460
free_percent:       0.73
table_name:         invoice_details

table_len:          148914176
tuple_count:        1858812
tuple_len:          139661736
tuple_percent:      93.79
dead_tuple_count:   1118
dead_tuple_len:     80704
dead_tuple_percent: 0.05
free_space:         1218040
free_percent:       0.82
table_name:         job_status_log

table_len:          173416448
tuple_count:        132974
tuple_len:          117788200
tuple_percent:      67.92
dead_tuple_count:   10670
dead_tuple_len:     7792692
dead_tuple_percent: 4.49
free_space:         46081516
free_percent:       26.57
table_name:         mail

table_len:          191299584
tuple_count:        433378
tuple_len:          145551144
tuple_percent:      76.09
dead_tuple_count:   1042
dead_tuple_len:     862952
dead_tuple_percent: 0.45
free_space:         42068276
free_percent:       21.99
table_name:         sessions

table_len:          548552704
tuple_count:        5446169
tuple_len:          429602136
tuple_percent:      78.32
dead_tuple_count:   24992
dead_tuple_len:     1929560
dead_tuple_percent: 0.35
free_space:         93157980
free_percent:       16.98
table_name:         job_state_log

table_len:          639262720
tuple_count:        556415
tuple_len:          221505548
tuple_percent:      34.65
dead_tuple_count:   66688
dead_tuple_len:     27239728
dead_tuple_percent: 4.26
free_space:         380168112
free_percent:       59.47
table_name:         jobs

table_len:          791240704
tuple_count:        8311799
tuple_len:          700000052
tuple_percent:      88.47
dead_tuple_count:   39
dead_tuple_len:     3752
dead_tuple_percent: 0
free_space:         11397548
free_percent:       1.44
table_name:         cron_logs

table_len:          1612947456
tuple_count:        10854417
tuple_len:          1513084075
tuple_percent:      93.81
dead_tuple_count:   0
dead_tuple_len:     0
dead_tuple_percent: 0
free_space:         13014040
free_percent:       0.81
table_name:         documents_old_addresses

table_len:          1832091648
tuple_count:        13729360
tuple_len:          1600763725
tuple_percent:      87.37
dead_tuple_count:   598525
dead_tuple_len:     80535904
dead_tuple_percent: 4.4
free_space:         38817616
free_percent:       2.12
table_name:         statements

table_len:          3544350720
tuple_count:        64289703
tuple_len:          2828746932
tuple_percent:      79.81
dead_tuple_count:   648849
dead_tuple_len:     28549356
dead_tuple_percent: 0.81
free_space:         143528236
free_percent:       4.05
table_name:         ps_page

table_len:          4233355264
tuple_count:        22866609
tuple_len:          3285722981
tuple_percent:      77.62
dead_tuple_count:   231624
dead_tuple_len:     31142594
dead_tuple_percent: 0.74
free_space:         706351636
free_percent:       16.69
table_name:         injectd_log

table_len:          4927676416
tuple_count:        55919895
tuple_len:          4176606972
tuple_percent:      84.76
dead_tuple_count:   795011
dead_tuple_len:     58409884
dead_tuple_percent: 1.19
free_space:         279870944
free_percent:       5.68
table_name:         documents_ps_page

table_len:          4953735168
tuple_count:        44846317
tuple_len:          3346823052
tuple_percent:      67.56
dead_tuple_count:   2485971
dead_tuple_len:     183639396
dead_tuple_percent: 3.71
free_space:         1038200484
free_percent:       20.96
table_name:         latest_document_address_links

table_len:          23458062336
tuple_count:        89533157
tuple_len:          19772992448
tuple_percent:      84.29
dead_tuple_count:   2311467
dead_tuple_len:     502940946
dead_tuple_percent: 2.14
free_space:         2332408612
free_percent:       9.94
table_name:         document_address

table_len:          28510109696
tuple_count:        44844664
tuple_len:          21711695949
tuple_percent:      76.15
dead_tuple_count:   1134932
dead_tuple_len:     300674467
dead_tuple_percent: 1.05
free_space:         5988985892
free_percent:       21.01
table_name:         documents

Here are the pgstatindex results for our largest indices.  I assumed
that negative index sizes are a reslt of integer overflow and ordered
the results accordingly.

index_size:         1317961728
version:            2
tree_level:         3
root_block_no:      12439
internal_pages:     13366
leaf_pages:         1182318
empty_pages:        0
deleted_pages:      13775
avg_leaf_density:   -157.76
leaf_fragmentation: 37.87
index_name:         documents_pkey

index_size:         1346609152
version:            2
tree_level:         3
root_block_no:      10447
internal_pages:     1937
leaf_pages:         162431
empty_pages:        0
deleted_pages:      12
avg_leaf_density:   66.56
leaf_fragmentation: 26.48
index_name:         statements_pkey

index_size:         1592713216
version:            2
tree_level:         3
root_block_no:      81517
internal_pages:     723
leaf_pages:         177299
empty_pages:        0
deleted_pages:      16400
avg_leaf_density:   74.15
leaf_fragmentation: 5.58
index_name:         latest_document_address2_precedence_key

index_size:         1617821696
version:            2
tree_level:         3
root_block_no:      81517
internal_pages:     720
leaf_pages:         185846
empty_pages:        0
deleted_pages:      10921
avg_leaf_density:   78.8
leaf_fragmentation: 10.96
index_name:         documents_ps_page_ps_page_id_idx

index_size:         1629798400
version:            2
tree_level:         3
root_block_no:      81517
internal_pages:     728
leaf_pages:         188325
empty_pages:        0
deleted_pages:      9896
avg_leaf_density:   88.23
leaf_fragmentation: 0.66
index_name:         ps_page_pkey

index_size:         1658560512
version:            2
tree_level:         3
root_block_no:      81517
internal_pages:     740
leaf_pages:         191672
empty_pages:        0
deleted_pages:      10048
avg_leaf_density:   86.7
leaf_fragmentation: 1.03
index_name:         ps_page_ps_id_key

index_size:         -31956992
version:            2
tree_level:         3
root_block_no:      12439
internal_pages:     5510
leaf_pages:         475474
empty_pages:        0
deleted_pages:      39402
avg_leaf_density:   72.19
leaf_fragmentation: 3.02
index_name:         latest_document_address2_pkey

index_size:         -321863680
version:            2
tree_level:         3
root_block_no:      81517
internal_pages:     1809
leaf_pages:         479805
empty_pages:        0
deleted_pages:      3383
avg_leaf_density:   25.63
leaf_fragmentation: 40.05
index_name:         documents_id_idx

index_size:         -461504512
version:            2
tree_level:         3
root_block_no:      49813
internal_pages:     3023
leaf_pages:         456246
empty_pages:        0
deleted_pages:      8682
avg_leaf_density:   34.37
leaf_fragmentation: 66.83
index_name:         documents_city

index_size:         -11818844162
version:            3
tree_level:
root_block_no:      11036
internal_pages:     10003
leaf_pages:         822178
empty_pages:        0
deleted_pages:      72121
avg_leaf_density:   54.52
leaf_fragmentation: 3.37
index_name:         document_address_pkey

index_size:         -12678348802
version:            3
tree_level:
root_block_no:      32210
internal_pages:     2410
leaf_pages:         359867
empty_pages:        0
deleted_pages:      7245
avg_leaf_density:   53.31
leaf_fragmentation: 52.7
index_name:         documents_recipient

index_size:         -13276282882
version:            3
tree_level:
root_block_no:      27346
internal_pages:     2183
leaf_pages:         360040
empty_pages:        0
deleted_pages:      0
avg_leaf_density:   58.39
leaf_fragmentation: 50
index_name:         documents_magic_id_key

index_size:         -14476328962
version:            3
tree_level:
root_block_no:      44129
internal_pages:     1998
leaf_pages:         339111
empty_pages:        0
deleted_pages:      6465
avg_leaf_density:   50.12
leaf_fragmentation: 52.85
index_name:         documents_zip10

index_size:         -14723809282
version:            3
tree_level:
root_block_no:      81515
internal_pages:     2470
leaf_pages:         326170
empty_pages:        0
deleted_pages:      15913
avg_leaf_density:   38.21
leaf_fragmentation: 77.19
index_name:         documents_state

index_size:         -14831697922
version:            3
tree_level:
root_block_no:      47536
internal_pages:     1607
leaf_pages:         341421
empty_pages:        0
deleted_pages:      208
avg_leaf_density:   45.28
leaf_fragmentation: 46.48
index_name:         documents_account_number

index_size:         -17118412802
version:            3
tree_level:
root_block_no:      81517
internal_pages:     1149
leaf_pages:         296146
empty_pages:        0
deleted_pages:      18027
avg_leaf_density:   80.86
leaf_fragmentation: 7.14
index_name:         document_address_precedence_key