[Chart showing throughput progression from 3.9 TiB/hour to 35+ TiB/hour when involving more processes]

3/FIN

Modern hardware + pgBackRest trick = game changer for zero-downtime operations at scale

Credit: Maxim Boguk, Postgres expert I've known for years, who now helps the @postgres_ai team excel with really large systems

2/

So we're building automated workflows that actually execute (via PRs/MRs with approvals):

1) Unused and redundant indexes → DROP INDEX CONCURRENTLY

2) Bloated indexes → REINDEX CONCURRENTLY (fully automated, with prior autovacuum tuning)

Launching very soon.

2/FIN

created telegram group for everyone who is hacking postgres source code or considers to start it: t.me/postgres_hac...

Key points to remember:
1. Heavyweight locks = same as just "locks"
- contention → fix your SQL/schema
- held until COMMIT/ROLLBACK
2. Lightweight locks = LWLocks:
- contention on them → lack of resources, misconfiguration, suboptimal workload patterns, or Postgres internal limitations

- we can observe pending and successful lock acquisition attempts using pg_locks (and joining it with pg_stat_activity on pid)

Lock Manager is a core component of Postgres responsible for managing heavyweight locks.

10/

- users can explicitly initiate a lock acquisition attempt
- attempts to acquire locks form a queue
- there are many types of locks and sophisticated rules of conflicts between them (see www.postgresql.org/docs/current...)

9/

- once acquired, they are held until the very end of transaction; IMPORTANT: acquired heavyweight locks can be released only when COMMIT or ROLLBACK happens, never before
- there is a deadlock detection mechanism – this is one of the jobs of Lock Manager

8/