Fwd: Re: Searchable chess positions in a Postgress DB

-------- Original Message --------
Subject: Re: [GENERAL] Searchable chess positions in a Postgress DB
Date: Fri, 13 Apr 2012 00:33:17 +1200
From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
Organisation: ArchiDevSys
To: Sidney Cadot <sidney(at)jigsaw(dot)nl>

On 12/04/12 01:14, Sidney Cadot wrote:
> Hi Gavin,
>
> I appreciate the neatness of your proposed solution, but I feel that
> having a separate "piece" table may blow up the storage requirements
> by too much. I'm looking at 400M Positions; a Position on average has
> about 23.8 pieces, So I'd be looking at a single 9,5 billion row
> table; and each Piece row will be about 20 bytes(?). I have no feeling
> about how Postgres will hold up on a table that big. Also it's about 6
> times the amount of storage compared to the direct approach of storing
> a 64-byte board in a Position row.

Hi Sydney,

Postgres can handle tables much biggrr than you need...

http://www.postgresql.org/about
[...]
There are active PostgreSQL systems in production environments that
manage in excess of 4 terabytes of data. Some general PostgreSQL limits
are included in the table below.
Limit Value
Maximum Database Size Unlimited
Maximum Table Size 32 TB
Maximum Row Size 1.6 TB
Maximum Field Size 1 GB
Maximum Rows per Table Unlimited
Maximum Columns per Table 250 - 1600 depending on column types
Maximum Indexes per Table Unlimited
[...]

Note the table def should use char(1), not char - too much Java
peogramming! :-)

The way I defined the tables would faciltate quite a wide range of queries.

I used to play in the New Zealand national open, but never got near the
prize money!

Cheers,
Gavin