Neues vom PostgreSQL Planet
Jędrzej Biedrzycki: Poor man’s fuzzy search
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
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.
David Z: Heap file and page in details
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
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
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)
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
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.
Andres Freund: Analyzing the Limits of Connection Scalability in Postgres
Muhammad Usama: Configuring Pgpool-II watchdog: It’s going to be a lot easier
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
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
Andres Freund: Measuring the Memory Overhead of a Postgres Connection
Bruce Momjian: Community Impact of 2nd Quadrant Purchase
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
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
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
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 14 – Support for OUT parameters in procedures
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics
Bruce Momjian: The Economics of Open Source Contributions
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
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.
