$30 off During Our Annual Pro Sale. View Details »

Postgres & Rails 6 Multi-DB: Pitfalls, Patterns and Performance

Postgres & Rails 6 Multi-DB: Pitfalls, Patterns and Performance

Rails 6 has officially added Multiple database support, and the options are overwhelming. We're here to help you make the right architecture decisions for your app. In this talk, we will look at performance gains and pitfalls to some common patterns including: separation of concerns, high-load tables, and data segmentation. We'll talk about read replicas, eventual consistency, and real-time (or near real-time) requirements for a Rails application using multiple Postgres databases.

Gabe Enslein

April 30, 2019
Tweet

More Decks by Gabe Enslein

Other Decks in Programming

Transcript

  1. Postgres & Rails 6 Multi-DB:
    Pitfalls, Patterns and
    Performance
    Gabriel Enslein

    View Slide

  2. Who uses Postgres and
    Rails?
    Who is on the latest and greatest versions of both?

    View Slide

  3. What does Rails 6 Multi-DB allow?
    ● Read and Write databases can be created in rake tasks
    ● Specify read and write DBs in the ActiveRecord Models
    ● API request-based DB selection
    ○ GET, HEAD -> Read DB
    ○ POST, PUT -> Write DB
    But how does this interact with Postgres?

    View Slide

  4. Rails 6 Multi-DB
    Part 1: https://github.com/rails/rails/pull/32274
    Part 2: https://github.com/rails/rails/pull/33637
    Part 3: https://github.com/rails/rails/pull/33770
    Part 4: https://github.com/rails/rails/pull/34052
    Part 5: https://github.com/rails/rails/pull/34491
    Part 6: https://github.com/rails/rails/pull/34495
    Part 7: https://github.com/rails/rails/pull/34505
    Part 8: https://github.com/rails/rails/pull/35073
    Parts ongoing: too many to list
    Thank you to the Rails contributors!

    View Slide

  5. Who is this Gabriel character?
    Heroku Data Engineer since 2016
    Sinatra and RoR API Services
    Worked with many different NoSQL and SQL Databases
    Interested in Postgres high traffic and data growth

    View Slide

  6. Terms/Phrases in the talk you’ll hear
    ● Bloat
    ● Vacuum
    ● Partitioning
    ● Sharding
    If you want to learn more, see me after
    ● Replication
    ● Statistics
    ● Caching
    ● Connection Pooling

    View Slide

  7. Self-Help/Life Coaching Business is booming!

    View Slide

  8. Super Secret Social interest platform revisited
    ● Customers have profiles
    ○ Shipping addresses
    ○ Email preferences
    ○ Social Media links
    ○ Payment Info
    ○ Etc.
    ● Each customer can posts from areas
    of interest
    ○ Favorite Quotes
    ○ TV Shows
    ○ Movies
    ○ Foods
    ○ Etc.
    Millions of users and hundreds of millions of updates per day

    View Slide

  9. Super Secret Social interest platform revisited
    Why do profile updates take so long?
    Why does it take so long to update addresses?
    Why does it take forever to load quotes a users added?

    View Slide

  10. Super Secret Social interest platform revisited
    - The Characters table is huge (750 GB+)
    - I can’t wait for all the queries on to finish on that one table
    - Updates on the Characters table turn my Rails app to a snail
    What can I do to speed up my Rails App?

    View Slide

  11. What can I do to speed up my Rails App?
    Add a read-replica
    Move Expensive Character queries to replica
    Why have a read replica if my app can’t use it directly?
    It feels like there’s a cap...

    View Slide

  12. Let’s Talk about the CAP...Theorem

    View Slide

  13. Let’s Talk about the CAP...Theorem
    There are 3 dimensions of Robust Distributed System
    1. Consistency
    2. Availability
    3. Partition Tolerance
    The theorem states you can at most achieve 2 of the 3

    View Slide

  14. Let’s Talk about the CAP...Theorem
    ● Help identify failure points
    ● Increase overall responsiveness
    ● Ensure longevity and stability of operations

    View Slide

  15. View Slide

  16. Curated Content: Characters of Interest
    Interest Outlet: Our favorite Characters
    Hundreds of thousands of TV Shows and Movies
    Show/Movie has many characters (some have hundreds)
    Varying information about each character or show

    View Slide

  17. Curated Content: Characters of Interest
    Interest Outlet: Our favorite Characters
    Movies and TV Shows are internally updated
    Characters per show or movie will be updated similarly
    Depending on a show/movie/character’s popularity
    depends query volume

    View Slide

  18. Curated Content: Characters of Interest
    Interest Outlet: Our favorite Characters
    Movies and TV Shows are internally updated
    Characters per show or movie will be updated similarly
    Depending on a show/movie/character’s popularity
    depends query volume

    View Slide

  19. What can I do to speed up my Rails App?

    View Slide

  20. Rails 6 can use them now with multi-DB
    This enables Rails to Read/Write split
    Interest Outlet: Our favorite Characters
    Pattern: Read DB for Characters of Interest

    View Slide

  21. What tools/changes do I need to make to set up or
    recognize environments?
    How do I define different databases?
    Interest Outlet: Our favorite Characters
    Pattern: Read DB for Characters of Interest

    View Slide

  22. Super Secret Social interest platform revisited
    production:
    primary:
    <<: *default
    url: <%= ENV['DATABASE_URL'] %>
    characters:
    <<: *default
    url: <%= ENV['CHARACTERS_DATABASE_URL'] %>
    migrations_paths: "db/characters_migrate"
    characters_replica:
    <<: *default
    url: <%= ENV['CHARACTERS_FOLLOWER_DATABASE_URL'] %>
    config/database.yml support was added for Multi-DB

    View Slide

  23. How does my Character class understand multi-DB?
    What does the ActiveRecord look like for inheritance?
    Interest Outlet: Our favorite Characters
    Pattern: Read DB for Characters of Interest

    View Slide

  24. Interest Outlet: Our favorite Characters
    class Character < ApplicationRecord
    connects_to database: { writing: :characters, reading: :characters_replica }
    end
    class TvShowCharacter < Character
    end
    class MovieCharacter < Character
    end
    Pattern: Read DB for Characters of Interest

    View Slide

  25. Interest Outlet: Our favorite Characters
    Having the Base model define the connection strategy via connects_to
    Child models will maintain the connection strategy for reusability
    Enables more robust strategies for certain operations to prioritize workloads
    Performance: Read DB for Characters of Interest

    View Slide

  26. Curated Content: Characters of Interest
    Interest Outlet: Our favorite Characters
    ActiveRecord::Base.connected_to(role: :reading) do
    TvShowCharacter.first # finds TvShowCharacter from replica connected to Character
    end
    Example: TV Show Characters query the read replica

    View Slide

  27. Where’s the Hottest trending TV character my
    data upload process added in the database?
    Why aren’t my users seeing their newest
    Characters added to their favorites?

    View Slide

  28. View Slide

  29. Interest Outlet: Our favorite Characters
    Pitfall: Follower Lag on Read Replicas
    heroku pg:info CHARACTERS_FOLLOWER_DATABASE-a super-secret-social
    Following: CHARACTERS_DATABASE_URL
    Behind By: 100K commits

    View Slide

  30. Interest Outlet: Our favorite Characters
    Pitfall: Follower Lag on Read Replicas
    What is follower lag really?
    ● Postgres sends Write-Ahead Log (WAL) to replay changes
    ● The more traffic to primary, the more WAL replicas replay
    ● Other factors on read replicas can affect replay

    View Slide

  31. Interest Outlet: Our favorite Characters
    Pitfall: Follower Lag on Read Replicas
    Are we missing any indexes for the read replica?
    What’s our connection usage look like?
    How are other database resources?

    View Slide

  32. Interest Outlet: Our favorite Characters
    Performance: Optimizing Read Replicas
    ● Optimizing indexing in Postgres for reads is very
    different from indexing for writes
    ● Size of datasets can change query plans
    ● Caching changes on the DB can affect performance

    View Slide

  33. EXPLAIN ANALYZE

    View Slide

  34. Interest Outlet: Our favorite Characters
    Performance: Optimizing Read Replicas
    EXPLAIN ANALYZE SELECT * FROM characters WHERE show_id >
    7000;
    QUERY PLAN
    ------------------------------------------------------------
    Seq Scan on characters (cost=0.00..483350.00 rows=15078620
    width=244)
    Filter: (show_id > 7000)

    View Slide

  35. Interest Outlet: Our favorite Characters
    Performance: Optimizing Read Replicas
    ● Things to look out for
    ○ Sort Method
    ○ Seq (Sequence) Scan
    ○ Groupings
    ○ Conditionals

    View Slide

  36. Interest Outlet: Our favorite Characters
    Performance: Optimizing Read Replicas
    ● Types of scans to favor
    ○ Bitmap Index Scan
    ○ Bitmap Heap Scan
    ○ Index or Index Only scans

    View Slide

  37. Interest Outlet: Our favorite Characters
    Performance: Optimizing Read Replicas
    EXPLAIN ANALYZE SELECT * FROM characters WHERE show_id > 7000;
    QUERY PLAN
    ----------------------------------------------------------------------
    --------
    Bitmap Heap Scan on characters (cost=2.27..204.35 rows=106
    width=244)
    Recheck Cond: (show_id > 7000)
    -> Bitmap Index Scan on characters_show_id (cost=0.00..2.27
    rows=106 width=0)
    Index Cond: (show_id > 7000)

    View Slide

  38. Interest Outlet: Our favorite Characters
    ● I updated my indexes and my lag is gone but queries aren’t
    improving
    ● I checked I’m not above connection limits too
    ● I reviewed my query analyzer
    Now what?
    Performance: Optimizing Read Replicas

    View Slide

  39. View Slide

  40. Interest Outlet: Our favorite Characters
    Pitfall: Write-heavy primaries slowing replication
    Users are adding tons of quotes every day (>1 TB+)
    They want even more features interacting with quotes
    Complaining about wanting to add Characters we don’t
    already have

    View Slide

  41. Interest Outlet: Our favorite Characters
    Pitfall: Write-heavy primaries slowing replication
    ● Volume of heavy usage queries to the read database can slow replay by
    locking shared Postgres internals
    ● Connection churning or pool saturation on the database can slow
    replication
    ● Large table sizes ( > 1 TB+)
    ● Volume of data being written from the primary database can slow
    replication

    View Slide

  42. Users only want quotes posted or updated in the last week
    Quotes older than a week just fill the DB cache…
    The database has a cache?
    We have way more Quotes than Users care about

    View Slide

  43. Interest Outlet: Our favorite Characters
    Pitfall: Write-heavy primaries slowing replication
    ● Database caches recently accessed records in memory
    ● Writing a lot of new data tends to cause a lot of churn on the Memory
    usage, slowing the database down significantly
    ● Most index positions are also stored as pointer in memory for faster disk
    access
    ● Indexes that are being accessed with the same high rate of change tables
    can add to the DB load

    View Slide

  44. View Slide

  45. We can Partition the Quotes Table!

    View Slide

  46. Interest Outlet: Inspirational Quotes
    Performance: Partitioning Large Tables
    Partitioning is a good way to less active data
    Default partitions enabled in Postgres 11
    Partition-level aggregation creates deep parallelization in
    queries

    View Slide

  47. Interest Outlet: Inspirational Quotes
    Performance: Partitioning Large Tables
    psql=> ALTER TABLE quotes
    ATTACH PARTITION quotes_y2016m07 FOR VALUES FROM
    ('2016-07-01') TO ('2016-08-01');

    View Slide

  48. POSTGRES PARTITIONING:
    NEEDS ITS OWN TALK

    View Slide

  49. But what about
    User-sourced Characters
    AND Quotes?

    View Slide

  50. User-sourced Content: Famous Character catchphrases
    Interest Outlet: Our favorite Characters
    Users want to be able to add Characters AND their famous Quotes
    What if we have separate databases for Quotes and Characters?
    Both Quotes and Characters have a primary and a read-replica database
    What does that look like from a code perspective?

    View Slide

  51. Hang on a second; even getting last week’s posts are
    super slow though, they’re so many quotes...
    User-sourced Content: Famous Character catchphrases
    Interest Outlet: Our favorite Characters

    View Slide

  52. Quotes TOTAL size 5 TB+
    Characters are now 3 TB+
    Uh-oh, we have way more Characters & Quotes...

    View Slide

  53. How can we handle the data
    growth?

    View Slide

  54. We can shard the Quotes to its own DB!

    View Slide

  55. Interest Outlet: Our favorite Characters
    Pattern: Sharding the write database for Quotes
    ● Separation of Concerns
    ● Prioritizes critical functionality of the main Postgres
    primary
    ● DRY principle easier to achieve with simplistic view of
    the specific Quotes DB workload

    View Slide

  56. Interest Outlet: Our favorite Characters
    Performance: Sharding the write database for Quotes
    ● Moving Quotes to its own DB relieves the rest of the
    Rails App
    ● Characters are flowing in a consistent fashion
    ● Other tables are performing as expected now

    View Slide

  57. Interest Outlet: Our favorite Characters
    class Quotes < ApplicationRecord
    connects_to database: { writing: :quotes, reading: :quotes_replica }
    End
    # A Custom write handler to write each to their respective primary DBs
    ActiveRecord::Base.connected_to(mode: :writing) do
    TvShowCharacter.create!
    Quote.create!
    end
    User-sourced Content: Famous Character catchphrases

    View Slide

  58. What’s happening to my
    Rails app?

    View Slide

  59. View Slide

  60. Interest Outlet: Our favorite Characters
    Pitfall: Sharding repeatedly resulting into DB loops
    ● Logic in App may repeatedly query data from sharded
    locations
    ○ e.g. Joins would would be done in Rails, not DBs
    ● Too many re-connections to multiple DBs can slow
    network time

    View Slide

  61. Interest Outlet: Our favorite Characters
    Pitfall: Sharding repeatedly resulting into DB loops
    ● DB/QueryCache may be churning adding additional
    slowdown to all results
    ● 95/99th Percentiles can be causing the worst of partial
    or full outages or even resource saturation

    View Slide

  62. View Slide

  63. Where’s the CAP? Flip it to PACELC Theorem
    When Partition Tolerance (P) is the critical
    case choose one:
    ● Availability (A)
    ● Consistency(C)
    ELSE (E) choose one:
    ● Latency (L)
    ● Consistency(C)

    View Slide

  64. What does that mean for me?
    ● Ultimately, Latency is our biggest concern now
    ● How do we avoid the network round trips?
    ● How can we minimize networking issues?
    ● How can we maximize our data usage for its
    size and this limitation?
    Where’s the CAP? Flip it to PACELC Theorem

    View Slide

  65. Considerations

    View Slide

  66. ● Which connection pools are you referring to?
    ○ DB connection pools or Rails connection pools?
    ○ Are the Rails app servers connection pooling properly?
    ○ Are they not over server limits or DB limits?
    Consideration: What about connection pools?

    View Slide

  67. ● What about Database connection pools?
    ○ Are all connections going through direct to Postgres
    DBs?
    ○ Is connection pooling enabled or set up?
    ○ Is Rails saturating DB connection pooling limits?
    Consideration: What about connection pools?

    View Slide

  68. ● Which caches are you referring to?
    ○ QueryCache in your Rails app?
    ○ One of your database caches?
    ■ Is it your writable database?
    ■ Is it one of your read-replica databases?
    Consideration: Why do my caches seem useless?

    View Slide

  69. ● What happens if you’re putting things into memory that you don’t use?
    ○ Postgres will use temp disk space to manage large queries
    ● It caches each whole record, then creates pointers to disk for data overflow
    ● Postgres can stop accepting connections if we run too many of these queries at
    the same time
    Refresher: My data is too big for DB
    Caches, what happens now?

    View Slide

  70. ● How many resources are left on the Rails app servers?
    ○ Memory
    ○ Connections
    ○ CPU
    Consideration: Where are all my resources?

    View Slide

  71. ● How many resources are there left to spare on the DBs?
    ○ Memory
    ○ I/O
    ○ CPU
    ○ Connections
    Consideration: Where are all my resources?

    View Slide

  72. View Slide

  73. ● Apps should have safety measures
    ○ Maintenance Mode capabilities
    ○ Feature Flagging
    ○ Circuit Breakers for when too many alerts fire
    Food For Thought

    View Slide

  74. ● Data Services should have thresholds
    ○ Tracking utilization of Data services to leave room for
    spikes
    ○ Web Traffic spikes can spike data storage and usage
    equally
    ○ Have a growth plan in place
    Food For Thought

    View Slide

  75. ● When needing new strategies, evaluate ideas early and
    often
    ○ Architecting Services for the principle of least
    privilege
    ○ Identifying services to lower scope to single purpose
    Food For Thought

    View Slide

  76. ● When needing new strategies, evaluate ideas early and
    often
    ○ Breaking up Monoliths
    ○ Identifying Critical/Real-time vs. non-real time or
    fault capable services
    ○ Refactoring Data relational restrictions
    Food For Thought

    View Slide

  77. Heroku Postgres improvements
    Postgresql 11 Released
    Improved Postgres CLI analytics from improved Postgres Query
    Analytics
    Data.heroku.com Database Analytics
    Heroku Postgres Connection Pooling

    View Slide

  78. Heroku Postgres 11 Generally Available
    New features include:
    Stored Procedures
    Additional Partitioning Features and Optimizations
    Enhanced Parallelization
    Learn More about Postgres 11 in our blog

    View Slide

  79. Heroku Postgres CLI analytics
    Database tuning and monitoring still vital
    - https://devcenter.heroku.com/articles/heroku-postgres-database-tuning
    Extended CLI analytics improved from Postgres 10 internals changes
    - https://github.com/heroku/heroku-pg-extras
    heroku pg:diagnose #and more!

    View Slide

  80. Heroku Connection Pooling: PgBouncer Beta
    ● Manages connections for your Postgres
    Database
    ○ Reduces need to open new connections
    ● Lives on the Database Server
    ● Uses Transactional connection Pooling
    What’s PgBouncer?

    View Slide

  81. Heroku Connection Pooling: PgBouncer Beta
    ● Better Visibility on connection statistics
    ● Exposes guard rails to protect your database
    ● Excellent for high-throughput asynchronous workloads
    Why use Connection pooling?

    View Slide

  82. Heroku Connection Pooling: PgBouncer Beta
    On Server Connection pooling is only available on Postgres 9.6, 10, 11
    - Read more about it: Postgres connection pooling
    - Still in public beta, more features to come!
    - Default connections direct to DBs still available after enabling
    Still offering Client-side buildpack
    - Client-side PgBouncer article

    View Slide

  83. Other resources for more learning
    ● Learn more about Rails DB performance
    monitoring with data.heroku.com
    ● Inspect your app with Seven Ways to Fortify
    your Application

    View Slide

  84. Questions welcome!
    Come by the Booth!

    View Slide