Skip to main content

#PostgresMarathon 2-002: Relation-level locks

· 5 min read
Nikolay Samokhvalov
CEO & Founder

Let's talk about relation-level locks and various confusions, surprises and what is worth to remember in practice.

The key page in Postgres docs describing relation-level locks is here: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES[1]

This page in the docs is called "13.3. Explicit Locking" and it might cause confusion because it talks about implicit locking (e.g., if you run a DML or DDL, locks are applied implicitly; while if you execute LOCK or SELECT .. FOR UPDATE, you explicitly request locks to be acquired). However, this might be just my own terminology bias.

This page has a useful "Table 13.2. Conflicting Lock Modes" that can help understand how a lock acquisition can be blocked by another, already acquired or pending (!) lock:

Table 13.2: Conflicting lock modes in Postgres

A possible confusion here is the word "row" used in some lock modes – we shouldn't think that those modes are row-level. They are still relation-level. There is a special concept of row-level locks, we'll dive into that separately. This confusion is covered in the docs though:

Remember that all of these lock modes are table-level locks, even if the name contains the word "row"; the names of the lock modes are historical.

Back to the table above, there is a very useful transformation of it in old blog post by Marco Slot, "PostgreSQL rocks, except when it blocks: Understanding locks" (2018, https://citusdata.com/blog/2018/02/15/when-postgresql-blocks/[2]) -- it's incomplete, but speaks common operations and can be used as quick reference:

Lock conflicts for common Postgres operations

Next, another terminology confusion might come from reading the section, "13.3.1. Table-Level Locks". Let's not be confused, it talks not only about tables, but rather relations.

In Postgres, the word "relation" can be applied to many objects, we can see it looking at the docs for pg_class.relkind (https://postgresql.org/docs/current/catalog-pg-class.html[3]):

r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index

Links

  1. [1] https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES
  2. [2] https://citusdata.com/blog/2018/02/15/when-postgresql-blocks/
  3. [3] https://postgresql.org/docs/current/catalog-pg-class.html

#PostgresMarathon 2-001: Lightweight and heavyweight locks

· 2 min read
Nikolay Samokhvalov
CEO & Founder

To warm up, let's talk about lightweight and heavyweight locks (or "regular locks" or just "locks").

I'm using these materials:

Links

  1. [1] https://www.postgresql.org/docs/current/explicit-locking.html
  2. [2] https://github.com/postgres/postgres/blob/master/src/backend/storage/lmgr/README

Self-driving Postgres

· 5 min read
Nikolay Samokhvalov
CEO & Founder

Historical note (October 2026): This July 2025 announcement is preserved as history; our current approach[1] is Postgres trouble fixed with evidence, changes tested on a copy of your data, and owner approval before production.

I'm excited to announce that Postgres AI has started work on a new project – open-source Self-Driving Postgres (SDP).

In the AI era, Postgres is the natural choice for AI builders. With fast-growing database clusters, the highest level of automation is essential. AI-driven growth demands efficient, proactive, and intelligent database management. Our goal is to reduce manual interventions as much as possible to achieve the highest level of operational efficiency and reliability.

Links

  1. [1] current approach https://postgres.ai/

Introducing Postgres how-tos: practical guides for real-world database challenges

· 3 min read
Nikolay Samokhvalov
CEO & Founder

Today, as part of our Launch Week (Day 4), we're excited to announce the release of our Postgres how-to guides collection[1] – a comprehensive set of dozens of practical guides covering real-world PostgreSQL challenges.

Links

  1. [1] Postgres how-to guides collection https://postgres.ai/docs/postgres-howtos

Full-stack preview environments with DBLab 4.0: isolated databases for every pull request

· 6 min read
Bogdan Tsechoev
Software Engineer

Preview environments are temporary deployment environments created for each pull request, offering major advantages over traditional shared staging environments. While platforms like Vercel (paid) and Coolify (open-source) solve application deployment, the database remains the bottleneck. Teams typically compromise: sharing one database (causing conflicts), deploying small test databases (lacking realistic data), or cloning large production databases (taking hours and costing heavily). DBLab 4.0's database branching solves this with O(1) economics[1], spinning up isolated production-scale Postgres clones in seconds for cost-effective full-stack previews.

Links

  1. [1] O(1) economics https://postgres.ai/blog/20250721-dblab-engine-4-0-released

Postgres AI Checkup service: expert-led, AI-assisted comprehensive database health assessment

· 5 min read
Bogdan Tsechoev
Software Engineer
Nikolay Samokhvalov
CEO & Founder

This is Day 3 of Postgres AI launch week[1]

It starts innocently enough.

You choose Postgres – solid, reliable, battle-tested. You pick a managed service like RDS or CloudSQL. They handle backups, high availability, disaster recovery. You can focus on building your product. Life is good.

Your startup grows. Users love what you've built. Data accumulates – gigabytes become terabytes. Traffic surges – hundreds of requests become thousands per second.

Then one day, everything changes.

Queries that ran in milliseconds now take seconds. Connection pools max out during peak hours. Replication lag appears out of nowhere. Your perfectly fine database suddenly isn't fine at all.

You reach out to your managed service support. The response? A generic checklist. "Have you tried increasing your instance size?" Days pass. Your users complain. Your team scrambles. The support ticket remains open, unhelpful.

This is when you realize: nobody cares about your database health as much as you do.

This is exactly when it's time to engage Postgres AI.

For 5+ years, the Postgres AI team has been rescuing companies from exactly this situation. Our clients include GitLab, Miro, Chewy, Midjourney, ClickUp, Photoroom, Gamma, Suno, Supabase – they faced a lot of database challenges during hypergrowth.

Our approach: comprehensive health checks using a methodology refined over hundreds of engagements. We call it Postgres AI Checkup – it identifies current issues and predicts future ones before they cripple your business.

Last year alone, we conducted 30+ deep checkups for 20+ companies. Today, we're launching this as a scalable service, enhanced with AI automation while keeping human experts at the core.

Start your first checkup: Console.Postgres.ai[2]

Links

  1. [1] Postgres AI launch week https://postgres.ai/launch-week
  2. [2] Console.Postgres.ai https://console.postgres.ai/

postgres_ai monitoring – expert-level Postgres monitoring tool for humans and AI

· 7 min read
Dmitry Fomin
Sr. Postgres engineer
Dementii Priadko
Postgres engineer

Today we're releasing postgres_ai monitoring v0.7[1], an open-source monitoring solution built specifically for Postgres experts who need rapid root cause analysis and deep performance insights. This isn't a tool for beginners—it's designed for experienced DBAs and SREs who need to understand complex performance issues in minutes, not hours.

Links

  1. [1] postgres_ai monitoring v0.7 https://gitlab.com/postgres-ai/postgres_ai

DBLab 4.0: instant database branching with O(1) economics

· 7 min read
Bogdan Tsechoev
Software Engineer

The cost of experimentation determines the pace of innovation. In database development, this cost has traditionally been measured in hours and thousands of dollars per environment. DBLab Engine 4.0 changes this equation fundamentally with instant database branching.

New version delivers comprehensive database branching for Postgres with unique set of characteristics:

  • Git-like semantics: branches are named pointers to snapshots
  • O(1) scaling for both storage and compute costs
  • True open source (Apache 2.0 license)

AI-assisted Postgres experiment: number of partitions vs. planning time

· 9 min read
Nikolay Samokhvalov

In one of the recent PostgresFM[1] episodes, Michael Christofides and Nikolay discussed planning time and what can affect it. One of the obvious negative factors we've discussed is the large number of partitions a partition table might have.

In this post, we're using our Postgres.AI assistant[2] to see how planning time depends on the number of partitions.

This is the very first blog post of its kind: it has an integrated AI that you can use to explore the topic discussed here further, to repeat the experiment, alter it in any direction, and study the Postgres behavior more effectively.

Links

  1. [1] PostgresFM https://postgres.fm/
  2. [2] Postgres.AI assistant https://postgres.ai/blog/20240127-postgres-ai-bot