Similarities Between Oracle & PostgreSQL
In this blog, we will compare similarities between Oracle & PostgreSQL in terms of Memory, Process, Files, General architecture, etc.
|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|
|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|
|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|
|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.
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.
- Setup Streaming Replication with repmgr and barman in PostgreSQL
- Offline Restore or Clone Database on the same server or rename database in Oracle
- Configure Logical Replication in PostgreSQL
- Upgrade Database (July 2021 Patch Apply 19c) 19.3 to 19.12 in Oracle
- Configure Streaming Replication (Record Based Log Shipping) in PostgreSql
201 Total Views, 5 Views Today
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.
Thanks for the visits!
Share Learn Grow!