Manual routing
In case the sharding key is not configured or can't be extracted from the query, PgDog supports explicit sharding directions, provided by the client in a query comment.
Shard number
Queries that need to be routed to a specific shard can include the shard number in a comment, using a key/value notation:
For example, the following query will be routed to shard 1:
The comment can appear anywhere in the query, including in the beginning and at the end.
Note
If you are using prepared statements, comments don't work very well. Consider using SET instead (see below).
SET
If you're using prepared statements or can't easily annotate your queries with comments, you can set the shard number using the SET statement:
This statement must be executed inside a transaction to make sure PgDog routes only that one transaction to the specified shard.
Shard numbering scheme
PgDog shards are numbered from 0 to N - 1, where N is the total number of shards. For example, in a 3-shard system, shard 0 is the first shard while shard 2 is the third shard.
Sharding key
If you don't know the shard number but have a sharding key, e.g., the value of a column used for sharding your database, you can specify it in a query comment:
PgDog will extract this value from the query and apply the sharding function to produce the actual shard number. For example, the following query will be routed to the right shard for sharding value 25:
/* pgdog_sharding_key: 25 */
INSERT INTO "orders" ("payment_id", "product_id")
VALUES ($1, $2) RETURNING *;
The same can be done using SET, inside a transaction:
Usage in frameworks
Some web frameworks support easily adding comments to queries. For example, if you're using Rails, you can add a sharding hint to your queries like so:
User
.where(email: "[email protected]")
.annotate("pgdog_shard: 0")
.to_sql
Others make it more difficult, but still possible. For example, Laravel has a plugin to make it work while SQLAlchemy makes you write a bit of code. Django appears to have a plugin.
While this approach works, it's always best to try automatic routing first. It's more convenient and less error-prone.
Usage in Rails
We've written a small gem to help manual routing in Rails/ActiveRecord applications. You can install it from rubygems.org:
You can then manually annotate your ActiveRecord calls with the sharding key (or shard number):
PgDog.with_sharding_key(1234) do
Users.where(email: "[email protected]").first
end
You can read more about this in our blog.