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.
- 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
Import Notes
Reply to msg id not found: 7be3f35d0608241019i745ee8dcs5d1ccd1f8d890496@mail.gmail.com
----- 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
Show quoted text
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