1

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
  • Can you add the result of select * from pg_stat_all_tables where relname='pg_largeobject' and schemaname='pg_catalog' to your question, with expanded display (\x in psql, produces one line per column) – Daniel Vérité Sep 21 '16 at 14:14
  • @Daniel, I have updated with statistics. – Spike Sep 21 '16 at 15:35
  • Any locking would hold the daemon? – Spike Sep 22 '16 at 5:23
  • you still need a VACUUM FULL: dba.stackexchange.com/questions/112637/… – maletin Sep 22 '16 at 22:57
  • @maletin, I could do. But I can not afford downtime (huge data - might take days) – Spike Sep 23 '16 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.