PostgreSQL and ORACLE architecture — in comparison

This talk will review the following components of the PostgreSQL DBMS, comparing architecture decisions with those of ORACLE DBMS:

1. What is working DBMS instance, which processes are there and what for DBMS uses them?
2. Which structures DBMS manages?
3. Durability mechanics.
4. MVCC design and database restoration possibilities
5. Storing data on the physical media

Each decision will be evaluated based on the experience with DBMSes of choice, ease of administration and future improvement possibilities.

Talk will be interesting for:
- PostgreSQL users, as it will allow to have a look on other DBMS;
- PostgreSQL administrators, as ORACLE provides huge administrative possibilities, which could be adopted also in the PostgreSQL;
- PostgreSQL hackers, as Postgres is being actively developed and this talk can bring up new development segments;
- those willing to migrate from ORACLE (or any other DBMS) into the Open-Source-based projects, as this talk will show the features of the open-source DBMS PostgreSQL in comparison with a commercial product. And Postgres looks very strong indeed!

This talk will review the following components of the PostgreSQL DBMS, comparing architecture decisions with those of ORACLE DBMS:

1. What is working DBMS instance, which processes are there and what for DBMS uses them?
2. Which structures DBMS manages?
3. Durability mechanics.
4. MVCC design and database restoration possibilities
5. Storing data on the physical media

Each decision will be evaluated based on the experience with DBMSes of choice, ease of administration and future improvement possibilities.

Presentation will start with the review of the database initialization and which files are necessary for the DBMS to start:
- initdb, postgresql.conf, postgresql.auto.conf, pg_hba.conf
- CREATE DATABASE / init.ora / spfile / listener.ora + tnsnames.ora

Discussion of the memory structures (global and local ones) will follow, including management of those structures by DBMS:
- shared_buffers / DSM / sessions' own memory
- SGA / PGA (a lot to tell here, really)

Next review of the background and foreground processes will follow:
- bgwriter, checkpointer, wal writer, logger, archiver, autovacuum, stats collector / сессии / replication
- SMON / PMON / DBW / LGWR / CKPT / D000 / S000 / etc.

Versionning, durability and backup mechanics will come next:
- MVCC / WAL / PITR / streaming replication
- UNDO / REDO / RMAN / Media Recovery / Flashback / DataGuard

In the end talk will discuss the ways DBMS store data on the media.

Each section will review common and individual approaches of DBMSes in comparison with each other, focusing on PostgreSQL as the leading DBMS of the conference.

Talk will be interesting for:
- PostgreSQL users, as it will allow to have a look on other DBMS;
- PostgreSQL administrators, as ORACLE provides huge administrative possibilities, which could be adopted also in the PostgreSQL;
- PostgreSQL hackers, as Postgres is being actively developed and this talk can bring up new development segments;
- those willing to migrate from ORACLE (or any other DBMS) into the Open-Source-based projects, as this talk will show the features of the open-source DBMS PostgreSQL in comparison with a commercial product. And Postgres looks very strong indeed!