PostgreSQL vs Firebird feature comparison finished

Tony Caduto wrote:

Other than that I would say PG kicks butt.

You're just realising that? :-)

If there is any interest I could also add MySQL 5.0 to the mix as the
third column.

I'd be interested to see that.

Regards, Dave

Tony Caduto wrote:

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb

Couple of corrections Tony:

- You don't necessarily need to stop the postmaster to take a filesystem
backup -
http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP.
Obviously that assumes logs will be replayed during recovery.

- The native win32 port will run on FAT32, we just prevent the installer
from initdb'ing on such a partition. You can do it manually however, but
tablespaces won't work.

I'm a little puzzled about why you list multi-threaded architecture as a
feature - on Windows it's a little more efficient of course, but the
multi-process architecture is arguably far more robust, and certainly
used to be more portable (I'm not sure that's still the case for
platforms we actually care about).

Regards, Dave.

On Aug 23, 2007, at 12:00 AM, Tony Caduto wrote:

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb

When comparing in the grid the only major advantage FB has is
probably BLOB support.
PG only suppports 1 gb while FB supports 32gb. Bytea is pretty
slow as well when compared to the FB BLOB support.

Actually, Postgres's large object facility allows storage of binary
data up to 2GB in size. http://www.postgresql.org/docs/8.2/
interactive/largeobjects.html

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Dave Page wrote:

Couple of corrections Tony:

- You don't necessarily need to stop the postmaster to take a filesystem
backup -
http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP.
Obviously that assumes logs will be replayed during recovery.

- The native win32 port will run on FAT32, we just prevent the installer
from initdb'ing on such a partition. You can do it manually however, but
tablespaces won't work.

I'm a little puzzled about why you list multi-threaded architecture as a
feature - on Windows it's a little more efficient of course, but the
multi-process architecture is arguably far more robust, and certainly
used to be more portable (I'm not sure that's still the case for
platforms we actually care about).

Regards, Dave.

Thanks Dave.
Will update ASAP.

I agree with you on the multi-threaded. I think I will add a note
saying the the multi-threaded architecture is only advantageous on Windows.
I have seen instances where the threaded version of Firebird completely
craps out because one of the threads has issues.

Will also make a note that it can run on FAT32 with some limitations.

Later,

Tony

Dave Page wrote:

Tony Caduto wrote:

Other than that I would say PG kicks butt.

You're just realising that? :-)

Ah, I new that around 2004 :-) I just have to convince Delphi users of
that :-)

Later,

Tony

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tony Caduto wrote:

Dave Page wrote:

Tony Caduto wrote:

Other than that I would say PG kicks butt.

You're just realising that? :-)

Ah, I new that around 2004 :-) I just have to convince Delphi users of
that :-)

My understanding is the Firebird is relatively non-configured though
isn't it? For a large scale client server app there is no question that
PG is going to wipe the universe with Firebird, but I would think that
Firebird may be better suited for embedded shipping that kind of thing.

Sincerely,

Joshua D. Drake

Later,

Tony

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

http://archives.postgresql.org/

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGzb0ZATb/zqfZUUQRAttRAJ4mamXurjzMDH9kqD3cWt9EC6RT7wCfRpkE
efUsuyz2f1GQKSs4dfgzr+A=
=JHrY
-----END PGP SIGNATURE-----

--- Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb

One row that you could elaborate on is:
CHECK CONSTRAINTS support for correlated sub-queries.
PostgreSQL doesn't official support this kink of constraint unless it is rolled up in a function.
I am not sure what support FB has for this.

Another Constraint row you could add would be:
CREATE ASSERTION which is a schema level constraint. Currently PostgreSQL doesn't support this,
I am not sure if FB does either.

Also you could mention PostgreSQL support for row-wise comparison:
i.e. WHERE ( last_name, city, gender ) = ( 'Doe', 'Paris', 'female' );

and PostgreSQL support for additional SQL comparison operators:
i.e. WHERE (( last_name, city, gender ) = ( 'Doe', 'Paris', 'female' )) IS UNKNOWN;
-- return all people who might meet this criteria if their null field where known.

Regards,
Richard Broersma Jr.

On 8/23/07, Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb
If there is any interest I could also add MySQL 5.0 to the mix as the
third column.

If you do, you should really do it as MySQL-isam and MySQL-innodb.

the limitations of each table handler are often as much different as
to make it another database server. i.e. no full text search on
innodb tables, no foreign keys on isam tables, etc...

If anyone is interested, I could answer the questions for Oracle and
you could add those, too. Be interesting to see a chart like that
(that stays updated after releases) for a large assortment of
databases.

If we add a bunch of different databases, it might be easier to
manipulate if it was stored in a database. MS-Access maybe? ;-)

LewisC

--- Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb

When comparing in the grid the only major advantage FB has is
probably
BLOB support.
PG only suppports 1 gb while FB supports 32gb. Bytea is pretty
slow as
well when compared to the FB BLOB support.

The other area is Character sets and collation. They support it at
a
field level as well as the database.

Other than that I would say PG kicks butt.

If there is any interest I could also add MySQL 5.0 to the mix as
the
third column.

Later,

Tony

---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an
appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly

-----------------------------------------------------------
Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

LewisC's Random Thoughts
http://lewiscsrandomthoughts.blogspot.com/

EnterpriseDB: The Definitive Reference
http://tinyurl.com/39246e
----------------------------------------------------------

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Lewis Cunningham wrote:

If anyone is interested, I could answer the questions for Oracle and
you could add those, too. Be interesting to see a chart like that
(that stays updated after releases) for a large assortment of
databases.

If we add a bunch of different databases, it might be easier to
manipulate if it was stored in a database. MS-Access maybe? ;-)

Let's get this up on the wiki.

Joshua D. Drake

LewisC

--- Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb

When comparing in the grid the only major advantage FB has is
probably
BLOB support.
PG only suppports 1 gb while FB supports 32gb. Bytea is pretty
slow as
well when compared to the FB BLOB support.

The other area is Character sets and collation. They support it at
a
field level as well as the database.

Other than that I would say PG kicks butt.

If there is any interest I could also add MySQL 5.0 to the mix as
the
third column.

Later,

Tony

---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an
appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly

-----------------------------------------------------------
Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

LewisC's Random Thoughts
http://lewiscsrandomthoughts.blogspot.com/

EnterpriseDB: The Definitive Reference
http://tinyurl.com/39246e
----------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGzdNoATb/zqfZUUQRAh/sAJ92Ko3lB6eCGSyJJyoPw5sn4VI44QCdGTjc
XzyzrDQKnA7mgoNXDohvUpY=
=Um04
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tony Caduto wrote:

Dave Page wrote:

Couple of corrections Tony:

- You don't necessarily need to stop the postmaster to take a filesystem
backup -
http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP.

Thanks Dave.
Will update ASAP.

I agree with you on the multi-threaded. I think I will add a note
saying the the multi-threaded architecture is only advantageous on
Windows.

And Solaris.

Joshua D. Drake

I have seen instances where the threaded version of Firebird completely
craps out because one of the threads has issues.

Will also make a note that it can run on FAT32 with some limitations.

Later,

Tony

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGzdyOATb/zqfZUUQRAjYtAJ9GxNvF46JXM34i6Kf0RE7TLwkGggCeN5QD
eELS+fyixPqlB/dYiGkC/vM=
=wN+j
-----END PGP SIGNATURE-----

On Thu, 23 Aug 2007, Tony Caduto wrote:

If there is any interest I could also add MySQL 5.0 to the mix as the third
column.

As already mentioned, MyISAM and InnoDB should get their own columns.

This is a really good comparision, focusing on features that I think
people understand rather than so much on technical trivia. Someone else
mentioned moving it onto the Wiki. Questions that pop into my head:

-Tony, would be you be comfortable with your work being assimilated into a
larger table that was hosted somewhere else but credited yours as a
source?

-Is the Wiki the right place to build this table at? Large Wiki tables
get very difficult to manage. It may be easier to build the table in
something else and then have that generate markup instead. I'd rather
edit this in a spreadsheet and write something to massage that into final
form than do all the edits within the Wikipedia editor.

-If this is going to turn into the grand feature comparision table,
everyone might as well be thinking from day one that inevitably there will
be columns for Oracle (with a volunteer to fill out already), SQL Server,
DB2, etc. and plan a useful way to manage all that data from the
beginning. That's another reason why the Wiki is a bad way to cope with
this data; adding another column is a painful and error-prone operation.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Thu, Aug 23, 2007 at 03:30:30PM -0400, Greg Smith wrote:

On Thu, 23 Aug 2007, Tony Caduto wrote:

If there is any interest I could also add MySQL 5.0 to the mix as the
third column.

As already mentioned, MyISAM and InnoDB should get their own columns.

Yes.

This is a really good comparision, focusing on features that I think
people understand rather than so much on technical trivia. Someone else
mentioned moving it onto the Wiki. Questions that pop into my head:

-Tony, would be you be comfortable with your work being assimilated into a
larger table that was hosted somewhere else but credited yours as a
source?

-Is the Wiki the right place to build this table at? Large Wiki
tables get very difficult to manage.

They're very easy to manage using things like the Firefox/Mozilla
plugin viewsourcewith
<https://addons.mozilla.org/en-US/firefox/addon/394&gt;

It may be easier to build the table in something else and then have
that generate markup instead. I'd rather edit this in a spreadsheet
and write something to massage that into final form than do all the
edits within the Wikipedia editor.

See above :)

-If this is going to turn into the grand feature comparision table,
everyone might as well be thinking from day one that inevitably
there will be columns for Oracle (with a volunteer to fill out
already), SQL Server, DB2, etc. and plan a useful way to manage all
that data from the beginning. That's another reason why the Wiki is
a bad way to cope with this data; adding another column is a painful
and error-prone operation.

Could be. Try viewsourcewith with your favorite editor and see
whether it eases the pain :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:

I agree with you on the multi-threaded. I think I will add a note
saying the the multi-threaded architecture is only advantageous on
Windows.

And Solaris.

I'm not entirely sure what makes multi-threading be advantageous on a
specific operating system, but I think FreeBSD should be added to that
list as well... They've been bench marking their threading support using
multi-threading in MySQL (not for the db, mind you - just for load ;),
and it performs really well.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

Alban Hertroys wrote:

Joshua D. Drake wrote:

I agree with you on the multi-threaded. I think I will add a note
saying the the multi-threaded architecture is only advantageous on
Windows.

And Solaris.

I'm not entirely sure what makes multi-threading be advantageous on a
specific operating system, but I think FreeBSD should be added to that
list as well... They've been bench marking their threading support using
multi-threading in MySQL (not for the db, mind you - just for load ;),
and it performs really well.

I'm not sure I necessarily agree with those two - we have no real proof
that a multithreaded architecture would be significantly more efficient
than a multi process. It certainly wouldn't be as robust as an error in
one backend thread could bring down the entire server.

Windows is a special case in this regard. The OS has been designed from
the outset as a threaded environment. The important point is not that
Windows threads are necessarily any more efficient than their Solaris or
FreeBSD counterparts, but that the multi-process architecture is alien
to Windows and is inherently slower. Two of the major bottlenecks we
have on Windows as a result are backend startup time and shared memory
access speed - both of which are significantly slower than on *nix.

Regards, Dave

Dave Page wrote:

Alban Hertroys wrote:

Joshua D. Drake wrote:

I agree with you on the multi-threaded. I think I will add a note
saying the the multi-threaded architecture is only advantageous on
Windows.

And Solaris.

I'm not entirely sure what makes multi-threading be advantageous on a
specific operating system, but I think FreeBSD should be added to that
list as well... They've been bench marking their threading support using
multi-threading in MySQL (not for the db, mind you - just for load ;),
and it performs really well.

I'm not sure I necessarily agree with those two - we have no real proof
that a multithreaded architecture would be significantly more efficient
than a multi process. It certainly wouldn't be as robust as an error in
one backend thread could bring down the entire server.

Windows is a special case in this regard. The OS has been designed from
the outset as a threaded environment. The important point is not that
Windows threads are necessarily any more efficient than their Solaris or
FreeBSD counterparts, but that the multi-process architecture is alien
to Windows and is inherently slower. Two of the major bottlenecks we
have on Windows as a result are backend startup time and shared memory
access speed - both of which are significantly slower than on *nix.

Regards, Dave

Thanks for explaining (again).

So actually the remark shouldn't be that "the multi-threaded
architecture is only advantageous on Windows", but more like "the
multi-process architecture is disadvantageous on Windows and hence a
multi-threaded architecture is preferred (on that particular OS)".

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

Alban Hertroys wrote:

So actually the remark shouldn't be that "the multi-threaded
architecture is only advantageous on Windows", but more like "the
multi-process architecture is disadvantageous on Windows and hence a
multi-threaded architecture is preferred (on that particular OS)".

Yeah - but I'm not sure thats necessarily something that should have a
place on a bullet point comparison.

Regards, Dave

"Dave Page" <dpage@postgresql.org> writes:

Alban Hertroys wrote:

So actually the remark shouldn't be that "the multi-threaded
architecture is only advantageous on Windows", but more like "the
multi-process architecture is disadvantageous on Windows and hence a
multi-threaded architecture is preferred (on that particular OS)".

Yeah - but I'm not sure thats necessarily something that should have a place on
a bullet point comparison.

Note that while we use the OS's "threads" api we're not really any more
multi-threaded on Windows than we are on Unix. We don't use any shared memory
data structures we don't on Unix using SysV shared memory, we don't use any
mutexes or other threaded programming techniques that we don't use on Unix,
and so on.

It's purely a question of which API we use to create the threads of execution.
Not an architectural change in Postgres.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Aug 24, 2007, at 4:09 AM, Alban Hertroys wrote:

I'm not entirely sure what makes multi-threading be advantageous on a
specific operating system, but I think FreeBSD should be added to that
list as well... They've been bench marking their threading support
using
multi-threading in MySQL (not for the db, mind you - just for load ;),
and it performs really well.

Maybe only for FreeBSD >= 6.0. Prior to that, the threading was
rather lackluster. I still think the separate process model is
superior, in that you get private data spaces with them.