Category: How to
Our “how to” blogs give you the best free PostgreSQL information:
How to improve your Postgres code, complete administration tasks with step-by-step instructions, speed up query performance or get your database performance up to top speed. Everything you need to know is written right here in our blog by top experts who deal with PostgreSQL every day, and test their knowledge out personally with major clients who really need the best we have to offer. Take advantage of our top knowledge – browse our blogs for your favorite topics.
In case you need assistance, we are here for you. Get in touch with us and let us know what you need.
View Access Logging – Fix it in PostgreSQL
By Kirk Wolak Continuous Improvement is an important part of reducing technical debt. Over 30 years our active database has collected some technical debt. We wanted to rename all of our views to be consistently named. Besides that, we had a lack of code reviews for what is happening in the database. The latter situation […]
Memory context: private memory management in PostgreSQL
© Laurenz Albe 2023 PostgreSQL uses shared memory for data shared between processes. With the exception of the dynamic shared memory segments used for exchanging data between parallel workers, the server allocates shared memory with a fixed size when it starts. But each PostgreSQL backend process also has to manage private memory to process SQL […]
Unexpected downsides of UUID keys in PostgreSQL
There are various compelling reasons to use universally unique identifiers (UUID) as primary keys. Two examples are: To be able to generate keys independently of the database To move sets of related records between different databases without having to deal with renumbering everything However, like everything good in life, UUID’s come with their own downsides. […]
Use HOT, so CLUSTER won’t rot in PostgreSQL
© Laurenz Albe 2023 CLUSTER is sometimes the last resort to squeeze performance out of an index scan. Normally, you have to repeat CLUSTER regularly to maintain good performance. In this article, I will show you how you can get away without re-CLUSTERing even in the face of concurrent UPDATEs. Thanks to Steven Hulshof for […]
What is a schema in PostgreSQL?
One way to organize data in PostgreSQL is to make use of schemas. What is a schema in PostgreSQL? And more importantly: What is the purpose of a schema and how can schemas be used to make your life easier? Let’s dive in and find out. The purpose of a schema Before you figure out […]
ERROR: invalid byte sequence – Fix bad encoding in PostgreSQL
(Inscription seen on a T-shirt) It’s annoying to get error messages caused by encoding problems. But it is more annoying not to get error messages and end up with bad data in the database. I’ll show you how to fix bad encoding. Some basic facts about encoding in PostgreSQL Each PostgreSQL database has a server […]
PostgreSQL ALTER DEFAULT PRIVILEGES – permissions explained
© Laurenz Albe 2023 Many people have heard that ALTER DEFAULT PRIVILEGES makes it easy to allow other users access to tables. But then, many people don’t understand the command in depth, and I hear frequent complaints that ALTER DEFAULT PRIVILEGES does not work as expected. Read on if you want to know better! Default […]
Let’s party and upgrade PostgreSQL and PostGIS together
Some time ago, I presented a typical approach to upgrade PostGIS under Ubuntu. Please take a look at my previous post to recall how we accomplished this task. Today, we’ll extend this procedure by upgrading PostgreSQL and PostGIS in one row to replay a quite realistic scenario: A customer runs PostgreSQL 13 with an […]
Docker and sudden death for PostgreSQL
© Laurenz Albe 2023 This is a short war story from a customer problem. It serves as a warning that there are special considerations when running software in a Docker container. The problem description The customer is running PostgreSQL in Docker containers. They are not using the “official” image, but their own. Sometimes, under conditions […]
Underscores in numeric constants in PostgreSQL 16
SQL and numeric constants NEW in PostgreSQL 16 – support for underscores in integer and numeric constants! I already wrote about support for different notations in the upcoming PostgreSQL 16. This new major version also implements the SQL:202x standard (draft), allowing you to use underscore separators for integers and numeric constants. Using underscore separators in […]
Row locks in PostgreSQL
© Laurenz Albe 2023 The PostgreSQL documentation has some information about row locks. But typically, you cannot see them in pg_locks, and not everybody knows how they work and how to track and debug row locks. This article intends to give some insight into how PostgreSQL row locks work “under the hood”. Why are there […]
Breaking your PostgreSQL database with bad CHECK constraints
© Laurenz Albe 2023 I am on a spree! After writing about breaking your database and transaction ID wraparound, here I am writing about damage caused by CHECK constraints! Trust me, I don’t want to destroy PostgreSQL databases. It’s just that this Twitter message was brought to my attention and triggered memories of other reports […]