I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with a very surprising conclusion: it looks like SELECTs are blocking SELECTs.
For example, the second report shows that this query:
SELECT infovalue from "info-field" where infotype ='xxxx' AND clientidnum = 146087240
is blocked by this query:
SELECT infotype FROM "info-field" WHERE infotype = 'yyyy' and refidnum = 0 LIMIT 1
And, even more surprising, that two instances of a single query are blocking each other:
SELECT "info-field".* FROM "info-field" where "info-field".infotype = 'www' and "info-field".infowho = 210745140 order by infoid desc LIMIT 1
SELECT "info-field".* FROM "info-field" where "info-field".infotype = 'www' and "info-field".infowho = 229912040 order by infoid desc LIMIT 1
The high-level problem I'm looking into is that there are occasional bursts of activity where there are dozens of postgres processes spinning in sys time (so my guess is lock spinning, hence the investigation into locks).
My first issue is that I'm surprised by this apparent read-blocking-read problem. This is PostgreSQL 9.3.5. What could cause something like this?
The second is that the number of active processes from pg_stat_activity is ~~60, while the number of rows in the second report is ~~1900! How could such difference happen? There are no INSERT or UPDATE queries going on with the tables involved in SELECTs, and there are ~~5 INSERT or UPDATES queries running total in the reports.
Table details (on the related columns):
infotype text, infowho integer, clientidnum integer, refidnum integer, infoid integer serial primary key
(refidnum, infotype), (clientidnum, infotype), (infowho, infotype), (infoid), partial index on (refidnum, xfield, infotype) where xfield in (1,2,3)