I have a table with following definition:
CREATE TABLE public.json_stream_packet_filter ( stream_packet_id bigint NOT NULL, filter_data jsonb, CONSTRAINT json_stream_packet_filter_pkey PRIMARY KEY (stream_packet_id) ) WITH ( OIDS=FALSE, autovacuum_enabled=true, autovacuum_vacuum_threshold=5000, toast.autovacuum_vacuum_threshold=5000 ); CREATE INDEX call_started_index ON public.json_stream_packet_filter USING btree (to_text((filter_data -> 'call'::text) ->> 'started_at'::text) COLLATE pg_catalog."default"); CREATE INDEX json_stream_packet_filter_filter_data_idx ON public.json_stream_packet_filter USING gin (filter_data jsonb_path_ops);
It absorbs a lot of updates, and spans with indices to about 6Gb, so every autovacuum takes about two hours. I thought that autovacuum doesn't block either select's, insert or updates, but in fact it is. I have a lot of updates waiting for autovacuum process with page Lock. I look into pg_locks by autovacuums pid and i saw this:
select locktype, mode, relation::regclass from pg_locks where pid = 3855 relation RowExclusiveLock json_stream_packet_filter_filter_data_idx relation RowExclusiveLock call_started_index relation RowExclusiveLock json_stream_packet_filter_pkey virtualxid ExclusiveLock relation ShareUpdateExclusiveLock json_stream_packet_filter
so my question is: how i could overcome it? How to tell autovacuum not to lock indice pages, or lock it not for so long time ?