Latest Blog Posts

pgroll 0.7.0 update
Posted by Andrew Farries in Xata on 2024-10-23 at 00:00
Learn about the latest changes in pgroll in the 0.7.0 release as we continue developing it into a leading open-source schema migration tool for Postgres.

Chicago PUG October 15 recording
Posted by Henrietta Dombrovskaya on 2024-10-22 at 19:05

As many of my followers may recall, we had the pleasure of Paul Jungwirth presenting at the Chicago PUG meetup on October 15. I have high hopes for having true temporal data support in Postgres 18, and I wanted to know all the details of what to expect. I believe I can speak on behalf of everyone who watched and was present on October 15, either in person or virtually – it was an exceptional talk! I do not recall any other meetups where so many people asked me about the recording – and it is finally here!

Enjoy!

Crunchy Postgres for Kubernetes 5.7: Faster Backups, Automated Snapshots, Postgres 17 and More
Posted by Greg Nokes in Crunchy Data on 2024-10-21 at 14:30

We are excited to announce the release of Crunchy Postgres for Kubernetes 5.7! This latest version brings a wealth of new features and enhancements designed to make your Postgres deployments on Kubernetes more flexible, efficient, secure, and robust than ever before.

We have highlighted a few of the features that we are excited about below. You can also check out the release notes for more details

Backup Features

We took a close look at our backup and disaster recovery systems to identify areas where we could improve performance and add functionality while continuing to maintain a focus on protecting your data.

Automated Snapshots

Crunchy Postgres for Kubernetes can now manage snapshots of your database. When you enable this feature, we will automatically create and manage snapshots of your database. We will also automatically use and apply them when possible to accelerate clones. We have seen some impressive improvements in clone creation speed. Expect to see more and more instances where we automatically use snapshots to speed up replica creation and restores.

For example, if you were running Crunchy Postgres for Kubernetes on GKE, you could use this stanza to enable snapshots:

spec:
  backups:
    snapshots:
      volumeSnapshotClassName: gke-snapshotclass

Asynchronous Archiving by Default

One of the standout features in 5.7 is the shift to asynchronous archiving by default. This change enhances performance by allowing write-ahead logging (WAL) archiving to occur asynchronously, reducing potential delays in data processing.

With the operator now managing the spool-path, there's also less configuration overhead. If you have previously set the spool-path in your configuration, you can safely remove that setting after upgrading. You can also delete the associated directory, streamlining your setup.

After upgrading, a new log file at pgdata/pgbackrest/log/db-archive-push-async.log will help you track WAL archiving, providing better insights into your database operations.

[...]

What I look forward to at PGConf.EU in Athens
Posted by Gülçin Yıldırım Jelínek in Xata on 2024-10-21 at 00:00
The 14th PostgreSQL Conference in Europe is taking place in Athens, Greece from 22-25 October 2024. In this blog post, I will be sharing what I look forward to at PGConf.EU 2024 including a great keynote, extension ecosystem summit, Xata dinner and more.

The future of Postgres?
Posted by Craig Kerstiens in Crunchy Data on 2024-10-18 at 16:50

I’m often asked what do I think the future for Postgres holds, and my answer has been mostly the same for probably 8 years now, maybe even longer. You see for Postgres itself stability and reliability is core. So where does the new stuff come from if it’s not in the stable core… extensions.

Extensions within Postgres are unlike most other databases allowing you to modify or well extend the standard Postgres behavior. You can build other storage backends, new types, etc. Postgres itself ships with a number of extensions within the “contrib”. The list of contrib extensions hasn’t changed any time recently, but even contrib is a small sampling of what is possible. Beyond core there is a whole world of extensions, I want dig into just a smalls sampling starting with a few in core…

pg_stat_statements is to me the most useful extension that exists. It records what queries were run, how long they took, and a number of other details about the queries. A key extension for managing performance of your database.

auto_explain another one in contrib that is helpful for performance. For queries that run over a certain period of time will automatically log the explain plan–helpful for performance debugging.

pg_prewarm useful to prewarming the cache ahead of a failover.

Let’s jump out of contrib a little bit now. Of course that isn’t everything that ships with Postgres there is more, explore for yourself.

Citus is one of the (to date) more advanced extensions ever created. Citus turns postgres into a sharded, distributed, horizontally scalable database. Citus is especially built to work well for B2B style multi-tenant apps, and now after being acquired years ago part of Microsoft.

Pg_search extends Postgres to support elastic-quality full text search directly within Postgres. I often say Postgres can do just about everything and be pretty capable. Things like time series and search it’s about 80% as good of some of the best in class options out there, but pg_search takes it further making it a full

[...]

Postgres 17 Presentation
Posted by Bruce Momjian in EDB on 2024-10-17 at 20:00

I recently created a presentation about what I think are the most important areas of improvement in Postgres 17:

  1. Incremental backup
  2. Improved data manipulation
  3. Improved optimizer handling
  4. Improved logical replicas

Peter Eisentraut and I also did a webinar about this, and a recording is now available.

pg_parquet: An Extension to Connect Postgres and Parquet
Posted by Craig Kerstiens in Crunchy Data on 2024-10-17 at 14:30

Today, we’re excited to release pg_parquet - an open source Postgres extension for working with Parquet files. The extension reads and writes parquet files to local disk or to S3 natively from Postgres. With pg_parquet you're able to:

  • Export tables or queries from Postgres to Parquet files
  • Ingest data from Parquet files to Postgres
  • Inspect the schema and metadata of existing Parquet files

Code is available at: https://github.com/CrunchyData/pg_parquet/.

Read on for more background on why we built pg_parquet or jump below to get a walkthrough of working with it.

Why pg_parquet?

Parquet is a great columnar file format that provides efficient compression of data. Working with data in parquet makes sense when you're sharing data between systems. You might be archiving older data, or a format suitable for analytics as opposed to transactional workloads. While there are plenty of tools to work with Parquet, Postgres users have been left to figure things out on their own. Now, thanks to pg_parquet, Postgres and Parquet easily and natively work together. Better yet, you can work with Parquet without needing yet another data pipeline to maintain.

Wait, what is Parquet? Apache Parquet is an open-source, standard, column-oriented file format that grew out of the Hadoop era of big-data. Using a file, Parquet houses data in a way that is optimized for SQL queries. In the world of data lakes, Parquet is ubiquitous.

Using pg_parquet

Extending the Postgres copy command we're able to efficiently copy data to and from Parquet, on your local server or in s3.

-- Copy a query result into a Parquet file on the postgres server
COPY (SELECT * FROM table) TO '/tmp/data.parquet' WITH (format 'parquet');

-- Copy a query result into Parquet in S3
COPY (SELECT * FROM table) TO 's3://mybucket/data.parquet' WITH (format 'parquet');

-- Load data from Parquet in S3
COPY table FROM 's3://mybucket/data.parquet' WITH (format 'parquet');

Let's take an example products table, but not just a basic

[...]

Implementing Bi-Directional Replication in PostgreSQL
Posted by semab tariq in Stormatics on 2024-10-17 at 07:18

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

The post Implementing Bi-Directional Replication in PostgreSQL appeared first on Stormatics.

pgenv 1.3.8 is out!
Posted by Luca Ferrari on 2024-10-17 at 00:00

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.

The build workflow of PostgreSQL 17 has slightly changed, so that new dependencies are required to produce the documentation. Thanks to the work by Brian Salehi now the pgenv build command performs a make world-bin (essentially world-bin is the target to build and install PostgreSQL without documentation). The documentation package is downloaded separately, since now the documentation pre-built has been removed from the source tree and is available as a separate tarball.

Moreover, this release includes another Brian’s little contribution that improves the descriptive messages about dependencies.

Enjoy!

Rails World 2024 Conference Recap
Posted by Andrew Atkinson on 2024-10-17 at 00:00

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.

As a book author and consultant, the focus for Rails World for me was on meeting people, raising awareness about my book, and generally chatting about how people are using Postgres and Rails. As a long-time Ruby community member, it was great to catch up with a lot of industry friends.

As a first time Rails World visitor, I was really impressed with the energy and vibes.

Let’s get into it.

Arrival: Wednesday

🇨🇦 I Landed in Toronto after a short two hour flight from Minneapolis. I was feeling excited to promote my book, meet attendees, and give away 8 copies over the next few days. I was feeling grateful!

Rails World Conference 2024 Landed in Toronto, let’s go!

To celebrate my book launch and successfully striking out on my own as an independent Postgres and Rails consultant, I hosted a happy hour gathering of Postgres Fans. We had a good turnout, conversations, and new and strengthened connections. We didn’t talk about Postgres much, but the event was a success! 🙌

Rails World Conference 2024 Party time, Postgres fans Happy Hour at The Queen & Beaver Public House

This was my first time hosting an event like this. I was able to sponsor a round of drinks and appetizers thanks to the success in book sales and my consulting business.

It felt great to bring folks together and I appreciated everyone coming out!

Afterwards, most of the group walked over to the Shopify pre-registration event to get badges, hang out, and grab dinner.

Rails World Conference 2024 Pre-registration party by Shopify, with John and Jesper

Conference Day 1: Thursday

In the kick-off, I appreciated the overview of the Rails Foundation activities, and was excited to see my GitHub handle up on screen briefly along with some friends! In the last year, I made s

[...]

PostgreSQL 17: JSON_TABLE(), MERGE with RETURNING, and Updatable Views
Posted by Andrew Atkinson on 2024-10-17 at 00:00

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 is a good starting point, as it covers a breadth of items.

In this post, we’ll pick out three items, and create some runnable examples with commands that can be copied and pasted into a Postgres 17 instance.

Let’s dive in!

PostgreSQL 17 via Docker

To easily try out PostgreSQL 17, let’s use Docker.

docker pull postgres:17

docker run --name my-postgres-container -e POSTGRES_PASSWORD=mysecretpassword -d postgres:17

docker exec -it my-postgres-container psql -U postgres

As an aside: for macOS, if you’re interested in using pg_upgrade, please see the post in-place upgrade from Postgres 14 to 15 as an example on how to upgrade your locally installed, earlier version.

From here, we’ll assume you’re connected to a 17 instance, ready to run commands.

SQL/JSON and JSON_TABLE

Postgres supports SQL/JSON, which is like a selector style expressional language that provides methods to extract data from JSON.

SQL/JSON path expressions specify item(s) to be retrieved from a JSON value, similarly to XPath expressions used for access to XML content.

When we combine SQL/JSON expressions with a new function JSON_TABLE(), we can do powerful transformations of JSON text data into query results that match what you’d get from a traditional table.

Let’s take a look at an example!

Each of these examples will be on this PostgreSQL 17 branch of my pg_scripts repo.

We’ll create a table “books” and insert a row into it. The books table has a “data” column with the “jsonb” data type.

Create the table:

CREATE TABLE IF NOT EXISTS books (
    id integer NOT NULL,
    name varchar N
[...]

Handling BLOBs In PostgreSQL
Posted by Stefanie Janine on 2024-10-15 at 22:00

BLOBs In PostgreSQL

Implementation

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.

Limitations

BYTEA 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.

What Can Be Stored In 1 GB

You can store up to 3,000 jpg images with a size of up to 5 MB in 1 GB.
An English protestant bible contains roughly 809,000 letters. That results in 1,236 bible texts that can be stored in 1 GB.

That should give you an impression what 1 GB of data is.

More Than 1 GB BLOBs

Binary data can be stored in files, that PostgreSQL is referencing to. That way the binary files are stored on the file system and can be created, updated, or deleted. The functions that can be used are documented, the usage is supported in ODBC, .NET, JDBC, or libpq.

The files are referenced in a table with an OID.

Limitations

But this method has limits, too. 4 TB is what PostgreSQL can handle as maximum per file. The maximum size of all objects is 32 TB

As OIDs are 32 bit numbers, there is a maximum of files, that can be handled, it is 2^32.

Documentation

In addtion to the documentation about Binary Data Types there is a PostgreSQL Wiki Page.
There are listed some pros and cons about BLOBs in databases.

Why I Believe You Should Not Use BLOBs In A Database

Even while the PostgreSQL Wiki](https://wiki.postgresql.org/wiki/BinaryFilesInDB) has some pros, I believe the reasons that are speaking against database BLOBs are more the important ones.

Getting files through databases involves another layer handling the data: the database.
That is obviously comming with cost:

  • It requires more memory than normal data read/writes
  • Backups will take significantly longer
  • Longer transactions for accessing or storing binary data
  • May become
[...]

Is pg_dump a Backup Tool?
Posted by Robert Haas in EDB on 2024-10-15 at 20:03
Recently, I've been hearing a lot of experienced PostgreSQL users reiterate this line: "pg_dump is not a backup tool." In fact, the documentation has recently been updated to avoid saying that it is a backup tool, to widespread relief. Experienced PostgreSQL users and developers have been publicly called out for having the temerity to assert that pg_dump is, in fact, a backup tool. I find this narrative deeply frustrating, for two reasons.Read more »

Dealing with trigger recursion in PostgreSQL
Posted by Laurenz Albe in Cybertec on 2024-10-15 at 13:04

A father tells his son to hit back the next time, but the son is afraid of trigger recursion.
© 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.

The beginner's mistake leading to trigger recursion

Triggers are the only good way to change data automatically. Constraints are the “policemen” that make sure rules are not voilated, but triggers are the workers that make the data stay in line. A beginner who has understood that may (quite correctly) wish to use a trigger to set the updated_at column in the following table:

CREATE TABLE data (
   id bigint
      GENERATED ALWAYS AS IDENTITY
      PRIMARY KEY,
   value text NOT NULL,
   updated_at timestamp with time zone
      DEFAULT current_timestamp
      NOT NULL
);

The column default will set updated_at when the row is inserted, but won't change the value when you update the row. For that, our beginner writes a trigger:

CREATE FUNCTION set_updated_at() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   UPDATE data
   SET updated_at = current_timestamp
   WHERE data.id = NEW.id;

   RETURN NEW;
END;$$;

CREATE TRIGGER set_updated_at
   AFTER UPDATE ON data FOR EACH ROW
   EXECUTE FUNCTION set_updated_at();

But that won't work as intended:

INSERT INTO data (value) VALUES ('initial') RETURNING id;

 id 
════
  1
(1 row)

UPDATE data SET value = 'changed' WHERE id = 1;
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "UPDATE data
   SET updated_at = current_timestamp
   WHERE data.id = NEW.id"
PL/pgSQL function set_updated_at() line 2 at SQL statement
SQL statement "UPDATE data
   SET updated_at = current_timestamp
   WHERE data.id = NEW.id"
PL/pgSQL function set_updated_
[...]

PostgreSQL Anonymizer 2.0 - Generating Fake Data
Posted by damien clochard in Dalibo on 2024-10-15 at 10:17

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”).

Photo Credit Markus Spiske

Logo PostgreSQL Anonymizer

Why is it important?

PostgreSQL Anonymizer 2.0 offers a wide range of functions to generate fake but realistic data. These functions are useful for writing masking rules and replacing sensitive data with data that “looks real.”

But that’s not the only benefit of these functions. In fact, they can also be used from the very first steps of a new project: when designing a data model, it is essential to “populate” the tables with data so as not to start development “empty-handed.”

Consider a new application that needs to rely on a classic customer table:

CREATE TABLE public.customer (
    id          INT PRIMARY KEY,
    firstname   TEXT,
    lastname    TEXT,
    email       TEXT
);

We want to insert 2000 people into this table, but how can we do it since the application doesn’t exist yet?

Why is it complicated?

Obviously, inserting John Doe 2000 times into the table is not really an option! The challenge is to produce realistic and context-appropriate data.

For example, if the application is intended for adult users, we want the birth dates to be between 1950 and 2006.

This is where PostgreSQL Anonymizer comes into play with its wide range of randomization functions and fake data generators.

INSERT INTO customer
SELECT
        i*100+pg_catalog.random(0,99), -- avoid collisions !
        anon.dummy_first_name(),
        anon.dummy_last_name(),
        anon.dummy_free_email()
FROM generate_series(1,2000) i;

Note: The random(x,y) function is one of the new features of PostgreSQL 17! For earlier versions, the anon.random_int_between(x,y) function is an equivalent alternative.

In total, PostgreSQL Anonymizer provides more than 70 fake data

[...]

PGDay Lowlands 2024 Reflections
Posted by Pavlo Golub in Cybertec on 2024-10-15 at 05:13

Introduction

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. 🙂

If you want to read about PGDay UK 2024, I recommend the blog post of my friend Stefan Fercot. Thank God he did a great job, so I can focus only on one event instead of two! 😅

A Special Connection with Amsterdam

PGDay Lowlands 2024 marks the first year of this event, but Amsterdam holds a special place in my professional journey. My first ever PostgreSQL community conference was in this beautiful city, way back in 2011, during pgconf.eu. So, Amsterdam isn't just another city for me; it's a key part of my community and professional life. I always love coming back here.

The conference schedule was pretty packed, so I only had a little time to visit my favorite places in Amsterdam this time. But the experience of the conference itself made up for that.

My Talk: "Customizing the Wordle Game Experience with PostgreSQL"

My presentation was titled "Customizing the Wordle Game Experience with PostgreSQL." Despite the fun name, the talk covered some serious and interesting topics, like full-text search dictionaries and customizing psql. The best part? We ended the talk by playing Wordle with the audience, where they had to guess PostgreSQL 5-letter keywords. I'm happy to report that Gianni Ciolli is officially the best keyword professional among us! 😄

A Second Chance

I initially presented this talk once at PgConf in New York, but unfortunately, my slot was in parallel with Bruce Momjian's session. Naturally, many attendees chose to go to his talk (honestly, I would have done the same!). That left me with little feedback and some doubts about the talk's appeal. So, I'm incredibly

[...]

Tuning the glibc memory allocator (for Postgres)
Posted by Tomas Vondra on 2024-10-14 at 10:00

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.

pgsql_tweaks 0.10.7 Released
Posted by Stefanie Janine on 2024-10-13 at 22:00

pgsql_tweaks is a bundle of functions and views for PostgreSQL

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.

General changes

It is only a minor release, no function or view has been changed.

What has been changed is the creation of the release files. Especially the test retult output. The file test/sql/out/pgsql_tweaks_test--0.10.7.out is now created with catptions and messages set to en_EN with the parameter LC_MESSAGES.
This does make the test results comparable.
In the README it is also explained, how to compare local test results with the released version test result.

In addtion the description on how to create the extension from source has been extended.

Contributions of w/c 2024-10-07 (week 41)
Posted by Jimmy Angelakos in postgres-contrib.org on 2024-10-12 at 19:45

SQL/JSON is here! (kinda “Waiting for Pg 17”)
Posted by Hubert 'depesz' Lubaczewski on 2024-10-11 at 21:37
Amazing. Awesome. Well, but what is it? We could store json data in Pg since PostgreSQL 9.2 – so it's been there for over 12 years now. How is the new shiny thing different? What does it allow you to do? Let's see if I can shed some light on it… For starters: SQL/JSON is … Continue reading "SQL/JSON is here! (kinda “Waiting for Pg 17”)"

CNPG Recipe 14 - Useful Command-Line Tools
Posted by Gabriele Bartolini in EDB on 2024-10-11 at 16:53

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.

Convert JSON into Columns and Rows with JSON_TABLE
Posted by Paul Ramsey in Crunchy Data on 2024-10-11 at 14:30

JSON_TABLE, new in Postgres 17

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. So you can easily take JSON data feeds and work with it like you would any other Postgres data in your database.

Shaking the Earth with JSON_TABLE

A few days ago, I was awakened in the middle of the night when my house started to shake. Living in the Cascadia subduction zone, when things start to shake I wake up really fast, because you never know if this one is going to be the Big One.

Fortunately this one was only a little one, 4.0 magnitude quake several miles to the north of the city, captured and memorialized in its own USGS earthquake page, almost as soon as it had finished shaking.

The USGS keeps a near-real-time collection of information about the latest quakes online, served up in a variety of formats including GeoJSON.

alt

The weekly feed of magnitude 4.5 quakes has a nice amount of data in it.

If we could import this feed into the database, we could use it for other queries, like finding potential customers to sell tents and emergency supplies to! (When the big one hits me, sell me some tents and emergency supplies.)

This readily available GeoJSON earthquake file seems like the perfect chance to try out the new JSON_TABLE. And maybe give me something to do in the middle of night.

Retrieving a JSON file with HTTP

The first step is to retrieve the feed. The simplest way is to use the http extension, which provides a simple functional API to making HTTP requests.

CREATE EXTENSION http;

The http_get(url) function returns an http_response, with a status code, content_type, headers and content. We could write a wrapper to check the status code, but for this example we will just assume the feed works and look at the content.

SELECT jsonb_pretty(content::jsonb)
  FRO
[...]

PostgreSQL Berlin October 2024 Meetup
Posted by Andreas Scherbaum on 2024-10-11 at 02:36

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.

More Release Note Details
Posted by Bruce Momjian in EDB on 2024-10-10 at 20:30

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.:

<!--
Author: David Rowley <drowley(at)postgresql(dot)org>
2024-01-23 [b262ad440] Add better handling of redundant IS [NOT] NULL quals
Author: David Rowley <drowley(at)postgresql(dot)org>
2024-04-12 [3af704098] Fix IS [NOT] NULL qual optimization for inheritance tabl
-->

Continue Reading »

Transform PostgreSQL into a Columnar Database Using Citus
Posted by semab tariq in Stormatics on 2024-10-10 at 09:23

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.

The post Transform PostgreSQL into a Columnar Database Using Citus appeared first on Stormatics.

Comparing Columnar to Heap Performance in Postgres with pg_timeseries
Posted by Shaun Thomas in Tembo on 2024-10-10 at 09:00
The pg_timeseries extension uses Hydra Columnar storage for historical data. Check this out if you want to know how this kind of storage compares to standard Postgres Heap storage during database writes.

PostgreSQL 17 - A Major Step Forward in Performance, Logical Replication and More
Posted by Ahsan Hadi in pgEdge on 2024-10-10 at 07:01

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.  In this blog I'll describe a couple of new performance features that you'll find in Postgres 17 as well as another important logical replication feature that I didn't cover in my earlier blog of this series.PostgreSQL has grown remarkably over the years, and with each major release has become a more robust, reliable, and responsive database for both mission critical and non-mission critical enterprise applications. The global and vibrant PostgreSQL community is contributing to PostgreSQL success, diligently ensuring that all changes are carefully scrutinized and reviewed before they are added to the project source code. It is also very encouraging to see big technology names like Microsoft, Google, Apple, and others investing in Postgres by developing in-house expertise and giving back to the open source community.Improvements to logical replication are paving the way for adding distributed PostgreSQL support to the core functionality. Distributed PostgreSQL refers to the implementation of PostgreSQL in a distributed architecture, allowing for enhanced scalability, fault tolerance, and improved performance across multiple nodes. A pgEdge fully distributed PostgreSQL cluster already provides essential enterprise features like improved performance with low latency, ultra-high availability, data residency, and fault tolerance. Now without further adieu let's discuss some PostgreSQL 17 performance features:     

Improved Query Performance with Materialized CTEs

Common Table Expressions (CTEs) in PostgreSQL are temporary result sets that can be referenced within a , , , or  statement. They enhance the readability and organization of complex queries and can be recursive, making them particularly useful for hierarchical [...]

sparql_fdw Foreign Data Wrapper Tested Against PostgreSQL 17
Posted by Stefanie Janine on 2024-10-09 at 22:00

sparql_fdw Forreign Data Wrapper

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 17

As multicorn2 is already working with PostgreSQL 17, I tested the sparql_fdw, too.

It worked like a charm and I have added PostgreSQL 17 to the supported versions in the README file, the merge request is pending.

Enhanced Postgres Release Notes
Posted by Greg Sabino Mullane in Crunchy Data on 2024-10-09 at 21:30

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.

You may have missed it if you were scanning the release notes, but after the end of each specific item in the release note is a small “section” symbol which looks like this: §. Each of these symbols is a link leading to the relevant commit for that item. Here’s what it looks like on the Postgres 17 release notes page:

pg_commit_plain.png

Clicking the section symbol will send you to the GIT link for each individual patch, for example, this one:

hackers email messsage

Note that there’s a “Discussion” link in each commit linking back to the full thread on the hackers channel.

Writing the release notes is hard work, and involves a good bit of debate in the community. We have to make sure we list all the changes, in a concise yet comprehensible manner, and decide what level of detail to include. Oftentimes, this level is not sufficient for people interested in learning about this feature. That’s where these new commit links are invaluable. They link to the git commit, which not only lets you see the exact code changes that were made, but show the actual commit message, which has more detail than what can be provided in the release notes.

Postgres Notes in Lots of Places

Postgres release notes appear in lots of different places so this addition will surely make its way into other downstream projects. This new link also now appears on “postgres all versions” - a project that I maintain that collates the information from all of the release notes for every version of Postgres (over 500 now!) into a single page. To make the link more visible and easier to use, I converted it to a larger Unicode scroll symbol, then added some tooltip tech to make it show information about the link like so:

[...]

PGXN Certifications RFC
Posted by David Wheeler in Tembo on 2024-10-09 at 16:26

A couple weeks ago, I drafted PGXN RFC–5 — Release Certification, which proposes to replace the simple inclusion of a SHA-1 hash digests in PGXN release META.json files with a JWS-signed release payload. From the introduction:

This RFC therefore proposes to extend v2 distribution metadata with a single additional property, certs, that contains one or more certifications that attest to the authenticity or other characteristics of a release on PGXN.

The certs value is an object that contains at least one property, pgxn, which itself contains a PGXN-generated RFC 7515 JSON Web Signature in the JWS JSON Serialization format. The pgxn property will allow clients not only to assemble the release URL and verify the downloaded file against checksums, but also validate it against a public key provided by PGXN.

The design allows multiple signatures, certifications, or other attestations, which in the future MAY allow authors or other entities to sign releases with their own keys. The new format appends a structure such as this to the distribution META.json file:

{
  "certs": {
    "pgxn": {
      "payload": "eyJ1c2VyIjoidGhlb3J5IiwiZGF0ZSI6IjIwMjQtMDktMTNUMTc6MzI6NTVaIiwidXJpIjoiZGlzdC9wYWlyLzAuMS43L3BhaXItMC4xLjcuemlwIiwiZGlnZXN0cyI6eyJzaGE1MTIiOiJiMzUzYjVhODJiM2I1NGU5NWY0YTI4NTllN2EyYmQwNjQ4YWJjYjM1YTdjMzYxMmIxMjZjMmM3NTQzOGZjMmY4ZThlZTFmMTllNjFmMzBmYTU0ZDdiYjY0YmNmMjE3ZWQxMjY0NzIyYjQ5N2JjYjYxM2Y4MmQ3ODc1MTUxNWI2NyJ9fQ",
      "signature": "cC4hiUPoj9Eetdgtv3hF80EGrhuB__dzERat0XF9g2VtQgr9PJbu3XOiZj5RZmh7AAuHIm4Bh-rLIARNPvkSjtQBMHlb1L07Qe7K0GarZRmB_eSN9383LcOLn6_dO--xi12jzDwusC-eOkHWEsqtFZESc6BfI7noOPqvhJ1phCnvWh6IeYI2w9QOYEUipUTI8np6LbgGY9Fs98rqVt5AXLIhWkWywlVmtVrBp0igcN_IoypGlUPQGe77Rw"
    }
  }
}

Review and feedback would be very much appreciated, especially on the list of unresolved questions toward the end.

Thanks to David Christensen and Steven Miller for the early review

[...]

Top posters

Number of posts in the past two months

Top teams

Number of posts in the past two months

Feeds

Planet

  • Policy for being listed on Planet PostgreSQL.
  • Add your blog to Planet PostgreSQL.
  • List of all subscribed blogs.
  • Manage your registration.

Contact

Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.