Re: Large tables (was: RAID 0 not as fast as expected)

Mike,

> On Mon, Sep 18, 2006 at 07:14:56PM -0400, Alex Turner wrote:
> >If you have a table with 100million records, each of which is
200bytes
> long,
> >that gives you roughtly 20 gig of data (assuming it was all written
> neatly
> >and hasn't been updated much).
>
I'll keep that in mind (minimizing updates during loads). My plan is
updates will actually be implemented as insert to summary/history table
then delete old records. The OLTP part of this will be limited to a
particular set of tables that I anticipate will not be nearly as large.

> If you're in that range it doesn't even count as big or
challenging--you
> can keep it memory resident for not all that much money.
>
> Mike Stone
>
I'm aware of that, however, *each* scan could be 100m records, and we
need to keep a minimum of 12, and possibly 50 or more. So sure, if I
only have 100m records total, sure, but 500m, or 1b... According to
Alex's calculations, that'd be 100G for 500m records (just that one
table, not including indexes).

From what Luke was saying, there's some issues once you get over a
couple hundred GB in a single table, so in the case of 12 scans, it
looks like I can squeeze it in given sufficient hardware, but more than
that and I'll have to look at history tables or some other solution. I'd
also think doing some sort of summary table/materialized view for
count/sum operations would be a necessity at this point.

I'm not sure that this is a good topic for the list, but in the interest
of sharing info I'll ask, and if someone feels it warrants a private
response, we can discuss off list. Would Bizgres be able to handle
tables > 200GB or so, or is it still quite similar to Postgres (single
threaded/process issues per query type things..)? What about Bizgres
MPP? And also, does switching from Postgres to Bizgres or Bizgres MPP
require any application changes?

Thanks for all the help,

Bucky