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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
