MVCC in Oracle vs. PostgreSQL, and a little no-bloat beauty

Let’s take an example:

  • at 12:00 I have 1200$ in my account
  • at 12:00 My banker runs long report to display the accounts balance. This report will scan the ACCOUNT tables for the next 2 minutes
  • at 12:01 an amount of 500$ is transferred to my account
  • at 12:02 the banker’s report has fetched all rows
  • When only the current version of blocks can be read, the updates must be blocked until the end of the query, so that the update happens only at 12:02 after the report query terminates. Then reading the current state is consistent:
  • When the previous version can be read, because the previous values are saved when an update occurs, the + $500 update can happen concurrently. The query will read the previous version (as of 12:00):

PostgreSQL tuple versioning

Oracle block versioning

No-Bloat demo (Oracle)

14:23:13 SQL> create table DEMO 
as select 1 num, current_timestamp time from dual;
Table created.
14:23:13 SQL> connect demo/demo@//localhost/PDB1
Connected.
14:23:13 SQL> set transaction isolation level serializable;
Transaction succeeded.Elapsed: 00:00:00.001
14:23:13 SQL> insert into DEMO values(-1,current_timestamp);1 row created.Elapsed: 00:00:00.003
14:23:13 SQL> declare
2 pragma autonomous_transaction;
3 begin
4 for i in 1..1e6 loop
5 update DEMO set num=num+1, time=current_timestamp;
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.Elapsed: 00:01:51.636
14:25:05 SQL> alter session set statistics_level=all;Session altered.Elapsed: 00:00:00.002
14:25:05 SQL> select * from DEMO;
NUM TIME
______ ______________________________________
1 12-AUG-19 02.23.13.659424000 PM GMT
-1 12-AUG-19 02.23.13.768571000 PM GMT
Elapsed: 00:00:01.011
14:25:06 SQL> select * from dbms_xplan.display_cursor(format=>'allstats last');
PLAN_TABLE_OUTPUT
____________________________________________________________________
SQL_ID 0m8kbvzchkytt, child number 0
-------------------------------------
select * from DEMO
Plan hash value: 4000794843--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 1000K|
| 1 | TABLE ACCESS FULL| DEMO | 1 | 2 | 1000K|
--------------------------------------------------------------
Elapsed: 00:00:00.043
14:25:06 SQL> commit;Commit complete.Elapsed: 00:00:00.00414:25:06 SQL> exec dbms_stats.gather_table_stats(user,'DEMO');PL/SQL procedure successfully completed.Elapsed: 00:00:00.034
14:25:06 SQL> select num_rows,blocks from user_tables where table_name='DEMO';
NUM_ROWS BLOCKS
___________ _________
2 8
Elapsed: 00:00:00.005
14:25:06 SQL> exit

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Acceptance Test the Shopping Cart

Simple servers with Ruby

Top 10 Strategies for a Successful Job Search as a Software Engineer (Developer)

Azure Monitor — Setting alert metrics and tracking resource health

Data Driven Development for Stream Processing

But Did You See the Moonwalking Bear?

🕵🏻‍♂️ New Airdrop: Linkka Finance

Star Developer Series: Ekundayo Blessing

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Franck Pachot

Franck Pachot

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

More from Medium

Run Data Analysitcs on Kubernetes 2X times faster

How to Run a Cassandra Operation in Docker

Denoise CT Scans with Kubeflow, Apache Spark & Apache Mahout

F6 Automobile Technology’s Multimillion Rows of Data Sharding Strategy Based on Apache…