Similarities Between Oracle and PostgreSQL

June 11, 2021

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.

Similarities Between Oracle & PostgreSQL

Recent articles