2

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?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
user430926
  • 4,017
  • 13
  • 53
  • 77
  • 1
    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 http://www.bkent.net/Doc/simple5.htm – Eelke Apr 09 '13 at 05:41
  • 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 Apr 09 '13 at 06:04
  • To solve your "slow query", please read this http://wiki.postgresql.org/wiki/SlowQueryQuestions and update your question accordingly. –  Apr 09 '13 at 07:13

1 Answers1

9

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.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • What if the first approach UPDATE A column, second approach also UPDATE other B column, will it wait until the first UPDATE transaction finish and then UPDATE the second one or will it be conflict? – user430926 Apr 09 '13 at 05:54
  • 2
    You should read [more about MVCC](http://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-READ-COMMITTED). TLDR: second UPDATE *may* wait for the first one if exact same row was updated. In this case normalization should help. – mvp Apr 09 '13 at 06:00
  • So ROW EXCLUSIVE lock on table to be updated but only lock the row I'm going to updated right? Not the whole table? 1st UPDATE : UPDATE mytable SET ... WHERE key = 1; – user430926 Apr 09 '13 at 06:53
  • 1
    That is correct - `UPDATE` will lock only if trying to update exact same unique key as another one is still updating. Otherwise, they will run without slowing down. – mvp Apr 09 '13 at 06:57
  • So lock on table just to prevent if other transaction wants to drop columns that I want to updated. This means that ROW EXCLUSIVE can be held by multiple transactions, right? – user430926 Apr 09 '13 at 08:55
  • Yes, but each one to their own row. It is called `ROW EXCLUSIVE` for a reason - it literally means that one row (and that row only) is exclusively locked by transaction that holds that lock. Of course, it prevents DDL operations from changing table schema while it is held. – mvp Apr 09 '13 at 09:01