Neues vom PostgreSQL Planet

In one of the recent PostgresFM episodes, Michael Christofides and Nikolay discussed planning time and what can affect it. One of the obvious negative factors we've discussed is the large number of partitions a partition table might have.

In this post, we're using our Postgres.AI assistant to see how planning time depends on the number of partitions.

On 17th of September 2024, Peter Eisentraut committed patch: Add temporal FOREIGN KEY contraints   Add PERIOD clause to foreign key constraint definitions. This is supported for range and multirange types. Temporal foreign keys check for range containment instead of equality.   This feature matches the behavior of the SQL standard temporal foreign keys, but … Continue reading "Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints"

This post contained content that could not be rendered in the Atom feed. Please use the official post link: https://tembo.io/blog/paradedb-search-analytics

Is it now possible to catch a login event.

PostgreSQL adds the login type for event triggers

PostgreSQL 17 adds a new firing event for event triggers: login. Therefore it is now possible to catch a login attempt on a database.

Caution: this is not the same as Oracle logon triggers, even if it resembles the same functionality to me.

However, thanks to this, is is now possible to get some more information when a login attempt succeeds.

PostgreSQL 17 includes a new (among others) tunable to control the ALTER SYSTEM command.

PostgreSQL 17 allow_alter_system tunable

Among the new excellent features of PostgreSQL 17, one captured my attention: the capability to disable the ALTER SYSTEM command via the tunable [allow_alter_system](https://www.postgresql.org/docs/current/runtime-config-compatible.html#GUC-ALLOW-ALTER-SYSTEM){:target="_blank"}.

There are multiple tools to run benchmarks on Postgres, but pgbench is probably the most widely used one. The workload is very simple and perhaps a bit synthetic, but almost everyone is familiar with it and it’s a very convenient way to do quick tests and assessments. It was improved in various ways (e.g. to do partitioning), but the initial data load is still serial - only a single process does the COPY. Which annoys me - it may take a lot of time before I can start with the benchmarks itself.

© Laurenz Albe 2024

Every year in fall, there is a new PostgreSQL release. After looking at the highlights of PostgreSQL v17, you may think, “what's the big deal?” Quite a few people might even be unhappy about the reminder that they should really upgrade some time soon. Time to explain how wonderful PostgreSQL v17 is!

PostgreSQL Person of the Week Interview with Vasilis Ventirozos: My name is Vasilis Ventirozos. I’m from Athens - Greece. I am 44 years old. A homeworker since 2013, a PostgreSQL DBA and performance engineer since simpler times.

On 17th of September 2024, Peter Eisentraut committed patch: Add temporal PRIMARY KEY and UNIQUE constraints   Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints. These are backed by GiST indexes instead of B-tree indexes, since they are essentially exclusion constraints with = for the scalar parts of the key and && for … Continue reading "Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE constraints"

This month, I'll be hosting a discussion of Thomas Munro's 2024.pgconf.dev talk, Streaming I/O and vectored I/O. As usual, there will be three sessions, and you can use this form to sign up for the session you prefer.

Introduction

In previous blogs, we talked about an overview of PostgreSQL’s table access method API here , how sequential scan is handled within this API here, and how data insertion is handled here.

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.

PostgreSQL has released a new version, with a host of updates and improvement to the world’s most advanced open-source database. In this blog post, I want to take you through the key new features implemented in PostgreSQL 17.

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.

Upgrading your PostgreSQL cluster is an important task to keep your database running smoothly and securely. With each new release, PostgreSQL introduces performance improvements, security patches, and new features that can benefit your system. However, upgrading can be a bit tricky, especially if you're working in a Windows environment, where certain challenges like permissions, service management, and file handling may differ from Linux setups.

In my last blog post, Evolution of Logical Replication, I mentioned the future development of a feature to allow "upgrades of logical replication nodes." The upcoming release of PostgreSQL 17 includes this feature. Previously, after major version upgrades (via pg_upgrade), users couldn't immediately connect and write data to logical replication nodes. This was because the slots were lost during upgrades, preventing replication from continuing.

The PostGIS Team is pleased to release PostGIS 3.5.0! Best Served with PostgreSQL 17 RC1 and GEOS 3.13.0.

This version requires PostgreSQL 12 - 17, GEOS 3.8 or higher, and Proj 6.1+. To take advantage of all features, GEOS 3.12+ is needed. SFCGAL 1.4+ is needed to enable postgis_sfcgal support. To take advantage of all SFCGAL features, SFCGAL 1.5 is needed.

A couple days ago I had a bit of free time in the evening, and I was bored, so I decided to play with BOLT a little bit. No, not the dog from a Disney movie, the BOLT tool from LLVM project, aimed at optimizing binaries. It took me a while to get it working, but the results are unexpectedly good, in some cases up to 40%. So let me share my notes and benchmark results, and maybe there’s something we can learn from it.

This post contained content that could not be rendered in the Atom feed. Please use the official post link: https://tembo.io/blog/self-hosted-ga

As I mentioned in my last blog post, as your cluster grows with multiple standby servers and potentially automated failover (using tools like Patroni), it becomes more practical to set up a dedicated repository host, also known as a dedicated backup server. This backup server can then trigger backups and automatically select the appropriate node in case of failover, eliminating the need for manual intervention.