Neues vom PostgreSQL Planet

Catching up with the last weeks of 2024, and entering into 2025, these are the last contributions we were able to know about:

This blog explains locking mechanisms in PostgreSQL, focusing on table-level locks that are required by Data Definition Language (DDL) operations.

2025 is picking up speed, and with CommitFest 2025-01 underway we're seeing some more PostgreSQL 18 changes again. In news closer to home, the PgPedia Week format has been expanded to include links to recent articles on PostgreSQL 18 , other PostgreSQL-related newsletters published in the preceding week, as well as to general PostgreSQL announcements. Oh, and there's also a new-ish section listing commits of interest from 25 years ago , in case anyone wants an occasional trip down memory lane.

On 27th of November 2024, Peter Eisentraut committed patch: Support LIKE with nondeterministic collations   This allows for example using LIKE with case-insensitive collations. There was previously no internal implementation of this, so it was met with a not-supported error. This adds the internal implementation and removes the error.

If you’re managing a PostgreSQL database and handling sensitive data or PII, the answer is simple: You need data-at-rest encryption. This isn’t just a “nice-to-have” feature—it’s often a legal or regulatory requirement. Compliance auditors, security officers, and privacy-conscious customers all expect it.  But is this enough?  We think NO! The reality check: No native TDE […]

Yup, you read it right. Idle transactions can cause massive table bloat that the vacuum process may not be able to address. Bloat causes degradation in performance and can keep encroaching disk space with dead tuples.
This blog delves into how idle transactions cause table bloat, why this is problematic, and practical strategies to avoid it.

Yup, you read it right. Idle transactions can cause massive table bloat that the vacuum process may not be able to address. Bloat causes degradation in performance and can keep encroaching disk space with dead tuples.
This blog delves into how idle transactions cause table bloat, why this is problematic, and practical strategies to avoid it.

HexaCluster is back with its Summary of PostgreSQL in 2024 like our article in 2023. We would first like to thank all PostgreSQL Users, Contributors, Organizations, and Sponsors who have directly or indirectly contributed to an exponential growth, year over year, in PostgreSQL adoptions across the planet. PostgreSQL continues its legacy as one of the […]

The post Summary of PostgreSQL in 2024 appeared first on HexaCluster.

I’ve had a wish list for a few years now of observability-related things I’d love to see someday in community/open-source Postgres. A few items from my wish list:

CloudNativePG has surpassed 5,000 stars on GitHub! More than just a number, this achievement reflects the trust, enthusiasm, and collaboration of the Postgres and Kubernetes open-source community. I look back at the journey, acknowledge the incredible contributions from users and maintainers, and invite everyone to join us in shaping the future of cloud-native PostgreSQL.

Introduction

PostgreSQL 18 will normally (as there is always a risk of seeing something reverted until its GA release) include this commit: Add backend-level statistics to pgstats.

The number of cool things you can do with the http extension is large, but putting those things into production raises an important problem.

The amount of time an HTTP request takes, 100s of milliseconds, is 10- to 20-times longer that the amount of time a normal database query takes.

This means that potentially an HTTP call could jam up a query for a long time. I recently ran an HTTP function in an update against a relatively small 1000 record table.

On 18th of December 2024, Michael Paquier committed patch: psql: Add more information about service name   This commit adds support for the following items in psql, able to show a service name, when available: - Variable SERVICE. - Substitution %s in PROMPT{1,2,3}.   This relies on 4b99fed7541e, that has made the service name available … Continue reading "Waiting for PostgreSQL 18 – psql: Add more information about service name"

A short review of PostgreSQL post rankings in 2024. The counting is limited, as there are no server logs and no cookies used at ProOpenSource websites.

The only tracking used is images on another instance with matomo. As some more protective browsers are blocking third party stuff, there is no counting for those.

Overall Statistics

As you may see in the image below, the access went up each year since the start of the blogs in 2021.

A very short "Week" this week, as the end-of-year holiday season inevitably sees a global lull in activity - we're all only human, after all. Wishing everyone a happy new PostgreSQL year!

PostgreSQL 18 changes

No user-visible features or other changes were added this week.

more...

During the New Year holiday week, I want to glance at one of Postgres' most robust features: the internal caching technique for query trees, also known as memoisation.

Based on the recently-released Go JSONPath and JSONTree playgrounds, I’ve updated the design and of the SQL/JSON Playground.

Many enterprise workloads are being migrated from commercial databases like Oracle and SQL Server to Postgres, which brings anxiety and challenges for mature operational teams. Learning a new database like Postgres sounds intimidating. In practice, most of the concepts directly transfer from databases like SQL Server and Oracle. Transactions, SQL syntax, explain plans, connection management, redo (aka transaction/write-ahead logging), backup and recovery – all have direct parallels.

On 11st of December 2024, Masahiko Sawada committed patch: Add UUID version 7 generation function.   This commit introduces the uuidv7() SQL function, which generates UUID version 7 as specified in RFC 9652. UUIDv7 combines a Unix timestamp in milliseconds and random bits, offering both uniqueness and sortability.   In our implementation, the 12-bit sub-millisecond … Continue reading "Waiting for PostgreSQL 18 – Add UUID version 7 generation function."

It’s Christmas time and relatively quiet in my day job, so let’s make it story time again! One more tale from the trenches: how wrong you can go with one table and one index?