Timeline for Range based sharding?

[Cory Tucker]

Hey guys, I went to your talk the other day at Heroku and on one of your slides you mentioned that range based partitioning might be on the future roadmap.  I was wondering if there was a planned release that was tied to or if it's just more of an open question at this point?  Also, if one had an existing setup already using pg_shard and wanted to convert to a range based partitioning setup, is it safe to presume that that process would be completely manual?

thanks

--Cory

[Ozgun Erdogan]

Hey Cory,

Could you open an issue on our GitHub page for range partitioning? We regularly prioritize new features based on their popularity and difficulty, and we'd like to track this on our project page.

https://github.com/citusdata/pg_shard/issues

Also, Jason's going to follow up on this thread shortly. We use PostgreSQL's constraint exclusion mechanism for hash partitioning, and he's testing if range partitioning could just working by flipping the right "distribution metadata" fields. He'll reply to this thread once we know more.

As a quick second question, were you looking to bulk load an existing data set? Or would you start with an empty table and add new rows?

Best,
Ozgun

[Jason Petersen]

Hi Cory,

 

Sorry for not responding sooner, but I wanted to run some tests with range partitioning to confirm a hunch. The logic within pg_shard is already capable of dealing with range-partitioned tables, it just presently lacks any functions to help you create them. So you can use them right now, but you'd need to do a little work to add new partitions later if you need them.

Example

Here are the steps to reproduce my little proof-of-concept for interacting with a range-based partition table:

1. Create a table with an integer column:
   CREATE TABLE example ( id INTEGER,name TEXT );
   
   
2. Distribute the table on the integer column:
   SELECT master_create_distributed_table('example', 'id');
   
   
3. Create the number of shards you want:
   SELECT master_create_worker_shards('example', 16);
   
   
4. Change the partition type to range:
   UPDATE pgs_distribution_metadata.partition
   SET    partition_method = 'r'
   WHERE  relation_id = 'example'::regclass;
   

At this point, example is a distributed table with sixteen shards that will shard data based on contiguous ranges of integer identifiers rather than on their hash values.

How it works

Peeking into some of pg_shard's metadata tables will reveal how this works… Here is our shard listing:

SELECT * FROM pgs_distribution_metadata.shard WHERE relation_id = 'example'::regclass;

┌───────┬─────────────┬─────────┬─────────────┬─────────────┐
│  id   │ relation_id │ storage │  min_value  │  max_value  │
├───────┼─────────────┼─────────┼─────────────┼─────────────┤
│ 10020 │      180262 │ t       │ -2147483648 │ -1879048194 │
│ 10021 │      180262 │ t       │ -1879048193 │ -1610612739 │
│ 10022 │      180262 │ t       │ -1610612738 │ -1342177284 │
│ 10023 │      180262 │ t       │ -1342177283 │ -1073741829 │
│   ... │         ... │ ...     │ ...         │ ...         │

By changing the partition type to range, we are changing how pg_shard interprets these intervals. Normally it looks for intervals by hashing partition values that appear in a query (for instance the values in an INSERT or WHERE clauses in a SELECT). But now it skips the hashing step and just looks for the interval that contains the value directly.

You'll notice the min_value and max_value columns are of type text. Though hash values are always integers, range partitions might need other types. Determine the boundaries of your shards and call the corresponding type's output function (e.g. date_out for dates) and rewrite the shards to represent the ranges you desire.

—Jason

[Cory Tucker]

Issue filed:  https://github.com/citusdata/pg_shard/issues/75

We will be bulk loading an existing dataset of ~50M rows or so.

thanks

--Cory

On Friday, February 6, 2015 at 2:33:39 PM UTC-8, Ozgun Erdogan wrote: