> I actually got a bit more respect for PostgreSQL tonight. It seems
> that one of my scripts was not committing changes after maintenance
> was conducted. Meaning that rows that would normally be removed
> after offline archiving was completed were in fact still around.
>
> Normally at any given point in time this table would grow 50K rows
> during a day, be archived that night and then loose rows that were
> no longer needed. This process, is what allowed MySQL to maintain
> any stability as the size of this table can balloon significantly.
>
> PostgreSQL with tweaking was handling a table with nearly 300K rows.
> That size alone would of dragged the MySQL system down to a near
> grind, and since most of those rows are not needed. One can imagine
> that queries are needlessly processing rows that should be outright
> ignored.
Having used MySQL once upon a time and run into it's problems when you
have more than 1M rows in a table, it took me a while when 1st using
PostgreSQL to trust that PostgreSQL can reliably handle millions or
billions of rows without crapping out randomly and corrupting itself.
If you would have let this grow, you'd run out of disk space long
before you hit anything close to a stability, reliability, or
performance problem with PostgreSQL. I have one table in particular
that has about 1.9B rows in it right now and it conservatively takes
about 0.04ms for non-complex queries to run against the table. In
MySQL land, I wouldn't dare let something grow that big... which
would've been a huge problem because the table mentioned above isn't
logging data or something I can routinely purge. It's a strange
feeling at first to not have to design your application around size or
tuple limitations of the database any more. :) I'm glad you're
enjoying PostgreSQL. -sc
--
Sean Chittenden
