Does YugabyteDB Support Composite Key Indexes?

YugabyteDB does support composite keys for tables as well as secondary indexes. If you are unfamiliar with a composite key, Wikipedia defines it as “a candidate key that consists of two or more attributes (table columns) that together uniquely identify an entity occurrence (table row).”

The partition key, also referred to as the sharding key, is the first column called out in the create index command. By default, this utilizes hash sharding. Any secondary columns, referred to as the clustering keys, by default are organized by range. Additional information on this can be found on docs page for “CREATE TABLE”.

We recently had a user get the following error when creating an index. The reason that the create index statement failed is in the error response:

ERROR: hash column not allowed after an ASC/DESC column

This means the issue is not that YugabyteDB does not use or cannot use composite indexes, but that you cannot use hash organization for a column after a range sharded column in an index definition.

As mentioned, hash based indexes are the default in YugabyteDB. The reason for this is that hashing allows the data to be spread as evenly as possible over the tablets (shards). However, hashing does not allow for efficient range scans, for which ascending or descending indexes are required. For creating multi-column or composite indexes, in most cases you should try to understand the cardinality of the columns, and put the most selective column first to prevent redundant work.

There is an interesting difference between how a table is sharded versus a secondary index. The primary key (PK) for your table has to be unique, meaning no row can share the same PK value. With non-unique secondary indexes this is not the case. If your rows share the same PK value they will be stored on the same tablet, even when sharding by hash. This can be useful when indexing for a range scan.

You can find additional blogs on secondary indexes in YugabyteDB  below:

• Index Scan in YugabyteDB
• Boost Secondary Index Queries with Index Only Scan
• How to Avoid Hotspots on Range-based Indexes in Distributed Databases
• How to Optimize Pagination for Distributed Data While Maintaining Ordering
• How To Design Distributed Indexes for Optimal Query Performance

New Video Tutorials

Check out some of our most popular “how to” content on the Yugabyte YouTube channel, including our YugabyteDB Friday Tech Talks (designed for engineers by engineers).

Upcoming Events and Training

Check out the upcoming YugabyteDB events, including all training sessions, conferences, and in-person and virtual events.

If You Have Questions About Distributed SQL

This blog series would not be possible without the support of fellow Yugabeings such as Denis Magda, Dorian Hoxha, Franck Pachot, and Frits Hoogland, to name a few. We also thank our incredible user community for not being afraid to ask questions.

So if you have questions, make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub, or Stack Overflow. For more tips and tricks, check out our Distributed Tips and Tricks archive.

Next Steps

Ready to start exploring YugabyteDB features?

You have some great options: run the database locally on your laptop (Quick Start), deploy it to your favorite cloud provider (Multi-node Cluster Deployment), or sign up for a free YugabyteDB Managed cluster. It’s easy! Get started today!