DBLab 4.0 made database branching instant. 4.1 added the controls around it — protection leases, Teleport, metrics. 4.2 closes the last item on 4.1's own roadmap — testing a Postgres major version upgrade on a clone — and removes the two remaining points of friction around it: getting configured in the first place, and cleaning up what accumulates afterwards.

DBLab 4.0 introduced instant database branching with O(1) economics. With 4.1, we're making it safe to hand off to a platform team: automatic resource governance, enterprise access control, production-safe data refresh, and native observability.

Postgres 18 can preserve planner statistics during major version upgrades. But can it work when upgrading from an older version to PG18 — which many plan to do soon? Let's test and see.

One of these queries is 32x faster than the other. Which one, and why?

select pt.*
from post_tags pt
where pt.tag_id = any($1)
and exists (
    select
    from posts
    where posts.post_id = pt.post_id
      and not deleted
);

select pt.*
from post_tags pt
where pt.tag_id = any($1)
and not exists (
    select
    from posts
    where posts.post_id = pt.post_id
      and deleted
);

These two queries are logically equivalent here because posts.post_id is a primary key — the subquery matches at most one row, so AND NOT deleted vs. AND deleted simply flips a boolean. A foreign key on post_tags.post_id guarantees no orphans.

Read on to find out. This pattern was first spotted by Maxim Boguk on a 400 GiB production table, where the speedup was approximately 50x.

When CREATE INDEX CONCURRENTLY or REINDEX INDEX CONCURRENTLY fails, Postgres leaves behind an invalid index. Many engineers assume these indexes are harmless placeholders waiting to be cleaned up. After all, the planner won't use them for queries, right?

Wrong. Invalid indexes are far from harmless. They continue to consume resources, generate I/O, block optimizations, and even cause lock contention — all while providing zero query performance benefit.

In this article, we'll demonstrate — with real evidence — the hidden costs of invalid indexes that every Postgres administrator should understand.

Your API is slowing down. You check your database and find 42 indexes on your users table. Which ones can you safely drop? How much performance are they costing you? Let's look at what actually happens in Postgres when you have too many indexes.

If you're a backend or full-stack engineer, you probably don't want to become an indexing expert — you just want your API fast and stable, without babysitting pg_stat_user_indexes.

Index maintenance includes multiple activities: dropping unused indexes, dropping redundant indexes, and rebuilding indexes on a regular basis to get rid of index bloat (and of course, keeping autovacuum well tuned).

There are many reasons why we need to keep our index set lean, and some of them are tricky.

Suppose you need to create a replica for a 1 TiB database. You have a fast server with NVMe storage and 75 Gbps network, but pg_basebackup typically delivers only 300-500 MiB/s due to its single-threaded architecture — regardless of how powerful your hardware is (though PG18 brings a surprise we'll discuss later).

The solution: replace pg_basebackup with pgBackRest and leverage parallel processing to achieve significantly faster replica creation, saturating (≈97% of) line rate on a 75 Gbps link.

Note: This is an R&D-style exploration focused on performance benchmarking on idle systems, not a production-ready automation guide. Many considerations important for production environments (monitoring, retry logic, integration with orchestration tools, etc.) are intentionally omitted to focus on the core performance characteristics.

In #PostgresMarathon 2-009 and #PostgresMarathon 2-010, we explored why execution 6 causes a lock explosion when building a generic plan for partitioned tables — the planner must lock all 52 relations because it can't prune without parameter values.

Today we'll test what actually happens with different plan_cache_mode settings.

In #PostgresMarathon 2-009, we focused on Lock Manager's behavior when dealing with prepared statements and partitioned tables.

And observed a lock explosion in our simple synthetic example: from 8 locks (custom plans) during first 5 calls, to 52 locks (building generic plan) in the 6th call, to 13 locks (using cached generic plan) in the 7th and subsequent calls. We left with questions:

• this lock explosion at the 6th call – why is it so exactly and can it be avoided?
• why do we lock all 12 partitions even though runtime pruning removes 11 of them?

Let's dig deeper.

In #PostgresMarathon 2-008, we discovered that prepared statements can dramatically reduce LWLock:LockManager contention by switching from planner locks (which lock everything) to executor locks (which lock only what's actually used). Starting with execution 7, we saw locks drop from 6 (table + 5 indexes) to just 1 (table only).

There we tested only a simple, unpartitioned table. What happens if the table is partitioned?