High activity short table and locks

Hi!

We have been using postgresql since a while without problems. But now I find
we experience some slowness and the weird thing is that it seems to happen
because of a very short table (less than 200 lines), called "lockers" (see
below it's structure).

This table is accessed very often by a lot of different sessions, with
SELECT, UPDATE and DELETE statements. We keep in this table some shared
state about our application users, so we make sure they don't work together
on the same things, thus the name.

Maybe we do something really wrong. Maybe we even should not use a database
for this kind of persistence (we tried a simple file but it was much worse).
We have improved the code by merging many little SELECTs into one bigger
with all the results needed. This ridiculously short table should hold in
cache memory, I'd suppose, so why is it so slow?

Today I found something that could help me to find an answer: by running the
"locks" sql below I have above 100 lines of results, with many locks
detected.

I have added the indexes sometime ago because, to my great surprise, it did
really improve the speed. I just tried to remove them on the fly and it was
worse, *but* I had less locks.

It seems I'm a bit stuck here. I'd appreciate some help. My main general
question is "how to handle very small but hot status table that has to be
updated every 30 seconds by 100 different persons, read and updated from
many sides, and also joined with some more common tables (i.e. much larger
but less hot)"

Thanks for your help. Please find below some tech info.

Table structure

Column | Type |
Modifiers
-----------------------+--------------------------+--------------------------------------------------------
l_id | integer | not null default
nextval('lockers_l_id_seq'::regclass)
l_xref_u_id | integer |
l_type | character varying |
l_what | character varying |
l_status | character varying |
l_tech_modification | timestamp with time zone | not null default now()
l_tech_creation | timestamp with time zone | not null default now()
l_tech_deleted | boolean | not null default false
l_status_modification | timestamp with time zone |
l_comment | character varying |
l_csl | character varying |
Indexes:
"lockers_pkey" PRIMARY KEY, btree (l_id)
"lockers_l_csl_idx" btree (l_csl)
"lockers_l_type_idx" btree (l_type)
"lockers_l_what_idx" btree (l_what)
"lockers_l_xref_u_id_idx" btree (l_xref_u_id)

Lockers SQL

SELECT pg_stat_activity.datname,pg_class.relname,pg_locks.transaction,
pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename, substr(pg_stat_activity.current_query,1,20),
to_char(pg_stat_activity.query_start,'HH24:MI'),
to_char(age(now(),pg_stat_activity.query_start),'HH24:MI') AS "age",
pg_stat_activity.procpid
FROM pg_stat_activity,pg_locks LEFT OUTER JOIN pg_class ON
(pg_locks.relation = pg_class.oid)
WHERE pg_locks.pid=pg_stat_activity.procpid
ORDER BY query_start;

Sample result of lockers SQL

vf_cn2fr | lockers | 468474452 | RowExclusiveLock
| t | vf_cn2fr | UPDATE lockers SET l | 15:50 | 00:00 | 30395
vf_cn2fr | lockers | 468474458 | AccessShareLock
| t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_csl_idx | 468474458 | AccessShareLock
| t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_what_idx | 468474458 | AccessShareLock
| t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_type_idx | 468474458 | AccessShareLock
| t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_l_xref_u_id_idx | 468474458 | AccessShareLock
| t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | lockers_pkey | 468474458 | AccessShareLock
| t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | | 468474458 | ExclusiveLock
| t | vf_cn2fr | SELECT * FROM locker | 15:50 | 00:00 | 30414
vf_cn2fr | pg_locks | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid_oid_index | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | | 468474459 | ExclusiveLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class_relname_nsp_index | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_class_oid_index | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_authid_rolname_index | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_stat_activity | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database_datname_index | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008
vf_cn2fr | pg_database_oid_index | 468474459 | AccessShareLock
| t | postgres | SELECT pg_stat_activ | 15:50 | 00:00 | 18008