Similarities Between Oracle & PostgreSQL
In this blog, we will compare similarities between Oracle & PostgreSQL in terms of Memory, Process, Files, General architecture, etc.
COMPONENTS | ORACLE | POSTGRESQL |
Architecture: Memory | Shared Global Area (SGA) | Shared Memory |
DB buffer cache | Shared buffer | |
Log Buffer | WAL (Write Ahead Log) Buffer | |
Private Global Area (PGA) | Work Memory | |
Architecture: Background Process | DB Writer | Background Writer |
Log Writer | WAL Writer | |
Archiver | Archiver | |
Mmon | Stats Collector | |
Architecture: General | Default Tablespace : System | pg_default |
Data Directory Views | pg_catalog | |
Server Process – sp | Postgres Process – pg | |
User Process – up | Client Process – cp | |
Data stored in Blocks | Data stored in Pages | |
Block Size : 8K (Default) | Page Size : 8K | |
Tablespace is used to store objects | Tablespace is used to store objects | |
Table is used to store data as ROWS | Table is used to store data as TUPLES | |
Table partition supported | Table partition supported | |
Range partition table | Range partition table | |
Btree Index | Hash Index | |
Sys is super user | postgres is super user | |
Privileges | Permissions | |
Table level privileges | Table level permission | |
Column level privileges | Column level permission | |
Datapump for logical backup | pg_dump for logical backup | |
Datapump for logical restore | pg_restore for logical restore | |
Physical backup offline | Physical backup offline | |
Physical backup online (RMAN) | Physical backup online (BART, BARMAN, pg_BackRest, pg_basebackup) | |
automatic statistics gathering job uses dbms_Stats | ANALYZE gathers statistics for the query planner to create the most efficient query execution paths | |
explain plan for select * from emp; | explain select * from emp; | |
select * from table (dbms_xplan.display) | EXPLAIN WITH ANALYZE option the query will actually execute and timing | |
Listener | Postmaster | |
Default Port : 1521 | Default Port : 5432 | |
Architecture: Files | Online redo logfiles to records changes | WAL files to records changes |
Archived log files | Archived WAL Files | |
initilization parameter file – initsid.ora/spfilesid.ora | Configuration parameter file – postgresql.conf | |
Architecture: MVCC (Multi Version Concurrency Control) | Oracle uses UNDO segments to implement MVCC | Uses the Transaction XID and xmin and xman pseudo columns for transaction row versioning |
High Water Mark | Dead tuples and Bloat concept | |
Table deleted – but still consuming space s know as dead tuple | ||
Space occupied by the dead tuples referred as Bloat | ||
Undo management Auto | Vacuum job – > Scan for dead tuples and cleaned which are not needed for any running transaction to reclaim space |
Click on below to know more about Memory, Process and Physical Storage of PostgreSQL.
PostgreSQL Architecture – Memory Components
PostgreSQL Architecture – Process
Postgresql Architecture – Physical Storage Structure
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
Recent articles
- Oracle Critical Database Patch ID for April 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for January 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for October 2023 along with enabled Download Link
- Oracle Critical Database Patch ID for July 2023 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2023 along with enabled Download Link