Right, let’s talk about the big, blunt instruments of the locking world: table-level locks. While row-level locks are like a surgeon’s scalpel, table locks are the sledgehammer you use when you need to block everyone from touching a table, or when you’re about to do something so drastic to the table’s very structure that you can’t have anyone peeking in.

The first thing you need to understand is that these locks aren’t some abstract concept; they are very real modes that the database engine applies, and they have a strict hierarchy of who blocks whom. The main modes you’ll encounter are:

  • ACCESS SHARE (ACCESS SHARE): The most permissive lock. It’s acquired by SELECT statements. Its only purpose in life is to block someone else who tries to acquire an ACCESS EXCLUSIVE lock. It’s like a polite “hey, someone’s reading here!” sign.
  • ROW SHARE (ROW SHARE): Acquired by SELECT ... FOR UPDATE and SELECT ... FOR SHARE. It conflicts with exclusive and access exclusive locks.
  • ROW EXCLUSIVE (ROW EXCLUSIVE): This is the lock you get anytime you modify data with INSERT, UPDATE, or DELETE. It’s called “row exclusive” but it’s a table-level lock—fun, right? It allows other writers to also acquire ROW EXCLUSIVE locks on the same table (because the real row-level locking happens to avoid conflicts), but it blocks exclusive writers.
  • SHARE UPDATE EXCLUSIVE (SHARE UPDATE EXCLUSIVE): A bit of a weird one. It’s acquired by VACUUM (without FULL), CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE. It blocks other SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, and EXCLUSIVE locks. Think of it as the “I’m doing maintenance, please don’t start other maintenance or try to change the schema” lock.
  • SHARE (SHARE): Acquired by CREATE INDEX (without the CONCURRENTLY option). This mode allows reads but blocks all writes. It’s the “freeze all data, but you can still look at it” lock.
  • SHARE ROW EXCLUSIVE (SHARE ROW EXCLUSIVE): Similar to SHARE but also blocks other SHARE ROW EXCLUSIVE and SHARE locks. It’s like a slightly more exclusive club.
  • EXCLUSIVE (EXCLUSIVE): This lock blocks all other EXCLUSIVE locks and any operation that would write to the table (ROW EXCLUSIVE), but it strangely allows ACCESS SHARE (so reads can still happen). You rarely see this used explicitly in SQL; it’s more of an internal lock.
  • ACCESS EXCLUSIVE (ACCESS EXCLUSIVE): The Big One. The King of Lock Mountain. This lock blocks every single operation, including simple SELECT statements. It’s the nuclear option.

When DDL Commands Bring the Hammer

This is where it gets crucial. Most Data Definition Language (DDL) commands—the ones that change the table’s structure—need that ACCESS EXCLUSIVE lock. They have to. You can’t have someone reading a column while you’re in the middle of dropping it from underneath them. The transaction would be a logical nightmare.

So, what commands demand this ultimate lock?

  • DROP TABLE (obviously)
  • TRUNCATE
  • ALTER TABLE (most variants, but not all—this is important)
  • VACUUM FULL
  • CLUSTER
  • REINDEX TABLE
  • CREATE INDEX (without CONCURRENTLY)

Here’s the kicker, and a very common pitfall: an ALTER TABLE ADD COLUMN command in modern PostgreSQL (v11+) is a beautiful exception. It’s now a metadata-only operation that acquires only a SHARE UPDATE EXCLUSIVE lock, meaning it can run alongside nearly all other activity. But if you ALTER TABLE ALTER COLUMN TYPE to change a data type? Boom, ACCESS EXCLUSIVE lock. The database has to rewrite every row to conform to the new type, so it has to block everyone.

-- This will be fast and barely block anyone (PostgreSQL 11+):
ALTER TABLE users ADD COLUMN IF NOT EXISTS middle_name text;

-- This will halt your entire application until it completes:
ALTER TABLE users ALTER COLUMN id TYPE bigint;

The ALTER TABLE Trap and How to Avoid It

The single biggest source of production outages I’ve seen related to locking is someone running a “quick” ALTER TABLE on a large table without understanding the lock mode it requires. You think it’ll be a millisecond operation, but on a busy table, your command might sit in a queue waiting for every single existing SELECT (which holds ACCESS SHARE) to finish. Then it has to wait for all writes. Your “quick” change now has a 30-minute outage attached to it.

Best practice: Before running any DDL, especially on a critical table, check the documentation to see what lock level it requires. Test it on a staging environment that simulates production load. For long-running operations that require ACCESS EXCLUSIVE, you must schedule a maintenance window.

For adding indexes, always prefer CREATE INDEX CONCURRENTLY. It’s a lifesaver. It uses a much weaker lock (SHARE UPDATE EXCLUSIVE) and builds the index in the background without blocking writes. It’s slower and consumes more resources, but it keeps your application online.

-- This will block writes for the duration of the index build:
CREATE INDEX idx_users_email ON users(email);

-- Do this instead. It's non-blocking:
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

The moral of the story? Respect the lock modes. Know what your commands are doing under the hood. The database isn’t being difficult; it’s enforcing consistency, and it’s your job to work with it, not against it. Plan your DDL like you’d plan a deployment: with care, testing, and an understanding of the impact.