To Postgres or not

Am Dienstag, den 12.07.2005, 17:16 +0000 schrieb Ted Slate:

Hello Everyone,

I considering moving a product to Solaris/Linux and trying to find a good
DB. Oracle is out due to cost so as far as i know the only reasonable
alternatives are Postgres or Codebase or MySQL. Does anyone here have any
experience using Codebase or MySql? If I stick with a true RDBMS then
Codebase is out. So that leaves Postgres and MySQL. I'm very used to all
the comforts of Oracle so I think Postgres stacks up better but maybe some
veterans could shed some light.

In other words, is Postgres really that much better than MySQL and/or the
other way around?

Also, in my searches I ran across an company called EnterpriseDB and another
like it. Basically they offer Postgres support. So I'm a little concerned
that I'm just robbing Peter to pay Paul.

There is no "on size fits all" when it comes to databases.
You write a list of things you really need and then go and compare
with whats on the market. Every other decision path will just go
religous.

Otoh, you can use this little list if its just some special
features you are looking for:

http://sql-info.de/mysql/gotchas.html

There are a number of good, objective, and informative comparisons available on the Internet, such as http://www.databasejournal.com/sqletc/article.php/3486596<http://www.databasejournal.com/sqletc/article.php/3486596>. You can Google something like "Postgresql mysql firebird comparison" to access these. I am migrating an application from Paradox and took quite a while to review the options before settling on PostgreSQL. I am in the early stages of the migration and so far, I have not been disappointed.

I've been using MySql for a long time, and have just recently moved to
PostgreSql (currently I have a few projects already running on postgresql)..
I have to say I prefer PostgreSql over MySql. One major factor in this is
the price (as mysql isn't free for commercial projects).
I can't really tell you to choose PostgreSql over Mysql because you might
just like MySql better .. my advice is - try them both and then decide.

Regards,
Tadej

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ted Slate
Sent: Tuesday, July 12, 2005 7:17 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] To Postgres or not

Hello Everyone,

I considering moving a product to Solaris/Linux and trying to find a good
DB. Oracle is out due to cost so as far as i know the only reasonable
alternatives are Postgres or Codebase or MySQL. Does anyone here have any
experience using Codebase or MySql? If I stick with a true RDBMS then
Codebase is out. So that leaves Postgres and MySQL. I'm very used to all
the comforts of Oracle so I think Postgres stacks up better but maybe some
veterans could shed some light.

In other words, is Postgres really that much better than MySQL and/or the
other way around?

Also, in my searches I ran across an company called EnterpriseDB and another
like it. Basically they offer Postgres support. So I'm a little concerned
that I'm just robbing Peter to pay Paul.

Thanks

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Jul 12, 2005, at 1:16 PM, Ted Slate wrote:

If I stick with a true RDBMS then Codebase is out. So that leaves
Postgres and MySQL.

The first sentence rules out MySQL, so the second sentence should
read "So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably expecting an ACID
database, which rules out MySQL too).

Vivek Khera, Ph.D.
+1-301-869-4449 x806

On Tue, Jul 12, 2005 at 05:16:44PM +0000, Ted Slate wrote:

Hello Everyone,

I considering moving a product to Solaris/Linux and trying to find a
good DB. Oracle is out due to cost so as far as i know the only
reasonable alternatives are Postgres or Codebase or MySQL. Does
anyone here have any experience using Codebase or MySql? If I stick
with a true RDBMS then Codebase is out. So that leaves Postgres and
MySQL. I'm very used to all the comforts of Oracle so I think
Postgres stacks up better but maybe some veterans could shed some
light.

PostgreSQL is more like an Oracle, DB2, MS-SQL Server, etc. than it is
like a MySQL or a BerkeleyDB. If your app is more Oraclish than
BerkeleyDBish, PostgreSQL is very likely your choice.

In other words, is Postgres really that much better than MySQL
and/or the other way around?

Here's my experience. With PostgreSQL, when you reach for a new
capability, it's usually right there, or at worst it's easy to
construct. With MySQL, you're constantly running into barriers and
having to kludge around them.

Also, in my searches I ran across an company called EnterpriseDB and
another like it. Basically they offer Postgres support. So I'm a
little concerned that I'm just robbing Peter to pay Paul.

Many kinds of support are available, and some of the best is free.
This mailing list, for example, is excellent, and people on Core
monitor it. <irc://irc.freenode.net/postgresql> is good, too. There
are also paid support options, as you've mentioned.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Even though PostgreSQL is more like an Oracle which is a good thing. It's
like Oracle in function not in cost and adminstration. PostgreSQL not only
fits into the enterprice really well it also fits in the mom and pop
shops(dentist office,corner store, you name it that may not have any IT
folks. So you get lots of the Oracle like bell and whistles while not having
the Oracle weight(admin,cost,complexity,etc) that only a larger company can
hold. Oracle claims to have a small business version of the 10g database.
But the docs say it's for small to mid sized "enterprises" not Joe Blow on
the corner shop or even Joe Blow and his 100 employees selling widgets from
their basement.

So my opinion in 99% of cases is why not use PostgreSQL over MySQL and in
many cases why not use PostgreSQL over Oracle. What does MySQL really offer
you? Go ahead and tell me it can query faster with one user logged in much
faster. If that is the case use flat files why waste your time on a
relational database. Anyways I could go on and on, I'll stop now and leave
it at this. PostgreSQL is great piece of software that we should all be
great full is out there for us to use!

PostgreSQL has in a nutshell:
Great documentation
Great community
Great features
Great cost(free)

On Wed, Jul 13, 2005 at 04:24:53PM -0400, Vivek Khera wrote:

On Jul 12, 2005, at 1:16 PM, Ted Slate wrote:

If I stick with a true RDBMS then Codebase is out. So that leaves
Postgres and MySQL.

The first sentence rules out MySQL, so the second sentence should
read "So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably expecting an ACID
database, which rules out MySQL too).

But there's no need to take our word for it... just google for 'mysql
gotchas'. :)
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Vivek Khera wrote:

The first sentence rules out MySQL, so the second sentence should read
"So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably expecting an ACID
database, which rules out MySQL too).

Does MySQL with InnoDB not qualify as an ACID-compliant database?

-Neil

On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote:

Vivek Khera wrote:

The first sentence rules out MySQL, so the second sentence should read
"So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably expecting an ACID
database, which rules out MySQL too).

Does MySQL with InnoDB not qualify as an ACID-compliant database?

Not if you intermix InnoDB tables with MyISAM tables, which AFAIK many
people seem to do.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... �Qui�n es el machito que tendr�a carnet?" (Mafalda)

On Jul 13, 2005, at 9:57 PM, Alvaro Herrera wrote:

On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote:

Vivek Khera wrote:

The first sentence rules out MySQL, so the second sentence
should read
"So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably expecting an ACID
database, which rules out MySQL too).

Does MySQL with InnoDB not qualify as an ACID-compliant database?

Not if you intermix InnoDB tables with MyISAM tables, which AFAIK many
people seem to do.

Don't forget that MySQL will create a table as MyISAM, even when told
to create it as an InnoDB table, if, by some chance, InnoDB is not
enabled. InnoDB has to be specified at the time of table creation,
but MySQL, true to form, will not generate a warning that InnoDB is
not enabled or present, and will instead create the table using
MyISAM. Worse yet, if this is not caught, and since MySQL also fakes
transaction support, it can look like everything is working, until
data has to be rolled back, and it can't be. Needless to say, this
can lead to some pretty messed data in your tables, depending on your
application.

It is the silence, combined with doing what it feels like and not
what you tell it to do, that makes me shy away from MySQL for
applications where data integrity is vital. You can't trust an error
to be raised when it should be. Very dangerous. I've heard these
issues are being addressed in 5.0 or 5.1 (I forget which).

# alvherre@alvh.no-ip.org / 2005-07-14 00:57:57 -0400:

On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote:

Vivek Khera wrote:

The first sentence rules out MySQL, so the second sentence should read
"So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably expecting an ACID
database, which rules out MySQL too).

Does MySQL with InnoDB not qualify as an ACID-compliant database?

Not if you intermix InnoDB tables with MyISAM tables, which AFAIK many
people seem to do.

MyISAM tables are/were useful for poor man's sequences exactly
because rollback doesn't affect them.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's way in.

Is there an easy way to go about this? Am I about to write my first
server side function for postgresql?

"Neil" == Neil Conway <neilc@samurai.com> writes:

Neil> Does MySQL with InnoDB not qualify as an ACID-compliant database?

Not when you can store a value that is larger than allowed, and it
is silently truncated to be within range. no *I* integrity there.

That's regardless of InnoDB or not.

MySQL is a toy compared to PostgreSQL.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

# postgresql@oculardata.com / 2005-07-14 02:14:16 -0500:

I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's way in.

CREATE TABLE tbl (col VARCHAR(32) NOT NULL);
CREATE UNIQUE INDEX ON tbl (UPPER(col));

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

On Thu, 14 Jul 2005 02:14:16 -0500, Rob Brenart <postgresql@oculardata.com> wrote:

I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's way in.

Is there an easy way to go about this? Am I about to write my first
server side function for postgresql?

how about
create unique index tbl_iname_idx on tbl (lower(name_field))

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

Am Donnerstag, den 14.07.2005, 00:57 -0400 schrieb Alvaro Herrera:

On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote:

Vivek Khera wrote:

The first sentence rules out MySQL, so the second sentence should read
"So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably expecting an ACID
database, which rules out MySQL too).

Does MySQL with InnoDB not qualify as an ACID-compliant database?

Not if you intermix InnoDB tables with MyISAM tables, which AFAIK many
people seem to do.

Even not in a clean InnoDB environment because still data is silently
truncated. (e.g. smallint takes 200 and truncates it to 127. Thats not
ACID :)

Rob Brenart schrob:

I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's
way in.

Is there an easy way to go about this?

Would creating an unique index on an expression be easy enough?
E.g. create unique index idx_foo_unique_nocase on foo ((lower(bar)));

Am I about to write my first server side function for postgresql?

I'm afraid not :-)

regards,
Andreas
--

The first sentence rules out MySQL, so the second sentence should
read "So that leaves Postgres". Your problem is solved ;-)

(If you are accustomed to Oracle, you are probably

expecting an ACID

database, which rules out MySQL too).

Does MySQL with InnoDB not qualify as an ACID-compliant database?

Not if you intermix InnoDB tables with MyISAM tables, which
AFAIK many people seem to do.

AFAIK the system tables has to be MyISAM, so it's not really possible to
run in any non-intermixed way.

//Magnus

To everyone,

Thanks very much for your replies, and if these responses are any indication
of Postgres quality then I'd be in good hands.

Ted

_________________________________________________________________
Don�t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/