Neues vom PostgreSQL Planet

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"

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;

When moving from Oracle to PostgreSQL, one of the key differences lies in how each database handles roles and privileges. Oracle's privilege model is deeply ingrained in enterprise systems, with fine-grained user controls and a strict distinction between users and roles. PostgreSQL, while just as capable, approaches roles and privileges differently, offering flexibility and simplicity, but it also requires a shift in mindset for Oracle users.

In previous installments in this series about locales and collations in PostgreSQL, we’ve made some vague allusions to the speed of the various collation functions. Let’s be a bit more analytical now.

The data here was gathered on a 4GB Linode instance running Ubuntu 24.04 and PostgreSQL 17.1. The test data was 1,000,000 records, each one a string of 64 random 7-bit ASCII characters. For each of the configurations, the test data was loaded into a table:

sql CREATE TABLE t(v varchar);

A small but great improvement in the security of PL/Perl.

PL/Perl now ties %ENV

PL/Perl is a great language, since it ties together two of my favourite pieces of technology: PostgreSQL and Perl.

While I do usually refer to PL/Perl as the capability to run Perl code within PostgreSQL, the correct naming is either PL/Perl or PL/Perlu, where the former is the trusted language and the latter is the untrusted one.

This article is the first in the series about the upcoming PostgreSQL 18 release. Let us take a look at the features introduced in the July CommitFest.

The OpenStreetMap (OSM) database builds almost 750GB of location data from a single file download. OSM notoriously takes a full day to run. A fresh open street map load involves both a massive write process and large index builds. It is a great performance stress-test bulk load for any Postgres system. I use it to stress the latest PostgreSQL versions and state-of-the-art hardware. The stress test validates new tuning tricks and identifies performance regressions.

Data is often called the new gold, and databases are where we store and manage this precious resource as it constantly changes and grows. At first glance, updating data might seem like a simple task—just modify a row. 

But behind the scenes, it’s more complex to ensure that data remains consistent and accessible. In today’s blog, I’ll answer some frequently asked questions from our customers and dive into why PostgreSQL relies on a process called VACUUM to efficiently manage data updates.

This year, the Lightning Talks at PGConf.EU were handled a bit different, compared to the previous years. If you attended a previous PGConf.EU, you remember a whiteboard which at some point appears near the registration desk and people rush to fill in 12 talks. First come, first selected. This year, Karen Jex suggested to have a box and everyone can drop in cards with a talk, and then 12 talks are randomly selected.

Postgres is already great, surely - even “too popular” one could complain with a twist…as this broadcast “was” actually supposed to be my Lightning Talk at last month’s pgConf.eu in Athens 🙂 But indeed, seems Postgres has in an awesome way gotten so big that it’s not like that anymore...

• November 12th: Katherine Saar, Stacey Haysler and Christophe Pettus hosted an online meetup for PostgreSQL San Francisco Area User Group, "Be Thankful: Logical Replication traps you did not fall into", presented by Kacey Holston.
• November 12th: Floor Drees published an episode for the "Hacking Postgres" podcast S3E2 (

  Elizabeth Garrett Christensen: Easy Totals and Subtotals in Postgres with Rollup and Cube

  18. November 2024 - 15:30

  Postgres is being used more and more for analytical workloads. There’s a few hidden gems I recently ran across that are really handy for doing SQL for data analysis, ROLLUP and CUBE. Rollup and cube don’t get a lot of attention, but follow along with me in this post to see how they can save you a few steps and enhance your date binning and summary reporting.