Skip to main content

26 posts tagged with "Postgres insights"

View All Tags

The hidden costs of CREATE TEMP TABLE: catalog bloat and LWLock:SInvalWrite

· 21 min read
Nikolay Samokhvalov
CEO & Founder
An abstract drawing: thin lines, each one a database session, and orange marks that move along them.An abstract drawing: thin lines, each one a database session, and orange marks that move along them.

For years I warned against creating temporary tables in hot code because of catalog bloat. This time I measured it. The bloat was real, and it was only one of the costs.

What does CREATE TEMP TABLE cost if your code runs it in every transaction? On Postgres 18, two things:

  1. Catalog rows and files, every time. A temp table per transaction ran at 3783 TPS, one that each session keeps at 17866, and a CTE doing the same work at 86997 (16 vCPU, no idle sessions). Creating and dropping the table explains the 4.7x step from 17866 to 3783.
  2. The shared invalidation queue, when there are many sessions. With 5000 idle sessions and a 500 TPS target, temp-table DDL got 127 TPS in one run and 249 in another.

We measured it with DBLab Forge[1], a system that runs full-scale synthetic database experiments to study Postgres behavior and anomalies. The real case behind it is from Kirk Wolak[2], whose team moved a busy search system from Oracle and kept temp tables in its most frequent query.

Every number here comes from the DBLab Forge study brief[3]. Kirk and I discuss it in Postgres.FM episode 196[4].

Links

  1. [1] DBLab Forge https://postgres.ai/products/dblab-forge
  2. [2] Kirk Wolak https://www.linkedin.com/in/kirk-wolak-7706737/
  3. [3] DBLab Forge study brief https://console.postgres.ai/shared/brief/dfe99713-f5a8-483b-9aab-1606a883b75d
  4. [4] Postgres.FM episode 196 https://postgres.fm/episodes/the-hidden-costs-of-create-temp-table

UUIDv4 vs UUIDv7: what a random primary key costs in Postgres

· 20 min read
Nikolay Samokhvalov
CEO & Founder

Postgres 18 ships uuidv7()[1], which generates UUIDv7 as defined in RFC 9562[2]. 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.

0.62–0.69×
UUIDv4 insert rate
vs an ordered key on the same server, at 100M rows
1.5–1.9×
UUIDv4 WAL per row
vs an ordered key on the same server, at 90M–100M rows
+33%
UUIDv4 primary key size
vs UUIDv7, at 100M rows

I asked DBLab Forge[3] 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[4] has every number with its source.

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

Links

  1. [1] uuidv7() https://www.postgresql.org/docs/18/functions-uuid.html
  2. [2] RFC 9562 https://www.rfc-editor.org/rfc/rfc9562#section-5.7
  3. [3] DBLab Forge https://postgres.ai/products/dblab-forge
  4. [4] full brief https://console.postgres.ai/shared/brief/9fffe2da-00c1-470b-a79d-a210ae2306bc
  5. [5] Postgres.FM episode 194, "UUIDv4 vs UUIDv7" https://postgres.fm/episodes/uuidv4-vs-uuidv7

What your Postgres is waiting for, and why the dashboard can't tell you

· 12 min read
Dmitry Fomin
Sr. Postgres engineer

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.

Dump/restore done. Now what?

· 15 min read
Nikolay Samokhvalov
CEO & Founder

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?”[1] (transcript included).

Links

  1. [1] Postgres.FM episode 193, “Dump/restore done. Now what?” https://postgres.fm/episodes/dump-restore-done-now-what

How moving one word can speed up a query 10–50x

· 6 min read
Maxim Boguk
Postgres Expert
Nikolay Samokhvalov
CEO & Founder

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

Query 1:

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
);

Query 2:

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.

#PostgresMarathon 2-013: Why keep your index set lean

· 7 min read
Nikolay Samokhvalov
CEO & Founder

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.

#PostgresMarathon 2-012: Ultra-fast replica creation with pgBackRest

· 10 min read
Nikolay Samokhvalov
CEO & Founder
Maxim Boguk
Postgres Expert

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.

#PostgresMarathon 2-011: Prepared statements and partitioned tables — the paradox, part 3

· 8 min read
Nikolay Samokhvalov
CEO & Founder

In #PostgresMarathon 2-009[1] and #PostgresMarathon 2-010[2], 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.

Links

  1. [1] #PostgresMarathon 2-009 https://postgres.ai/blog/20251028-postgres-marathon-2-009
  2. [2] #PostgresMarathon 2-010 https://postgres.ai/blog/20251029-postgres-marathon-2-010

#PostgresMarathon 2-010: Prepared statements and partitioned table lock explosion, part 2

· 2 min read
Nikolay Samokhvalov
CEO & Founder

In #PostgresMarathon 2-009[1], 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.

Links

  1. [1] #PostgresMarathon 2-009 https://postgres.ai/blog/20251028-postgres-marathon-2-009

#PostgresMarathon 2-009: Prepared statements and partitioned table lock explosion, part 1

· 3 min read
Nikolay Samokhvalov
CEO & Founder

In #PostgresMarathon 2-008[1], 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?

Links

  1. [1] #PostgresMarathon 2-008 https://postgres.ai/blog/20251014-postgres-marathon-2-008