Neues vom PostgreSQL Planet
Luca Ferrari: pgenv 1.3.8 is out!
A new release of pgenv that simplifies the management of PostgreSQL 17.
pgenv 1.3.8 is out!Yesterday, David Wheeler releader version 1.3.8 of pgenv, that solves a few problems in dealing with the latest PostgreSQL release version 17.
Stefanie Janine: Handling BLOBs In PostgreSQL
PostgreSQL does not have a BLOB data type as specified in the SQL standard. The nearest implementation is the data type BYTEA. Since PostgreSQL 9.0 it does handle data by standard as hexadecimal data.
LimitationsBYTEA has a limit of max 1 GB of data.
Binary data cannot be indexed or searched for content. They can be inserted, updated (fully replaced), or deleted.
Robert Haas: Is pg_dump a Backup Tool?
Laurenz Albe: Dealing with trigger recursion in PostgreSQL
© Laurenz Albe 2024
Many a beginner falls into the trap of trigger recursion at some point. Usually, the solution is to avoid recursion at all. But for some use cases, you may have to handle trigger recursion. This article tells you what you need to know about the topic. If you were ever troubled by the error message “stack depth limit exceeded”, here is the solution.
damien clochard: PostgreSQL Anonymizer 2.0 - Generating Fake Data
After several months of development, version 2.0 of PostgreSQL Anonymizer has entered the beta phase, and this is an opportunity for us to launch a series of articles to present its new capabilities in preview!
For this first technical overview, let’s see how to generate fake data (also known as “synthetic data”).
Pavlo Golub: PGDay Lowlands 2024 Reflections
This year, PGDay Lowlands 2024 was paired with PGDay UK 2024, which gave me the chance to try something new—taking the train directly from London to Amsterdam. Naturally, I was curious about passing through the famous tunnel under La Manche (some folks call it the English Channel, but I am still unsure why 😜). Spoiler alert: there's nothing cool to see, just darkness. 🙂
Tomas Vondra: Tuning the glibc memory allocator (for Postgres)
If you’ve done any Postgres development in C, you’re probably aware of the concept of memory contexts. The primary purpose of memory contexts is to absolve the developers of having to track every single piece of memory they allocated. But it’s about performance too, because memory contexts cache the memory to save on malloc/free calls. But malloc gets the memory from another allocator in libc, and each libc has its own thing. The glibc allocator has some concurrency bottlenecks (which I learned the hard way), but it’s possible to tune that.
Stefanie Janine: pgsql_tweaks 0.10.7 Released
The soucre code is available on GitLab, a mirror is hosted on GitHub.
One could install the whole package, or just copy what is needed from the source code.
The extension is also available on PGXN.
Jimmy Angelakos: Contributions of w/c 2024-10-07 (week 41)
- Claire Giordano hosted Tom Lane on the Talking Postgres podcast on Wed Oct 10th.
- Robert Haas hosted the October Hacking Workshop.
- Teresa Giacomini, Isaac Alves, My Nguyen produced the Activity Book for Postgres v4 with advice from Ariana Padilla Acosta, Adam Wolk, Derk van Veen, Boriss Mejias, and several others.
Hubert 'depesz' Lubaczewski: SQL/JSON is here! (kinda “Waiting for Pg 17”)
Gabriele Bartolini: CNPG Recipe 14 - Useful Command-Line Tools
In this CNPG recipe, we explore three essential command-line tools that simplify working with CloudNativePG in Kubernetes: view-secret, view-cert, and stern. These tools enhance tasks such as inspecting secrets, verifying certificates, and tailing logs across multiple pods, streamlining your PostgreSQL management experience in a cloud-native environment. Whether you’re troubleshooting or optimising workflows, these utilities will help you boost productivity and gain better control over your Kubernetes-based PostgreSQL deployments.
Paul Ramsey: Convert JSON into Columns and Rows with JSON_TABLE
If you missed some of the headlines and release notes, Postgres 17 added another huge JSON feature to its growing repository of strong JSON support with the JSON_TABLE feature. JSON_TABLE lets you query JSON and display and query data like it is native relational SQL.
Andreas Scherbaum: PostgreSQL Berlin October 2024 Meetup
On August 9th, 2024, we had the PostgreSQL October Meetup in Berlin. Amazon hosted it, and sponsored the Meetup in their office in the Brüderstraße, in Berlin Mitte.
Bruce Momjian: More Release Note Details
Over the years, we occasionally get requests for more detail on release note items, particularly for the major release notes. Historically, our only response was to tell people to view the SGML/XML file used to generate the web and PDF versions of the release notes. In the file, above each release note item, is a comment block which contains the commit headers responsible for the item, e.g.:
semab tariq: Transform PostgreSQL into a Columnar Database Using Citus
Columnar databases are transforming the way we handle large datasets by storing data in columns rather than rows. This approach enhances performance, especially for analytical queries, by allowing faster data retrieval and efficient storage. As businesses generate more data than ever, understanding the benefits of columnar databases becomes crucial. In this blog, we'll explore how these databases work, their advantages over traditional row-based systems, and why they are becoming a popular choice for data-driven organizations.
Shaun Thomas: Comparing Columnar to Heap Performance in Postgres with pg_timeseries
Ahsan Hadi: PostgreSQL 17 - A Major Step Forward in Performance, Logical Replication and More
After a successful 3rd beta in August 2024, the PostgreSQL development group released the GA version on September 26th. Recently, I blogged about some of the key logical replication features that you'll see in PostgreSQL 17 https://www.pgedge.com/blog/logical-replication-features-in-Postgres 17.
Stefanie Janine: sparql_fdw Foreign Data Wrapper Tested Against PostgreSQL 17
The sparql_fdw is a foreign data wrapper to connect to query web databases with the SPARQL protocol from inside PostgreSQL written in Python.
Test Against PostgreSQL 17As multicorn2 is already working with PostgreSQL 17, I tested the sparql_fdw, too.
Greg Sabino Mullane: Enhanced Postgres Release Notes
There is something new you may not have seen in the release notes for Postgres 17. No, not a new feature - I mean inside the actual release notes themselves! The Postgres project uses the git program to track commits to the project, and now each item in the release notes has a link to the actual commit (or multiple commits) that enabled it.
David Wheeler: PGXN v2 Update
Speaking of PGXN news, I neglected to link to this post I wrote for the Tembo Blog last month, a fairly detailed accounting of what’s been happening on the PGXN v2 project:
