Photo from PGDay UK 2023, credit: Jimmy Angelakos This month’s PGSQL Phriday topic, proposed by Pavlo Golub, is Postgres Events. I recorded an episode of Postgres FM on this with Nikolay, but somehow even in 52 minutes I didn’t manage to cover a couple of things I wanted to share, so here we are. IRL events Pavlo mentions in his invitation that “we all know that the community’s heartbeat lies in PostgreSQL Events”. I’m not sure I fully agree, but it certainly seems like a shortcut to getting embraced if you’re new and want to get involved! I’m a big fan of local meet-ups and try to attend any ..read more

Cover photo by Deepak Rautela Over the past 5 years, new Postgres versions have been getting better and better at running operations in parallel. This is especially useful for analytical queries, which can sometimes speed up massively by fully utilising a large server (with lots of cores). However, for fast, transactional queries, the overhead of starting and managing processes is almost always undesirable. As such, the default settings in Postgres are on the conservative side. If you think your workload could benefit from more parallelism, then this is for you. The first limiting factor The f ..read more

Introduction auto_explain is a very cool PostgreSQL module that can be used to log the execution plans of your slowest queries automatically. It does this by tracking every query (or a sample of them), only logging the output for those slower than a threshold you set. This post isn’t intended to be an introduction to auto_explain, but that could be a good future post. For now, if you’re new to it, I’d recommend reading the auto_explain section of the PostgreSQL documentation. Here we’ll be looking into the overhead of auto_explain. Many places warn about its overhead, but I’ve found concrete ..read more

Photo by Sergio Ibanez One of the worst performance hits a Postgres query can take is having to perform a sort or hash operation on disk. When these space-intensive operations require more memory than is available, Postgres uses disk space instead. Disk space is much slower to read and write than RAM, so this generally takes significantly longer. The best solution to this problem is to avoid having to perform the operation entirely, for example by adding a judicious index. The second best solution is to reduce the amount of space required for the operation, by working on fewer rows or less dat ..read more

This month’s PGSQL Phriday invitation from Tomasz Gintowt is on the topic of “Partitioning vs sharding in PostgreSQL“. What are partitioning and sharding? It has been possible to do partitioning in PostgreSQL for quite a while — splitting what is logically one large table into smaller physical tables. Since version 10, a huge leap was made with the introduction of declarative partitioning, and more improvements have come every year since. Sharding is a different story — splitting what is logically one large database into smaller physical databases. The primary tool for this in the PostgreSQL e ..read more

This month’s PGSQL Phriday topic is “database change management”, proposed by Dian Fay. While I’ll try to make this topic about performance (as I always do) let me first embark on a quick trip down memory lane. My background in database change management Back in 2010, my first full-time job was at a company called Redgate, who’s flagship product at the time was SQL Compare. To this day, that tool allows folks to quickly compare SQL Server databases, review the differences, and generate a script to make one match the other. Fun fact: since then, an excellent former teammate of mine, Neil Anders ..read more

Indexes are really important for Postgres performance, but they’re often misunderstood and misapplied. This post aims to give you a good grounding in indexes to avoid a lot of beginner mistakes. Step one: understand what you want to achieve Because indexes are such a powerful tool, a new index is often viewed as “the answer” to whatever performance problems people are experiencing. Wading straight in and creating an index for every sequential scan in sight is the simplest thing to do, but indexes have costs as well as benefits. Not only do indexes take up memory, they raise the cost of wr ..read more

A couple of weeks ago, I invited folks to blog about pg_stat_statements, as I was hosting the eighth monthly PGSQL Phriday. It was fun seeing the posts roll in last week— I’m so grateful to everyone who got involved. Posts roundup The first entry I spotted was a wonderful piece by Dian Fay about a recent experience using pg_stat_statements to solve a real performance issue. I love this kind of practical post, and opportunity to learn how others do things. I especially loved the points about stddev_exec_time, and casting the user_id to regrole in the query — very helpful. The next post was from ..read more

I’m proud to be the host of this month’s PGSQL Phriday event, the eighth so far. Given my interest in database performance, the topic I’ve chosen for this month is… drumroll… pg_stat_statements (woo! yeah! cheers from the crowd!) Hold up, what’s this PGSQL Phriday thing? PGSQL Phriday is a new(ish) monthly community blogging event. The goal of it is to encourage folks to blog more, share knowledge, and learn from each other! There are also a few simple rules for getting involved, here’s a summary: Publish your blog post on Friday, 5th May 2023 Include “PGSQL Phriday #008” in your title or ..read more

Cover photo: Paul Teysen The PGSQL Phriday topic this month is non-relational data, proposed by the excellent Ryan Lambert of Rustproof Labs. At first, I thought this might be yet another topic I can’t contribute much to… but then I remembered we do now store some some blobs in Postgres!  The challenge When we added the ability to publish query plans, back in May 2021, we needed a way to store them server-side. We considered our options, but went with the most logical place given our infrastructure — a bucket in Google Cloud. More recently, when we added the ability to save plans, we had ..read more