Re: Why Not MySQL? - Mailing list pgsql-hackers

> > We use PostgreSQL for a fairly large application I wrote, the database
is
> > still pretty small, it carries info on about 25-30,000 people and about
> > 5,000 jobs. Recently we've had huge trouble with PostgreSQL -- it seems
that
> > every month I stump someone with the obscure things that happen to our
data
> > :-)
>
> What version are you using ?

6.5.3 :-)

> > >From corrupted indexes to corrupted system tables, it's almost always
> > unrecoverable. Luckily I always have a backup to restore from and the
world
> > goes on... We've only recently started to notice that the backend is
slowing
> > down. It seems that with every additional applicant added it get
> > exponentially slower... So, sadly I have to go find another backend for
this
> > application -- a commercial one too so we can get "commercial support"
> > (yuck)..
>
> Could you be a little more specific on your performance issues ?

Well, I'm just noticing that simple select queries are taking 3-5 seconds -
on a table with 63 fields, 10000ish  rows of data. The ID fields are
indexed, as well as several always-searched varchar() fields.

Here are some typical queries my application might generate. Please, let me
know if you see anything that can be improved!

select * from applicants as a where a.created::date = '05-01-2000' and
a.firstname ~* '^mitch' limit 10 offset 0

select * from applicants as a,applicants_states as s where a.firstname ~*
'^mitch' and s.rstate='AL' and s.app_id=a.app_id limit 10 offset 0

.... There are 63 fields in the 'applicants' table, all of which are
searchable. Would it be a good or bad thing to index all fields that are
searchable?

> The usual way to deal wih them is tuning your db structure and/or
> queries or
> setting backend options to use more memory for stuff or other such
> things.

I'd love some pointers!  This machine has lots-n-lots of memory. I'd love to
make postgre use more than normal if it would get me better speed!

> If there is something wrong with the structure or queries, then a
> database
> switch will help you very little, unless your front-end tool has some
> special
> support for _some_ databases and not for others.

PHP/C is what I use to access PostgreSQL.

> > So, could you guys suggest some other backends I might look into?
>
> The usual - Oracle, Interbase, Informix, DB2, Sybase, Solid
>
> The website is usually obtained by putting www inf front and com at the
> end ;)
>
> And let us know of your results.
>
> > I know
> > it's an odd place for me to ask but the flat truth is that I think *I*
am to
> > blame for my Postgres troubles and even taking all of the problems into
> > account I think PG is the best damn free RDBMS out there. It's
functionality
> > is superior to everyone else's, it's developers are no less than amazing
and
> > well -- I trust you guys to give me some honest opinions.. The
functionality
> > I need is basically what PG has.. Transactions are a must as well as
some
> > sort of sequence -- stability over performance but performance is very
> > important too. It also needs to run native on FreeBSD..
> >
> > Oracle is out as we use FreeBSD and someone out there decided that they
> > wouldn't support FreeBSD (in the license as well as in the code!)..
>
> Is FreeBSD a religious issue there or can it be negotiated ?

Not religious, though I love that OS like I love my right arm.. I am stuck
with x86 hardware -- that's pretty much it (and eliminates Solaris and
several other as a possible OS I suppose)..

THANKS!!

-Mitch Vincent