Pick a Shard Key

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:

$ 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:

$ 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:

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
Citus run_command_on_shards: 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

Why One Database Isn't Enough

Sharding Fundamentals

Routing and Hot Shards

Shard Keys and Partitioning

Resharding and Cross-Shard Queries

1) Check the current distribution column

2) Inspect how rows are spread across shards

3) How to evaluate shard-key candidates

4) Example: distribute a table by a different column

5) Troubleshooting and tips

Links and references

Watch Video

Practice Lab

​1) Check the current distribution column

​2) Inspect how rows are spread across shards

​3) How to evaluate shard-key candidates

​4) Example: distribute a table by a different column

​5) Troubleshooting and tips

​Links and references

Watch Video

Practice Lab

1) Check the current distribution column

2) Inspect how rows are spread across shards

3) How to evaluate shard-key candidates

4) Example: distribute a table by a different column

5) Troubleshooting and tips

Links and references