Re: Questions about Postgres and Oracle

From: Igor Neyman <igor.neyman_at_gmail.com>
Date: Thu, 6 Dec 2012 15:47:29 -0500
Message-ID: <CAJoeKmvWqgOAZgVTsS_GyDeqoJiorT-SHZYR-n9Bz-MD=pyf9g_at_mail.gmail.com>

Yes, not in the "core", but as extensions added:

pg_stat_statements
pg_autoexplain
pg_buffercache
pg_adminpack
pg_freespacemap

pgstattuple
etc...

On Thu, Dec 6, 2012 at 3:44 PM, Matthew Zito <matt_at_crackpotideas.com> wrote:

> I seem to remember reading somewhere that in Postgres 8.4+ and 9+, there's
> a lot more performance statistics available, though there's definitely not
> as many tools to help analyze them compared to Oracle.
>
>
> On Thu, Dec 6, 2012 at 3:35 PM, Igor Neyman <igor.neyman_at_gmail.com> wrote:
>
>> 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
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 06 2012 - 21:47:29 CET