Category: Hans-Juergen Schoenig
PostgreSQL: CREATE STATISTICS – advanced query optimization
PostgreSQL query optimization with CREATE STATISTICS is an important topic. Usually, the PostgreSQL optimizer (query planner) does an excellent job. This is not only true for OLTP but also for data warehousing. However, in some cases the optimizer simply lacks the information to do its job properly. One of these situations has to do with […]
PostgreSQL: ANALYZE and optimizer statistics
Our PostgreSQL 24×7 support team recently received a request from one of our customers who was facing a performance problem. The solution to the problem could be found in the way PostgreSQL handles query optimization (specifically, statistics). So I thought it would be nice to share some of this knowledge with my beloved readers. The […]
Patroni Environment Setup: PostgreSQL High Availability for Windows
PostgreSQL High-Availability has been one of the most dominant topics in the field for a long time. While there are many different approaches out there, Patroni seems to have become one of the most dominant solutions currently in use out there. Many database clusters are running on Linux. However, we have seen some demand for […]
Upgrading and updating PostgreSQL
Recently, PostgreSQL 13 was released. People are asking what are best ways upgrading and updating PostgreSQL 12 or some other version to PostgreSQL 13. This blog post covers how you can move to the latest release. Before we get started, we have to make a distinction between two things: Updating PostgreSQL Upgrading PostgreSQL Let’s take […]
BLOB cleanup in PostgreSQL
PostgreSQL offers a nice BLOB interface which is widely used. However, recently we came across problems faced by various customers, and it makes sense to reflect a bit and figure out how PostgreSQL handles BLOBs – and especially BLOB cleanup. Using the PostgreSQL BLOB interface In PostgreSQL, you can use various means to store binary […]
pg_crash: Crashing PostgreSQL automatically
PostgreSQL is a rock-solid database that is widely used for highly critical applications. Bugs are rarely seen. However, everything fails once in a while. The entire stack (hardware, operating system, etc) are subjected to occasional failure. Thus one has to prepare for that. The main problem is: How can one simulate failure? In many cases […]
PostgreSQL clustering: vip-manager
Patroni has dramatically simplified the setup of the PostgreSQL High-Availability cluster. Personally I consider Patroni to be by far the best tool out there to cluster PostgreSQL and to ensure HA in the most reliable and straightforward way possible. The main questions asked by many people are: How can you make a PostgreSQL cluster transparent? […]
PostgreSQL: Foreign keys and insertion order in SQL
In SQL, the concept of foreign keys is an important one that can be found in all professional databases used in the industry. The core idea is to prevent your PostgreSQL database from storing inconsistent data by enforcing constraints ensuring the correctness of your tables (at least as far as relations between objects are concerned). […]
zheap: Inspecting storage sizes
To dig a bit deeper into zheap and PostgreSQL storage technology in general I decided to provide some more empirical information about space consumption. As stated in my previous blog post about zheap is more efficient in terms of storage consumption. The reasons are: The tuple header is much smaller Alignment has been improved The […]
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 […]
shared_buffers: Looking into the PostgreSQL I/O cache
The PostgreSQL caching system has always been a bit of a miracle to many people and many have asked me during consulting or training sessions: How can I figure out what the PostgreSQL I/O cache really contains? What is in shared buffers and how can one figure out? This post will answer this kind of […]
Setting PostgreSQL configuration parameters
A lot has been written about configuring postgresql.conf, postgresql.auto.conf and so on. However, sometimes it requires to take a second look in order to understand, how PostgreSQL really handles configuration parameters. You will notice that PostgreSQL configuration offers more than meets the eye at first glance. So let us dive into PostgreSQL GUCs and configuration […]