Pglocks.org

80•hnasr•10mo ago

Comments

whilenot-dev•10mo ago

I'm a bit lost here.

Locking is a challenging problem in complex systems. Is this list to be interpreted as a "TODO: get rid of locking conflicts in future releases" or more a "NOTE: be aware there are known conflicts that will not change - find ways to work around them"?

EDIT: Also, is the creation of this list an automated or a manual effort?

tux3•10mo ago

I think this is intended as educational material, not a list of things to fix.

The locks are here by necessity, it is not so easy at all to get rid of them. And even in special cases where it is possible, the complexity you have to introduce is not to be taken lightly...

If even a tenth of these disapppeared, it would be incredible, in a very surprising way.

atombender•10mo ago

The creator looks like a developer and teacher, not a Postgres core team member. So I assume this is for documentation purposes.

I actually like this a lot, as there isn't a single place in the Postgres documentation that lists all the possible locks; it's spread out all over. Having a quick reference for what kinds of commands you'd be blocking with your transaction is valuable.

It's pretty evident that the pages have been programmatically generated, but I'd love know what it's generated from. I think you can derive this information from the documentation, but not sure if you can do it in an automated way without an LLM.

braiamp•10mo ago

> there isn't a single place in the Postgres documentation that lists all the possible locks

Did you read this page? https://www.postgresql.org/docs/current/explicit-locking.htm...

atombender•10mo ago

That's a great page, but it has several issues.

First, it isn't complete; as I said, the locking behaviour is spread out all over the Postgres documentation. For example, that page doesn't list what locks DROP INDEX takes. To find that out, you have to go to the documentation page for that command and read it carefully. In fact, really carefully — the locking behaviour is only documented under the section about CONCURRENTLY.

The page also doesn't list what possible commands are then blocked. Locks interact in subtle (and incorrectly named!) ways that are explained in the tables on that page ("Conflicting lock modes"), so to understand if something will block something else you have to look at the two commands you are curious about and then look at how their locks interact.

gulcin_xata•10mo ago

I agree, it is not so straightforward to find out.

braiamp•10mo ago

These are database locks, which means that depending which arrives first, the later transaction has to wait till the first one finishes to complete. These locks are about SQL commands and which commands can run concurrently with the others. There's a graph here of how that looks like https://pankrat.github.io/2015/django-migrations-without-dow...

Usually for maximum performance (minimum latency, maximum throughput) you want to have operations not lock each other, unless absolutely necessary, in which case you want them to be short.

whilenot-dev•10mo ago

You make it sound like the conflict is just affecting performance and won't result in a deadlock. So it's for performance aware postgres clients/users, and not for postgres developers?

andyferris•10mo ago

It is a guide for developers using postgres as a client, who need to write systems that don't deadlock, are performant and are correct. These are the (rather sharp) tools that postgres provides for doing so (or else you can use e.g. serializable isolation and optimistic concurrency, but in my experience that has too many false positives and bail out rather eagerly, whereas these tools let you be very precise and granular).

mebcitto•10mo ago

Other relevant talks/blogs that I found really useful for understanding Postgres locks are:

* Unlocking the Postgres Lock Manager by Bruce Momjian: https://momjian.us/main/writings/pgsql/locking.pdf

* Anatomy of table-level locks by Gulcin Yildirim Jelinek: https://xata.io/blog/anatomy-of-locks

pasxizeis•10mo ago

Shameless plug: I wrote a tool[1] that executes a given migration against a test database (e.g. in your CI) and reports back what locks it acquired.

The rationale being to have a "lock diagnostics report" commented in your PR's migration file.

It's a prototype and has a few rough edges and missing functionality, but feedback is more than welcome.

[1] https://github.com/agis/pglockanalyze

jononor•10mo ago

Very practical! Locking is one of the things that can really bite when doing migrations.

LiteLLM Python package compromised by supply-chain attack

Microsoft's "Fix" for Windows 11: Flowers After the Beating

I Quit Editing Photos

So where are all the AI apps?

Debunking Zswap and Zram Myths

curl > /dev/sda: How I made a Linux distro that runs wget | dd

Ripgrep is faster than grep, ag, git grep, ucg, pt, sift (2016)

Opera: Rewind The Web to 1996 (Opera at 30)

Secure Domain Name System (DNS) Deployment 2026 Guide [pdf]

Nanobrew: The fastest macOS package manager compatible with brew

Box of Secrets: Discreetly modding an apartment intercom to work with Apple Home

Missile Defense Is NP-Complete

Log File Viewer for the Terminal

NanoClaw Adopts OneCLI Agent Vault

MSA: Memory Sparse Attention

iPhone 17 Pro Demonstrated Running a 400B LLM

Autoresearch on an old research idea

No-build, no-NPM, SSR-first JavaScript framework if you hate React, love HTML

The Jellies That Evolved a Different Way to Keep Time

BIO – The Bao I/O Co-Processor

LLM Neuroanatomy II: Modern LLM Hacking and Hints of a Universal Language?

A 6502 disassembler with a TUI: A modern take on Regenerator

FCC updates covered list to include foreign-made consumer routers

Show HN: Cq – Stack Overflow for AI coding agents

Dune3d: A parametric 3D CAD application

Claude Code Cheat Sheet

Microservices and the First Law of Distributed Objects (2014)

Finding all regex matches has always been O(n²)

IRIX 3dfx Voodoo driver and glide2x IRIX port

The Resolv hack: How one compromised key printed $23M