Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id A9A8F98F705 for ; Tue, 5 Jun 2012 14:16:06 -0300 (ADT) Received: from mail-yw0-f46.google.com ([209.85.213.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SbxMR-0003fS-Oq for pgsql-admin@postgresql.org; Tue, 05 Jun 2012 17:16:05 +0000 Received: by yhmm54 with SMTP id m54so4081199yhm.19 for ; Tue, 05 Jun 2012 10:15:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:content-type:x-mailer:thread-index:content-language; bh=AlbAr3BO/322da2Lulgj9ht2l1avnbbJvAXbh5kA3oU=; b=nd6Lb2mbGTyAMpXfAZyGwF9N857TIf/0Bggn08f7G3VBLRVPjgbqbWUknDYa/DrMgV EkQJ0ZIIGM8C2ZwhwMbhACv7Yz7cMZel/AIAGtMzfVp0FJiP3iQ5QUQMmrzR3gSdQT9U 8THGPsj7Ns9je0GArF8leKDWeI4uXwRyPLqSiakmuYPs83d9V0zQC7snJESXxqohfiQN B4YoHwqyxSVfDOtNfrXUecLGFES3ZUeI2imYfr695uvRIqTVugoXr2vBgIujeCw1muiI Ve0tlNVOWSPEaMlg20SEEW1de18cjNjuORPZSJNTJB9x9y6D4m052edMSAk83zg0saO1 XKvQ== Received: by 10.50.203.39 with SMTP id kn7mr3524742igc.53.1338916550253; Tue, 05 Jun 2012 10:15:50 -0700 (PDT) Received: from Tower (CPE001c1008f624-CM0011aec4f376.cpe.net.cable.rogers.com. [174.118.17.120]) by mx.google.com with ESMTPS id ud8sm2912239igb.4.2012.06.05.10.15.48 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 05 Jun 2012 10:15:48 -0700 (PDT) From: "Igor Shmain" To: "'Jan Nielsen'" Cc: References: <000901cd3dd7$8654a690$92fdf3b0$@gmail.com> In-Reply-To: Subject: Re: Data split -- Creating a copy of database without outage Date: Tue, 5 Jun 2012 13:15:30 -0400 Message-ID: <017001cd433e$d00c41f0$7024c5d0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0171_01CD431D.48FE7280" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQKV3/IsTUoVocuh2ViALPGAauRL7gITFWy2lUoh5/A= Content-Language: en-ca X-Pg-Spam-Score: -1.7 (-) X-Archive-Number: 201206/5 X-Sequence-Number: 39596 This is a multipart message in MIME format. ------=_NextPart_000_0171_01CD431D.48FE7280 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi Jan, Thank you so much for your input and sorry about the delay. I understand most of the limitations, compromises, and sacrifices that come with distributed databases (sharding). But definitely not all of them. After working with databases for about 25 years, I have some basic knowledge about how they work (mssql, oracle, sybase). But I am new to postgres and new to distributed db approach, so any feedback in those areas is extremely welcome :-) If anybody is aware of any successful implementations of distributed approach with postgres and any information around this area, please let me know. If I cannot find a suitable prototype and has to "invent the wheel", I hope to be able to contribute back to the community with my story one day. Thank you once again, -igor From: Jan Nielsen [mailto:jan.sture.nielsen@gmail.com] Sent: June-02-12 11:00 PM To: Igor Shmain Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Data split -- Creating a copy of database without outage Hi Igor, On Tue, May 29, 2012 at 2:13 PM, Igor Shmain wrote: I need to design a solution for a database which will grow and will require a horizontal split at some moment. Ok Here is how I am planning to do it: Every record in every table has a shard number. After the database becomes too large (gets too many requests), the tables need to be horizontally split. It means that for every table all records with some specific shard numbers need to be moved to a new database. Since PG does not support the notion of a shard at the database layer, you will have to create this yourself. The key to scaling shards is ensuring there there are no cross-shard joins (or any other shared data); this is an application-design question so unless you "own" the application-design, this will be difficult to achieve. If you do own the design, the application's entity-tree will have to be carefully constructed to avoid any sharing of resources across shards. RDBMS programmers are taught just the opposite so this will require diligence from your team with a very clear, likely simple, application-entity design amenable to shard-ing. My plan is to (1) create a copy of the database on a new server (backup/restore?), (2) synchronize the databases (how?), and then (3) forward all requests for the moved shards to the new database. (I will get rid of the unneeded shards later). An important requirement: there should be no outage for the users. As I mentioned above, it is unlikely that you will be able to shard an RDBMS at this level; you need to look at the application entity-design. What are the ways to achieve it? If you mean to dictate a shard solution, very few will be able to help. On the other hand, if you mean to ask for help in finding a "low-cost scale-able PG DB solution for a typical RDBMS application with the potential for rapid growth", then there are a number of options which have been employed with success. For predominately read applications, use a single master database replication strategy: http://www.postgresql.org/docs/current/static/different-replication-solution s.html For predominately write applications, you will likely need a multi-master solution (unless you can dictate the application entity-design) which is more complex territory from a scale-ability and application design standpoint but you might look at Postgres-XS and Londiste for ideas. Cheers, Jan ------=_NextPart_000_0171_01CD431D.48FE7280 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi Jan,

 

Thank you so much for your input and sorry about the = delay.

 

I understand most of the limitations, compromises, and sacrifices = that come with distributed databases (sharding). But definitely not all = of them. After working with databases for about 25 years, I have some = basic knowledge about how they work (mssql, oracle, sybase). But I am = new to postgres and new to distributed db approach, so any feedback in = those areas is extremely welcome :-)

 

If anybody is aware of any successful implementations of distributed = approach with postgres and any information around this area, please let = me know. If I cannot find a suitable prototype and has to “invent = the wheel”, I hope to be able to contribute back to the community = with my story one day.

 

 

Thank you once again,

-igor

 

 

From:= Jan = Nielsen [mailto:jan.sture.nielsen@gmail.com]
Sent: June-02-12 = 11:00 PM
To: Igor Shmain
Cc: = pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Data split -- = Creating a copy of database without outage

 

Hi Igor,

On Tue, May 29, 2012 at 2:13 PM, Igor Shmain <igor.shmain@gmail.com> = wrote:

I need to = design a solution for a database which will grow and will require a = horizontal split at some moment.

 

Ok

 

Here is how = I am planning to do it: Every record in every table has a shard number. = After the database becomes too large (gets too many requests), the = tables need to be horizontally split. It means that for every table all = records with some specific shard numbers need to be moved to a new = database.

 

Since PG does not support the notion of a shard at the = database layer, you will have to create this yourself. The key to = scaling shards is ensuring there there are no cross-shard joins (or any = other shared data); this is an application-design question so unless you = "own" the application-design, this will be difficult to = achieve. If you do own the design, the application's entity-tree will = have to be carefully constructed to avoid any sharing of resources = across shards. RDBMS programmers are taught just the opposite so this = will require diligence from your team with a very clear, likely simple, = application-entity design amenable to = shard-ing.

 

My plan is = to (1) create a copy of the database on a new server (backup/restore?), = (2) synchronize the databases (how?), and then (3) forward all requests = for the moved shards to the new database. (I will get rid of the = unneeded shards later). An important requirement: there should be no = outage for the users.

 

As I mentioned above, it is unlikely that you will be = able to shard an RDBMS at this level; you need to look at the = application entity-design.

 

What are = the ways to achieve it?

 

If you mean to dictate a shard solution, very few will = be able to help. On the other hand, if you mean to ask for help in = finding a "low-cost scale-able PG DB solution for a typical RDBMS = application with the potential for rapid growth", then there are a = number of options which have been employed with success. For = predominately read applications, use a single master database = replication strategy:

 

 

For predominately write applications, you will likely = need a multi-master solution (unless you can dictate the application = entity-design) which is more complex territory from a scale-ability and = application design standpoint but you might look at Postgres-XS and = Londiste for ideas.

 

 

Cheers,

 

Jan

 

------=_NextPart_000_0171_01CD431D.48FE7280--