home ¦ Archives ¦ Atom ¦ RSS

PgSQL Indexes

Two recent Postgres performance nuggets, both touching on indexes.

First, Craig Kerstiens talks about performance measurement with pg_stat, but lands on conditional indexes:

To further optimize this we would great a conditional OR composite index. A conditional would be where only current = true, where as the composite would index both values. A conditional is commonly more valuable when you have a smaller set of what the values may be, meanwhile the composite is when you have a high variability of values.

Next, the Instagram team illustrates some of how they’ve scaled with Postgres. They provide functional indexes as one of their tips:

On some of our tables, we need to index strings (for example, 64 character base64 tokens) that are quite long, and creating an index on those strings ends up duplicating a lot of data. For these, Postgres’ functional index feature can be very helpful:

Both very good to know.

© 2008-2024 C. Ross Jam. Built using Pelican. Theme based upon Giulio Fidente’s original svbhack, and slightly modified by crossjam.