Databases

Updated: 2020-06-29

Over the years, different kinds of databases were developed for different use cases. DB-Engines is tracking more than 300 databases. Choose wisely.

Types

Relational Databases

  • relations: between rows and columns(a relation is a table, not between tables)
  • a unique key identifying each row.

It is a safe bet to start With MySQL or PostgreSQL. Many mobile apps rely on SQLite.

MySQL is the top one open-source db on DB-Engines, also proved its scalability at Facebook, and Uber migrated from Postres to MySQL

MariaDB is a fork of MySQL and it is gaining popularity quickly.

Cloud services:

Google and Amazon have their home-grown relational database. All 3 biggest clouds managed popular databases.

  • Amazon Aurora: Amazon's own relational database
  • Amazon RDS: Amazon managed MySQL/PostgreSQL/MariaDB
  • Google Cloud Spanner: Google's own relational database
  • Google Cloud SQL: Google managed MySQL/PostgreSQL
  • Azure Database for MySQL/PostgreSQL/MariaDB: Microsoft managed db.

NoSQL

MongoDB is probably the most popular open source NoSQL database. DynamoDB is the one shining in the cloud.

There are many open source db under Apache, some of them are implementations of the famous papers published by companies like Google

Cloud services:

Graph Databases

Relational databases can function as general purpose graph databases, but they are not very efficient to traverse the graphs. Multiple queries and joins are often needed.

E.g. Facebook created its own huge social graph, every entity is a node(like a person, a business, a page, a group), and the different types of relationships are the edges. It is backed by TAO, which is actually a caching layer over MySQL.

Graph databases you can use if you choose not to build it in-house like Facebook:

  • Neo4j: a Java graph db.
  • JanusGraph: started as a ford of TitanDB(now TitanDB is discontinued). Supported by Google.
  • Amazon Neptune

    • graph model: Property Graph and W3C's RDF,
    • graph query: Apache TinkerPop Gremlin and SPARQL
  • Giraph: based on Google's Pregel, however Pregel is deprecated.

Cache

Choose Redis for cache for all new projects. Though Memcache is used extensively at Facebook.

Redis: not often used as a primary data store, but for storing and accessing ephemeral data(loss can be tolerated) – metrics, session state, caching.

Some databases are optimized for flash, making them cheaper alternatives to caches. E.g. Aerospike

key-value stores, by design, have trouble linking values together (in other words, they have no foreign keys).

  • Amazon ElastiCache
  • Google Cloud Memorystore
  • Azure Cache for Redis

Read more in the cache page.

Time Series Database

Especially useful for:

  • DevOps Monitoring
  • IoT Applications
  • Real-time analytics

Examples:

Solr is losing popularity to Elasticsearch.

Cloud Services:

  • Amazon CloudSearch
  • Microsoft Azure Search
  • Google Search Appliance

Data Warehouse

Read more from the Data Warehouse page.

SQLite

Often used in web browsers and mobile apps.

Pros

  • the whole database is in one single file on disk, can be embedded inside the application, very portable.
  • zero-config, easy to setup (e.g. no GRANT, use file permissions)

Cons

  • not a client–server database, no network capabilities, cannot be used over a network.
  • not for write-intensive deployments, not for high concurrency use case, since it relies on file-system locks, versus server-based databases handle locks in daemons
  • no type checking, the type of a value is dynamic and not strictly constrained by the schema
  • no user management; no way to tune for higher performance
  • not that reliable, comparing to other RDBMS like MySQL

Google Cloud Datastore vs Cloud Firestore vs Firebase Realtime Database

  • Firestore is the successor of Datastore; Datastore is deprecated
  • Firebase Realtime Database was the original database offering for Firebase
  • Cloud Firestore, as the name suggests, is a "joint-venture" of Google Cloud and Firebase, it is the new flagship database offering of Firebase
  • Both Firestore and Realtime Database are NoSQL, using JSON as data format

Schemaless(Uber's)

Write only

  • row key: UUID, equivalent to “primary key"
  • column key: string
  • ref key: version number(the highest is the latest)

https://eng.uber.com/schemaless-part-one/

Riak

Uses vector lock to resolve write conflicts

Spanner

Spanner is a globally-scalable database system used internally by Google; it is the successor of the BigTable database. (Link to the paper)

Cloud Spanner is the managed database on Google Cloud Platform.

As a successor of BigTable, Spanner also uses SSTable; however it starts to migrate to use a columnar format instead.

Spanner does not have auto-increment key; do not use numbers in incremental order as keys, including timestamps, because Spanner is distributed and sharded by key, such keys will result in hotspots and hurt performance.

Natively support ProtoBuf.

CockroachDB

An open source version of Google Spanner, bigtable=>spanner(google)=>CockroachDB(out of google)

https://www.cockroachlabs.com/

CockroachDB uses RocksDB, an embedded key-value store, internally. Though RocksDB is from Facebook, but it is based on LevelDB, which was also from Google.

Accumulo

Based on based on the Bigtable technology from Google

HBase

  • modeled after google’s bigtable
  • HBase features compression, in-memory operation, and Bloom filters on a per-column basis as outlined in the original Bigtable paper.
  • In the parlance of Eric Brewer’s CAP Theorem, HBase is a CP type system.

LevelDB and RocksDB

How it works:

  • SSTables are arranged in several levels.
  • SSTables are non-overlapping within a level, e.g. 2 levels:

    • level 1: 2 SSTables, one with key space [a, b), and another [b,c)
    • level 2: single SSTable with key space [a, c)
    • the query with a string starting with a will look at [a,c) on level 2 and [a, b) on level 1. Since the strings are sorted, it takes log(n)

Cassandra

Unlike either monolithic or master-slave designs, Cassandra makes use of an entirely peer-to-peer architecture. All nodes in a Cassandra cluster can accept reads and writes, no matter where the data being written or requested actually belongs in the cluster.

  • Shard data automatically
  • Handle partial outages without data loss or downtime
  • Scales close to linearly

HBase

  • High write throughput
  • Horizontal scalability
  • Automatic Failover
  • Strong consistency within a data center

Aerospike

Five-dimensional data model(similar to Bigtable or Cassandra):

  • namespaces(databases): contain sets (tables) of records
  • sets: table
  • records:

    • key: identify records
    • metadata: generation tracks record modification, time-to-live (TTL) specifies record expiration
    • bin: name value pairs.

Traits:

  • Data is sharded and balanced between servers using a Paxos-based membership algorithm.
  • Aerospike makes a dangerous assumption for a distributed datastore: it assumes the network is reliable

Comparing to in memory cache:

  • optimized for flash storage
  • cheaper than the in memory cache
  • no need to re-load data into memory after outrage

CouchDB

  • append only: data is virtually incorruptible and easy to replicate, back up, and restore.
  • not for ad hoc queries
  • multiple masters

Amazon RDS

Data is continuously backed up to S3 in real time, with no performance impact.

CosmosDB

Instead of creating an index tree for each column, Cosmos DB employs one index for the whole database account

Comparisons

InnoDB vs RocksDB

  • InnoDB: B-tree
  • RocksDB: LSM(log-structured merge tree)

LSM vs B-tree: http://smalldatum.blogspot.com/2016/01/summary-of-advantages-of-lsm-vs-b-tree.html

  • We have found RocksDB, when compared to InnoDB, uses less storage space to store an UDB instance and generates less write traffic overall
  • A B-Tree wastes space when pages fragment. An LSM doesn't fragment.

Timescale vs InfluxDB

https://blog.timescale.com/timescaledb-vs-influxdb-for-time-series-data-timescale-influx-sql-nosql-36489299877

as cardinality moderately increases, InfluxDB performance drops dramatically due to its reliance on time-structured merge trees (which, similar to the log-structured merge trees it is modeled after, suffers with higher-cardinality datasets). This of course should be no surprise, as high cardinality is a well known Achilles heel for InfluxDB