Contents

• shard_manager Extension
• Installation
• Usage
• Configuration
• Tables
• Security
• Build Instructions
• Dependencies
• Copyright and License

shard_manager Extension

Shard Manager is an extension designed to bring database sharding to PostgreSQL.

Inspired by Instagram's numerous posts on their sharding algorithm, Shard Manager can potentially generate unique IDs across numerous tables for hundreds of years. In addition, we have included management and deployment functions to simplify shard management.

Installation

To use Shard Manager, it must first be installed. Simply execute these commands in the database that needs sharding functionality:

CREATE SCHEMA shard;
CREATE EXTENSION shard_manager WITH SCHEMA shard;

The shard schema isn't strictly necessary, but we recommend keeping namespaces isolated.

Usage

Shard manager works by injecting itself into an existing schema template when shards are created. Let's make a very basic schema now:

CREATE SCHEMA comm;

CREATE TABLE comm.yell (
  id       SERIAL PRIMARY KEY NOT NULL,
  message  TEXT NOT NULL
);

That was easy! Now, to use Shard Manager, there are three basic steps:

• Registration
• Creation
• Initialization

The registration step records all tables that should be included in a specific template. The schema itself is the template, but Shard Manager won't copy all tables by default. We can register tables like this:

SELECT shard.register_base_table('comm', 'yell', 'id');

Any table registered in this manner will be copied to new shards.

Next, we have to create a new physical shard. Shard Manager uses schemas as shard containers. It names them by copying the root template schema name, and appends the current shard number, up to the amount of bits reserved for our shard IDs. For us, this means we need to create a shard for the comm schema:

SELECT shard.create_next_shard('comm', 'localhost');

The second parameter is set to localhost for now, but it's merely a tracking value. We don't currently make use of foreign tables. The idea here, is that the values in the shard.shard_map table can be used as a physical/logical map for application use.

The next step is to fill our new shard schema. Again, we have a helper function for this:

SELECT shard.init_shard_tables('comm', 1);

Now if we examine the comm1 schema, we will find our yell table in sharded form. Let's see what happens if we insert into the table:

INSERT INTO comm1.yell (message) VALUES ('I like cows!');

If we SELECT from the table, we should see something like this:

-[ RECORD 1 ]------------
id      | 245816446945281
message | I like cows!

That ID is huge! But in this case, that's expected. We use all of a 64-bit integer to store the ID, so the value should be extremely high. But did you also notice that the shard system automatically overwrote the DEFAULT imposed by our use of a SERIAL type? It also makes sure the column is a BIGINT type so it can store the whole shard ID without numeric overflow.

This is all handled automatically to encourage shard use.

Configuration

Configuring Shard Manager has been simplified by the introduction of two functions designed to handle setting validation and other internals. To see all settings at once, execute this query to examine the contents of the shard_config table.

SELECT config_name, setting FROM shard.shard_config;

Shard manager should produce several fields it sets by default:

 config_name |  setting   
-------------+------------
 epoch       | 2014-02-11
 shard_count | 2048
 ids_per_ms  | 2048

In this case, Shard Manager was installed on 2014-02-11, and can handle up to 2048 shards, with 2048 IDs per shard, per millisecond. That's over two million IDs per second, per shard. All modifications to these settings must take place before calling init_shard_tables. This helps protect any existing ID values from collision due to changed ID generation assumptions.

To change settings, use the set_shard_config function as seen here:

SELECT shard.set_shard_config('shard_count', '1000');

The output is actually important in this case:

WARNING:  Assuming 2048 IDs per ms on 512 shards, Shard Manager 
will produce unique values until 2571-08-04 06:00:00
 set_shard_config 
------------------
 512

Notice how Shard Manager automatically adjusted the number of shards to the next lowest valid power of two. Since it uses a 64-bit integer internally, it must ensure lossless conversion. The warning is mainly a notice to inform any user that changes watched settings of Shard Manager's current capabilities. Again, we strongly recommend experimenting with various settings before creating new shards. Changes can not be made to system settings following shard initialization!

Tables

Shard Manager has a few tables that provide information about its operation and configuration. These tables include:

Table Name | Description --- | --- shard_config | Contains all settings Shard Manager uses to control shard allocation. shard_map | Maintains a physical/logical mapping for applications to find shards. Tracks whether shards have been initialized for use. shard_table | Master resource where all registered shard tables are tracked. Every schema can have its own list of tables.

Security

Due to its low-level operation, Shard Manager works best when executed by a database superuser. However, we understand this is undesirable in many cases. Certain Shard Manager capabilities can be assigned to other users by calling add_shard_admin. For example:

CREATE USER shard_user;
SELECT shard.add_shard_admin('shard_user');

This user can now call any of the shard management functions. These functions should always work, provided the user who created the shard_manager extension was a superuser. To revoke access, call the analog function:

SELECT shard.drop_shard_admin('shard_user');

Build Instructions

To build it, just do this:

cd shard_manager
make
sudo make install

If you encounter an error such as:

make: pg_config: Command not found

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

export PG_CONFIG=/path/to/pg_config
make
sudo make install

And finally, if all that fails (and if you're on PostgreSQL 8.1 or lower, it likely will), copy the entire distribution directory to the contrib/ subdirectory of the PostgreSQL source tree and try it there without pg_config:

export NO_PGXS=1
make
make install

Dependencies

The shard_manager extension has no dependencies other than PostgreSQL.

Copyright and License

Copyright (c) 2014 OptionsHouse

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.