The Future of Distributed Databases is Relational | QCon London 2018 | Sumedh Pathak

Years ago when working at Amazon on shopping cart infrastructure and the precursor to DynamoDB, my co-founder and I realized that while distributed key value stores were useful for a few use-cases, we missed many of the benefits of relational databases: transactions, joins, and the power of the lingua franca of RDBMS’s: SQL. So we challenged ourselves to modernize the traditional relational database, to take a robust open source relational database and transform it into a distributed database.

This talk is about my team’s journey to create a more modern relational database. I’ll talk about the distributed systems problems we had to solve in order to scale out the Postgres open source database, in order to achieve parallelism and a concomitant increase in performance. I'll describe the architecture of the distributed query planner; how we extend traditional relational algebra operators to plan distributed queries and scale reads. I’ll also describe distributed deadlock detection, and how that enabled us to scale out transactions spanning multiple machines.

Transcript

1. Sumedh Pathak, Co-Founder & VP Engineering, Citus Data
   QCon London 2018
   The Future of Distributed
   Databases is Relational
   @moss_toss | @citusdata
   

   View Slide

2. About Me
   • Co-Founder & VP
   Engineering at Citus Data
   • Amazon Shopping Cart
   (former)
   • Amazon Supply Chain &
   Order Fulfillment (former)
   • Stanford Computer Science
   Citus Data co-founders, left-to-right
   Ozgun Erdogan, Sumedh Pathak, & Umur Cubukcu
   Photo cred: Willy Johnson, Monterey CA, Sep 2017
   Sumedh Pathak | Citus Data | QCon London 2018
   

   View Slide

3. View Slide

4. Why
   RDBMS?
   Sumedh Pathak | Citus Data | QCon London 2018
   

   View Slide

5. Streaming Storage
   Map
   Reduce
   NoSQL
   SQL
   Database
   SELECT ...
   Application
   Application
   Sumedh Pathak | Citus Data | QCon London 2018
   Because your architecture could be simpler
   

   View Slide

6. An RDBMS is a
   general-purpose
   data platform
   Sumedh Pathak | Citus Data | QCon London 2018
   Fast writes
   Real-time & bulk
   High throughput
   High concurrency
   Data consistency
   Query optimizers
   

   View Slide

7. PostgreSQL
   MySQL
   MongoDB
   SQL Server +
   Oracle
   Source: Hacker News, https://news.ycombinator.com
   Startups Are Choosing Postgres
   % database job posts mentioning each database, across 20K+ job posts
   Sumedh Pathak | Citus Data | QCon London 2018
   

   View Slide

8. but RDBMS’s
   don’t scale, right?
   Sumedh Pathak | Citus Data | QCon London 2018
   

   View Slide

9. but RDBMS’s don’t scale
   RDBMS’s are hard to scale
   Sumedh Pathak | Citus Data | QCon London 2018
   

   View Slide

10. What exactly needs to Scale?
    - Tables (Data)
    - Partitioning, Co-location, Reference Tables
    - SQL (Reads)
    - How do we express and optimize distributed SQL
    - Transactions (Writes)
    - Cross Shard updates/deletes, Global Atomic Transactions
    Sumedh Pathak | Citus Data | QCon London 2018
    1
    2
    3
    

    View Slide

11. Scaling
    Tables
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

12. How to partition the data?
    - Pick a column
    - Date
    - Id (customer_id, cart_id)
    - Pick a method
    - Hash
    - Range
    

    View Slide

13. Partition data across nodes
    R
    1
    R
    2
    R
    3
    R
    4
    R
    5
    R
    6
    R
    7
    Coordinator
    Node
    Worker
    Nodes
    Shards
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

14. Worker → RDBMS, Shard → Table
    

    View Slide

15. Reference Tables
    N
    1
    N
    1
    N
    1
    N
    1
    Copies of same table
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    Node
    Worker
    Nodes
    

    View Slide

16. Co-Location
    R
    1
    R
    2
    R
    3
    R
    4
    S
    1
    S
    2
    S
    3
    S
    4
    Explicit Co-Location API.
    E.g. Partition by Tenant
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    Node
    Worker
    Nodes
    

    View Slide

17. What about Foreign Keys?
    

    View Slide

18. The key to scaling tables...
    - Use relational databases as a building block
    - Understand semantics of application—to be smart about
    partitioning
    - Multi-tenant applications
    

    View Slide

19. Scaling
    SQL
    

    View Slide

20. FROM table R
    SELECT x Project
    x
    (R) → R’
    WHERE f(x) Filter
    f(x)
    (R) → R’
    … JOIN … R × S → R’
    SQL ↔ Relational Algebra
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

21. FROM sharded_table Collect(R
    1
    ,R
    2
    ,...) → R
    Distributed Relational Algebra
    R
    1
    R
    2
    C
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

22. Project
    x
    (Collect(R
    1
    ,R
    2
    ,...)) = Collect(Project
    x
    (R
    1
    ), Project
    x
    (R
    2
    )...)
    Commutative property
    R
    1
    R
    2
    C
    R
    1
    R
    2
    C
    P
    x
    P
    x
    P
    x
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

23. Collect(R
    1
    ,R
    2
    ,...) x Collect(S
    1
    ,S
    2
    ,...) = Collect(R
    1
    × S
    1
    ,R
    2
    × S
    2,
    ...)
    Distributive property
    R
    1
    R
    2
    C
    ×
    C
    S
    1
    S
    2
    R
    1
    R
    2
    C
    ×
    S
    1
    S
    2
    ×
    X = Join Operator
    

    View Slide

24. SUM(x)(Collect(R
    1
    ,R
    2
    ,...)) = SUM(Collect(SUM(R
    1
    ), SUM(R
    2
    )...))
    Associative property
    R
    1
    R
    2
    C
    R
    1
    R
    2
    C
    Sum
    x
    Sum
    x
    Sum
    x
    Sumedh Pathak | Citus Data | QCon London 2018
    Sum
    x
    

    View Slide

25. SELECT sum(price) FROM orders, nation
    WHERE orders.nation = nation.name AND
    orders.date >= '2012-01-01' AND
    nation.region = 'Asia';
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

26. Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

27. Sumedh Pathak | Citus Data | QCon London 2018
    Volcano style processing
    Data flows
    from
    bottom
    to top
    

    View Slide

28. Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

29. Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

30. Sumedh Pathak | Citus Data | QCon London 2018
    Parallelize
    Aggregate
    Push Joins & Filters
    below collect. Run in
    parallel across all
    nodes
    Filters & Projections
    done before Join
    

    View Slide

31. SELECT sum(intermediate_col)
    FROM ;
    SELECT sum(price)
    FROM orders_2 JOIN nation_2
    ON (orders_2.name = nation_2.name)
    WHERE
    orders_2.date >= '2017-01-01'
    AND
    nation_2.region = 'Asia';
    SELECT sum(price)
    FROM orders_2 JOIN nation_1
    ON (orders_2.name = nation_1.name)
    WHERE
    orders_2.date >= '2017-01-01'
    AND
    nation_2.region = 'Asia';
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

32. Executing Distributed SQL
    SQL database
    orders_1
    nation_1
    orders
    nation
    SELECT sum(price)
    FROM orders_2 o JOIN nation_1
    ON (o.name = n.name)
    WHERE o.date >= '2017-01-01'
    AND n.region = 'Asia';
    SELECT sum(price)
    FROM orders_1 o JOIN nation_1
    ON (o.name = n.name)
    WHERE o.date >= '2017-01-01'
    AND n.region = 'Asia';
    SELECT sum(price) FROM ;
    SQL database
    orders_2
    nation_1
    

    View Slide

33. The key to scaling SQL...
    - New relational algebra operators for
    distributed processing
    - Relational Algebra Properties to optimize
    tree: Commutativity, Associativity, &
    Distributivity
    - Map / Reduce operators
    

    View Slide

34. Scaling
    Transactions
    

    View Slide

35. Money Transfer, as an example
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

36. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    Coordinator
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

37. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    

    View Slide

38. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    COMMIT;
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    

    View Slide

39. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    Coordinator
    BEGIN;
    UPDATE accounts SET balance =
    balance + 100 WHERE id = ‘BOB’;
    Sumedh Pathak | Citus Data | QCon London 2018
    BEGIN;
    UPDATE accounts SET balance = balance
    - 100 WHERE id = ‘ALICE’;
    

    View Slide

40. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    BEGIN;
    UPDATE accounts SET balance =
    balance + 100 WHERE id = ‘BOB’;
    COMMIT
    Sumedh Pathak | Citus Data | QCon London 2018
    BEGIN;
    UPDATE accounts SET balance = balance
    - 100 WHERE id = ‘ALICE’;
    Coordinator
    

    View Slide

41. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    PREPARE TRANSACTION ‘citus_...98’; PREPARE TRANSACTION ‘citus_...98’;
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    

    View Slide

42. What happens during PREPARE?
    State of transaction stored
    on a durable store
    Locks are
    maintained
    &
    

    View Slide

43. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    Coordinator
    PREPARE TRANSACTION ‘citus_...98’; ROLLBACK TRANSACTION ‘citus_...98’;
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

44. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    PREPARE TRANSACTION ‘citus_...98’; PREPARE TRANSACTION ‘citus_...98’;
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    

    View Slide

45. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    COMMIT PREPARED ‘citus_...98’; COMMIT PREPARED ‘citus_...98’;
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    

    View Slide

46. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    COMMIT PREPARED ‘citus_...98’; COMMIT PREPARED ‘citus_...98’;
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    

    View Slide

47. A
    1
    A
    2
    BEGIN;
    UPDATE accounts SET balance = balance -
    100 WHERE id = ‘ALICE’;
    UPDATE accounts SET balance = balance +
    100 WHERE id = ‘BOB’;
    COMMIT;
    A
    3
    A
    4
    COMMIT PREPARED ‘citus_...98’; COMMIT PREPARED ‘citus_...98’;
    Sumedh Pathak | Citus Data | QCon London 2018
    Coordinator
    

    View Slide

48. But....
    Deadlocks!
    

    View Slide

49. Example
    // SESSION 1
    BEGIN;
    UPDATE accounts SET balance
    = balance - 100 WHERE id =
    ‘ALICE’;
    (LOCK on ROW with id
    ‘ALICE’)
    // SESSION 2
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

50. Example
    // SESSION 1
    BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE
    id = ‘ALICE’;
    (LOCK on ROW with id ‘ALICE’)
    // SESSION 2
    BEGIN;
    UPDATE accounts SET balance
    = balance + 100 WHERE id =
    ‘BOB’;
    (LOCK on ROW with id ‘BOB’)
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

51. Example
    // SESSION 1
    BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE
    id = ‘ALICE’;
    (LOCK on ROW with id ‘ALICE’)
    UPDATE accounts SET balance
    = balance + 100 WHERE id =
    ‘BOB’;
    // SESSION 2
    BEGIN;
    UPDATE accounts SET balance = balance + 100 WHERE
    id = ‘BOB’;
    (LOCK on ROW with id ‘BOB’)
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

52. Example
    // SESSION 1
    BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE
    id = ‘ALICE’;
    (LOCK on ROW with id ‘ALICE’)
    UPDATE accounts SET balance = balance + 100
    WHERE id = ‘BOB’;
    // SESSION 2
    BEGIN;
    UPDATE accounts SET balance = balance + 100 WHERE
    id = ‘BOB’;
    (LOCK on ROW with id ‘BOB’)
    UPDATE accounts SET balance
    = balance - 100 WHERE id =
    ‘ALICE’
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

53. How do Relational DB’s solve this?
    S1
    S2
    - Construct a
    Directed Graph
    - Each node is a
    session/transaction
    - Edge represents a
    wait on a lock
    Waiting
    on ‘Bob’
    Waiting on
    ‘Alice’
    

    View Slide

54. S1
    S2
    S1 S2
    S2 S1
    S2 Waits on S1 S1 Waits on S2
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

55. S1
    S2
    S1 S2
    S2 S1
    S2 Waits on S1 S1 Waits on S2
    Distributed
    Deadlock
    Detector
    S1
    S2 S2
    S1
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

56. keys to scaling transactions
    - 2PC to ensure atomic transactions across nodes
    - Deadlock Detection—to scale complex & concurrent
    transaction workloads
    - MVCC
    - Failure Handling
    4
    

    View Slide

57. It’s 2018. Distributed can be Relational
    - Scale tables—via sharding
    - Scale SQL—via distributed relational algebra
    - Scale transactions—via 2PC & Deadlock Detection
    Sumedh Pathak | Citus Data | QCon London 2018
    Now, how do we implement all of this?
    

    View Slide

58. Postgres
    Sumedh Pathak | Citus Data | QCon London 2018
    

    View Slide

59. PostgreSQL
    Planner
    Executor
    Custom scan
    Commit / abort
    Extension (.so)
    Access methods
    Foreign tables
    Functions
    ...
    ...
    ...
    ...
    ...
    ...
    ...
    CREATE EXTENSION ...
    

    View Slide

60. PostgreSQL PostgreSQL
    PostgreSQL
    shards
    shards
    shard
    shards
    shards
    shard
    SELECT … FROM distributed_table …
    SELECT … FROM shard… SELECT … FROM shard…
    Citus
    

    View Slide

61. PostgreSQL
    Citus
    PostGIS PL/Python
    JSONB
    2PC
    Replication
    ...
    Sequences
    Indexes
    Full-Text Search
    Transactions
    …
    dblink
    Sumedh Pathak | Citus Data | QCon London 2018
    Rich
    ecosystem of
    tooling, data
    types,
    & extensions
    in
    PostgreSQL
    

    View Slide

62. PostgreSQL can be
    extended into an all-purpose
    distributed RDBMS
    

    View Slide

63. I believe the future of distributed
    databases is relational.
    

    View Slide

64. Thank you!
    Sumedh Pathak
    sumedh@citusdata.com
    @moss_toss | @citusdata | citusdata.com
    QCon London 2018 | The Future of Distributed Databases is Relational
    

    View Slide