Horizontal partition in postgres-xl?

Question

I am using postgres-xl in ubuntu. I have a table with 9 row. I want to divide that table into 3 parts. Any idea how i do this?

postgres=# SELECT * FROM cities;
 name | location 
------+----------
 a    |        1
 a    |        2
 a    |        4
 a    |        3
 a    |        4
 a    |        5
 a    |        6
 a    |       11
 a    |       14
(9 rows)

@Crud3x 9 row so 9 number 1,2,3,4,5,6,7,8,9. a,1 is first and a,14 is last. — Muhammad Raza, Aug 13 '15 at 08:17
You missunderstood me. Where do you want to split it into 3 parts? Like 1. {a.1,a.2,a.4} and 2. {a.3,a.4,a.5) and 3. {a.6,a.11,a.14} ?? — C4d, Aug 13 '15 at 08:20

Marco Slot · Answer 1 · 2015-08-19T11:16:41.030

2

Not sure how to do it with Postgres-XL, but with the pg_shard extension you could hash-partition the table into 3 (or more) pieces:

CREATE TABLE cities (name text, location int);
SELECT master_create_distributed_table('cities', 'location');
SELECT master_create_worker_shards('cities', 3, 2);

To get started with pg_shard you can find documentation at: https://github.com/citusdata/pg_shard

edited Aug 19 '15 at 11:16

answered Aug 18 '15 at 15:24

Marco Slot

401
3
5

score 1 · Answer 2 · answered Sep 08 '16 at 10:00

You would need to specify your distribution strategy upon table creation:

CREATE TABLE cities (
    name VARCHAR,
    location VARCHAR,
    PRIMARY KEY (location)
)
DISTRIBUTE BY HASH(location);

Note that there are several pitfalls with respect to constraints, see also PostgresXL CREATE TABLE documentation.

Horizontal partition in postgres-xl?

2 Answers2