LinkedIn LIVE: Jimmy Angelakos - PostgreSQL Mistakes and How to Avoid Them

If you're interested in #PostgreSQL, make sure you attend my April 3rd LinkedIn Live event (10am ET/3pm BST)!

I'll break down real #SQL anti-patterns from my book (via @manning.com) and fix them live.
👇
🎟️ Register: linkedin.com/events/74433...
📖 Book: hubs.la/Q03Nc0hv0

#Postgres #webinar

That imperative mindset leads to a pattern I see everywhere: one CTE filters rows, the next LEFT JOINs and aggregates with GROUP BY, the next filters the result. Reads like a pipeline. Performs terribly. The GROUP BY creates a wall the planner can't optimize past, even after in-lining.

Good CTE, bad CTE The planner treats CTEs very differently depending on how you write them. Here's what happens under the hood, version by version, through PostgreSQL 18.

Rewriting badly written CTEs is a constant source of work for me. Not because CTEs are bad. Mostly because developers use them to force execution order on the database.

"First do this, then do that".

Learn more at boringsql.com/posts/good-c...

#postgresql #sql

Lister Learning and Teaching Centre

This week: #PostgreSQL #Edinburgh #meetup on 📅 Thurs, March 12th! Join @boringsql.com and @vyruss.org for talks on making sure your queries don't break production & on how easy it is to run #Postgres on #Kubernetes with @cloudnativepg.bsky.social
👇
luma.com/5pglgx8h

#CloudNativePG #k8s #TechMeetups

Major progress for RegreSQL 2.0. It can now force pretty much any statistics.

Yesterday I highlighted the current limitation of overriding statistics. Today I can show you planner being completely independent and choosing Bitmap Index Scan on table with 1 tuple.

Production query plans without production data PostgreSQL 18 makes optimizer statistics portable. Export them from production and inject them into test databases to get realistic query plans without the data.

What happens when you inject production statistics into a 10,000-row PostgreSQL test table? The query plan flips.



PostgreSQL 18 makes this a one-liner.

pg_dump --statistics-only


Plain SQL, under 1MB, no data needed.

Learn the mechanics in my latest blog post.


boringsql.com/posts/portab...

You just might be onto something 🤣 I've been looking for something for the back of the t-shirts and I think we have a strong candidate

Equality: 0.5%
Ranges: 33%
May your indexes be with you

Or what about cases where there are no statistics at all? Like materialized CTEs, temporary tables, or computed expressions in WHERE clauses. The planner falls back to hardcoded constants. 0.5% selectivity for equality. 33% for ranges. Good luck.

PostgreSQL Statistics: Why queries run slow Slow PostgreSQL queries? Stale statistics are often to blame. Learn how the query planner uses statistics to estimate row counts and how to fix them.

#postgresql takes a bite of 30,000 rows from your 50 million row table and calls it a day. That's 0.06% of your data deciding if your query will be fast or slow.

boringsql.com/posts/postgr...

It's not magic. It's called SQL query regression testing. 🍻 as postmortem 😉

Step 1: "LGTM, this query is fine"
Step 2: Deploy to production
Step 3: Pray 🙏
Step 4: SRE - "This query is definitely NOT fine"
Step 5: "But... it worked on MY database" 🔥

No ifs. No buts. On 12 March @postgresedi.bsky.social find out how to become an atheist when it comes to your SQL queries.

Busted :) These are the ones I'm speaking at - next time I'll have to do better proposal so Paris find me worthy 😉

#postgresql season is officially on. I'll be on the ground at

🇪🇺 Nordic pgDay in Helsinki (March)
🇪🇺 German pgConf in Essen (April)
🇺🇸 PGDATA in Chicago (June)
🇬🇧 pgDay in London (September)

I’m looking forward to all the chats and fun that's ahead.

Inside the 8KB Page: PostgreSQL Page Layout Visualized Interactive visualization of PostgreSQL's 8KB page: header, line pointers, free space, and tuple data.

To make it bit more fun, you can play with the 2nd visualizer.

boringsql.com/visualizers/...

Inside PostgreSQL's 8KB Page A byte-level tour of the 8KB page. PostgreSQL's atomic unit of storage. We deep dive into page headers, line pointers, and free space using pageinspect to see exactly how your data lives on disk.

Epitome of boring. What else to write on site called boringSQL then dry introduction of what is inside #postgresql 8KB page?

boringsql.com/posts/inside...

The Alchemy of Shared Buffers: Balancing Concurrency and Performance Josef Machytka: Speaker portfolio

"The Alchemy of Shared Buffers" must have been amazing presentation by Josef Machytka during CERN pgDay (had to skip that one unfortunately). It's worth checking the slides.

medium.com/@josef.machy...

Reading Buffer statistics in EXPLAIN output Learn how to interpret buffer statistics in PostgreSQL EXPLAIN output. Understand shared hits, disk reads, temp spills, and planning buffers to accurately diagnose and fix query performance bottleneck...

Back to Buffers. This time you can learn how to read and understand Buffer statistics in EXPLAIN output in my latest article boringsql.com/posts/explai... #postgresql

While this led to 2748 lines dropped from RegreSQL it gets things done. Stay tuned for 2.0 release!

That's where new project "fixturize" comes in:

github.com/boringSQL/fi...

its goal is to retrieve data snapshots with referential integrity intact, while masking sensitive data in the process.

But it's not bad news. Agonising over fixtures forced me to look at the problem from another direction - how to quickly and securely use production data. Gave up on sampling, only to find a better solution:

graph-traversal based data extraction with masking to strip customer data and PII.

While other changes leading up to v2.0 are strong quality-of-life improvements, I've decided to pull generated fixture support out of RegreSQL entirely for now.

There are weeks of full wins, and those where it's time to eat the humble cake. RegreSQL has been making great progress, but I've been fighting the fixture system more and more. After many conversations and endless evenings - it's time to admit:

fixtures are a genuinely hard problem

Still recharging after this week. But it's that good kind of tired - the one you get when something actually matters.

@praguepgdevday.bsky.social was my first time speaking at a bigger Postgres event. The RegreSQL session went well, but honestly? The conversations afterwards were the real thing

That’s a wrap at #P2D2! 🎉 Huge thanks to our amazing speakers, organizers, and attendees for making it unforgettable.

PostgreSQL, people, and Prague—what a combo. We can’t wait to do it again next year. See you soon — brzy na viděnou! 🇨🇿💙

Introduction to Buffers in PostgreSQL How PostgreSQL actually manages memory, from shared_buffers and dirty pages to the OS page cache sitting underneath it all.

Buffers came up during Postgres FM chat and people asked why they matter so much. So I wrote about it - with interactive demos to make it click.

Introduction to Buffers: boringsql.com/posts/introd...

Do the visuals help you to grasp the concept easier? Let me know.

First testing of RegreSQL integration with Rails / ActiveRecord via `rake regresql:export`.

Postgres FM | RegreSQL Nik and Michael are joined by Radim Marek from boringSQL to talk about RegreSQL, a regression testing tool for SQL queries they forked and improved recently. Here are some links to things they ment...

I got myself talking about the motivation behind RegreSQL on Postgres FM 🎙️

We nerded out a bit about why timing is a terrible metric, why buffers actually matter, statistics, performance – and why all of this is more relevant in the age of agentic coding.

postgres.fm/episodes/reg...

Test if queries hit the right indexes at production scale – without needing to have production data.

I'll show you exactly how during @praguepgdevday.bsky.social - Session "It works on my database - Regression testing of SQL queries" is last of the day, but I intend to make it worth staying for.

The hidden cost of PostgreSQL arrays Deep dive into PostgreSQL arrays: why they're document storage in disguise, the TOAST performance trap, GIN vs B-tree indexing, the dangerous ANY() operator, and when junction tables beat arrays.

Where do you stand when it comes to #postgresql arrays?

If you want to learn more, check out my latest deep-dive into the topic of arrays, exploring TOAST, GIN indexing gotchas, and when to just use a link table.

boringsql.com/posts/good-b...

Writing about instant clones of PostgreSQL databases was not a clever move. Now I have to keep repeating to myself:

I will NOT rewrite the whole control plane
I will NOT scrap everything for #freebsd jails
I will NOT rebuild everything with native ZFS snapshots