Forum OpenACS Q&A: What is PostgreSQL like for someone used to Oracle?

Collapse
1: What is PostgreSQL like for someone used to Oracle?
Posted by Dave Abercrombie on 04/18/01 03:55 AM
I am curious about PostgreSQL as compared to Oracle. As someone who is very familiar with Oracle (DBA, PL/SQL, query optimization, supplied packages, data dictionary, dynamic performance views, unusual indexes like bitmaps and index-organized tables, etc.) what would using PostgreSQL feel like? What would I miss from Oracle? What would I fall in love with in PostgreSQL? Just curious ...
Collapse
2: Response to What is PostgreSQL like for someone used to Oracle? (response to 1)
Posted by Stephen van Egmond on 04/18/01 04:28 AM
PostgreSQL feels like Oracle minus lots and lots and lots of administration overhead.  You don't have to create table spaces, it just uses files in your Unix volume.  Installation takes 30 seconds if you use a Debian package or RPM, from deciding to install, to being in psql (the equivalent of sql*plus).From a DBA perspective, Postgres still has live backups, multi-gigabytes data sets, fine-grained locking, and the like.From an application construction perspective, Postgres does not have a lot of the bells and whistles that Oracle does: it doesn't automaticallly integrate with foreign data sources, it doesn't have a JVM or a web server built in.Of the items you mentioned:pl/sql: pgsql has a procedural language that is reminiscent of Oracle's.query optimization: you can build indices, e.g. CREATE INDEX foo ON function(table.colume) will build an index on that function's output.packages - ?data dictionary - ?. If you mean whether the database can be self-describing, there is a COMMENT ON command that survives dump/reload.Dynamic performance views - ?unusual indexes - this is pgsql's bread and butter.index-organized tavles - see pgsql's CLUSTER and VACUUM.I've installed both postgres and oracle. Postgres has far, far fewer sharp edges.  There are fewer features that would appeal to your average bank, but it's good enough for about 85% of the installations I can imagine.
Collapse
3: Response to What is PostgreSQL like for someone used to Oracle? (response to 1)
Posted by Don Baccus on 04/18/01 05:03 AM
In addition, PG has interesting academic features, like rtree (GiST) indices that some Russians are using to build a full-text search index.  The index type has interesting formal attributes that frankly I've not had time to study, but the Russians are making good use of it, helping write documentation, and I find it intriguing (in the Berkeley days it was added to make indexing geographical data, with varying point in a polygon as an example).

PG is much simpler than Oracle.  Its optimizer's surprisingly good (and getting better with each release).  It has warts in that it has a non-overwriting storage manager (nightly VACUUMs are a necessity, not practical if you're, say, SABRE or the IRS on April 15th but for must of us no problem).  It's not able to compute queries directly in an index.

On the other hand, the basic engine is a couple of times faster than Oracle, and that's no bullshit.  When it fails - due to optimizer weakness, lack of features, etc - it really hurts.  What?  Can't just glom in InterMedia?  SQLJ?  Built-in Java libraries callable from PL/SQL?  All true.

What?  My query plan sucks?  True also - but we know that the Oracle optimizer doesn't always get it right.  Oracle has those optimizer hints, and PG gives you ways to turn off certain kinds of joins and table scans.  Oracle has finer granularity in this area - maybe it needs it?

On the other hand you don't need a library to administer it.  You don't even need a book...

For a web service, PG has a lot of the important things.  Web services  are high-concurrency (if you're lucky, if you're not and go bankrupt at least you'll not have spent part of your VC money on an Oracle license).  PG has a sophisticated row-level concurrency model with an equally sophisticated suite of locking options for those who want to squeeze the ultimate level of concurrency out of their system.

Similar to Oracle in this regard.

You should just try it and decide yourself.  The fact that the ACS runs just fine under it, and can support heavy-traffic sites, should tell you something.  sourceforge.net is backed by PG (after concurrency issues caused them to abandon MySQL).