Thread: Large database design advice
Hello, I am designing database for a web product with large number of data records. - Few tables but number of objects is tens-hundreds of thousands. - less than 100 queries per second. The application has basically tens thousands of (user) accounts, every account has associated hundreds of items. My initial thought is to design it like this: Table: account --------------------- account_id BIGSERIAL Table: item --------------------- account_id BIGINT item_id INT Questions: Should table account be designed with BIGSERIAL key, or if it's going to have six-digit number of records, other method should be used? Should I use compound key for table item (account_id+item_id) or item_id should be BIGSERIAL and global sequence with key being only item_id? How generally this design will hold up against this amount of data? Thanks.
Joe,
with a normal "serial", without "big", you can have 9.223.372.036.854.775.807 records individually numbered.
so you are talking about 10*100*1000=1000000 in words one million records? That is not very big.
So you have a combined primary key for item? That is technically totally correct, no problem.
Non the less I recommend to have a separate primary key column for item. As you are starting with databases, some things will be easier to do without having a combined key as primary.
Insufficen data to parse "this amount" :) You did not give us any information about how WIDE your rows are. Some million records are nothing if there just hold social security numbers in them; they can get quite stressfull if every row carries 3 blobs containing Videos.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.
with a normal "serial", without "big", you can have 9.223.372.036.854.775.807 records individually numbered.
- Few tables but number of objects is tens-hundreds of thousands.
- less than 100 queries per second.
so you are talking about 10*100*1000=1000000 in words one million records? That is not very big.
Table: item
---------------------
account_id
item_id
So you have a combined primary key for item? That is technically totally correct, no problem.
Non the less I recommend to have a separate primary key column for item. As you are starting with databases, some things will be easier to do without having a combined key as primary.
How generally this design will hold up against this amount of data?
Insufficen data to parse "this amount" :) You did not give us any information about how WIDE your rows are. Some million records are nothing if there just hold social security numbers in them; they can get quite stressfull if every row carries 3 blobs containing Videos.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.
On Thu, Aug 24, 2006 at 06:21:01PM +0200, Harald Armin Massa wrote: > with a normal "serial", without "big", you can have > 9.223.372.036.854.775.807 records individually numbered. Not true; see the documentation: http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL "The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work just the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 2^31 identifiers over the lifetime of the table." I think you're confusing the size of the sequence (always 64 bits) with the size of the column (32-bit integer for serial, 64-bit bigint for bigserial) that will hold the sequence's value. -- Michael Fuhr
On Thu, Aug 24, 2006 at 07:19:29PM +0200, Harald Armin Massa wrote: > so with serial there are only 2.147.483.648 possible recordnumbers. Actually 2147483647 using the default sequence start value of 1 and going up to 2^31 - 1, the largest positive value a 32-bit integer can hold. You could get the full 32-bit range (4294967296) by allowing negative numbers and setting the sequence's MINVALUE and RESTART value to -2^31 (-2147483648). > Which should still be enough for "millions of records" Correct. -- Michael Fuhr
----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Harald Armin Massa" <haraldarminmassa@gmail.com> Cc: "Joe Kramer" <cckramer@gmail.com>; <> Sent: Thursday, August 24, 2006 2:37 PM Subject: Re: [GENERAL] Large database design advice > On Thu, Aug 24, 2006 at 07:19:29PM +0200, Harald Armin Massa wrote: >> so with serial there are only 2.147.483.648 possible recordnumbers. > > Actually 2147483647 using the default sequence start value of 1 and > going up to 2^31 - 1, the largest positive value a 32-bit integer > can hold. You could get the full 32-bit range (4294967296) by > allowing negative numbers and setting the sequence's MINVALUE and > RESTART value to -2^31 (-2147483648). > >> Which should still be enough for "millions of records" > > Correct. > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.405 / Virus Database: 268.11.5/426 - Release Date: 23/8/2006 > >