When you create indexes on a table, here are a few simple guidelines to make your life easier down the road.
1. Start the index name with the table name, like mytable_. In Postgres, every index name needs to be unique in the database. If you have several tables that all have a column named created_date, for example, you couldn’t do this:
|
1 2 |
create index created_date on badges(created_date); create index created_date on comments(created_date); |
Instead, you wanna do this so each index name is unique and you don’t get errors:
|
1 2 |
create index badges_created_date on badges(created_date); create index comments_created_date on comments(created_date); |
2. Name the index with the key columns in order. When you’re reading an explain plan, this will make it easier for you to understand where the data is coming from, and its sort order in the index.
3. Don’t put individual included column names in the name. Your index might include half a dozen non-key columns to help with select performance, but there’s no need to list those individually in the index name. Instead, just put _includes at the end of the index name to denote that it has included columns, like this:
|
1 2 3 |
create index users_displayname_includes on users(displayname) include(location, websiteurl, reputation); |
4. For partial indexes, use a _partial suffix as well. You don’t need to put the full filter clause in the index name. Index names just can’t handle all your complicated filtering sql.
Are there any other index naming conventions that you think should be included in this list? Drop a note in the comments, and if I agree with you, I’ll promote your suggestion up into this list and credit you by name.
2 Comments. Leave new
Really nice list. One addition, I really like it when people who use the occasional non-btree index add the type as a suffix (e.g. _hash _brin _gin …). Like your other tips, it really helps when looking at explain plans.
I prefix my indices (indexes?) with ix_, for some reason 🙂 (I guess I picked that up from MSSQL back in the day?)
I don’t suffix my indexes unless unorthodox ones, as Michael mentioned.