On 27 May 2010 14:48, Nikolas Everett <nik9000@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
