An ode to 17 databases in N minutes

Hi

I'm Toby

Dramatis Personae

NoSQL

NewSQL

PostSQL

Post-Rock Ambient Punk SQL

ACID

CAPtain murica 

CAP

Conjecture

Formal Proof

BASE

Everything is Distributed

P ? C : A

SCIENCE!

OMG

jepsen

Note to self:

Probably 

don't do 

the next bit

Links

PostgreSQL

Hstore

  CREATE TABLE characters (
     id serial PRIMARY KEY,
     name TEXT,
     attributes hstore
  );
      
  INSERT INTO characters (name, attributes) VALUES (
    'postgresql',
    'class    => "relational",
     skills   => joins,
     api      => SQL over TCP/IP'
  );      
      

activerecord-postgres-hstore

  class Programmer < ActiveRecord::Base
    serialize :languages, ActiveRecord::Coders::Hstore
  end

  # Contains key 'ruby'
  Programmer.where("languages ? 'ruby'")

  # Key 'ruby' has value '2.0'
  Programmer.where("languages -> 'ruby' = '2.0'")

  # And slightly different for no extra charge
  Programmer.where("languages @> 'ruby=>2.0'")

  # And likes
  Programmer.where("languages -> 'ruby' LIKE '%1.9%'")

JSON

MySQL

See also

Dynamo/Db

Links

Riak

Artist's impression
Actual results may vary

Consistent Hashing

Vritual Nodes

Simple API

  # Read an Object
  GET /buckets/{bucket}/keys/{key}

  # Store an Object with Generated Key
  POST /buckets/{bucket}/keys  

  # Store an Object with Specified Key
  PUT /buckets/{bucket}/keys/{key} 

  # Counters (CRDT)
  POST /buckets/{bucket}/counters/{key}
  

Ripple

  require 'ripple'

  # Documents are stored as JSON objects in Riak but have rich
  # semantics, including validations and associations.
  class Email
    include Ripple::Document
    property :from,    String, :presence => true
    property :to,      String, :presence => true
    property :sent,    Time,   :default => proc { Time.now }
    property :body,    String
  end

  email = Email.find("37458abc752f8413e")  # GET /riak/emails/37458abc752f8413e
  email.from = "toby@ninefold.com"
  email.save                               # PUT /riak/emails/37458abc752f8413e

Vector Clocks

Links

BigTable

Largest BigTable Cluster 

GPS

Atomic Clock

Cassandra

Eventually Persistent

Title

CREATE TABLE playlists (
  id uuid,
  song_order int,
  song_id uuid,
  title text,
  album text,
  artist text,
  PRIMARY KEY  (id, song_order ) 
);

INSERT INTO playlists (id, song_order, title, artist, album)
VALUES (8a172618-b121-4136-bb10-f665cfc469eb, 1, 'Elevator of the Machine', 'Mount Fuji Doomjazz Corporation', 'Egor');

INSERT INTO playlists (id, song_order, title, artist, album)
VALUES (8a172618-b121-4136-bb10-f665cfc469eb, 2, 'Knock by the Stairs', 'Mount Fuji Doomjazz Corporation', 'Egor');

INSERT INTO playlists (id, song_order, title, artist, album)
VALUES (8a172618-b121-4136-bb10-f665cfc469eb, 3, 'Oktober', 'Bersarin Quartet', 'Bersarin Quartet');

Links

Memcache

I'm a cache, not a database!

Simple

Client Server

Rails Fragment Cache

config.action_controller.perform_caching = true
config.cache_store = :mem_cache_store, "1.cache.com", "2.cache.com"

<% cache(cache_key_for_products) do %>
  All available products:
  <% Product.all.each do |p| %>
    <% cache(p) do %>
      <%= link_to p.name, product_url(p) %>
    <% end %>
  <% end %>
<% end %>    

Rails with IdentityCache

class Product < ActiveRecord::Base
  include IdentityCache

  has_many :images

  cache_has_many :images, :embed => true
end

@product = Product.fetch(id)

@images = @product.fetch_images

Redis

Data Structures

# strings
SET akey "Hello"
GET akey

# hashes
HSET anhash greeting "Hello" 
HGET anhash greeting

# lists
RPUSH alist "hello"
RPUSH alist "world"
LRANGE alist 0 -1

# sets
# sorted sets
# pub/sub

Optimistic Locking

MULTI
INCR foo
INCR bar
EXEC

Check and Set

WATCH mykey
val = GET mykey
val = val + 1
MULTI
SET mykey $val
EXEC

Resque

class ImageConversionJob
  def work
    # convert some kind of image here
  end
end

resque = Resque.new
resque << ImageConversionJob.new

Gratuitous Aside

Lua Scripting

    EVAL "local result = redis.call('ZREVRANGE', 'myset', 0, 10, 'WITHSCORES'); 
redis.call('ZREMRANGEBYRANK','myset',0, 9); 
return result;" 0
    

Also

Neo4j

Almost, but not quite, entirely unlike a RDBMS

Optimized for highly connected data

MATCH (m:Movie {title:"Star Wars"})<-[:ACTS_IN]-(actor)
RETURN actor;

Links

Couch

MongoDB

To recap: MongoDB is neither AP nor CP. The defaults can cause significant loss of acknowledged writes. The strongest consistency offered has bugs which cause false acknowledgements, and even if they're fixed, doesn't prevent false failures.

Consistency and fault-tolerance are hard. Genuinely hard. The devil is always in the details, and it takes a concentrated mind to navigate through these waters correctly

Mongoid

class Artist
  include Mongoid::Document
  field :name, type: String
  embeds_many :instruments
end

class Instrument
  include Mongoid::Document
  field :name, type: String
  embedded_in :artist
end

syd = Artist.where(name: "Syd Vicious").between(age: 18..25).first
syd.instruments.create(name: "Bass")
syd.with(database: "bands", session: "backup").save!

RethinkDB

Hyperdex

Hyperspace hashing deterministically maps objects to servers to enable efficient object insertion and retrieval. But it differs from these techniques because it takes into account the secondary attributes of an object when determining the mapping for an object. Specifcally, it maps objects to coordinates in a multi-dimensional Euclidean space - a hyperspace - which has axes defined by the objects' attributes. Each server in the system is mapped onto a region of the same hyperspace, and owns the objects that fall within its region. Clients use this mapping to deterministically insert, remove, and search for objects.

  require "hyperdex"
  client = HyperDex::Client::Client.new("127.0.0.1", 1982)

  client.put(:space, 'hello', {:v => "Hello World!"})
  puts client.get(:space, 'hello')
  
  client.atomic_add(:space, 'votes', {upvotes: 1})
    

  current_balance = client.get('accounts', 'account-number')['balance']

  raise InsufficientFunds if current_balance < amount

  balance = mybalance - amount
  
  # cond_put(spacename, key, predicates, attributes)
  success = client.cond_put('accounts', 'account-number', {balance: current_balance}, {balance: balance})
  

HBase

Hadoop

• Hadoop
• Hadoop Distributed File System
• Zookeeper
• Hive
• HiveQL
• Mahout
• Map/Reduce

Small-to-medium Data

UnQLite

Elasticsearch

Tire

  s = Tire.search(search_index) do
    filter :range, {
      "@timestamp" => {
        "from" => from.iso8601,
        "to" => to.iso8601
      }
    }
    query {
      string "@tags:unicorn"
    }
  end

  s.results
  

  {
    "filter": {
      "and": [
        {
          "range": {
            "@timestamp": {
              "from": "2013-09-01T00:00:00+00:00",
              "to": "2013-09-26T23:59:59+00:00"
            }
          }
        },
        {
          "terms": {
            "@tags": ["error", "migration"],
            "execution": "and"
          }
        },
        {
          "not": {
            "filter": {
              "term": { "@source": "client.log" }
            }
          }
        }
      ]
    },
    "sort": [
      {
        "@timestamp": "desc"
      }
    ]
  }
  

curl -XPUT 'http://localhost:9200/events/event' -d '{
    "event": {
        "user" : "toby",
        "post_date" : "2014-02-18T20:00:00",
        "event" : "page_view"
    }
}'

GET _search
{
    "query" : {
        "term" : { "user" : "toby" }
    },
   "facets" : {
      "event" : { 
        "terms" : {"field" : "event"},
        "facet_filter" : {
            "term" : { "user" : "toby" }
        },
        "global": true
      }
    }
}

{
   "took": 8,
   "timed_out": false,
   "_shards": {
      "total": 10,
      "successful": 10,
      "failed": 0
   },
    "facets": {
      "event": {
         "_type": "terms",
         "missing": 0,
         "total": 5,
         "other": 0,
         "terms": [
            {
               "term": "page_view",
               "count": 5
            }
         ]
      }
    }
}

Links

• Kibana
  

Like Chess