Thread: MySQL has transactions

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

speed with tranactions on
row level locking
I though that postgresql had more data type
extensable interface
choice of index types
better performance under load
triggers
to name a few. Has anyone actually benchmarked mySQL with transations
enabled?



-----Original Message-----
From: David Wall [mailto:d.wall@computer.org]
Sent: Wednesday, 24 January 2001 3:30 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] MySQL has transactions


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

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

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-timework. 

> 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
issuestoo.... 

>
> 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
fromthe outset. They're also sure to put quite substantial overhead on the processing of writes, so we'll see how well
itperforms 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:
>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

> * 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
----------------------------------

On Wed, 24 Jan 2001, Willis, Ian (Ento, Canberra) wrote:

> speed with tranactions on
> row level locking
> I though that postgresql had more data type

no, MySQL has 'type of the week' contests to see how many non-compliant
types they can think of, and add them ...

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

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
> 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.

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

> 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

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
> > locks tables like vacuum?).
> >
> > Anybody used both of the current releases who can comment?

>> I though that postgresql had more data type

> no, MySQL has 'type of the week' contests to see how many non-compliant
> types they can think of, and add them ...

Something I'm curious about: does MySQL support user-defined datatypes?
If so, how hard is it to make a new datatype?  I think datatype
extensibility is one of the cooler features of Postgres ...

            regards, tom lane

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

On Wed, 24 Jan 2001, Tom Lane wrote:

> Something I'm curious about: does MySQL support user-defined datatypes?

No, it does not.

> If so, how hard is it to make a new datatype?  I think datatype
> extensibility is one of the cooler features of Postgres ...

Check this out:
http://www.mysql.com/documentation/mysql/bychapter/manual_Comparisons.html#Compare_PostgreSQL


                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
If you give a man enough rope, he'll claim he's tied up at the office.

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

> > Something I'm curious about: does MySQL support user-defined datatypes?
>
> No, it does not.
>
> > If so, how hard is it to make a new datatype?  I think datatype
> > extensibility is one of the cooler features of Postgres ...

Making new datatypes sounds good, but in practice how easy is it to do?  I
don't see old SQL92 types like BLOB being supported by the database, and
large objects appears quite broken in its implementation, so adding new
types can't be all that easy to do.

David

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

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

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:

> 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

> 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.