PostgreSQL lock mode

Question

From this document http://www.postgresql.org/docs/current/static/explicit-locking.html

I knew that PostgreSQL provides various locking modes to control concurrent access to data in tables.

My problem is that I will have many sessions accessing my database, but I am embarrassed if I made 1 large table with 40 columns or many tables with fewer columns (one-to-one relationship).

• Because when I select the data, I will select it all ---> it takes more time when I select from many tables using INNER JOIN, but it takes less time to select from one large table. This way my php will respond more slowly if I use a lot of tables.

• But when I use only one table, meanwhile many sessions will update my data in the table, I am afraid of deadlocks or delays, because the UPDATE, DELETE and INSERT commands acquire the ROW EXCLUSIVE locking mode in the target table. In general, this lock mode will be received by any command that modifies the data in the table.

Can anyone suggest which one works best? One big table or many tables?

Answer 1

It is true that INSERT , UPDATE or DELETE must receive a ROW EXCLUSIVE table lock to update.

However, this lock does not interfere with the normal operation of SELECT . SELECT only requires an ACCESS SHARE lock. This lock is compatible with ROW EXCLUSIVE - in other words, you can do a SELECT perfectly, while other data is updated using INSERT , UPDATE or DELETE if you have not acquired any explicit locks.

In other words, you will never see any deadlocks using the second approach (just don't use SELECT FOR UPDATE , and everything will be fine).

See more in the PostgreSQL Documentation .