Buddha Bart is right. I wasn't clear enough in what I said.
By saying you'd have to write your own locking mechanism, I was targeting that towards achieving a row level locking type system. Postgres and Mysql don't support row level locking. In the case of the last client I worked on w/ Postgres...there were 40 users spread across four different floors. Two of the three had change permissions-----once the database grew to a substantial size----the developers there before me experienced tremendous lag on the DB. After doing some experimentation--they realized the lag was based on two things. One----improper database design and two----the default table locking mechanism w/i postgres was slow as a one legged grandma----even if you were changing one record in a 300,000 record table---the whole table was locked until your change was complete.
They ended up moving to a table-lock-control----if a user wanted to change a record----the "custom" lock table was first checked to see if someone else was trying to change it. Although this didn't increase speed in the thousandth percent range----it did noticably improve the speed----and write collisions's were intercepted by custom code instead of letting Postgres handle the default lock.
By saying you'd have to write your own locking mechanism, I was targeting that towards achieving a row level locking type system. Postgres and Mysql don't support row level locking. In the case of the last client I worked on w/ Postgres...there were 40 users spread across four different floors. Two of the three had change permissions-----once the database grew to a substantial size----the developers there before me experienced tremendous lag on the DB. After doing some experimentation--they realized the lag was based on two things. One----improper database design and two----the default table locking mechanism w/i postgres was slow as a one legged grandma----even if you were changing one record in a 300,000 record table---the whole table was locked until your change was complete.
They ended up moving to a table-lock-control----if a user wanted to change a record----the "custom" lock table was first checked to see if someone else was trying to change it. Although this didn't increase speed in the thousandth percent range----it did noticably improve the speed----and write collisions's were intercepted by custom code instead of letting Postgres handle the default lock.
