Re: partitioning question -- how to guarantee uniqueness across partitions

On Jun 29, 3:45 am, armstrong(dot)w(dot)(dot)(dot)(at)gmail(dot)com (Whit Armstrong) wrote:
> Thanks, Tom.
>
> Let me give a little more detail on my actual data rather than the
> simple example I sent.
>
> I have a 60GB table of loan balances, which I've partitioned into 26 tables.
>
> The loan id's are a sequence of 6 characters, so the partitioning rule
> I've used is the first character of the loan id, which yields roughly
> equal sized partitions of 2.8 GB or so.
>
> Each loan can only have one balance per month, so the primary key on
> each partition is set to be loan_id and asofdate.
>
> However, this data is meant to be available via a rails application,
> hence, the need for a surrogate key of integers which is unique across
> the entire set of partitions.
>
> Creation of new rows in the partitioned tables should not be an issue
> under normal circumstances because I see that all of the child tables
> use the same sequence for generating new id's.
>
> However, what makes me nervous is that there is no explicit constraint
> in the database that prevents duplicate id's from being created, and
> I'm not sure how the rails app would react if for whatever reason
> duplicate id keys wound up in the table.
>
> Any suggestions?
>
> Thanks,
> Whit
>
>
>
> On Sun, Jun 28, 2009 at 1:27 PM, Tom Lane<t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Whit Armstrong <armstrong(dot)w(dot)(dot)(dot)(at)gmail(dot)com> writes:
> >> I have a simple example copied from the 8.3 manual on partitioning
> >> (http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html).
>
> >> My question is, if you create a serial type in the parent table which
> >> is meant to be the primary key across all the partitions, how does one
> >> guarantee uniqueness of that key?
>
> > One doesn't.  That is not an appropriate way to set up a partitioned
> > table.  You need a primary key that can actually be used as a meaningful
> > partitioning key.  In this example, the id is completely useless and
> > what you should be looking at is making the data_value be the primary
> > key.
>
> >                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Hi Whit,

you could consider using GUIDs instead of Integer for primary keys.
Here is a nice blog post explaining how to do this in Rails:
http://ariejan.net/2008/08/12/ruby-on-rails-uuid-as-your-activerecord-primary-key/

Best Regards,
Arndt Lehmann