Advertisement ยท 728 ร— 90

Posts by Radim @ boringSQL

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

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

1 week ago 2 1 0 1

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.

1 week ago 0 0 0 0
Preview
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

1 week ago 0 0 1 0
Lister Learning and Teaching Centre

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

1 month ago 3 2 0 0
Post image

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.

1 month ago 2 0 0 0
Preview
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...

1 month ago 2 0 0 0

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

1 month ago 1 0 0 0

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.

1 month ago 1 0 1 0
Preview
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...

1 month ago 1 0 1 0

It's not magic. It's called SQL query regression testing. ๐Ÿป as postmortem ๐Ÿ˜‰

1 month ago 3 0 0 0
Advertisement

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.

1 month ago 6 2 2 0

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

1 month ago 0 0 0 0

#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.

1 month ago 1 0 1 0
Preview
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/...

1 month ago 1 0 0 0
Preview
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...

1 month ago 1 0 1 0
Preview
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...

2 months ago 1 0 0 0
Preview
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

2 months ago 1 0 0 0

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

2 months ago 1 0 0 0
GitHub - boringSQL/fixturize: Extract seed data from any PostgreSQL database Extract seed data from any PostgreSQL database. Contribute to boringSQL/fixturize development by creating an account on GitHub.

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.

2 months ago 1 0 1 0

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.

2 months ago 1 0 1 0
Advertisement

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.

2 months ago 0 0 1 0

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

2 months ago 1 0 1 0

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

2 months ago 1 0 0 0
Post image

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! ๐Ÿ‡จ๐Ÿ‡ฟ๐Ÿ’™

2 months ago 3 2 0 0
Preview
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.

2 months ago 0 0 0 0
Post image

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

2 months ago 1 0 0 0
Preview
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...

2 months ago 1 0 1 0

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.

2 months ago 3 2 0 0
Preview
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...

2 months ago 0 0 0 0

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

3 months ago 0 0 0 0
Advertisement