Neues vom PostgreSQL Planet
Kaarel Moppel: Postgres User Group Tallinn next event on 27th of November
Hubert 'depesz' Lubaczewski: Changes on pgdoc.link
Ibrar Ahmed: Understanding and Reducing PostgreSQL Replication Lag
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.
Christophe Pettus: Locale Cooking: Common Scenarios and Suggestions
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;
Umair Shahid: Transitioning from Oracle to PostgreSQL: Roles & Privileges
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.
Christophe Pettus: Talk Fast: The Speed of Various Collations
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);
Luca Ferrari: PL/Perl now ties %ENV
A small but great improvement in the security of PL/Perl.
PL/Perl now ties %ENVPL/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.
Pavel Luzanov: PostgreSQL 18: part 1 or CommitFest 2024-07
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.
Greg Smith: Loading the World! OpenStreetMap Import In Under 4 Hours
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.
semab tariq: What Happens Behind the Scenes When You Modify a Row in PostgreSQL?
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.
Andreas Scherbaum: PGConf.EU 2024 Lightning Talks
Kaarel Moppel: Postgres on Spot VMs - only for the crazy?
Jimmy Angelakos: Contributions for the week of 2024-11-11 (Week 46 overview)
- 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
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.
Andreas 'ads' Scherbaum: Ayse Bilge Ince
Luca Ferrari: dbicdump: using PostgreSQL schemas as package separator in produced Perl classes
A way to instrument dbicdump to use PostgreSQL schemas as package separators.
dbicdump: using PostgreSQL schemas as package separator in produced Perl classesPerl DBIx::Class is a great Object Relational Mapper (ORM), and I use it regularly with dbicdump, which is a tool to synchronize your existing database structure with the classes your program is going to use.
Gülçin Yıldırım Jelínek: Prague PostgreSQL Meetup on November 18th
Christophe Pettus: The Doom That Came To PostgreSQL: When Collations Change
In this installment of our series on locales and PostgreSQL, we’ll talk about what can happen with the library that provides locales changes out from under a PostgreSQL database. It’s not pretty.
Douglas Hunley: On CVEs, BoMs, and Releases
Craig Kerstiens: A change to RelResultInfo - A Near Miss with Postgres 17.1
Since its inception Crunchy Data has released new builds and packages of Postgres on the day community packages are released. Yesterday's minor version release was the first time we made the decision to press pause on a release. Why did we not release it immediately? There appeared to be a very real risk of breaking existing installations. Let's back up and walk through a near miss of Postgres release day.