[Postgres-xl-general] Sharding by schemas

Pavan Deolasee pavan.deolasee at gmail.com
Fri Feb 27 00:45:06 PST 2015


On Thu, Feb 26, 2015 at 10:32 PM, Lucas Moauro <lagenar at gmail.com> wrote:

> Hi there,
>
> I'm new to Postgres-XL and would like to make some questions before making
> the decision of using it or not.
>
> I have a multi tenant application with independent accounts and the
> database is getting pretty big. What I have done so far to increase the
> performance is to create a postgresql schema for each account and replicate
> the tables that are independent for each account. All the accounts contain
> the same tables, but with their own data.
>
> This setup is effective but now I would like to distribute the different
> schemas across many database servers. What I have seen in the docs is
> mostly do shard a particular table by certain rule but that doesn't fit in
> my case.
>
> Is it possible to distribute the data easily with my current setup using
> Postgres-XL? if so could you point me to any documentation that could help?
>
>
IIUC you would like to have one or a set of schemas to be hosted on one
datanode, right? If so, there is no straight forward way to do that. You
could create all tables in that schema to be targeted on specific nodes by
using TO NODE directive of CREATE TABLE. For example,

CREATE TABLE account1.tab () TO NODE (datanode1);
CREATE TABLE account2.tab () TO NODE (datanode2);

But I think a better way is to go back to a single table model and shard
the table on account id. That way the data would automatically distributed
and co-located based on the account. Later, as long as your queries have a
WHERE clause on the account id, your queries should get routed to specific
datanode. I don't know if you have flexibility to change your data model
though in which case the first solution would probably work better.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20150227/7d32b5ff/attachment.htm>


More information about the postgres-xl-general mailing list