> ## Documentation Index
> Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Pick a Shard Key
> Guidance on choosing and testing shard keys in Citus to achieve even data distribution, evaluate candidates, redistribute tables, and troubleshoot shard size skew.
In this lesson we'll test different shard keys on our Citus tables and observe which keys produce even data distribution versus which cause shard-size skew. The goal is to select a shard key that balances storage and query load across worker nodes.
## 1) Check the current distribution column
Query the Citus metadata table `citus_tables` to see which column each distributed table uses:
```bash theme={null}
$ psql -h localhost -p 5432 -U postgres -d chirp -c "SELECT table_name, distribution_column FROM citus_tables;"
Welcome to the KodeKloud Hands-On Lab
All rights reserved
table_name | distribution_column
-----------------------+--------------------
users | user_id
users_by_country | country
```
This tells you the column Citus currently hashes on for each distributed table.
## 2) Inspect how rows are spread across shards
Use `run_command_on_shards` to run a per-shard row count and inspect balance. For example, count rows per shard on `users_by_country`:
```bash theme={null}
$ psql -h localhost -p 5432 -U postgres -d chirp -c "SELECT shardid, result AS rows FROM run_command_on_shards('users_by_country', 'SELECT count(*)::text FROM users_by_country');"
shardid | rows
---------+------
102 | 1000
103 | 1200
104 | 800
```
From the output above you can see per-shard row totals. Ideally, a well-chosen shard key produces roughly even counts across shards; significant imbalance signals a poor key for uniform distribution.
| Shard ID | Rows |
| -: | -: |
| 102 | 1000 |
| 103 | 1200 |
| 104 | 800 |
## 3) How to evaluate shard-key candidates
When evaluating shard keys, prefer columns with these properties:
| Criterion | Why it matters |
| - | - |
| High cardinality | More distinct values spread data more evenly across shards |
| Query locality (used in WHERE clauses) | Keeps related data together so queries hit fewer shards |
| Stable value distribution | Avoid hot shards caused by skewed value frequency |
| Matching workload access patterns | Improves performance by aligning distribution to common query shapes |
When picking a shard key, aim for high-cardinality columns that are commonly used in your queries’ WHERE clauses. Avoid columns with highly skewed distributions (e.g., a small set of very frequent values) since they create hotspot shards. If you need to change distribution for an existing large table, create a new distributed table with the desired column and migrate data—there is no single `ALTER TABLE ... DISTRIBUTE BY` that rehashes a large table in place.
## 4) Example: distribute a table by a different column
To create a distributed table that uses `user_id` as the distribution column:
```sql theme={null}
SELECT create_distributed_table('users', 'user_id');
```
After redistributing (or after testing other candidate columns), re-run `run_command_on_shards` to compare per-shard counts and verify improved balance.
## 5) Troubleshooting and tips
* If one shard is much larger than others, inspect the frequency distribution of the shard key values to find hotspots.
* Consider adding a compound shard key (multiple columns) if single columns don't provide good distribution and your queries include those columns.
* Test redistribution in a staging environment before running large-scale data migrations in production.
## Links and references
* Citus metadata tables: [https://docs.citusdata.com/en/latest/reference/metadata\_tables.html](https://docs.citusdata.com/en/latest/reference/metadata_tables.html)
* Citus `run_command_on_shards`: [https://docs.citusdata.com/en/latest/reference/run\_command\_on\_shards.html](https://docs.citusdata.com/en/latest/reference/run_command_on_shards.html)
* Citus `create_distributed_table`: [https://docs.citusdata.com/en/latest/commands/create\_distributed\_table.html](https://docs.citusdata.com/en/latest/commands/create_distributed_table.html)
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.