Indexing is the key to good performance. However, people often ask: Is there an alternative to btree indexing? Can we make indexes in PostgreSQL smaller? Can we create indexes in PostgreSQL faster? And how can we index in a data warehouse? This blog will answer all those questions and show which options you have to […]

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

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

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

© 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 wasn’t finished! PostgreSQL v14 added “bottom-up” index entry deletion, which targets reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes. Why do we get index […]

Index missing? When an index is missing, good performance won’t be kissing a PostgreSQL user looking for efficiency but instead feels like a legacy. To satisfy a DBA’s desire and thirst, let us load some data first. pgbench is the tool of the day but the next listing will explain that anyway: Loading millions of […]

© Laurenz Albe 2020 A while ago, I wrote about B-tree improvements in v12. PostgreSQL v13, which will come out later this year, will feature index entry deduplication as an even more impressive improvement. So I thought it was time for a follow-up. Deduplication for B-tree indexes If the indexed keys for different table rows […]

  Since I only recently learned about the concept of “killed index tuples”, I thought there might be some others who are not yet familiar with this interesting PostgreSQL concept. This may give you an explanation the next time you encounter wildly varying execution times for the same execution plan of the same PostgreSQL query. […]

  Foreign key constraints are an important tool to keep your database consistent while also documenting relationships between tables. A fact that is often ignored is that foreign keys need proper indexing to perform well. This article will explain that and show you how to search for missing indexes. Index at the target of a […]

  We all know that you have to pay a price for a new index you create — data modifying operations will become slower, and indexes use disk space. That’s why you try to have no more indexes than you actually need. But most people think that SELECT performance will never suffer from a new […]

Why should I get rid of unused indexes? Everybody knows that a database index is a good thing because it can speed up SQL queries. But this does not come for free. The disadvantages of indexes are: Indexes use up space. It is not unusual for database indexes to use as much storage space as […]

Many people ask about index scans in PostgreSQL. This blog is meant to be a basic introduction to the topic. Many people aren’t aware of what the optimizer does when a single query is processed. I decided to show how a table can be accessed and give examples. Let’s get started with PostgreSQL indexing. Indexes […]