Neues vom PostgreSQL Planet

This post is about a new PostgreSQL connection pooler that I came across recently: pgcat, which provides some useful features that I have not seen with the other poolers. Please mind the pgcat connection pooler is in beta, and they actually are looking for beta testers! If you are interested, read the pgcat beta test announcement/request.

It’s official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release.

Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code.

If you missed the July CommitFest, our previous article will get you up to speed in no time.

Here are the patches I want to talk about:

The PostGIS development team is pleased to provide security, bug fixes and performance enhancements 3.3.2, 3.2.4, 3.1.8 and 3.0.8 for the 3.3, 3.2, 3.1, and 3.0 stable branches.

This time for sure (h/t Bullwinkle)

The PostGIS Team is pleased to release PostGIS 2.5.9! This is the end-of-life release of the 2.5 branch.

2.5.9

Reducing replication lag with IO concurrency in Postgres 15

PostgreSQL 15 improves crash recovery and physical replication performance of some large and very busy databases by trying to minimise I/O stalls. A standby server might now have an easier time keeping up with the primary.

New features introduced in PostgreSQL 15 allow selective publication of tables' contents within logical replication publications, through the ability to specify column lists and row filter conditions. Let's see how they affect performance.

The last release of PostgreSQL 10 took place on Nov 10, 2022. While there are many articles on the new and exciting features in PostgreSQL, I thought this would be a good time to reflect on the impact of PostgreSQL 10.

PostgreSQL 10 had a lot of firsts. It was the first PostgreSQL release to have the two digit numbering scheme (PostgreSQL 10 vs. PostgreSQL 9.6). It was the first release with logical replication, declarative partitioning, and SCRAM.

Unique indexes are used to ensure that some values or combinations of values are unique. However, they only work when the uniqueness constraint should be enforced on all table rows because it can not be limited to ignore e.g. soft-deleted ones. But with partial indexes, supported by PostgreSQL, this is possible because a WHERE condition can control the included rows of the index. With MySQL, the behaviour to exclude soft-deleted rows in the uniqueness constraint needs to be emulated.

In the first part of the article, we discussed how to achieve minimum security by adding a username and password to Patroni REST API and switching from HTTP traffic to HTTPS. In this article, we will be covering how to secure REST APIs using certificate authentication.

Certificate authentication is the only option if we want to prevent everyone else from accessing the “Safe” endpoints, which otherwise don’t require any credentials.

As the last Postgres release, carrying the number 15, seemed kind of an “anniversary” release to me, I thought it would be fun / useful to run a series of tests to try to gauge the scale of improvements over the last 5 years, i.e. putting v10 and v15 in...

Postgres provides several tools to simplify application development. Triggers, for example, allow one event to automatically invoke subsequent inserts, updates, or deletes to either reinforce business logic, or for auditing purposes. But are they valid for all such scenarios? Are there times when triggers would be detrimental to performance and scalability? What are our alternatives? Well, there’s only one way to find out! [Continue reading...]

The original idea behind relational databases was "structure, then data": you needed to define what the data looked like before being able to insert any content. This strict data structure definition helped keeping datasets in order by verifying data types, referential integrity, and additional business conditions using dedicated constraints.

Rewriting OR is not always the best solution
© Laurenz Albe 2022

There are various areas in PostgreSQL like Partitioning, Logical Replication, Parallel Query, Vacuum, etc. which improve with each new version. In this blog, I'll summarize the various enhancements in Logical Replication that users could see in the recently released PostgreSQL 15.

This blogpost is about a memory optimization that is applied to YugabyteDB version 2.15.3.0 that we applied that can make a huge difference in common situations with large amounts of memory allocated to multiple backends.

I laid the groundwork for investigating PostgreSQL and YugabyteDB here.

This is a further investigation, and some additional remarks.

PostgreSQL Person of the Week Interview with Wilfried Roset: I’m originally from Paris area in France and I live now in Bordeaux with my wife and my 2 years old son. I’m more a SRE responsible for databases from an infrastructure perspective. I love the new term associated with this role DBRE: Databases Reliability Engineer.

For PGSQL Phriday #002, Andreas Scherbaum asked us to consider PostgreSQL backups, how we do them, any recommendations we have, and whether pg_dump is a backup tool. To be honest, I feel pretty unqualified to talk much about backups in Postgres, partially because it’s been a couple of years since I’ve had to manage the … Continue reading "Adventures in PostgreSQL Backups"

It’s time again for the another PGSQL Phriday, this time, the question has been asked: How do you do PostgreSQL backups? Honesty up front. I’m very much just beginning my journey of learning PostgreSQL. I’ve been documenting that learning over here at Simple-Talk (more on the way there), including backups. For this post, I’m not […]

The post Validating Backups: PGSQL Phriday #002 appeared first on Grant Fritchey.

This is an introduction to the hypopg PostgreSQL extension for YugabyteDB 2.15.3.0. Hypopg allows the creation of hypothetical indexes, so indexes that do not really exist. This means this allows you to see what an index would do if it were created, without it actually being created, and therefore not influencing anything on the database. YugabyteDB 2.15.3.0 is a preview version of the YugabyteDB database.

The description from the documentation: