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
What would be the problem? Is there anyway to solve this problem?
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