Should I Use the Primary Key in All Queries? How Will I Find the Right Tablet Without the Primary Key?

Any table created in YugabyteDB is sharded by a key regardless if it is defined by the user. The same applies to one assigned by the system.

Therefore, for the user-defined primary key, YSQL can find the row if you specify the key efficiently.

If you don’t specify it, as mentioned above, YSQL has no other option than to visit every row to validate whether it is the correct one based on the specified filters. This reading of all rows is called a ‘seq scan,’ which you will find with the EXPLAIN command. This reveals the execution plan.

You see the same behavior if you have a user-defined key but do not use it in the query.

YSQL will scan all of the data across all of your nodes, incurring heavy latencies. Therefore, we encourage users to create a primary key to be used in their queries. In cases where you have access to other columns on your table, you can create an index on these column(s) to speed up the performance of that query. You can also use the INCLUDE *keyword* for a query that will add the data, but it won’t use it for index ordering.

Additional content:

• Index Scan in YugabyteDB
• Natural versus Surrogate Primary Keys in a Distributed SQL Database
• Primary Key—YugabyteDB documentation site
• 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

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!