Postgresql – pg_stat_activity shows lots of easy queries running for minutes


After application server start its connection pool fills quickly and application can't work properly.
I went through pg_stat_activity and noticed lots of queries running for minutes:
enter image description here

But postgres's slow query log does not show any long-running queries (i've set 100ms as minimum query time).

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2

I suggested that there are some locks on that table but SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED; shows nothing.

Structure of persistence_journal is pretty easy:

CREATE TABLE IF NOT EXISTS public.persistence_journal (
  persistence_id VARCHAR(255) NOT NULL,
  sequence_number BIGINT NOT NULL,
  created BIGINT NOT NULL,
  message BYTEA NOT NULL,
  PRIMARY KEY(persistence_id, sequence_number)

persistence_journal contains 1 678 121 rows with ~37 000 unique persistence_ids with average 44 rows per persistence_id.

Running SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

active  1
idle    43
idle in transaction 45

This "transaction" contains two pretty simple queries:

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2
select max("deleted_to") from "persistence_deleted_to" where "persistence_id" = $1

persistence_deleted_to contains zero rows.

System's top shows almost zero CPU load.

PostgreSQL 9.4, Ubuntu 14.04

Best Answer

Your problem are the "idle in transaction" sessions. You should fix your application to avoid that. The queries in pg_stat_activity in those cases are the last query run, not the current one (since there isn't a current one).