- What would be a suitable setting for autovacuum to act on this table?
We have a "sequence" table, it does what sequence of postgres should do, but perhaps by using other banks the development opted for this approach, which is to use a table in the bank to store the last id inserted in other tables.
An example of the sequence table:
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
The next ID of any record in the database will be the "no_sequence" + 1, so this table receives a high amount of UPDATEs, about 100 per minute and very rarely some INSERT, one per year maybe less, my doubt autovacuum account, today I use the following configuration:
'autovacuum_vacuum_scale_factor' = 0.20
'autovacuum_vacuum_threshold' = 50
When you get to 51 updated tuples you will clear the table if the autovacuum is available, in a more general context the autovacuum runs every 30 minutes or so if you ignore this table:
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
In the example above, we saw that the n_dead_tup passed 51 because the autovacuum is sleeping 60 seconds.
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
- What would be a suitable setting for autovacuum to act on this table?
If you need more information, please let us know.