Thread: 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/

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

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)

# 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/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

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             :                 :
+---------------------------------------+-----------------+

# 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

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

On Wed, 2005-07-13 at 23:57, 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.

And the tendency to NOT throw errors or warnings is frightening.

create table blah (field1 int, field2 text) type=inoodb

will gladly create a myisam table, no warnings, no errors, nothing
else.  Then if you try to start a transaction on it, it quietly starts
it, no errors again, until you try to roll it back.

Sorry, but that does NOT qualify as ACID, no matter what some folks may
say on their blogs.

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/

Klint Gore wrote:

>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.
>
>
Not suprising when you get the same advice from a handful of people, but
this is exactly what I needed, thanks all.

>>  create unique index tbl_iname_idx on tbl (lower(name_field))

By the way, in case it wasn't obvious, this has a nice side-benefit.
Namely, PG will use that index for caseless lookups, so you can do
this:

   select * from tbl where lower(name_field) = lower('John');

very efficiently.

- John D. Burger
   MITRE

On Jul 14, 2005, at 3:14 AM, Rob Brenart 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?

create your unique index using the lower() function.

and don't steal threads to start a new one (ie, replying then just
changing the subject)

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