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

On Jun 28, 2009, at 11:45 AM, 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.

As long as your inserts always use the default value,
nextval('sequence_name'), for the id values then that can never happen
unless you at some point use setval('sequence_name', X) where X <= the
max value already present in your partitioned table, which you should
never be doing anyway.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k