
PostgreSQL Locking Revealed - seaghost
http://blog.nordeus.com/dev-ops/postgresql-locking-revealed.htm
======
danmaz74
Tangential: does anybody know how dependable is pg_repack[1]? I just
discovered it and the promise is great - being able to do a VACUUM FULL, and
even move tables around, without locking them and thus without downtime - but
I'm not sure what are the risks in using it in production.

[1] [http://reorg.github.io/pg_repack/](http://reorg.github.io/pg_repack/)

~~~
cldellow
We use it on medium size tables (around ~100M rows and ~100GB each). No
problems. The perf boost we get from clustering rows in the right spot on the
disk is huge.

~~~
e12e
Out of curiosity, as you apparently have some real data and real use-cases -
have you benchmarked SSD vs disk for your use-case? In particular, do you
still see a (comparable, or not) speed-up from better clustering of rows on
SSD? (I would guess not, unless you have a lot of fragmentation at less than
SSD write-size (typically 4k?) - and AFAIK postgres should already be trying
to fix that for you, by how it lays out data on disk. But SSD do have somewhat
better sequential read than random-read, even if not as dramatic as spinning
disks).

~~~
cldellow
There's still a speedup, but it's dwarfed by the magnetic->SSD boost.

We do have fragmentation within a block. Our records on the heap are about 150
bytes, so maybe 30 rows per 4K page. In the pathological case where there is 1
row per page due to fragmentation, queries have to fetch 30x the number of
blocks, which is a killer on a spinny disk.

~~~
codinghorror
People still use spinning rust for databases?

~~~
dikaiosune
I have to deal with a couple of plattered databases. It's not optimal, but
there's usually some cost constraint, or there are only 100 users on it a
week. Or both, in which case it's no big deal and it's very very cheap.

------
andrewchambers
I love Datomic because of the way it removes the need to understand these
locks completely. With datomic you can reason far more easily about a set of
concurrent updates than dealing with SQL locking levels.

The trade is you are essentially always running with your database as fully
serialized writes.

~~~
akurilin
Can that be achieved by setting all writes in postgres to isolation level
serializable?

~~~
andrewchambers
Not in the same way. Because the datomic database is immutable queries can
take as long as they want and the results will never be inconsistent.

~~~
lucian1900
Postgres transactions can also take as long as they want and the results will
never be inconsistent.

The downside to SERIALIZABLE is that conflicts become visible failures to the
client, so you have to handle them and retry. Datomic solves this by
serialising through the function you pass, but at great latency cost.

~~~
akurilin
No free lunch.

------
akurilin
These locks can be non-trivial to reason about at times. On 9.3 I've been
bitten numerous times by concurrent transactions attempting to escalate share
locks to exclusive lock because of a FK and getting stuck forever, without the
db being able to break up that deadlock. If you intentionally limit the # of
connections to the db (which you should), that can lead to downtime. Needed to
learn about SELECT FOR UPDATE pretty quickly after that. It seems like that's
somewhat ameliorated in 9.4+, although it's still possible in a few corner
cases, if I recall correctly from talking to RhodiumToad.

~~~
lobster_johnson
I believe that if you set the foreign key constraint as deferred, the lock
won't be attempted until commit time (and then only briefly), which should
help a lot -- assuming your problem is with long-running transactions that
hold locks while they are open.

------
Animats
I notice they mention advisory locks, which aren't used much. MySQL has them,
too. They're useful for such things as preventing two copies of the same
application from running. This works across machine boundaries, and if you
lose the database connection or the client machine or the host machine, the
lock is released, so you don't have all that nonsense about Linux/UNIX lock
files hanging around.

I use those for tasks that should only have one copy running on a cluster, but
any machine in the cluster can run.

------
postila
There is also interesting new module pg_stat_wait that enables LWLocks
monitoring (as well as other types of locks), it is already used in
mail.yandex.ru (the 2nd largest email service in Russia). However the module
requires Postgres be patched
[https://github.com/postgrespro/postgres/tree/waits_monitorin...](https://github.com/postgrespro/postgres/tree/waits_monitoring_94)
Related discussion in -hackers: [http://www.postgresql.org/message-
id/flat/3F71DA37-A17B-4961...](http://www.postgresql.org/message-
id/flat/3F71DA37-A17B-4961-9908-016E6323E612@postgrespro.ru#3F71DA37-A17B-4961-9908-016E6323E612@postgrespro.ru)

------
kngl
There is also pgrowlocks[1] that can be usefull.

[1]
[http://www.postgresql.org/docs/current/static/pgrowlocks.htm...](http://www.postgresql.org/docs/current/static/pgrowlocks.html)

------
nierman
note: there are also "skip locked" and "nowait" clauses in Postgres 9.5 for
more flexibility when dealing with row level locking. This gives you the
option of failing immediately (nowait) or just getting back the set of rows
that aren't locked (skip locked).

[https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9....](https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#SKIP_LOCKED)

------
DrScump
I could swear that I saw Stonebreaker comment on this very subject in a talk
from the past year or so (available on Youtube).

