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.
Reproducible builds: a PostgreSQL query optimization example
This post shows how to optimize a slow query that came out of the Reproducible Builds project. The Reproducible Builds initiative aims to make software compilation entirely deterministic, with no variation in the output when the build is run again. This makes software supply chain attacks much harder, but has also advantages for software quality […]
PostGIS, ArcGIS Enterprise and the Tour de France route
PostGIS and ArcGIS Enterprise: From Annemasse to Morzine For those who sadly missed our after-business workshop together with our new partner Synergis in June 2023, you now have the chance to catch up and learn more about how PostGIS and ArcGIS Enterprise fit and work together. I’ll not bore you with theoretical stuff but showcase […]
Subqueries and performance in PostgreSQL
© Laurenz Albe 2023 SQL allows you to use subqueries almost anywhere where you could have a table or column name. All you have to do is surround the query with parentheses, like (SELECT …), and you can use it in arbitrary expressions. This makes SQL a powerful language – and one that can be […]
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 […]