Skip to content

Manual query 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 or a SET statement.

How it works

Queries can be routed to a shard directly using its number in the configuration. If this information is not available to the client, the sharding key can be passed in as well, letting PgDog decide where the query should be sent instead.

Two mechanisms are supported for providing these query routing hints: query comments or the SET SQL command.

Query comment

PostgreSQL query language supports adding inline comments to queries. They are ignored by the parser but can be used by PgDog to communicate routing hints. The following comments are supported:

Comment Description Example
pgdog_shard Instructs the query router to send this query to the specified shard, as a direct-to-shard query. /* pgdog_shard: 0 */ SELECT * FROM users
pgdog_sharding_key Gives the sharding key to the query router and lets it decide where the query should be sent. /* pgdog_sharding_key: 1234 */ SELECT * FROM users

Examples

The following query will be sent to shard number zero:

/* pgdog_shard: 0 */ CREATE INDEX CONCURRENTLY users_id_idx USING btree(id);

This query will be sent to whichever shard maps to the key "us-east-1":

/* pgdog_sharding_key: 'us-east-1' */ SELECT * FROM users WHERE is_admin = true;

The comment can appear anywhere in the query, as long as it's syntactically valid.

Limitations

Since parsing comments is not free, this method is best used for infrequent commands, like schema migrations or queries executed manually by an administrator. For faster query routing, consider supplying the sharding key directly in the query.

Additionally, using query comments with a high cardinality value, like the pgdog_sharding_key, may substantially increase the size of the prepared statements cache. To avoid this, consider the SET command instead.

SET

The SET command comes from the PostgreSQL query language and is used to change database settings at runtime. Since PgDog uses the Postgres parser, it can intercept this command and perform different actions. For providing routing hints to the query router, we reserved two system settings:

Setting name Description Example
pgdog.shard Equivalent to pgdog_shard comment. The transaction will be sent to the indicated shard only. SET pgdog.shard TO 0
pgdog.sharding_key Equivalent to pgdog_sharding_key comment. The key will be used by the query router to compute the shard number for the transaction. SET pgdog.sharding_key TO 'us-east-1'

Examples

The following transaction will be sent to shard number zero:

BEGIN;
SET pgdog.shard TO 0;
CREATE INDEX users_id_idx USING btree(id);
COMMIT;

This transaction will be sent to whichever shard maps to the key "us-east-1":

BEGIN;
SET pgdog.sharding_key TO 'us-east-1';
SELECT * FROM users WHERE is_admin = true;
COMMIT;

Limitations

Since SET changes session variables, we want to avoid leaking this state between transactions. For this reason, routing hints provided using this method are only supported inside transactions. For example:

BEGIN;
SET pgdog.sharding_key TO 'us-east-1';
SELECT * FROM users WHERE is_admin = true;
COMMIT;

Latency

Starting a transaction and sending a SET command has implications on overall query latency. The SET command used for providing routing hints is not sent to Postgres, so this should somewhat mitigate its impact. However, to remove it entirely, consider using async queries, if supported by your database driver.

Async queries are sent in batches and the database driver doesn't wait for a response from the first query before sending the following one.

Usage in ORMs

Some web frameworks support adding comments to queries. For example, if you're using Rails, you can add a sharding hint to SELECT queries like so:

User
  .where(email: "[email protected]")
  .annotate("pgdog_shard: 0")
  .to_sql

SELECT "users".* FROM "users" WHERE "email" = $1 /* pgdog_shard: 0 */

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.

Usage in Rails

We've written a small gem to help manual routing in Rails/ActiveRecord applications. You can install it from rubygems.org:

gem install pgdog

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.

Read more