I have a table which is having oid column and few oid column's data might be frequently modified from my application. I thought, it would rewrite the data and replace with new data (basically, thought it will not use additional space). But, its keep on generating new oid whenever modification being done. (pg_largeobject table entries increased)
To solve this issue, lo_manage(oid_column) trigger created for my table before any update or delete for each row. And it works (like, its removing old oids). pg_largeobject entries not increased. but pg_largeobject table size keep on increasing.
I read about autovacuum daemon, which will consider if the table is getting any update / delete transactions (autovacuum_vacuum_threshold - default 50) or reaches threshold table will be considered for autovacuum by the daemon.
I have not enabled this (autovacuum_vacuum_threshold is not assigned), so I am assuming its taking 50 by default. And I am sure atleast 200 oids being modified in my table. But I could not see any autovuum logs related to pg_largeobject table.
What would be the problem? Is there anyway to solve this problem?
Update 1:
mydb=# select * from pg_stat_all_tables where relname='pg_largeobject' and schemaname='pg_catalog';
-[ RECORD 1 ]-----+---------------
relid | 2613
schemaname | pg_catalog
relname | pg_largeobject
seq_scan | 0
seq_tup_read | 0
idx_scan | 38012971
idx_tup_fetch | 51509025
n_tup_ins | 11520615
n_tup_upd | 0
n_tup_del | 8569404
n_tup_hot_upd | 0
n_live_tup | 5453142
n_dead_tup | 6774593
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
