2

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 ?

  • 1
    First enable log_autovacuum_min_duration to get a sense of what's going on. – Daniel Vérité Feb 28 '17 at 13:28
  • 1
    i already know that autovacuum takes really long, an hour or an hour and a half. What is the help of log_autovacuum_min_duration ? – ArtemP Feb 28 '17 at 13:47
  • 1
    Your query of pg_locks does not show any page locks being held. What are the locks the blocked processes are blocking on? – jjanes Feb 28 '17 at 16:03
  • there is a lot of queries that waiting on page lock, i just didnt paste their result from pg_stat_activity or from pg_locks. Believe me, there is a LOT of queries waiting by 30 min and above. And it's just an update of single row wainting so much. Astonishing. – ArtemP Feb 28 '17 at 16:39
  • Do those autovacuum processes perform an anti-wraparound vacuum? – dezso Mar 1 '17 at 10:26

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.