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 July 2024 along with enabled Download Link
- 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
Apprecіate the recοmmendation. Wіll, try it out.
Thanks for your post.
Thanks, Sardar for the review and feedback.
Regards,
Team DBsGuru.
Great post.
Thanks for the review and feedback.
Regards,
Team DBsGuru.