MySQL has transactions

Postgresql's SQL implementation is way ahead of MySQL's relatively
stunted vocabulary. But on the other hand, MySQL implements most
of the popular functionality. The other thing is that MySQL is
blindingly fast and has a very uncomplicated API.

If you need real SQL and can't afford Oracle/Sybase/DB2 then the
obvious choice is Postgresql. If you need speed and simplicity
and maximum ease of administration and maintenance, that would
be MySQL.

-joseph

David Wall wrote:

At 8:30 PM -0800 1/23/01, David Wall wrote:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types, what are the main advantages postgresql has
over it? I don't think mysql has subselects and such, but they did add a
master-slave replication feature as well as online reorganization (perhaps
locks tables like vacuum?).

Anybody used both of the current releases who can comment?

I haven't seen the new mysql. My feeling is that all things being
equal, gluing transactions on top of a database implementation can
not possibly be as stable and correct as building them in from the
beginning. The design heuristic that applies is "Make it run first,
THEN make it run fast." Mysql was built to run fast from the
beginning, and now they're jamming in functionality. So if I needed
transactions I'd go with postgres until mysql has a track record.

I happen to be on a project at this very moment in which we're
converting a mysql database to postgres specifically to get
transactions, and I prefer making the conversion rather than taking a
chance on mysql transactions.

I'd be interested to hear any arguments or real-life experiences pro or con.

Steve Leibel

I haven't seen the new mysql. My feeling is that all things being
equal, gluing transactions on top of a database implementation can
not possibly be as stable and correct as building them in from the
beginning. The design heuristic that applies is "Make it run first,
THEN make it run fast." Mysql was built to run fast from the
beginning, and now they're jamming in functionality. So if I needed
transactions I'd go with postgres until mysql has a track record.

You may be right, though they did this with berkeley db, which I guess is
pretty stable with transaction support.

The problems I'm having with postgresql are mainly in the area of blobs. I
need to store several binary objects, generally in the 800-2400 byte range,
and I also need to store text messages sent by people, and I have to deal
with truncation and such to stay within the 8k row-size limit. I've heard I
can update the blocksize to 32k, but then I've read this has other negative
impacts and that 7.1 solves it anyway -- but when will that be stable and
ready?

Anyway, I'm giving them both a quick test, primarily with regard to
transactions and blobs. I can report back what I learn, but it will only be
at a testing level, and I'd prefer to hear from production users.

David

On Wed, 24 Jan 2001 01:09:06 -0500
Steve Leibel <stevel@bluetuna.com> wrote:

Hi all
I have had the unpleasant experience of developing for MySQL at work, while at home I can enjoy using PostGres for my part-time work.

At 8:30 PM -0800 1/23/01, David Wall wrote:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types, what are the main advantages postgresql has
over it? I don't think mysql has subselects and such, but they did add a
master-slave replication feature as well as online reorganization (perhaps
locks tables like vacuum?).

Anybody used both of the current releases who can comment?

I must admit, I *haven't* used the version of MySQL with transaction support enabled, but they have numerous other issues too....

I haven't seen the new mysql. My feeling is that all things being
equal, gluing transactions on top of a database implementation can
not possibly be as stable and correct as building them in from the
beginning. The design heuristic that applies is "Make it run first,
THEN make it run fast." Mysql was built to run fast from the
beginning, and now they're jamming in functionality. So if I needed
transactions I'd go with postgres until mysql has a track record.

I happen to be on a project at this very moment in which we're
converting a mysql database to postgres specifically to get
transactions, and I prefer making the conversion rather than taking a
chance on mysql transactions.

I'd be interested to hear any arguments or real-life experiences pro or con.

Firstly, I agree whole-heartedly with this. Transactions are unlikely to work well if they haven't been designed in from the outset. They're also sure to put quite substantial overhead on the processing of writes, so we'll see how well it performs now. But since I've not used the transaction-enabled MySQL at all, I think that's all I'm fit to say at this point...

Other irritations I've found with MySQL are (briefly):
- no subselects (makes for ugly hacks in code)
- no views
- no foreign keys
- no constraint support
- completely lacking date integrity checking (eg will accept '2001-15-45' as a valid date).
- no rules
- no triggers
- no intersects or unions
- table-level locking only
- inability to go beyond FS limits of filesize for databases
All in all, about the only thing MySQL has going for it is the replication.

The only issues I've had with PostGres are:
- this doesn't work: select a from b where a.fld in (select c from d where e = f intersect select c from d where e=g)
but I believe that will be working in 7.1
- 8k row limit
pretty severe, but can be fixed at copmpile-time to 32k. Completely removed for 7.1

Thanks guys!

Ciao

On Tue, 23 Jan 2001, David Wall wrote:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types, what are the main advantages postgresql has
over it? I don't think mysql has subselects and such, but they did add a
master-slave replication feature as well as online reorganization (perhaps
locks tables like vacuum?).

I've been using PostgreSQL since about 1997, and only used MySQL for the
first time last year, and MySQL just seems to sparse for a lot of things.
The lack of foreign key contraints and views is a problem for me.
PostgreSQL still has more features, like triggers, rules, referential
integrity, views, sub-selects, row-level locking, to name a few things.

I think MySQL is a very good way to introduce beginners to SQL and
database concepts, but you can only go so far with it. It's very good for
doing archiving of static data and fast retrieval for websites, but I
wouldn't build an e-commerce site with it.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Dear Lord:
I just want *___one* one-armed manager so I never have to hear "On
the other hand", again.

I completely agree! I've been "shopping" for affordable databases over the
last months and have come to the conclusion that postgresql has the most
powerful features. I ruled out mySql immediately because of the same things
you pointed out. I found Interbase to be the biggest contender of postgresql.

On my wishlist for postgresql the top three would be:

* 24x7 support (load-balancing, failover, online-backup, multiple parallel
servers, ...)
* Fast case insensitive text search via indexes (function based indexes)
* Java in the server (for triggers and functions)

I know I'm quite modest :-)

Alexander Jerusalem
ajeru@gmx.net
vknn

At 12:06 24.01.01, Zak McGregor wrote:

* Fast case insensitive text search via indexes (function based indexes)

Try to:

create table test (s text);
create index ix_test_s on test(lower(s));

And try select * from test where lower(s) = 'test';

If you made vacuum, and have enough data in the table you will get index
scan. Also you will get index scan for this:
select * from test where lower(s) like 'test%';

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

El Mi� 24 Ene 2001 08:06, Zak McGregor escribi�:

Other irritations I've found with MySQL are (briefly):
- no subselects (makes for ugly hacks in code)
- no views
- no foreign keys

Didn't know they didn't have foreign keys. :-(

- no constraint support
- completely lacking date integrity checking (eg will accept '2001-15-45'
as a valid date).

That is pretty ugly.

- no rules

- no triggers
- no intersects or unions
- table-level locking only
- inability to go beyond FS limits of filesize for databases
All in all, about the only thing MySQL has going for it is the replication.

The only issues I've had with PostGres are:
- this doesn't work: select a from b where a.fld in (select c from d where
e = f intersect select c from d where e=g) but I believe that will be
working in 7.1
- 8k row limit
pretty severe, but can be fixed at copmpile-time to 32k. Completely
removed for 7.1

Both (AFAIK) are added in 7.1

--
System Administration: It's a dirty job,
but someone told I had to do it.
-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Wow! postgresql is a miracle! :-)

I'm starting to wonder why anybody would want to use Oracle...

Alexander Jerusalem
ajeru@gmx.net
vknn

At 14:02 24.01.01, Denis Perchine wrote:

On Wed, 24 Jan 2001 10:18:56 -0300
"Martin A. Marques" <martin@math.unl.edu.ar> wrote:

El Mi� 24 Ene 2001 08:06, Zak McGregor escribi�:

Other irritations I've found with MySQL are (briefly):
- no subselects (makes for ugly hacks in code)
- no views
- no foreign keys

Didn't know they didn't have foreign keys. :-(

Not only that - this is what the MySQL site used to say about foreign
keys:

The FOREIGN KEY syntax in MySQL exists only for compatibility with other
SQL vendors CREATE TABLE commands: It doesn't do anything.
...
Foreign keys is something that makes life very complicated, because the
foreign key definition must be stored in some database and
then the hole [sic] 'nice approach' by using only files that can be moved,
copied and removed will be destroyed. In the near future we will extend
FOREIGN KEYS so that the at least the information will be saved and may be
retrieved by mysqldump and ODBC.

Ciao

David Wall writes:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types,

I count 25 documented and distinct data types for MySQL, and 30 for
PostgreSQL.

http://www.postgresql.org/devel-corner/docs/postgres/datatype.htm
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Column_types

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

There have been several recent benchmarks by non-mysql and postgres people
and the speed argument does not seem to be valid.

Even though MySQL still beats postgres in speed if they are compared with
one user on the DB, postgres seems to destroy MySQL in speed as you tend to
add users.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Joseph N. Hall @5sigma.com>" <" <heard_it_on_the_internet>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, January 24, 2001 12:19 AM
Subject: [GENERAL] Re: MySQL has transactions

Postgresql's SQL implementation is way ahead of MySQL's relatively
stunted vocabulary. But on the other hand, MySQL implements most
of the popular functionality. The other thing is that MySQL is
blindingly fast and has a very uncomplicated API.

If you need real SQL and can't afford Oracle/Sybase/DB2 then the
obvious choice is Postgresql. If you need speed and simplicity
and maximum ease of administration and maintenance, that would
be MySQL.

-joseph

David Wall wrote:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types, what are the main advantages postgresql

has

over it? I don't think mysql has subselects and such, but they did add

a

master-slave replication feature as well as online reorganization

(perhaps

David Wall schrieb:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types, what are the main advantages postgresql has
over it? I don't think mysql has subselects and such, but they did add a
master-slave replication feature as well as online reorganization (perhaps
locks tables like vacuum?).

Anybody used both of the current releases who can comment?

Thanks,
David

Another question: now the sap-db is open and the source will be
released
this years, what is the advantage of MySQL over SPA-DB ?

Marten

I count 25 documented and distinct data types for MySQL, and 30 for
PostgreSQL.

In my case, I'd just settle for a workable BLOB/LONGBLOB. I think counting
types is less interesting than meeting ones needs. They "redefine" types
like BLOB as LONGVARBINARY and TEXT as LONGVARCHAR, but does that add two
types? Anyway, blobs are prettys standard for sql, and that's what I'm
looking to have work for me.

David

On Wed, 24 Jan 2001, Peter Eisentraut wrote:

David Wall writes:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types,

I count 25 documented and distinct data types for MySQL, and 30 for
PostgreSQL.

Not to mention that Postgres has an extensible type system whereas MySQL
does not.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
When a man knows he is to be hanged in a fortnight, it concentrates his
mind wonderfully.
-- Samuel Johnson

You should probably ask the MySQL people that.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Marten Feldtmann" <M.Feldtmann@t-online.de>
To: "David Wall" <d.wall@computer.org>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, January 24, 2001 11:47 AM
Subject: Re: [GENERAL] MySQL has transactions

David Wall schrieb:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types, what are the main advantages postgresql

has

over it? I don't think mysql has subselects and such, but they did add

a

master-slave replication feature as well as online reorganization

(perhaps

I currently use both and here's my quick sound-bite summary of the things
I have used and like from each one. I won't go into the stuff I don't like
so as to avoid the start of a flame war. If you want, email me and we can
discuss it off the list.

MySQL 3.32.x
----------------
* "heap" table type (create temporary table in RAM)
* good disaster-recovery tools
* excellent documentation (online documentation forum), GNU info file
* some useful extensions to SQL (REPLACE, DROP <table> IF EXISITS, SHOW
TABLES)
* very flexible config files
* easy to upgrade between versions

PostgreSQL 7.1 beta
----------------
* mature transaction support
* stored procedures in SQL, PL/PgSQL, Perl, and TCL
* triggers, foreign keys
* more complete SQL (UNION, EXISTS, CREATE VIEW)
* excellent shell (psql)
* very friendly/well organized development team and mailing list :-)
* JDBC type 4 driver
* user-defined data types

Basically I have used MySQL for some web-based projects written in PHP
where most of the logic is application logic and the database needs are
reasonably simple, but need to be fast and stable. I generally have found
it to work well in that regard.

For some Java applications I am working on that require the use of an
application server, and have more extensive database logic, I use Pg.
Depending on your needs you may be able to use either one, although my
bias is usually to go with Pg since it's generally more featureful and
recent benchmarks have shown it to be faster under heavy loads.

Hope this helps.

Norm

--------------------------------------
Norman Clarke
Combimatrix Corp Software Development
Harbour Pointe Tech Center
6500 Harbour Heights Pkwy, Suite 301
Mukilteo, WA 98275

tel: 425.493.2240
fax: 425.493.2010
--------------------------------------

On Tue, 23 Jan 2001, David Wall wrote:

Great comparison. I've just compiled MySQL 3.23.32 with the Berkeley DB
support for transactions (the binary distribution, sad to say, does not
include it!). I know that MySQL also has a type 4 JDBC driver by Mark
Matthews and it's worked well for me in the past using the pre-BDB
transaction files.

I do love the features of Postgresql 7.0.3, but the large object support has
been really bad, causing an 800 byte binary item to require 24K of disk
space across two files, neither of which are part of the backup of the
database, and neither of which are deleted when the row pointing to them is
deleted. (There's a vacuumlo that solves that one in the background.) And
the JDBC library doesn't seem to want me to use the BYTEA type for small
byte arrays. What I really want is a good-old BLOBs with minimal overhead
that are truly part of the database and its transaction/backup world.

David

Hi David,

Thanks for the pointer on the type 4 driver for MySQL.

I too found the tuple size limitations in 7.0.3 constraining - I'm helping
develop an app that stores genetic sequences which are routinely much
larger than the 8 to 32k limit in 7.0.3.

Since my project timeline has a June release date I've been developing for
Pg 7.1 and have been quite pleased with the results and stability so far.
I believe it's pretty close to release now, so if your timeline allows for
it you may wish to give it a try.

Like anything it's not perfect but I think Pg is by and large a better
long-term solution for my project than MySQL. Our first alpha version runs
with a MySQL backend (we needed blobs), and the lack of the "standard" SQL
features (triggers, foreign keys, stored procedures) led to many
uncomfortable workarounds. Much of the core database logic needed to go
into Java, which lead to the need for extensive collaboration with other
programmers on the team.

I was afraid to use the then-alpha transactions in MySQL because "CHECK
TABLE" and isamchk did not work for BDB tables. Hopefully this is resolved
now.

By using Postgres I have been able to provide the other programmers a
clean API to access the database: complex queries are reduced to "SELECT *
FROM <view> WHERE ..." and error checking can occur inside constraints,
stored procedures and triggers. This has made all of us more a great deal
more productive.

I have heard there is some interest among the MySQL developers to get
stored procedures in MySQL using the same Zend scripting engine used by
PHP. If they were to do that, implement foreign keys, implement row-level
locking, and get the performance of BDB tables up to par with MyISAM (or
get transactions in MyISAM tables), then I think it will be quite usable
for complex schemas.

Norm

--------------------------------------
Norman Clarke
Combimatrix Corp Software Development
Harbour Pointe Tech Center
6500 Harbour Heights Pkwy, Suite 301
Mukilteo, WA 98275

tel: 425.493.2240
fax: 425.493.2010
--------------------------------------

On Wed, 24 Jan 2001, David Wall wrote:

Since my project timeline has a June release date I've been developing for
Pg 7.1 and have been quite pleased with the results and stability so far.
I believe it's pretty close to release now, so if your timeline allows for
it you may wish to give it a try.

Thanks. In fact, I've done just that after reading the release notes. I've
put in 7.1beta3, and it appears to be supporting large objects much better,
but it's harder to tell now because everything uses the OID for the name
(tables ,database). It appears that the two files are no longer being
created. I hope that this means the the large objects are also included in
the backup with pg_dump and they are automatically removed when the row
containing them is removed.

I was afraid to use the then-alpha transactions in MySQL because "CHECK
TABLE" and isamchk did not work for BDB tables. Hopefully this is resolved

now.

It does appear to be better now, but I've not spent too much time because I,
too, believe that Postgresql will be the better route for me. I just need
to figure out if the JDBC driver will let me store small binary objects
using types like BYTEA.

David

At 11:18 AM 1/24/01 -0500, Adam Lang wrote:

There have been several recent benchmarks by non-mysql and postgres people
and the speed argument does not seem to be valid.

Even though MySQL still beats postgres in speed if they are compared with
one user on the DB, postgres seems to destroy MySQL in speed as you tend to
add users.

Things change, and they've changed quite quickly. Postgres 95 was abysmal.
And Postgresql 6.4 was subpar.

Lots of people used MySQL because there wasn't a decent alternative at that
time, and it was good at what it did.

When I first started running DBs on Linux, it was either MySQL or
Postgres95. And believe me MySQL won hands down. I had problems indexing a
400,000 row table on Pg95 - it took longer than I could wait, especially
since MySQL did it a lot faster :). Sure Pg had transactions etc but it was
way too slow to be practical.

When Postgresql 6.5 came out it, it was VERY MUCH better ( many many thanks
to the developers and all involved). And I'm waiting for a solid 7.1 to fix
that <8KB issue.

So give it a few years and maybe things will be different, maybe not. But
it's been a good journey so far :), whether you're on the MySQL or
Postgresql wagon (just duck the stuff being thrown about from time to time
;) ).

Cheerio,
Link.

On Wed, 24 Jan 2001, Norman J. Clarke wrote:

* excellent documentation (online documentation forum), GNU info file

PostgreSQL has an excellent book, and good manpages and general
documentation.

When Postgresql 6.5 came out it, it was VERY MUCH better ( many many

thanks

to the developers and all involved). And I'm waiting for a solid 7.1 to

fix

that <8KB issue.

Technically..

<= BLCKSZ (can be up to 32k)

I've been using PostgreSQL with a 32k BLCKSZ since 7.0 (on a productions
server) and haven't had a problem one..

-Mitch

At 10:02 AM 1/25/01 -0500, you wrote:

When Postgresql 6.5 came out it, it was VERY MUCH better ( many many

thanks

to the developers and all involved). And I'm waiting for a solid 7.1 to

fix

that <8KB issue.

Technically..

<= BLCKSZ (can be up to 32k)

I've been using PostgreSQL with a 32k BLCKSZ since 7.0 (on a productions
server) and haven't had a problem one..

Yep but doesn't quite help my webmail app :).

I'm wondering if TOAST is going to be efficient enough for me to plonk
multimegabyte email attachments into the database.

However I've also a suspicion that there might be problems doing

INSERT INTO mytable (a) values ( 'aa.......');

Where aa... is a few megabytes long :). There's probably a query size limit
somewhere between my app and TOAST.

Cheerio,
Link.

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

I'm wondering if TOAST is going to be efficient enough for me to plonk
multimegabyte email attachments into the database.

Should work. The main limitation on TOAST is that it wants to treat
each datum as a unit, ie you must fetch or store the whole value in one
go. When your datums get big enough that that's inconvenient, you won't
like TOAST so much. I don't foresee it being a big issue for emailable
items though ...

However I've also a suspicion that there might be problems doing

INSERT INTO mytable (a) values ( 'aa.......');

Where aa... is a few megabytes long :). There's probably a query size limit
somewhere between my app and TOAST.

I've tested this, it works fine since 7.0 or so.

Amusing anecdote: since 7.0, MySQL's "crashme" test crashes when run
against Postgres. Postgres is fine, it's the perl job running the
crashme script that goes belly-up. It seems that crashme's loop that
tries to discover the maximum query length is more memory-hungry than
Postgres itself, and so the perl job hits the kernel-imposed maximum
process size before the backend does. Moral: before assuming Postgres
can't do something, make sure your own code can hold up its end...

regards, tom lane