Обсуждение: 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.


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
>
>