Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 39 Sekunden

Jędrzej Biedrzycki: Poor man’s fuzzy search

11. Oktober 2020 - 20:39
0. The problem

When I hear the term fuzzy searching, I think of high computation cost, but this is not always the case. In the great book Art of Computer Programming by D. Knuth, vol. 3, ch. 6: Searching we can read about the soundex algorithm. It was originally used to deal with misspelled English surnames. The other usages could involve for example:

Dimitri Fontaine: What’s new in pg_auto_failover 1.4 for Postgres high availability

10. Oktober 2020 - 19:02

Postgres is an amazing RDBMS implementation. Postgres is open source and it’s one of the most standard-compliant SQL implementations that you will find (if not the most compliant.) Postgres is packed with extensions to the standard, and it makes writing and deploying your applications simple and easy. After all, Postgres has your back and manages all the complexities of concurrent transactions for you.

In this post I am excited to announce that a new version of pg_auto_failover has been released, pg_auto_failover 1.4.

Jonathan Katz: PostgreSQL Monitoring for Application Developers: The DBA Fundamentals

10. Oktober 2020 - 17:52

I am an accidental DBA, with a huge emphasis on "accidental." I came to PostgreSQL as an application developer who really liked to program with SQL and use the database to help solve my problems. Nonetheless, these systems would enter into production, and as such I had to learn to support them.

David Z: Heap file and page in details

10. Oktober 2020 - 1:56
1. Overview

PostgreSQL is a great open source database, and many users chose it because of the efficiency of its central algorithms and data structures. As a software developer, I was always curious about how each part was done, such as the physical files storage. The reason is that I always see a lot of files and folders were created after a simple initdb command. For example,

Viorel Tabara: An Overview of PostgreSQL Query Caching & Load Balancing

9. Oktober 2020 - 19:32

The topic of caching appeared in PostgreSQL as far back as 22 years ago, and at that time the focus was on database reliability.

Michał Mackiewicz: Scaling row level security to group roles

9. Oktober 2020 - 13:07

Row level security is a great Postgres feature that allows to grant privileges to selected rows only, without having to create additional data structures. The common setup is to add a column with users’ names, and a policy that compares this column value with CURRENT_USER:

Egor Rogov: Indexes in PostgreSQL — 10 (Bloom)

9. Oktober 2020 - 2:00

In the previous articles we discussed PostgreSQL indexing engine and the interface of access methods, as well as B-trees, GiST, SP-GiST, GIN, RUM, and

Andres Freund: Analyzing the Limits of Connection Scalability in Postgres

8. Oktober 2020 - 20:30

One common challenge with Postgres for those of you who manage busy Postgres databases, and those of you who foresee being in that situation, is that Postgres does not handle large numbers of connections particularly well.

While it is possible to have a few thousand established connections without running into problems, there are some real and hard-to-avoid problems.

Muhammad Usama: Configuring Pgpool-II watchdog: It’s going to be a lot easier

8. Oktober 2020 - 13:13

Watchdog is the high availability component of Pgpool-II. Over the past few releases watchdog has gotten a lot of attention from the Pgpool-II developer community and received lots of upgrades and stability improvements.

One of the not very strong areas of pgpool-II watchdog is its configuration interface. Watchdog cluster requires quite a few config settings on each node, and it’s very easy to get it wrong and hard to debug.

For example in a three-node Pgpool-II cluster, we normally require to configure the following parameters in each pgpool.conf

Jonathan Katz: PostgreSQL Monitoring for Application Developers: The Vitals

8. Oktober 2020 - 11:17

My professional background has been in application development with a strong affinity for developing with PostgreSQL (which I hope comes through in previous articles).

Tomas Vondra: OLTP performance since PostgreSQL 8.3

8. Oktober 2020 - 11:00
A couple years ago (at the 2014 in Madrid) I presented a talk called “Performance Archaeology” which showed how performance changed in recent PostgreSQL releases. I did that talk as I think the long-term view is interesting and may give us insights that may be very valuable. For people who actually work on PostgreSQL […]

Andres Freund: Measuring the Memory Overhead of a Postgres Connection

8. Oktober 2020 - 4:01
One fairly common complaint about postgres is that is that each connection uses too much memory. Often made when comparing postgres' connection model to one where each connection is assigned a dedicated thread, instead of the current model where each connection has a dedicated process. To be clear: This is a worthwhile discussion to have. And there are several important improvements we could make to reduce memory usage. That said, I think one common cause of these concerns is that the easy ways to measure the memory usage of a postgres backend, like top and ps, are quite misleading.

Bruce Momjian: Community Impact of 2nd Quadrant Purchase

7. Oktober 2020 - 18:30

Last week 2nd Quadrant was purchased by EDB. While this is certainly good news for these companies, it can increase risks to the Postgres community.

Hans-Juergen Schoenig: zheap: Reinvented PostgreSQL storage

7. Oktober 2020 - 9:00

In PostgreSQL table bloat has been a primary concern since the original MVCC model was conceived. Therefore we have decided to do a series of blog posts discussing this issue in more detail. What is table bloat in the first place? Table bloat means that a table and/or indexes are growing in size even if the amount of data stored in the database does not grow at all.

Jonathan Katz: How to Setup PostgreSQL Monitoring in Kubernetes

6. Oktober 2020 - 11:27


You don't need monitoring until you need it. But if you're running anything in production, you always need it.

Andreas 'ads' Scherbaum: Andreas Kretschmer

5. Oktober 2020 - 16:00
PostgreSQL Person of the Week Interview with Andreas Kretschmer: I was born in Meißen, Saxony, Germany, Planet Earth. I’m married and we have 3 wonderful daughters.

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 14 – Support for OUT parameters in procedures

5. Oktober 2020 - 12:10
On 5th of October 2020, Peter Eisentraut committed patch: Support for OUT parameters in procedures   Unlike for functions, OUT parameters for procedures are part of the signature. Therefore, they have to be listed in pg_proc.proargtypes as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE.

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics

5. Oktober 2020 - 8:39
On 11st of September 2020, Alvaro Herrera committed patch: psql: Display stats target of extended statistics   The stats target can be set since commit d06215d03, but wasn't shown by psql.   Author: Justin Pryzby <> Discussion: Reviewed-by: Georgios Kokolatos <> Reviewed-by: Tatsuro Yamada <> Since PostgreSQL 10 we have so called extended statistics.

Bruce Momjian: The Economics of Open Source Contributions

2. Oktober 2020 - 18:45

This long article describes the many challenges of managing open source projects and the mismatch between resource allocation, e.g., money, and the importance of the software to economic activity.

David Christensen: Using CTEs to do a binary search of large tables with non-indexed correlated data

2. Oktober 2020 - 2:00

Photo by Colin Knowles, used under CC BY-SA 2.0.

Query optimization can take different forms depending on the data represented and the required needs. In a recent case, we had a large table that we had to query for some non-indexed criteria. This table was on an appliance that we were unable to modify, so we had to find a way to query efficiently without indexes that would have made it easier.