Database

Basics

• ACID - DBMS transaction properties

• Transactions Isolation levels

• Read lock, Write lock

• Optimistic lock & Pessimistic lock

  • JOOQ Optimistic lock, Pessimistic lock

  • JPA Optimistic Locking

Relational DB

• PostgreSQL

  • Default isolation level: Read Committed

  • Replication: Master-slave

  • Partitioning method: partitioning by range, list and (since PostgreSQL 11) by hash

  • Index

    • How do indexes work in a relational DB? Types and scenarios?

    • Efficient Use of PostgreSQL Indexes

    • Index types:

      • B tree

      • Hash Index

      • GIN(Gernalized inverted index)

      • GIST

  • PostgreSQL 锁解密

• MySQL

  • Default isolation level: Repeatable Read

  • Replication: master-master, master-slave

  • Partitioning method: horizontal partitioning, sharding with MySQL Cluster or MySQL Fabric

  • B+ Tree Index

  • Designing Schemaless, Uber Engineering’s Scalable Datastore Using MySQL

  • The Architecture of Schemaless, Uber Engineering’s Trip Datastore Using MySQL

• Oracle

  • Commercial

    Replication:

    • master-master

      • All nodes can do read / write

        • Support fail-over

        • Consistency:

          • Most multi-master replication systems are only loosely consistent, i.e. lazy and asynchronous, violating ACID properties.

          • Performance: Eager replication systems are complex and increase communication latency.

        • Integrity: Issues such as conflict resolution can become intractable as the number of nodes involved rises and latency increases.

      • master-slave

        • Only master node can modify data. Slave nodes waiting for master node for high consistency

        • No automatic fail over

      • Cluster

        • No single point of failure

        • Very high throughput

        • 99.99% uptime

        • Auto-Sharding

        • Real-Time Responsiveness

        • On-Line Operations(Schema changes etc)

        • Distributed writes

        • See known limitations

    • Partitioning method: horizontal partitioning

NoSQL #NoSQLFor?

• Column-oriented

  • Cassandra

    • Strength:

      • ability to scale while still being reliable

      • high availability (high velocity writes, and lower velocity reads)

    • Structured / non-structured data. Flexible wide column.

    • A cluster of equal-privileged nodes in a ring structure.

• Graph

• Document-oriented

  • Mongo

    • JSON document based

• Key-value

  • Properties:

    • Can provide a good look up for sharded RDB

  • ready to talk about:

    • Cache Hit Ratio

    • Evictions

    • Expired objects

  • Redis

    • Support persistence

    • Support key-objects (string, hash, list, set, sorted set)

    • Can selectively outdate data

    • Support single threaded only

  • Memcached

    • volatile caching

    • When value is smaller than 1 MB, very fast.

    • Support multi-thread

    • #MemcachedWorkWithMysql

  • Advanced data structures

Trouble Shooting

• How do you know that your database queries are taking time? How will you solve it?

  • Oracle v$session view

Implementation:‌

• Designing a friend list

  • Relational DB

    • Instead of just insert 1 row and do bi-directional search. Insert 2 rows at the same time to describe from both user's perspectives would help keep search SQL simple and make sharding possible.

  • NoSQL

    • Graph DB

      • ​TAO (Facebook's solution), Open Graph, Neo4j‌

Reference:‌

• ​Quora: How does Facebook maintain a list of friends for each user? Does it maintain a separate table for each user?​

Reference

• Second Level Cache - English Chinese

• Uber replace Postgres with Mysql #1