Get the newest tricks and useful tips about the world of PostgreSQL and Data Science! Our authors and technicians share their knowledge to keep you going with your data.
Searching for a quick tip? Check out our microblog, where we share our insights as they happen – short and simple!
Entity-attribute-value (EAV) design in PostgreSQL – don’t do it!
© Laurenz Albe 2021 Customers have often asked me what I think of “Entity-attribute-value” (EAV) design. So I thought it would be a good idea to lay down my opinion in writing. What is entity-attribute-value design? The idea is not to create a table for each entity in the application. Rather, you store each attribute […]
PostgreSQL on WSL2 for Windows: Install and setup
This post explains how to install PostgreSQL on WSL2 for Windows, apply the necessary changes to PostgreSQL settings, and access the database from the Windows host. Even though this knowledge can be found in different bits and pieces spread out all over the internet, I want to compile a short and straightforward how-to article. I […]
TCP keepalive for a better PostgreSQL experience
© Laurenz Albe 2021 If you’ve heard about TCP keepalive but aren’t sure what that is, read on. If you’ve ever been surprised by error messages like: server closed the connection unexpectedly SSL SYSCALL error: EOF detected unexpected EOF on client connection could not receive data from client: Connection reset by peer then this article […]
WITH HOLD cursors and transactions in PostgreSQL
© Laurenz Albe 2021 Both cursors and transactions are basic ingredients for developing a database application. This article describes how cursors and transactions interact and how WITH HOLD can work around their limitations. We will also see the dangers involved and how to properly use WITH HOLD cursors in a PL/pgSQL procedure. Cursors in PostgreSQL […]
SHOW TABLES in PostgreSQL: what’s wrong with it?
In this article, I will answer the questions: why isn’t there a SHOW TABLES command in PostgreSQL, when will SHOW TABLES in PostgreSQL be available, and how do I list tables with native PostgreSQL methods? Why isn’t there a SHOW TABLES command in PostgreSQL? People who come from MySQL are always asking the same question: […]
OpenStreetMap service by CYBERTEC
In response to repeated customer requests seeking spatial datasets based on the OpenStreetMap service, CYBERTEC decided to start an initiative to address this demand. CYBERTEC implemented a “download OpenStreetMap” service which periodically generates extracts of OpenStreetMap data in various forms, and outputs the data as an sql dump to streamline and simplify its usage. Extracts […]
Gaps in sequences in PostgreSQL
© Laurenz Albe 2021 Most database tables have an artificial numeric primary key, and that number is usually generated automatically using a sequence. I wrote about auto-generated primary keys in some detail in a previous article. Occasionally, gaps in these primary key sequences can occur – which might come as a surprise to you. This […]
JSON in PostgreSQL: how to use it right
© Laurenz Albe 2021 The comprehensive JSON support in PostgreSQL is one of its best-loved features. Many people – particularly those with a stronger background in Javascript programming than in relational databases – use it extensively. However, my experience is that the vast majority of people don’t use it correctly. That causes problems and unhappiness […]
Building an Oracle to PostgreSQL migrator: Lessons learned
Moving from Oracle to PostgreSQL has become a popular sport, widely adopted by many who want to free themselves from license costs, hefty support costs and also technical limitations on the Oracle side. The same is true for people moving from MS SQL and other commercial platforms to PostgreSQL. However, my impression is that moving […]
PostgreSQL schedulers: comparison table
Hello, my name is Pavlo Golub, and I am a scheduler addict. That began when I implemented pg_timetable for PostgreSQL. I wrote a lot about it. In this post, I want to share the result of my investigations on the schedulers available for PostgreSQL. I gave a talk about this topic at the CERN meetup, so […]
PostgreSQL: Create indexes after bulk loading
Over the years, many of our PostgreSQL clients have asked whether it makes sense to create indexes before – or after – importing data. Does it make sense to disable indexes when bulk loading data, or is it better to keep them enabled? This is an important question for people involved in data warehousing and […]
Index bloat reduced in PostgreSQL v14
© Laurenz Albe 2021 PostgreSQL v12 brought more efficient storage for indexes, and v13 improved that even more by adding deduplication of index entries. But Peter Geoghegan is not done yet! PostgreSQL v14 will bring “bottom-up” index entry deletion, which is targeted at reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes. […]