On Wed, Feb 10, 2010 at 4:16 PM, Dave Crooke dcro...@gmail.com wrote:
Hi Rama
I'm actually looking at going in the other direction
I have an app using PG where we have a single table where we just added a
lot of data, and I'm ending up with many millions of rows, and I'm finding
that the single table schema simply doesn't scale.
In PG, the table partitioning is only handled by the database for reads,
for insert/update you need to do quite a lot of DIY (setting up triggers,
etc.) so I am planning to just use named tables and generate the necessary
DDL / DML in vanilla SQL the same way that your older code does.
My experience is mostly with Oracle, which is not MVCC, so I've had to
relearn some stuff:
Just a nit, but Oracle implements MVCC. 90% of the databases out there do.
- Oracle often answers simple queries (e.g. counts and max / min) using
only the index, which is of course pre-sorted. PG has to go out and fetch
the rows to see if they are still in scope, and if they are stored all over
the place on disk it means an 8K random page fetch for each row. This means
that adding an index to PG is not nearly the silver bullet that it can be
with some non-MVCC databases.
- PG's indexes seem to be quite a bit larger than Oracle's, but that's gut
feel, I haven't been doing true comparisons ... however, for my app I have
limited myself to only two indexes on that table, and each index is larger
(in disk space) than the table itself ... I have 60GB of data and 140GB of
indexes :-)
- There is a lot of row turnover in my big table (I age out data) a
big delete (millions of rows) in PG seems a bit more expensive to process
than in Oracle, however PG is not nearly as sensitive to transaction sizes
as Oracle is, so you can cheerfully throw out one big DELETE from FOO where
... and let the database chew on it .
I find partitioning pretty useful in this scenario if the data allows is.
Aging out data just means dropping a partition rather than a delete
statement.
