2 posts tagged with "Postgres.AI Assistant"

It is well-known that having an extra index leads to certain types of overhead:

1. so-called write amplification, slowing odwn INSERTs and UPDATEs (DELETEs are not affected):
   1. not only writes are slowed down, but index changes are written to WAL, increasing its volume,
   2. thus, backups are also affected,
   3. and physical replication as well, more needs to be replicated;
2. (a very obvious one!) extra disk space is used;
3. overhead in memory – space in the buffer pool and page cache, occupied by index pages.

But it is much less known that extra indexes slow down SELECTs, and might even cause performance cliffs. Let's explore it.

In one of the recent PostgresFM episodes, Michael Christofides and Nikolay discussed planning time and what can affect it. One of the obvious negative factors we've discussed is the large number of partitions a partition table might have.

In this post, we're using our Postgres.AI assistant to see how planning time depends on the number of partitions.

This is the very first blog post of its kind: it has an integrated AI that you can use to explore the topic discussed here further, to repeat the experiment, alter it in any direction, and study the Postgres behavior more effectively.