Neues vom PostgreSQL Planet
semab tariq: Implementing Bi-Directional Replication in PostgreSQL(link is external)
In today's fast-paced digital world, ensuring that your data is always up-to-date and accessible is crucial. For businesses using PostgreSQL, replication is a key feature that helps achieve this. While many are familiar with streaming replication, bi-directional replication offers unique advantages that can enhance data availability and reliability. In this blog post, we'll explore what bi-directional replication is, how it differs from streaming replication, and provide a practical example to setup bi directional replication in PostgreSQL
Andrew Atkinson: PostgreSQL 17: JSON_TABLE(), MERGE with RETURNING, and Updatable Views(link is external)
It’s time for a new Postgres release! PostgreSQL 17 shipped a few weeks ago, with lots of new features to explore.
As a mature database system, prized for reliability, stability, and backwards compatibility, new features aren’t often the most splashy. However, there are still goodies that could become new tools in the toolboxes of data application builders.
The Postgres 17 release notes(link is external) is a good starting point, as it covers a breadth of items.
Andrew Atkinson: Rails World 2024 Conference Recap(link is external)
This is Part 1 of my recap of Rails World 2024, a phrenetic two-day conference in Toronto, Canada, September 2024, with 1000+ attendees.
In this post, I’ll describe some sessions, but mostly they’re saved for part 2, once I watch all the sessions I missed now that the full Rails World 2024 Playlist is on YouTube(link is external).
Luca Ferrari: pgenv 1.3.8 is out!(link is external)
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(link is external), that solves a few problems in dealing with the latest PostgreSQL release version 17.
Stefanie Janine: Handling BLOBs In PostgreSQL(link is external)
PostgreSQL does not have a BLOB data type as specified in the SQL standard(link is external). The nearest implementation is the data type BYTEA(link is external). 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?(link is external)
Laurenz Albe: Dealing with trigger recursion in PostgreSQL(link is external)
© 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(link is external)
After several months of development, version 2.0 of PostgreSQL Anonymizer(link is external) 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(link is external)
This year, PGDay Lowlands 2024(link is external) was paired with PGDay UK 2024(link is external), 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)(link is external)
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(link is external)
The soucre code is available on GitLab(link is external), a mirror is hosted on GitHub(link is external).
One could install the whole package, or just copy what is needed from the source code.
The extension is also available on PGXN(link is external).
Jimmy Angelakos: Contributions of w/c 2024-10-07 (week 41)(link is external)
- Claire Giordano(link is external) hosted Tom Lane on the Talking Postgres podcast on Wed Oct 10th(link is external).
- 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(link is external), Boriss Mejias, and several others.
Hubert 'depesz' Lubaczewski: SQL/JSON is here! (kinda “Waiting for Pg 17”)(link is external)
Gabriele Bartolini: CNPG Recipe 14 - Useful Command-Line Tools(link is external)
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(link is external)
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(link is external) 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(link is external)
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(link is external)
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(link is external) 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(link is external)
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(link is external)
Ahsan Hadi: PostgreSQL 17 - A Major Step Forward in Performance, Logical Replication and More(link is external)
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(link is external).