Neues vom PostgreSQL Planet

Efficient data retrieval is crucial in any production environment, especially for databases handling heavy traffic and large datasets. PostgreSQL’s operator classes are a powerful but often overlooked tool for fine-tuning index performance. They allow you to control how PostgreSQL compares data within an index, helping to streamline searches and improve query efficiency in ways that default settings simply can’t match.

• Rushabh Lathia(link is external) was interviewed as “PostgreSQL Person of the Week”
• Markus Winand(link is external) updated the modern-sql.com(link is external) website, it now includes all the PostgreSQL v17 features.
• Gulcin Yildirim Jelinek organized the Prague November Meetup. Tudor Golubenco and Noémi Ványi* spoke.

In a previous article(link is external) in this series(link is external), I explored the magic of INSERT...UNNEST for improving PostgreSQL batch INSERT performance. While it’s a fantastic technique, I know it’s not the fastest option available (although it is very flexible). Originally, I hadn't intended to loop back and benchmark all the batch ingest methods, but I saw a lot of confusion out there, so I'm back, and this time I'm looking at COPY too.

The Postgres open source project is nearly 30 years old, I personally started using it about 20 years ago. And I’ve been contributing code for at least 10 years. But even with all that experience I find it really difficult to make judgments about how the performance changed over the years. Did it improve? And by how much? I decided to do some benchmarks to answer this question.

This year, I traveled to many conferences, such as the PGconf.eu(link is external) in Athens for example or the P2D2 in Prague. Every conference is a great opportunity to get unique insights and networking possibilities. However, despite its proximity to Austria, I’ve never been able to make it to the DOAG (Deutsche Oracle-Anwendergruppe(link is external)) conference in recent years. This year, I decided it was finally time to change that!

Upgrades between PostgreSQL major versions(link is external) are famously annoying. You can’t just install the server binaries and restart, because the format of the data directory is incompatible.

Why is that? Why can’t we just keep the data format compatible?

Perhaps surprisingly, the data format is actually mostly compatible, but not completely. There are just a few things missing that are very hard to solve.

When I work with other relational databases I am reminded about how I can't use my favorite PostgreSQL hacks in them. I call these hacks PostgreSQLisms. A PostgreSQLism is a pattern of SQL unique to PostgreSQL or descendants of PostgreSQL. In this post I'm going to cover some of my favorite ones.

Continue reading "Some of my favorite PostgreSQLisms"(link is external)

New Blog Post Title: How can I send mail or HTTP request from database? This question happens every now and then in one of PostgreSQL support places. Whenever it happens, I just suggest to not try, as interacting with outside world from database can be problematic, and instead use LISTEN/NOTIFY. But it occurred to me, … Continue reading ""(link is external)

PostgreSQL users often employ analytical queries that sort and group data by different rules. Optimising these operators can significantly reduce the time and cost of query execution. In this post, I will discuss one such optimisation: choosing the order of columns in the GROUP BY expression.

Several weeks ago, I started a pgsql-hackers thread(link is external) proposing a new extension file organization and a search path GUC(link is external) for finding extensions.

In a world where we constantly seek to automate repetitive tasks, it is common to write down a query in a script, make it more convenient, and eventually integrate the whole thing into a project’s codebase. Tools like SQL*Plus and psql can be powerful allies in this game, as relevant as Bash or Python interpreters.

Your database is ticking along nicely - until a simple DELETE brings it to its knees. What went wrong? While we tend to focus on optimizing SELECT and INSERT operations, we often overlook the hidden complexities of DELETE. Yet, removing unnecessary data is just as critical. Outdated or irrelevant data can bloat your database, degrade performance, and make maintenance a nightmare. Worse, retaining some types of data without valid justification might even lead to compliance issues.

Meet Postgres User Group Tallinn / Estonia Just a short shout-out to maybe get an extra attendee or two - as actually appeared that we’ve never done any advertising for our local Estonian Postgres User Group on the Postgres feed at all - although we’ve been out there since years...

Based on checking logs, and my own personal needs I added more categories of keywords to pgdoc.link: environment variables, like: PGHOST config file names, like: pgpass functions from some contrib modules, like: akeys, or cube_ll_coord various keyword-like functions (?), like: current_user. This happened thanks to bug report by Benjie Gillam This brought the total number … Continue reading "Changes on pgdoc.link"(link is external)

Replication lag in PostgreSQL occurs when changes made on the primary server take time to reflect on the replica server. Whether you use streaming or logical replication, lag can impact performance, consistency, and system availability. This post covers the types of replication, their differences, lag causes, mathematical formulas for lag estimation, monitoring techniques, and strategies to minimize replication lag.

We’ve gone through a lot of detail about locales and collations here, but what should you do when it is time to set up a database? Here is a cookbook with some common scenarios, with recommendations.

“I want maximum speed, I am running on PostgreSQL version 17 or higher, and it’s OK if collation is whacky for non-7-bit-ASCII characters.”

Use the C.UTF-8 local from the built-in locale provider.

CREATE DATABASE mydb locale_provider=builtin builtin_locale='C.UTF8' template=template0;