Re: Postgres VS Oracle

Josh Berkus wrote:
> David,
>
> First of all, it's considered very rude to cross-post to 5 different mailing
> lists. pgsql-advocacy is the right list for this question; please don't post
> to more than one list at a time in the future.
>
>> I am DBA for Oracle and beginner on Postgres. For an company in France, I
>> must make a comparative study, between Postgres and Oracle. Can you send
>> any useful document which can help me.
>> Scalability ? Performance? Benchmark ? Availability ? Architecture ?
>> Limitation : users, volumes ? Resouces needed ? Support ?
>> Regards
>
> You may not be aware, but we have a large French PostgreSQL community:
> www.postgresqlfr.org
>
> I know that Jean-Paul and Dimitri have experience in porting applications, so
> you should probably contact them to get local help & information on comparing
> the two DBMSes.

I'm not French but I've written a few web apps that used both PostgreSQL and
Oracle, among others, as back ends. That is, the same app was deployed to
both RDBMSes. We had very small data sets, so I cannot speak authoritatively
about high-end performance or scalability. My main concerns were SQL
compatibility and completeness, ease of development and ease of database
administration.

Oracle and PostgreSQL came out about even on SQL compatibility and
completeness. I do not know where either has an advantage. Moving DDL
between the two was a matter of knowing that PostgreSQL calls CLOB "TEXT" and
BLOB "BYTEA" - annoying but not fatal. Working in Java there is no difference
between the SQL or JDBC calls once the database is up.

I particularly look for features like subSELECTs anywhere SQL allows them,
complete JOIN syntax, and literal row expressions
("( 'Smith', 30, 0, 'Mr.')"). Both systems are excellent in this regard.

Ease of development has to do with tools like psql. PostgreSQL is easier for
me to use. Oracle has these huge and somewhat opaque tools, from my point of
view. Oracle's tools seem to me geared primarily for folks who manage
enterprise databases and probably aren't intended as much for the lowly
programmer during app development.

For maintenance I find Postgres much easier. Oracle's tools and procedures,
installation style and the like have much more of a "big iron" feel to them,
which might lead one to wonder if PostgreSQL is lackadaisical about enterprise
db maintenance. It is not. AFAICS either product gives the DBA everything
needed to keep that terabyte data store humming. The learning bump for
PostgreSQL looks much smaller to me, though.

At the low end PostgreSQL is clearly superior. I am much more able to
effectively manage small- to moderate-load databases without being a fully
expert DBA using PostgreSQL. I am not experienced at managing large-scale
databases but on the smaller scale I've done a bit, and PostgreSQL is much
lighter-weight on the practitioner's mind.

YMMV.

--
Lew