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. […]
PostgreSQL: The power of a SINGLE missing 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 […]
Deduplication in PostgreSQL v13 B-tree indexes
© 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 […]
Killed index tuples
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. […]
Are your foreign keys indexed?
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 […]
Adding an index can decrease SELECT performance
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 […]
Get rid of your unused indexes!
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 […]