PostgreSQL locking mode

1
votes

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

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

My problem is I have many sessions will accessing my DB , but I'm confuse should I made 1 big table with 40 column or many tables with fewer column (one to one relationship).

  1. Because when I select the data I will select all of it ---> it takes more time when I select from many tables using INNER JOIN, but it takes less time to select from 1 big table. So it will my php respond slower if I'm using many tables.

  2. But when I use just one table meanwhile many session will update my data in the table, I'm afraid of deadlocks or delay because commands UPDATE, DELETE, and INSERT acquire ROW EXCLUSIVE lock mode on the target table. In general, this lock mode will be acquired by any command that modifies data in a table.

Could anyone suggested which is the best approach should I made? One big table or many tables?

1
postgresqllockingrelational-database
Your question for one big table or many tables is impossible to answer without knowing what you are storing in it. Standard advice is to normalize your schema bkent.net/Doc/simple5.htm - Eelke
I stored device information for example id, name, public ip, private ip, gateway, mask, external port, internal port, etc.. I'm confused should I made into one big table with many column or split it into many tables, for exp: dev_info, dev_ip, dev_port - user430926
To solve your "slow query", please read this wiki.postgresql.org/wiki/SlowQueryQuestions and update your question accordingly. - a_horse_with_no_name

1 Answers

8
votes

It is true that INSERT, UPDATE or DELETE must acquire ROW EXCLUSIVE lock on table to be updated.

However, this lock does not prevent SELECT from working normally. SELECT only requires ACCESS SHARE lock. This lock is compatible with ROW EXCLUSIVE - in other words, you can perfectly execute SELECT while other data is updated by INSERT, UPDATE or DELETE, as long as you don't acquire any explicit locks.

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

Read more in PostgreSQL documentation.