Thread: page locking? too many btree indexes...
Can too many btree indexes cause page level locking?
I read this…
http://www.postgresql.org/docs/7.4/static/locking-indexes.html
The concern is the exclusive page-level locking that occurs on inserts to the index.
I am experiencing locking related on two tables. Each has several indexes on it (4 or more). One table is frequently updated (20%), occasional inserts(10%), and many reads (70%) and the other has many inserts and reads every 20 secs.
The largest table is with many inserts currently has 2.5 million records and has inserts of about 200 per second. The read is every 20 seconds.
Thanks,
Brian
On Tue, Oct 26, 2004 at 11:44:42AM -0400, Brian Maguire wrote:
> Can too many btree indexes cause page level locking?
Yes, too many btree indexes can, as can a single btree index.
> I am experiencing locking related on two tables. Each has several
> indexes on it (4 or more). One table is frequently updated (20%),
> occasional inserts(10%), and many reads (70%) and the other has many
> inserts and reads every 20 secs.
Most likely, your problem is not related to the indexes. Yes, there is
page-level exclusive locking on the indexes when there's insert or
delete operations going on, but they don't cause deadlocks. The likely
cause of your problem is foreign key relationships. Those are
implemented using row-level exclusive locking, and they can (and often
do) cause deadlocks.
Do you have any foreign keys defined?
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)