11 posts tagged with "performance"

Postgres 18 ships uuidv7(), which generates UUIDv7 as defined in RFC 9562. Yet many tables still have id uuid default gen_random_uuid(), which is UUIDv4: 122 random bits. Does a random primary key matter?

Barely, while the index fits in memory. Once it outgrows memory, a lot: in our runs at 100M rows, UUIDv4 inserted at 0.62 to 0.69 of the rate of an ordered key on the same server, and its index crowded the table out of shared_buffers.

WAL is a separate cost, driven less by memory than by how many index leaves each checkpoint cycle touches. At 90M to 100M rows, UUIDv4 wrote 1.5 to 1.9 times the WAL per row, while UUIDv7 kept up with int8.

I asked DBLab Forge to measure it: three tables with the same schema and the same insert stream, differing only in the primary key (UUIDv4, UUIDv7 or int8), seven runs on cloud servers, and every hypothesis written down with a falsifier before the first row. This post is the short version; the full brief has every number with its source.

Kirk Wolak and I talk about it in Postgres.FM episode 194, "UUIDv4 vs UUIDv7" (transcript included). The video shows the charts as we talk.

A checkout request was slow. The dashboard said the time went to the commit path and to disk reads. The team tried the two fixes that follow from that, and the slowest requests stayed about as slow. The cause was a lock that the dashboard ranked fifth, at 2% of the time. This post explains why a wait dashboard can be right about the time and wrong about the problem, using only what Postgres exposes today. It ends where the next post, about a tool that finds such causes, begins.

Your pg_restore finished without errors. The data is there. The indexes are in place. Is it time to switch traffic? A successful restore can still leave you with painfully slow queries—and the first read can do more than just read. What is missing from the migration runbook?

Kirk Wolak and I explain why in Postgres.FM episode 193, “Dump/restore done. Now what?” (transcript included).

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.

This is Day 3 of Postgres AI launch week

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

In one of the recent PostgresFM 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 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.