Postgres - 小表中的 autovacuum

• 什么是 autovacuum 对该表起作用的合适设置？

我们有一个“sequencia”表，它做了sequencepostgres 应该做的事情，但也许通过使用其他数据库，开发选择了这种方法，即使用数据库中的表来存储插入到其他表中的最后一个 id。

表“sequencia”的一个例子：

 id_entidade                      no_sequencia    
 -------------------------------  --------------- 
 CLIENTEGESTAO                    33325146        
 VEICULOREVISAO                   14541831        
 DADOSBOOK                        11627492        
 HISTORICOQUILOMETRAGEM           9992701         
 RESULTADOINTEGRACAO              4089476         
 CAMPOSREGISTROINTERFACE          2892807         
 REGISTROINTEGRACAO               1731981         
 ACAO                             1585661         
 ENCAMINHAMENTO                   1263190         
 REQUISICAOOS                     948967          
 MOTIVOEVENTO                     916872          
 EVENTO                           818115          
 VEICULO                          -35041          
 CLIENTE                          -64414

数据库中任何记录的下一个 ID 将是“no_sequencia”+ 1，因此该表收到大量更新，每分钟约 100 次，很少有一些 INSERT，每年一次可能更少，我的疑问仍然存在autovacuum，今天我使用以下配置：

`autovacuum_vacuum_scale_factor` = 0.20
`autovacuum_vacuum_threshold` = 50

当达到 51 个更新的元组时，如果 autovacuum 可用，将清除表，在更一般的上下文中，如果我们忽略此表，autovacuum 每 30 分钟左右运行一次：

 relname                    n_live_tup   % live   n_dead_tup    Tamanho     Registros    Configuracao                                                                                                                               last_autovacuum      autovacuum_count  
 -------------------------  -----------  -------  ------------  ----------  -----------  -----------------------------------------------------------------------------------------------------------------------------------------  -------------------  ------------------
 sequencia                  191          65       99            752 KB      191          {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50}                                                                       17/01/2018 19:41:31  13806             
 campanha                   3541         98       67            3400 KB     3821         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 19:40:49  194               
 sms                        2071256      99       1026          773160 KB   2071070      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 19:24:31  20                
 campanhaexecucao           1948         96       61            136 KB      1980         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 19:05:48  10                
 telefonema                 8957384      99       1792          1086424 KB  8957050      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:22:45  17                
 email                      1059515      99       3776          945368 KB   1056970      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:11:53  3                 
 oportunidade               698662       99       317           105992 KB   698440       {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:06:54  1                 
 interesse                  698254       99       550           93936 KB    698043       {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:06:46  1                 
 encaminhamento             8277408      99       1998          1187264 KB  8277230      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:03:24  39                
 evento                     3689441      99       5672          2376448 KB  3688780      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 17:06:11  36                
 sessao                     1736         94       102           216 KB      1736         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 16:22:44  18                
 acao                       14931180     99       6635          4250392 KB  14924700     {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=25000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=50000}  17/01/2018 15:25:32  10                
 parametroempresa           349          81       77            48 KB       348          {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50}                                                                       17/01/2018 14:22:41  22                
 tipoevento                 4565         99       19            3656 KB     4565         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 14:08:40  3                 
 usuariotipoevento          64254        98       992           4544 KB     64270        {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 14:03:41  5                 
 registrointerface          4050719      99       1194          549800 KB   4049100      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 13:30:35  2                 
 regraleadmontadora         117          90       13            48 KB       117          {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50}                                                                       17/01/2018 11:58:37  6                 
 usuarioformacontato        78593        98       1389          5944 KB     79230        {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 11:22:39  3                 
 arquivointegracao          1843         93       134           296 KB      1787         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 02:00:50  12                

在上面的示例中，我们看到n_dead_tup已经过了 51，autovacuum 正在休眠 60 秒。

 name ---------------------------------- setting ----- unit   
 autovacuum                              on             
 autovacuum_analyze_scale_factor         0.1            
 autovacuum_analyze_threshold            50             
 autovacuum_freeze_max_age               200000000      
 autovacuum_max_workers                  3              
 autovacuum_multixact_freeze_max_age     400000000      
 autovacuum_naptime                      60            s      
 autovacuum_vacuum_cost_delay            20            ms     
 autovacuum_vacuum_cost_limit            -1             
 autovacuum_vacuum_scale_factor          0.2            
 autovacuum_vacuum_threshold             50             
 autovacuum_work_mem                     -1            kB     

• 什么是 autovacuum 对该表起作用的合适设置？

该过程运行良好，这只是预防措施，如果您需要更多信息，请告知。