Re: Article on MySQL vs. Postgres

On Tue, 4 Jul 2000, Benjamin Adida wrote:

> Dropping indexes and recreating them does not cause downtime. I've run a

Just got hit with a 'bits moved;recreate index' on the PostgreSQL search
engine ... drop'd and re-created index on the fly, no server shut down ...

> couple of postgres-backed web sites for months on end with no issues. I've
> survived a heavy slashdotting on my dual Pentium II-400, with Postgres
> WRITES and READS on every Slashdot-referred hit, resulting in perfectly
> respectable serving times (less than 3-4 seconds to serve > 20K of data on
> each hit). No caching optimization of any kind on the app layer. And I'd
> forgotten to vacuum my database for a few days.

We had a *very* old version of PostgreSQL running on a Pentium acting as
an accounting/authentication backend to a RADIUS server for an ISP
... uptime for the server itself was *almost* 365 days (someone hit the
power switch by accident, meaning to power down a different machine
*sigh*) ... PostgreSQL server had been up for something like 6 months
without any problems, with the previous downtime being to upgrade the
server ...
> > > Features:
> > Postgres is undoubtedly far, far more advanced than MySQL is. Postgres
> > now supports foreign keys, which can help with referential integrity.
> > Postgres supports subselects and better support for creating tables as
> > the result of queries. The "transaction" support that MySQL lacks is
> > included in Postgres, although you'll never miss it on a website, unless
> > you're building something for a bank, and if you're doing that, you'll
> > use oracle.
> 
> I'm just shocked at this. Where did this "transactions aren't necessary"
> school of thinking originate? 

Ummm, hate to disparage someone else, and I may actually be incorrect, but
I'm *almost* certain that MySQL docs, at one time, had this in it
... where they were explaining why they didn't have and never would have
transaction support.  Obviously this mentality has changed since, with
the recent addition of transactions through a third-party database product
(re: Berkeley DB) ...

> I've been developing database-backed web sites for 5 years now, and I
> can't conceive of building a serious web site without transactions.
> How do you guarantee that a record and its children records are all
> stored together successfully? Do you run on a magic power grid that
> never fails? Do you never have code-related error conditions that
> require rolling back a series of database edits?

Actually, hate to admit it, but it wasn't until recently that I clued into
what transaction were for and how they wre used :(  I now use them for
just about everything I do, and couldn't imagine doing without them ...