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

Databases that are ACID compliant must provide consistency, even when there are concurrent updates.

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

PostgreSQL tuple versioning

PostgreSQL is doing something like a Copy-On-Write. When you update one column of one row, the whole row is copied to a new version, probably in a new page, and the old row is also modified with a pointer to the new version. The index entries follow the same: as there is a brand new copy, all indexes must be updated to address this new location. All indexes, even those who are not concerned by the column that changed, are updated just because the whole row is moved. There’s an optimization to this with HOT (Heap Only Tuple) when the row stays in the same page (given that there’s enough free space).

Oracle block versioning

Oracle avoids moving rows at all price because updating all indexes is often not scalable. Even when a row has to migrate to another block, Oracle keeps a pointer (chained rows) so that the index entries are still valid. That’s only when the row size increases and doesn’t fit anymore in the block. Instead of Copy-on-Write, the current version of the rows is updated in-place and the UNDO stores, in a different place, the change vectors that can be used to re-build a previous version of the block.

No-Bloat demo (Oracle)

Here is a small demo to show this no-bloat beauty. The code and the results explained is after the screenshot.

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

https://twitter.com/FranckPachot Passionate about all databases. Oak Table member, Oracle ACE Director & OCM 12c. Other blog posts: http://blog.dbi.pachot.net

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