()

Similarities Between Oracle & PostgreSQL


In this blog, we will compare similarities between Oracle & PostgreSQL in terms of Memory, Process, Files, General architecture, etc.

COMPONENTS ORACLEPOSTGRESQL
Architecture: MemoryShared Global Area (SGA)Shared Memory
DB buffer cacheShared buffer
Log BufferWAL (Write Ahead Log) Buffer
Private Global Area (PGA)Work Memory
Architecture: Background ProcessDB WriterBackground Writer
Log WriterWAL Writer
ArchiverArchiver
MmonStats Collector
Architecture: GeneralDefault Tablespace : Systempg_default
Data Directory Viewspg_catalog
Server Process – spPostgres Process – pg
User Process – upClient Process – cp
Data stored in BlocksData stored in Pages
Block Size : 8K (Default)Page Size : 8K
Tablespace is used to store objectsTablespace is used to store objects
Table is used to store data as ROWSTable is used to store data as TUPLES
Table partition supportedTable partition supported
Range partition tableRange partition table
Btree IndexHash Index
Sys is super userpostgres is super user
PrivilegesPermissions
Table level privilegesTable level permission
Column level privilegesColumn level permission
Datapump for logical backuppg_dump for logical backup
Datapump for logical restorepg_restore for logical restore
Physical backup offlinePhysical backup offline
Physical backup online (RMAN)Physical backup online (BART, BARMAN, pg_BackRest, pg_basebackup)
automatic statistics gathering job uses dbms_StatsANALYZE 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
ListenerPostmaster
Default Port : 1521Default Port : 5432
Architecture: FilesOnline redo logfiles to records changesWAL files to records changes
Archived log filesArchived WAL Files
initilization parameter file – initsid.ora/spfilesid.oraConfiguration parameter file – postgresql.conf
Architecture: MVCC (Multi Version Concurrency Control)Oracle uses UNDO segments to implement MVCCUses the Transaction XID and xmin and xman pseudo columns for transaction row versioning
High Water MarkDead 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 AutoVacuum 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

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

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!

5 thoughts on “Similarities Between Oracle and PostgreSQL

Comments are closed.