sharding.rdoc

doc/sharding.rdoc
Last Update: 2023-05-26 07:47:08 -0700

Primary/Replica Configurations and Database Sharding ↑

Sequel has support for primary/replica configurations (writable primary database with read only replicas databases), as well as database sharding (where you can pick a server to use for a given dataset). Support for both features is database independent, and should work for all database adapters that ship with Sequel.

The :servers Database option ↑

Sharding and read_only support are both enabled via the :servers database option. Using the :servers database option makes Sequel use a connection pool class that supports sharding, and the minimum required to enable sharding support is to use the empty hash:

DB=Sequel.connect('postgres://primary_server/database', servers: {})

In most cases, you are probably not going to want to use an empty hash. Keys in the server hash are not restricted to type, but the general recommendation is to use a symbol unless you have special requirements. Values in the server hash should be either hashes or procs that return hashes. These hashes are merged into the Database object’s default options hash to get the connection options for the shard, so you don’t need to override all options, just the ones that need to be modified. For example, if you are using the same user, password, and database name and just the host is changing, you only need a :host entry in each shard’s hash.

Note that all servers should have the same schema for all tables you are accessing, unless you really know what you are doing.

Primary and Replica Database Configurations ↑

Single Primary, Single Replica ↑

To use a single, read-only replica that handles SELECT queries, the following is the simplest configuration:

DB=Sequel.connect('postgres://primary_server/database', 
  servers: {read_only: {host: 'replica_server'}})

This will use the replica_server for SELECT queries and primary_server for other queries. The :read_only key in the :servers hash is special in that it sets the default database for Dataset methods that use SELECT queries (which are generally read queries that do not modify the database).

If you want to ensure your queries are going to a specific database, you can force this for a given query by using the .server method and passing the symbol name defined in the connect options. For example:

# Force the SELECT to run on the primary server
DB[:users].server(:default).all

# Force the DELETE to run on the read-only replica
DB[:users].server(:read_only).delete

Single Primary, Multiple Replicas ↑

Let’s say you have 4 replica servers with names replica_server0, replica_server1, replica_server2, and replica_server3.

num_read_only = 4
read_only_host = rand(num_read_only)
read_only_proc = proc do |db|
  {host: "replica_server#{(read_only_host+=1) % num_read_only}"}
end
DB=Sequel.connect('postgres://primary_server/database',
  servers: {read_only: read_only_proc})

This will use one of the replica servers for SELECT queries and use the primary server for other queries. It’s also possible to pick a random host instead of using the round robin approach presented above, but that can result in less optimal resource usage.

Multiple Primary, Multiple Replicas ↑

This involves the same basic idea as the multiple replicas, single primary, but it shows that the primary database is named :default. So for 4 primary servers and 4 replica servers:

num_read_only = 4
read_only_host = rand(num_read_only)
read_only_proc = proc do |db|
  {host: "replica_server#{(read_only_host+=1) % num_read_only}"}
end
num_default = 4
default_host = rand(num_default)
default_proc = proc do |db|
  {host: "primary_server#{(default_host+=1) % num_default}"}
end
DB=Sequel.connect('postgres://primary_server/database',
  servers: {default: default_proc, read_only: read_only_proc})

Sharding ↑

There is specific support in Sequel for handling primary/replica database combinations, with the only necessary setup being the database configuration. However, since sharding is always going to be implementation dependent, Sequel supplies the basic infrastructure, but you have to tell it which server to use for each dataset. Let’s assume a simple scenario, a distributed rainbow table for SHA-1 hashes, sharding based on the first hex character (for a total of 16 shards). First, you need to configure the database:

servers = {}
(('0'..'9').to_a + ('a'..'f').to_a).each do |hex|
  servers[hex.to_sym] = {host: "hash_host_#{hex}"}
end
DB=Sequel.connect('postgres://hash_host/hashes', servers: servers)

This configures 17 servers, the 16 shard servers (/hash_host_[0-9a-f]/), and 1 default server which will be used if no shard is specified (“hash_host”). If you want the default server to be one of the shard servers (e.g. hash_host_a), it’s easiest to do:

DB=Sequel.connect('postgres://hash_host_a/hashes', servers: servers)

That will still set up a second pool of connections for the default server, since it considers the default server and shard servers independent. Note that if you always set the shard on a dataset before using it in queries, it will not attempt to connect to the default server. Sequel may use the default server in queries it generates itself, such as to get column names or table schemas, so you should always have a default server that works.

To set the shard for a given query, you use the Dataset#server method:

DB[:hashes].server(:a).where(hash: /31337/)

That will return all matching rows on the hash_host_a shard that have a hash column that contains 31337.

Rainbow tables are generally used to find specific hashes, so to save some work, you might want to add a method to the dataset that automatically sets the shard to use. This is fairly easy using a Sequel::Model:

class Rainbow < Sequel::Model(:hashes)
  dataset_module do
    def plaintext_for_hash(hash)
      raise(ArgumentError, 'Invalid SHA-1 Hash') unless /\A[0-9a-f]{40}\z/.match(hash)
      server(hash[0...1].to_sym).where(hash: hash).get(:plaintext)
    end
  end
end

Rainbow.plaintext_for_hash("e580726d31f6e1ad216ffd87279e536d1f74e606")

:servers_hash Option ↑

The connection pool can be further controlled to change how it handles attempts to access shards that haven’t been configured. The default is to assume the :default shard. However, you can specify a different shard using the :servers_hash option when connecting to the database:

DB = Sequel.connect('postgres://...', servers_hash: Hash.new(:some_shard))

You can also use this feature to raise an exception if an unconfigured shard is used:

DB = Sequel.connect('postgres://...', servers_hash: Hash.new{raise 'foo'})

If you specify a :servers_hash option to raise an exception for non configured shards you should also explicitly specify a :read_only entry in your :servers option for the case where a shard is not specified. In most cases it is sufficient to make the :read_only entry the same as the :default shard:

servers = {read_only: {}}
(('0'..'9').to_a + ('a'..'f').to_a).each do |hex|
  servers[hex.to_sym] = {host: "hash_host_#{hex}"}
end
DB=Sequel.connect('postgres://hash_host/hashes', servers: servers, 
  servers_hash: Hash.new{raise "Invalid Server"})

Sharding Plugin ↑

Sequel comes with a sharding plugin that makes it easy to use sharding with model objects. It makes sure that objects retrieved from a specific shard are always saved back to that shard, allows you to create objects on specific shards, and even makes sure associations work well with shards. You just need to remember to set to model to use the plugin:

class Rainbow < Sequel::Model(:hashes)
  plugin :sharding
end

Rainbow.server(:a).first(id: 1).update(plaintext: 'VGM')

If all of your models are sharded, you can set all models to use the plugin via:

Sequel::Model.plugin :sharding

server_block Extension ↑

By default, you must specify the server/shard you want to use for every dataset/action, or Sequel will use the default shard. If you have a group of queries that should use the same shard, it can get a bit redundant to specify the same shard for all of them.

The server_block extension adds a Database#with_server method that scopes all database access inside the block to the given shard by default:

DB.extension :server_block
DB.with_server(:a) do
  # this SELECT query uses the "a" shard
  if r = Rainbow.first(hash: /31337/)
    r.count += 1
    # this UPDATE query also uses the "a" shard
    r.save
  end
end

The server_block extension doesn’t currently integrate with the sharding plugin, as it ties into the Dataset#server method. This shouldn’t present a problem in practice as long as you just access the models inside the with_server block, since they will use the shard set by with_server by default. However, you will probably have issues if you retrieve the models inside the block and save them outside of the block. If you need to do that, call the server method explicitly on the dataset used to retrieve the model objects.

The with_server method also supports a second argument for the default read_only server to use, which can be useful if you are mixing sharding and primary/replica servers:

DB.extension :server_block
DB.with_server(:a, :a_read_only) do
  # this SELECT query uses the "a_read_only" shard
  if r = Rainbow.first(hash: /31337/)
    r.count += 1
    # this UPDATE query also uses the "a" shard
    r.save
  end
end

arbitrary_servers Extension ↑

By default, Sequel’s sharding support is designed to work with predefined shards. It ships with Database#add_servers and Database#remove_servers methods to modify these predefined shards on the fly, but it is a bit cumbersome to work with truly arbitrary servers (requiring you to call add_servers before use, then remove_servers after use).

The arbitrary_servers extension allows you to pass a server/shard options hash as the server to use, and those options will be merged directly into the database’s default options:

DB.extension :arbitrary_servers
DB[:rainbows].server(host: 'hash_host_a').all
# or
DB[:rainbows].server(host: 'hash_host_b', database: 'backup').all

arbitrary_servers is designed to work well in conjunction with the server_block extension:

DB.with_server(host: 'hash_host_b', database: 'backup') do
  DB.synchronize do
    # All queries here default to the backup database on hash_host_b
  end
end

If you are using arbitrary_servers with server_block, you may want to define the following method (or something similar) so that you don’t need to call synchronize separately:

def DB.with_server(*)
  super{synchronize{yield}}
end

The reason for the synchronize method is that it checks out a connection and makes the same connection available for the duration of the block. If you don’t do that, Sequel will probably disconnect from the database and reconnect to the database on each request, since connections to arbitrary servers are not cached.

Note that this extension only works with the sharded threaded connection pool. If you are using the sharded single connection pool, you need to switch to the sharded threaded connection pool before using this extension. If you are passing the :single_threaded option to the Database, just remove that option. If you are setting:

Sequel.single_threaded = true

just remove or comment out that code.

JDBC ↑

If you are using the jdbc adapter, note that it does not handle separate options such as :host, :user, and :port. If you would like to use the :servers option when connecting to a JDBC database, each hash value in the servers option should contain a :uri key with a JDBC connection string for that shard as the value. Example:

DB=Sequel.connect('jdbc:postgresql://primary_server/database', 
  servers: {read_only: {uri: 'jdbc:postgresql://replica_server/database'}})