© 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 […]

© 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 […]

© 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 […]

© 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 […]

© 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 […]

© 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. […]

© Laurenz Albe 2021 Since v10, PostgreSQL has provided support for scram-sha-256 for password hashing and authentication. This article describes how you can adapt your application safely. Why do we need scram-sha-256? PostgreSQL uses cryptographic hashing for two purposes: The actual database password is a hash of the clear text password entered by the user. […]

© Laurenz Albe 2021 For many people, “cross join” is something to be afraid of. They remember the time when they forgot the join condition and the DBA was angry, because the query hogged the CPU and filled the disk. However, there are valid use cases for cross joins which I want to explore in […]

© Laurenz Albe 2021 When analyzing customer’s problems, I have seen the mess you can get into if you disable autocommit in your interactive client, so I’d like to bring this topic to a wider audience. What is autocommit? In PostgreSQL, like in any other ACID-complicant database, each statement runs in a transaction: if the […]

© Laurenz Albe 2021 EXPLAIN ANALYZE is the key to optimizing SQL statements in PostgreSQL. This article does not attempt to explain everything there is to it. Rather, I want to give you a brief introduction, explain what to look for and show you some helpful tools to visualize the output. How to call EXPLAIN […]

© Laurenz Albe 2021 In this article, I’ll talk about row level triggers, which are the most frequently used kind of triggers. I will describe what the return value of the trigger function means and suggest a useful code simplification. Triggers in PostgreSQL A trigger in PostgreSQL consists of two parts: a trigger function the […]

© Laurenz Albe 2021 Users with an Oracle background consider tablespaces very important and are surprised that you can find so little information about them in PostgreSQL. This article will explain what they are, when they are useful and whether or not you should use them. What is a tablespace Essentially, a tablespace in PostgreSQL […]