Re: Are you frustrated with PostgreSQL

On Mon, 14 Jul 2003, Terence Chang wrote:

> Hi all:
>
> I am new to PostgreSQL DB, however I have years experience with Oracle 8i
> and MS SQL. I am in the process to promot PostgreSQL to my future client,
> due to the cost. I am just wondering if overall people feels frustrated with
> PostgreSQL or feels happey with it.

The most frustrating thing about postgresql is other people's uninformed
opinions about it. I.e. a lot of folks here where I work haven't looked
at it since version 6.5 came out, and assume it hasn't changed much since
then.

> I know MySQL is simpiler and cheap. With my years experience with enterprise
> level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
> stored procedure. Sorry to MySQL lovers.

MySQL is also, unfortunately, written by people who don't "get it". I
like Monty and his crew, really, but things like storing 0000-00-00 as a
valid date, or storing numerics as floats internally point out that MySQL
wasn't designed by database and accounting types, but more by web
developer / C hackers.

> I have the following questions. Please reply me offline, so the mailling
> list won't get flood. Thanks!

Nah, this list can handle it, and we all like to pitch in and add our two
cents to these kinds of conversations.

> 1. What is your favorite GUI tool for PostgreSQL?

A white board... :-)

I don't use one. We are, however, looking at the Postgresql Manager from
EMS. For our Windows type folks this seems like the closest to the tools
available for MSSQL et. al.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

No, there isn't enough work to be a full time DBA. I am the default DBA
(i.e. I was stupid enough to say "sure, I'll install it." :-)

Postgresql has a moderately steep learning curve for the first month or
two, then you get the hang of it and have your cron jobs set up to
vacuumdb it and all is good.

> 3. What is the biggest challenge you have with PostgreSQL? Administration or
> Programming?

Administration: scheduled backups, vacuumings, analyzings all need to be
monitored to ensure they are happening. Upgrades can be a bit painful
sometimes for some folks, but I've never had an issue, since our
production databases are only a few gigabytes total.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

I love it. It's one of the nicest open source projects going, written by
some of the best coders out there, and has one of the most civil tones on
its mailing lists. Support is better than any commercial package I've
ever used.

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?

I've been running Postgresql since version 6.5.2 or so. I once caused the
backend to die back then with an unconstrained join on some huge tables.
Since 7.0 came out I have had zero unscheduled downtime. That's about 4
years of continuous operation, always up.

My primary recommendation if you are looking at running postgresql is to
first test your hardware thouroughly (assume it's bad, prove it's good.)
then test your postgresql install thouroughly to make sure it and your OS
are properly tuned to handle the load you'll be throwing at it.

If you can start small (intranet development first) and move on to bigger
projects, that will give you time to learn the system while its load is
slowly increasing instead of putting something online your first week with
1,000 users.

Postgresql can handle that many users, but you've got to know what you're
doing both with it and the OS you're running it on, as well as how you
write you application. If you're going to handle thousands of users, look
at connection pooling.