Re: Questions about Postgres and Oracle
Date: Thu, 6 Dec 2012 15:35:20 -0500
Message-ID: <CAJoeKmsSvWq=59cN8CQB+RO4tAX06ErXw28nSkYEfEw-jJt0zA_at_mail.gmail.com>
Years ago, when for some specific reason we were forced to switch from Oracle to Postgres, I compiled this short list that describes Postgres specifics for Oracle DBA:
-- # of data files: in PG every segment (table, index) is stored in one or more OS files dedicated to this segment -- no control over the size of data files (versus oracle's "autoincrement", "maxsize")
-- different implementation of MVCC (multiversion concurrency control), there are write-ahead logs, but no rollback segments (or UNDO tablespace)
- requires regular "vacuuming"/autovacuuming, otherwise tables
will get bloated
-- there is also "autovacuuming" that collects statistics used when
execution plan created by query optimizer
-- PG "visibility" problem (UNDO/Rollback info stored in the table
itself) (index -> table)
-- no updates in "in place"
-- PG use of OS file cache (limiting upper limit for db buffer cache)
-- under Win env.: PG's multiple processes versus Oracle
multi-threaded architecture
-- in PG no built-in connection polling (like Oracle's shared
servers), but very good external connection polling tools, i.e.
pgbouncer, pgpool
-- PG is not instrumented as well as Oracle (though there are some
some db objects "access" stats collected in appropriate pg_..._stats
tables/views)
-- no statspack, AWR - but some info about db instance is available
and could be stored in "hand-made" repository
-- autotrace, 10046 - in PG: "explain analyze", "auto_explain" contrib module
-- PG version upgrades "in place" (without db dump/restore) comes only
when upgrading from 8.4 and up
- rather similar procedural language (PlPgSQL - PL/SQL), but no support for anonymous PL/sql blocks (at least untill version 9.0)
- no stored procedures, no packages in PG (there are in Enterprize DB)
- some support for partitioning
- PG doesn't support synonyms All in all solid database. And, oh yes readers don't block writers (or vice verse)
Regards,
Igor Neyman
On Thu, Dec 6, 2012 at 11:33 AM, Sandra Becker <sbecker6925_at_gmail.com>wrote:
> I learned yesterday that our new billing application is being written to
> use a Postgres database instead of the Oracle database it currently uses.
> Somehow or other (development hasn't figured it out yet), they will pull
> data from the Oracle database and load it into Postgres. I know nothing
> about Postgres, but I'm wondering how simple it will be to manage. Also do
> reads block writes?
> I also learned that development plans to move away from Oracle by breaking
> up and rewriting the current convoluted, complex application and building a
> lot of small Postgres databases to handle the various pieces and somehow
> joining them all together to replicate what we currently do with the Oracle
> database because "Oracle DBs are an outdated, monolithic way to handle data
> and not at all scalable". I've never heard this before yesterday and
> wonder how other companies are positioning themselves regarding the future
> of their databases. Can anyone shed a little light on the subject or point
> me to a good resource?
>
> --
> Sandy
> Transzap, Inc.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 06 2012 - 21:35:20 CET