Thread: PostgresSQL vs. Informix
PostgreSQL Team, I have a large OEM customer (one of the top 8 for Sun worldwide) that is considering embedding PostgreSQL into the core of their primary product instead of Informix. He is trying to build his case for this change. Do we have anything that I can forward to him (something like the presentation you did at CEC?) to help him build his case? This is an urgent request from my customer given that his timeline is relatively short. Any help you can give me will be very appreciated. Thanks, Chad Hendren Original question: Have you seen any studies (either by Sun or others) that compares PostgresSQL to other commercial database software (Informix, Oracle, Sybase,etc.)? I am interested seeing a feature by feature comparison of PostgresSQL and Informix. -- Chad Hendren Solutions Architect Sun Microsystems, Inc. 3655 North Point Parkway, Suite 600 Alpharetta, GA 30005 US Phone 770-360-6402 Mobile 770-596-4778 Email Chad.Hendren@Sun.COM
Hi Chad I had a devil of a time finding any usable doc on Informix whereas Postgres/MySQL and Oracle all had documentation aplenty Im still looking for a whitepaper or any benchmarks http://www.iiug.org/forums/ids/index.cgi/noframes/read/3517 Anyone? M-- ----- Original Message ----- Wrom: VFVWRKJVZCMHVIBGDADRZFSQHYUCDDJBLVLMH To: <pgsql-general@postgresql.org> Sent: Wednesday, November 28, 2007 10:32 AM Subject: [GENERAL] PostgresSQL vs. Informix > PostgreSQL Team, > > I have a large OEM customer (one of the top 8 for Sun worldwide) that is > considering embedding PostgreSQL into the core of their primary product > instead of Informix. He is trying to build his case for this change. > Do we have anything that I can forward to him (something like the > presentation you did at CEC?) to help him build his case? > > This is an urgent request from my customer given that his timeline is > relatively short. Any help you can give me will be very appreciated. > > Thanks, > > Chad Hendren > > Original question: > > Have you seen any studies (either by Sun or others) that compares > PostgresSQL to other commercial database software (Informix, Oracle, > Sybase,etc.)? I am interested seeing a feature by feature comparison of > PostgresSQL and Informix. > > > -- > Chad Hendren > Solutions Architect > Sun Microsystems, Inc. > 3655 North Point Parkway, Suite 600 > Alpharetta, GA 30005 US > Phone 770-360-6402 > Mobile 770-596-4778 > Email Chad.Hendren@Sun.COM > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On Nov 28, 2007 10:32 AM, Chad Hendren <Chad.Hendren@sun.com> wrote: > PostgreSQL Team, > > I have a large OEM customer (one of the top 8 for Sun worldwide) that is > considering embedding PostgreSQL into the core of their primary product > instead of Informix. He is trying to build his case for this change. > Do we have anything that I can forward to him (something like the > presentation you did at CEC?) to help him build his case? > > This is an urgent request from my customer given that his timeline is > relatively short. Any help you can give me will be very appreciated. > > Thanks, > > Chad Hendren > > Original question: > > Have you seen any studies (either by Sun or others) that compares > PostgresSQL to other commercial database software (Informix, Oracle, > Sybase,etc.)? I am interested seeing a feature by feature comparison of > PostgresSQL and Informix. there was a very excellent case study, written by cc'd person, that is unhappily not available at the moment. see: http://archives.postgresql.org/pgsql-advocacy/2005-08/msg00147.php (maybe try contacting author or the open source db migration people) see also: http://www.spec.org/jAppServer2004/results/res2007q3/ also consider that sun is backing postgresql, and that informix is looking more and more like a legacy platform. not to bash informix though...but if postgresql meets your requirements it is a great db to ship out for licensing reasons, etc. merlin
Hi Merlin, Chad, Merlin Moncure schrieb: > there was a very excellent case study, written by cc'd person, that is > unhappily not available at the moment. see: > http://archives.postgresql.org/pgsql-advocacy/2005-08/msg00147.php Thank you :-) The link in the post is not up to date. The linked case study made it into my thesis on Open Source Database Migration. This is its "Informix to PostgreSQL" chapter: http://www.osdbmigration.de/thesis/node98.html The full thesis is available for download here: http://www.osdbmigration.org/misc/migrating_OSDB_jh.pdf There is some more, probably helpful stuff on my osdbmigration-site, like eg the feature matrix: http://www.osdbmigration.org:8080/osdb/osdb-features Unfortunately most of the stuff there is not up to date anymore (written Dec 2005). Feel free to contact me personally if you have any more questions. Jutta -- ____________________________________________________ Jutta Horstmann (Dipl. Inform., Dipl. Pol.) data in transit email: jh@dataintransit.com Ellerstr. 38 fon: +49.228.2401295 53119 Bonn mob: +49.176.61188960 Germany web: www.dataintransit.com ____________________________________________________
On Nov 28, 2007, at 9:32 AM, Chad Hendren wrote: > PostgreSQL Team, > > I have a large OEM customer (one of the top 8 for Sun worldwide) > that is considering embedding PostgreSQL into the core of their > primary product instead of Informix. He is trying to build his > case for this change. Do we have anything that I can forward to > him (something like the presentation you did at CEC?) to help him > build his case? > > This is an urgent request from my customer given that his timeline > is relatively short. Any help you can give me will be very > appreciated. > > Thanks, > > Chad Hendren > > Original question: > Have you seen any studies (either by Sun or others) that compares > PostgresSQL to other commercial database software (Informix, > Oracle, Sybase,etc.)? I am interested seeing a feature by feature > comparison of PostgresSQL and Informix. > You should speak with Josh Berkus (http://www.ittoolbox.com/profiles/ josh_berkus) and Jignesh Shah (http://blogs.sun.com/jkshah/), both also work for Sun. Josh has been a core Postgres team member for years now and heads up the community relations stuff and Jignesh has been heading up lots of Postgres benchmarking on Solaris. 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
On Nov 28, 2007 9:32 AM, Chad Hendren <Chad.Hendren@sun.com> wrote: > I have a large OEM customer (one of the top 8 for Sun worldwide) that is > considering embedding PostgreSQL into the core of their primary product > instead of Informix. He is trying to build his case for this change. > Do we have anything that I can forward to him (something like the > presentation you did at CEC?) to help him build his case? I can't offer much in the way of a case study, but I am an Informix customer that has been doing an informal feasibility study on switching to PostgreSQL. I've learned a ton from the high quality PG docs and from the PG mailing lists. Both have their pros and cons. I haven't had equal hardware to compare PG performance, but my impression is that it is Informix is faster and has a more robust query planner. And by that, I mean that the Informix planner does a great job without things like data type casts and other help from the SQL developer. If I had to put a number on it, I would say that PG is 75 to 80% as fast as Informix. Please, don't anyone start a flame war over this, I'm just guessing here. With equal hardware and some more tuning, I'm sure I could do better with PG. Another edge for Informix is the fully synchronous replication feature where a committed transaction on the primary is guaranteed to be committed to disk on the secondary if you configure things appropriately. High availability is critical for us and that's been a weak spot for PG as far as I'm concerned. Yes, PG has replication, but in my opinion, it's not 'enterprise' class just yet. Lastly on the Informix side, they have more advanced online backup/restore tools. It's similar to PG PITR backup but does not depend on file-system level backup tools. The option I use (called ontape) stores data in a proprietary format to disk or tape. It also has an incremental backup option so you don't have to do a full dump every time. There is a more advanced Informix backup tool called onbar, but I haven't worked with it enough to comment on it. What does PG have going for it? Price, obviously. I'd love to have that $100k that I just spent back. PG has better conformance to SQL language standards, so portability of code would be easier. PG has some better built in functions and indexing features. I prefer the PLPGSQL language for stored procedures to Informix.PG has more options for stored procedure languages (python, perl). PG has table inheritance, Informix does not. One of the most impressive things about PG has been these mailing lists. Informix support is OK, but the front-line support drones just don't have the same access to developers who really know what's going on that you can get directly on this list. Heck, PG developers have answered my questions here on the weekend! I don't know if you can even put a price on such direct access to high-level gurus. I wish I had a better feature comparison list for you. I'm sure I've failed to mention a lot of great things about PG here, but like I said, my evaluation has been pretty informal. However at this time, I have concluded that we could move our company from Informix to PG without having to give up too much, other than the big licensing fees. We use a lot of open source software at our company and I would love to add PostgreSQL to the list. Jeff
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/28/07 17:11, Jeff Larsen wrote: [snip] > > Lastly on the Informix side, they have more advanced online > backup/restore tools. It's similar to PG PITR backup but does not > depend on file-system level backup tools. The option I use (called > ontape) stores data in a proprietary format to disk or tape. It also > has an incremental backup option so you don't have to do a full dump > every time. There is a more advanced Informix backup tool called > onbar, but I haven't worked with it enough to comment on it. > > What does PG have going for it? Price, obviously. I'd love to have > that $100k that I just spent back. PG has better conformance to SQL > language standards, so portability of code would be easier. PG has > some better built in functions and indexing features. I prefer the > PLPGSQL language for stored procedures to Informix.PG has more options > for stored procedure languages (python, perl). PG has table > inheritance, Informix does not. That's similar to how I'd compare PG to the niche legacy database (Rdb/VMS) that we use. > One of the most impressive things about PG has been these mailing > lists. Informix support is OK, but the front-line support drones just > don't have the same access to developers who really know what's going > on that you can get directly on this list. Heck, PG developers have > answered my questions here on the weekend! I don't know if you can > even put a price on such direct access to high-level gurus. Rdb has Informix beat there. Greybeard engineers are always on the Rdb mailing list, and the support staff are long-timers who have access to a continuously updated 20+year VAX NOTES database that came along when Oracle purchased Rdb from DEC. > I wish I had a better feature comparison list for you. I'm sure I've > failed to mention a lot of great things about PG here, but like I > said, my evaluation has been pretty informal. However at this time, I > have concluded that we could move our company from Informix to PG > without having to give up too much, other than the big licensing fees. > We use a lot of open source software at our company and I would love > to add PostgreSQL to the list. We couldn't do it because our databases are too big for single- threaded backups. The proprietary backup scheme is transactionaly consistent and format lets us easily restore to systems with wildly different disk layouts than the original database. - -- Ron Johnson, Jr. Jefferson LA USA %SYSTEM-F-FISH, my hovercraft is full of eels -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHTgd3S9HxQb37XmcRAs5kAKCSuOLOguqhpf/DT0OxbA6ew33CWQCfaVf1 KBzM2RxA91WQEa7MM02SKZg= =lvNg -----END PGP SIGNATURE-----
Chad --
<...>
>
> Have you seen any studies (either by Sun or others) that compares
> PostgresSQL to other commercial database software (Informix, Oracle,
> Sybase,etc.)? I am interested seeing a feature by feature comparison of
> PostgresSQL and Informix.
>
Hope this ain't too late!
I can't provide a feature by feature comparison and I've never seen benchmarks comparing Informix to PostgreSQL.
I can however speak from the experience of having migrated a moderate sized set of databases from Informix 9.x to PostgreSQL. Alas, we went from Sun Solaris with Informix to Linux with PostgreSQL so a direct comparison is not realistic. But in most things we have found the two roughly the same; when there are differences Informix has usually been faster but not always.
A few almost random points in now particular order, in addition to what other posters have said:
* SQL is fairly similar with some exceptions
DISTINCT vs UNIQUE; see also DISTINCT ON; Informix handles NULLs in
concatenations differently (see COALESCE in PostgreSQL SQL); LIMIT
is handled differently. UPDATE STATISTICS is roughly like ANALYZE.
* MVCC has some real differences; in particular SELECT COUNT(*) FROM foo;
is noticibly slower in PostgreSQL (but much less so in 8.3beta).
Read the manual sections on autovacuum/vacuum carefully. Logging differs
as well. Rollbacks are cheaper in PostreSQL.
* Beware the default settings on PostgreSQL configs -- they are very
conservative and can lead to performance issues if you don't
tweak them.
* High speed loader is slightly faster than COPY FROM, but not by a lot.
* SPL conversion is a pain and I've basically recoded everything, although
it gets easiers once you get used to the differences (returning set
values for instance). Only had a few dozen procedures had to get done.
* We use the PostGIS spatial extension (akin to the Spatial Blade in Informix)
Informix had better documentation, and a bit more functionality, but
if a spatial user defined function went bad it could (and would) bring
the whole instance down with a hard crash; rebooting the server was often
necessary. The very few times we've seen PostGIS slay a PostgreSQL instance,
the PostgreSQL recovery way far easier -- sometimes not even needing a recycle.
YMMV
* Informix's replication and backups are superior. But for most purposes I think
PostgreSQL has a usable variant. But certainly not for all needs.
* PostgreSQL spawns a process for each connection (make sure you tweak shared
memory!); this leads to higher apparent loads and lots more processes in
a "ps" or "top" listing than you'd see on an Informix database server since
Informix uses internal threads. But throughput is the metric ...
* There is no such thing as raw disk space in PostgreSQL; the claim is it doesn't
gain them much since modern OSs have spent a lot of time on regular file
system speed; the argument goes on that when Informix and Oracle were young
so were the servers they were on, and they had to invent everything for
themselves.
* There is no equivalent of a "synonym"; a view can be used to fake this sometimes
but where Informix lets you create a synonym to a table in another database /
instance, PostgreSQL doesn't. dblink can be used to poke a hole to other
databases though, including non-postgres ones.
* Locking differs some -- no such thing as a page level lock.
HTH -- might add more if I think of anything.
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
On Wed, Nov 28, 2007 at 11:11:07PM -0700, Gregory Williamson wrote: > * There is no equivalent of a "synonym"; a view can be used to fake this sometimes > but where Informix lets you create a synonym to a table in another database / > instance, PostgreSQL doesn't. dblink can be used to poke a hole to other > databases though, including non-postgres ones. dblink, a contrib module, is only for Postgres databases. There are several other options including dblink-tds and DBI-Link on pgfoundry which let you connect to other kinds of data store. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Nov 28, 2007, at 6:11 PM, Jeff Larsen wrote: > I can't offer much in the way of a case study, but I am an Informix > customer that has been doing an informal feasibility study on > switching to PostgreSQL. I've learned a ton from the high quality PG > docs and from the PG mailing lists. > I switched a semi-large Informix install to PG and I had nearly opposite experience. I had nothing but issues with Informix. We had a nice expensive support contract and got snuffed over bugs and problems we'd find. (It took them 9 weeks to admit a bug, and they said they may have a fix in another 10-15 weeks. In the meantime, just learn to live with it). We also hit a cool bug that prevented us from taking a "real" backup of one of our informix installs. that was fun. Informix support again refused to help us. The PG community on the other hand is very eager to help. While we don't write them a check for $lots, they give better support. Plus given it is open source you can always look under the hood while digging up a problem. I found for some things informix would work nicer, but in other areas pg would smash it or would have other things to help make up for it. Replication was a breeze to setup in Informix. I'm trying to think of some other things.. failing miserably... heh. Granted, these tests were a while ago - dealing with Informix 7 and some 9. Some of the big things I loved in PG over Informix were the stored procedures were much more flexible, triggers worked better, I could store more data in it (I got bit by the 2^21 pages of data per table limit in Informix. Luckly that error comes up as another unrelated error if you hit it), and I found administration to be much simpler. Sorry I don't have more details, this was mostly back around 2001-02 or so. iirc, didn't some big us govt group move to PG from Informix? I think Bruce mentioned something once about that.. -- Jeff Trout <jeff@jefftrout.com> http://www.dellsmartexitin.com/ http://www.stuarthamm.net/
In answering the question, it might help to have information on two background items. The first item is the politics of the situation. I gather from Chad Hendren's post that an individual in the top-8-OEM-customer enterprise is advocating for Postgresql. The second item is the existing technical situation. What is unsatisfactory about Informix at this time? What is mission critical about Informix's performance that Postgresql will have to meet? (Postgresql has yet to conquer the computer cluster/grid arena and this frustrates many would-be power users.) Does the customer have mission critical applications that are closely coupled or dependent on Informix features or disfeatures? On Wednesday 2007-11-28 08:32, Chad Hendren wrote: > PostgreSQL Team, > > I have a large OEM customer (one of the top 8 for Sun worldwide) that is > considering embedding PostgreSQL into the core of their primary product > instead of Informix. He is trying to build his case for this change. > Do we have anything that I can forward to him (something like the > presentation you did at CEC?) to help him build his case? > > This is an urgent request from my customer given that his timeline is > relatively short. Any help you can give me will be very appreciated. > > Thanks, > > Chad Hendren > > Original question: > > Have you seen any studies (either by Sun or others) that compares > PostgresSQL to other commercial database software (Informix, Oracle, > Sybase,etc.)? I am interested seeing a feature by feature comparison of > PostgresSQL and Informix.
Jeff Trout wrote: > iirc, didn't some big us govt group move to PG from Informix? I > think Bruce mentioned something once about that.. US National Weather Service changed 170 weather sites from Informix to Postgres. Postgres worked well for them. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +