Re: 110,000,000 rows

On 27 May 2010 14:48, Nikolas Everett <nik9000(at)gmail(dot)com> wrote:
> I've had a reporting database with just about a billion rows.  Each row
> was horribly large because the legacy schema had problems.  We partitioned
> it out by month and it ran about 30 million rows a month.  With a reasonably
> large box you can get that kind of data into memory and indexes are
> almost unnecessary.  So long as you have constraint exclusion and a good
> partition scheme you should be fine.  Throw in a well designed schema and
> you'll be cooking well into the tens of billions of rows.
> We ran self joins of that table reasonably consistently by the way:
> SELECT lhs.id, rhs.id
> FROM bigtable lhs, bigtable rhs
> WHERE lhs.id > rhs.id
>      AND '' > lhs.timestamp AND lhs.timestamp >= ''
>      AND '' > rhs.timestamp AND rhs.timestamp >= ''
>      AND lhs.timestamp = rhs.timestamp
>      AND lhs.foo = rhs.foo
>      AND lhs.bar = rhs.bar
> This really liked the timestamp index and we had to be careful to only do it
> for a few days at a time.  It took a few minutes each go but it was
> definitely doable.
> Once you get this large you do have to be careful with a few things though:
> *It's somewhat easy to write super long queries or updates.  This can lots
> of dead rows in your tables.  Limit your longest running queries to a day or
> so.  Note that queries are unlikely to take that long but updates with
> massive date ranges could.  SELECT COUNT(*) FROM bigtable too about 30
> minutes when the server wasn't under heavy load.
> *You sometimes get bad plans because:
> **You don't or can't get enough statistics about a column.
> **PostgreSQL doesn't capture statistics about two columns together.
>  PostgreSQL has no way of knowing that columnA = 'foo' implies columnB =
> 'bar' about 30% of the time.
> Nik

What's that middle bit about?

> AND '' > lhs.timestamp AND lhs.timestamp >= ''
> AND '' > rhs.timestamp AND rhs.timestamp >= ''

If blank is greater than the timestamp? What is that doing out of curiosity?

Thom