Re: PostgresSQL vs. Informix - Mailing list pgsql-general

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