Re: database file size bloat - Mailing list pgsql-general

Maybe you might want to try out MySQL?  A little while ago, I compared both
MySQL and PostgreSQL to see how they stacked up (for my purposes, anyway).  I
came to the conclusion that while MySQL is a very fast read-only database, it
doesn't support transactions, row-level locks, stored-procedures, sub-selects,
etc.  PostgreSQL has a lot more basic database support, but it is harder to
install and maintain (in my opinion), has worse documentation, and a number of
interesting quirks...for example, the fixed-size row limitation that can only be
changed by a recompilation, or the VACUUM problem described here.  Other issues
I had included the way the backend seemed to work...it is certainly very
demanding when it comes to shared memory, and I had concerns about the process
pool (whether or not pg-sql could handle enough connections) instead of threads,
which most other databases seem to use.

MySQL is an easier installation, requires less maintenance, doesn't have
row-size limitations, and is fully threaded.  PostgreSQL supports a great deal
of basic SQL functionality that MySQL doesn't.  MySQL is good for read-only
databases because it seems to be rather ineffective when it comes to concurrent
writes to the same table (either lock the whole table or lock nothing at all)
and no commit/rollback.  PostgreSQL seems to offer what MySQL lacks, but in
reality it also lacks a lot of what MySQL has.

In my case, I am still looking, but maybe there is a more immediate solution out
there for you. ;)

-Brian

>Date: Fri, 14 Apr 2000 16:29:23 +1000 (EST)
>From: Matthew Arnison <matthewa@physics.usyd.edu.au>
>To: Ed Loehr <eloehr@austin.rr.com>
>cc: pgsql-general@postgresql.org, Rabble-Rouser <rabble@protest.net>, Manse
Jacobi <jacobi@freespeech.org>
>Subject: Re: [GENERAL] database file size bloat
>MIME-Version: 1.0
>X-Mailing-List: pgsql-general@postgresql.org
>X-UIDL: da0ddbd3341ee90e18bd247f40f6bffe
>
>the bloat is a big problem. i just checked it again, and the db has
>balloooned to 20 megs again, with i think 2650 unused pages. this is after
>vacuuming it last night. i guess we need to setup the vacuum script to run
>every hour. i am worried about this locking out users during the
>vacuuming, although i guess if it happens more often, it should take less
>time.
>
>meanwhile, as for upgrading, i think i'll try 6.5.3 first.
>
>version 7 is still in beta. is it atleast as stable as 6.5.3? is it
>atleast as fast as 6.5.3?
>
>this is a live site allright.
>
>thanks for your advice,
>matthew.
>
>On Thu, 13 Apr 2000, Ed Loehr wrote:
>
>> Matthew Arnison wrote:
>> >
>> > three times now this week (on two different servers) the raw database on
>> > disk has ballooned in size, from about 10 megs to 50 megs in two cases,
>> > and from about 10 megs to 250 megs in another case.
>> >
>> > a VACUUM VERBOSE ANALYZE; cleans it back down to the proper size, but
>> > meanwhile all the queries take half a minute, instead of less than a
>> > second. and our load average skyrockets.
>>
>> Hi Matthew,
>>
>> I have no explanation for the bloat, but it is a well-known "postgresqlism"
>> that you should consider running vacuum analyze at least nightly, possibly
>> more frequently.  [I run it hourly.]
>>
>> Also, there are about 300 reasons to consider upgrading to 7.0, most having
>> to do with bug fixes and performance improvements.  Unfortunately, there
>> may be a few incompatibilities (particularly in some pl/pgsql
>> incantations), so don't assume a seamless upgrade.
>>
>> Regards,
>> Ed Loehr
>>
>
>