Category: Hans-Juergen Schoenig
Hans-Jürgen Schönig has worked with PostgreSQL since the 90’s. He is the CEO and technical lead of CYBERTEC PostgreSQL International, a market leader in the field. He’s served countless customers around the globe since the year 2000. He is also the author of the well-received “Mastering PostgreSQL” book series, as well as several other books about PostgreSQL replication and administration.
ALTER TABLE … ADD COLUMN … done right in PostgreSQL
Running ALTER TABLE … ADD COLUMN can have side effects which have the potential to cause serious issues in production. Changing data structures is an important issue and often comes up, therefore it’s important to understand what is really going on. Let’s dive in and see how to run ALTER TABLE … ADD COLUMN in […]
Column order in PostgreSQL does matter
I’ve recently seen some really broad tables (hundreds of columns) in a somewhat inefficiently structured database. Our PostgreSQL support customer complained about strange runtime behavior which could not be easily explained. To help other PostgreSQL users in this same situation, I decided to reveal the secrets of a fairly common performance problem many people don’t […]
Handling Bonus Programs in SQL
Bonus cards, “Miles & more”, bonus points – don’t we all love and hate them at the same time? Recently we had an interesting use case which made me think about sharing some of the techniques we used in this area to reduce client code by writing some clever SQL. This post will show you […]
PostgreSQL 15: Using MERGE in SQL
It’s been many years since the first ideas to implement the MERGE command surfaced on the PostgreSQL mailing list. Now this important feature has finally made it into PostgreSQL core, in PostgreSQL 15. To show people how this vital command works, I have decided to come up with a technical preview to introduce my readers […]
Find and fix a missing PostgreSQL Index
Spot a missing PostgreSQL index Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible. 🙂 However, if you want to ensure that your database performs well and if you are generally not in favor of user complaints – better watch out […]
Multiranges in PostgreSQL 14
Understanding Multiranges Range types have been around in PostgreSQL for quite some time and are successfully used by developers to store various kinds of intervals with upper and lower bounds. However, in PostgreSQL 14 a major new feature has been added to the database which makes this feature even more powerful: multiranges. To help you […]
PostgreSQL: mapping timestamps (date_bin)
date_bin and timestamps in PostgreSQL Date and time are relevant to pretty much every PostgreSQL application. A new function was added to PostgreSQL 14 to solve a problem which has caused challenges for many users out there over the years: How can we map timestamps to time bins? The function is called date_bin. What people […]
PostgreSQL: Creating checksums for tables
There are certain types of questions which accompany any PostgreSQL consultant throughout his or her entire career. Invariably, one of those questions is: “How can we compare the content of two tables?” This is an important thing to know, because we might want to ensure that data is identical after a migration, or perhaps we […]
Manage encryption keys with PostgreSQL TDE
Manage encryption keys with PostgreSQL TDE PostgreSQL TDE is a version of PostgreSQL which offers on-disk encryption for PostgreSQL. Currently we at CYBERTEC maintain this piece of software for public use. What we have noticed recently is that people kept asking about how to integrate PostgreSQL TDE with existing key management solutions. To help you […]
zheap UNDO logs discarding in PostgreSQL
Some time ago, I posted some information about zheap, a storage engine for PostgreSQL. The idea behind zheap is to make UPDATE statements run faster in order to keep table bloat under control. Currently, PostgreSQL copies data on UPDATE and puts the copy of the row into the same data file. While this isn’t a […]
pg_rewrite: PostgreSQL Table Partitioning
PostgreSQL table partitioning is by far one of the most popular new PostgreSQL features. Many people look to partitioned tables as a way to improve performance and broaden scalability. However, partitioning comes with a little problem: How can you partition an existing table without locking up the database? The answer is: pg_rewrite can help you […]
Primary Keys vs. UNIQUE Constraints in PostgreSQL
Most of my readers will know about primary keys and all kinds of table constraints. However, only a few of you may have ever thought about the difference between a primary key and a UNIQUE constraint. Isn’t it all just the same? In both cases, PostgreSQL will create an index that avoids duplicate entries. So […]