how databases work - for developers, accidental dbas and managers

© 2013 EDB All rights reserved. 1

How Databases Work

• Gurjeet Singh | 09.18.14


Database Architect @ EDB– Index Advisor

– Optimizer Hints

– Postgres Enterprise Manager

– Postgres Plus Cloud Database

Postgres Contributor– Postgres Hibernator and others

Learnt From Mistakes

Who Am I


Impart some new knowledge, that helps you do yourjob better.

(The “Full Monty” slides is where the meat of the talkis)

Goal


• ACID

• Application Interaction• Backend Operations• Concurrent/Cooperating Operations

• OS/Disk Interaction• NoSQL

Agenda


ACID


ACID

Traits of a transaction in an RDBMS• Atomicity

– Changes done by a transaction are one unit; all ornothing.

• Consistency– Transaction end leaves database in a consistent

state; constraints cannot be violated.• Isolation

– Transactions behave as if only one transaction isin progress at a time.

• Durability– Transactions, once committed, will not be lost.


NoSQL


NoSQL

• We are not going to discuss NoSQL databases.• They compromise on at least one of ACID properties


Application Interaction


Application Interaction• Applications use a database driver• The driver uses a custom protocol (language)• Enables Client-Server/n-Tier architectures

Java

C/C++

JavaScript

Python

Ruby


Backend Operations


Backend Operations

• Query Processing– Tokenize/Parse

– Parse Analysis

– Plan/Optimize

– Execute


Backend Operations – Tokenize/Parse

SELECT e.id, d.name

FROM emp e JOIN dept d

ON e.dept = d.id

WHERE emp.name = 'Agent Smith';

SELECT e . id , d . name

FROM emp e JOIN dept d

ON e . dept = d . id

WHERE emp . name = 'Agent Smith' ;


Backend Operations – Tokenize/Parse

'Agent Smith'e.name

WHERE ListFROM ListSELECT List

=deptempd.namee.id

Parse Tree

ON

d.ide.dept

=


Backend Operations – Parse Analysis

• Ensure that– The objects (tables, columns, functions, etc.) exist

– Resolve/expand views• Type Coercion


Backend Operations – Plan/OptimizeGenerate all possible plans to access objects

• Compare cost of– Accessing tables using various methods

● Full Table Scan, Index Scan, Bitmap Index, etc.– Joining tables using various methods

● Nested Loop join, Hash Join, Sort-Merge Join– Other operations like distinct, ordering, grouping.

• Choose the best plan

• Best may mean

– Fastest (e.g. LIMIT clause)

– Cheapest


Backend Operations – Plan/OptimizePlan/Execution Tree

Index Scan - emp

Nested Loop

Result

Condition: name ='Agent Smith'

Full Table Scan - dept

Filter: e.dept =d.id

To Client


Backend Operations - Execute

• Traverse the Execution Tree recursively• Pull rows from each execution node, until exhausted• Insert/Update rows

– Delete in Postgres is just an update of row header

– Update is similar to Delete + Insert

– Multi-Version Concurrency Control (MVCC)• Send result/rows to the client


Concurrent/CooperatingOperations


Concurrent/Cooperating Operations

• Read data from disk• Share data

• Locks to prevent concurrent update• Write data to disk


Block Structure

Free Space

Block Header

Row Pointers

Row Header


MVCC

Isolation; A transaction will:• Ignore changes done by newer transactions• Ignore changes done by running transactions

• Ignore changes done by rolled-back transactions• Only “see” changes by older and committed

transactions

Effectively:

• Readers don't block writers• Writers don't block readers


MVCC

Provides• Atomicity; with the help of commit log (clog)• Isolation

Consistency

• Ensured by the individual backends


Full MontyClient 1 Client 2 Client 3 Client n

Network

Backend 1 Backend 2 Backend n Postmaster

Checkpointer

Autovacuum

RAM

Disk Cache

Shared Buffers

WAL Buffers

Controller Cache Controller Cache

BGWriter


Full Monty

• WAL – Write Ahead Log– Record change before the actual change

– Sequential, always growing infinite log• Checkpoint

– Flush dirty data blocks to database

– Truncate WAL; prevent infinite growth• Autovacuum

– Clean up dead row versions• BGWriter

– Write dirty blocks so backends don't have to


Be Curious

Thank You

how databases work - for developers, accidental dbas and managers

Software