Neues vom PostgreSQL Planet

Your database is configured for the needs of your day-to-day OLTP (online transaction processing) application workload, but what if you need to run analytics queries against your application data? How can you do that without compromising the performance of your application?

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?

Another calendar year draws to an end, so this will be the last PgPedia Week of 2024.

PostgreSQL 18 changes

As always, the end-of-year holidays mean commit log activity is lower than usual, but we did see one new potential performance improvement, with commit 58a359e5 (" Speedup tuple deformation with additional function inlining ") promising query performance increases of around 5-20% in deform-heavy OLAP-type workloads.

more...

2024 was a transformative year for CloudNativePG, marked by significant contributions to the Kubernetes ecosystem, increased adoption, and a growing community. This article reflects on key milestones, including the integration of advanced Kubernetes features, conference highlights, and personal insights, while looking ahead to the opportunities awaiting PostgreSQL in the cloud-native era.

I can’t get through a zoom call, a conference talk, or an afternoon scroll through LinkedIn without hearing about vectors. Do you feel like the term vector is everywhere this year? It is. Vector actually means several different things and it's confusing. Vector means AI data, GIS locations, digital graphics, and a type of query optimization, and more. The terms and uses are related, sure. They all stem from the same original concept. However their practical applications are quite different. So “Vector” is my choice for this year’s name collision of the year.