Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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.

2a55ca3e1a512c34282f4295ab435283?s=128

Gabe Enslein

April 30, 2019
Tweet

Transcript

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

    Enslein
  2. Who uses Postgres and Rails? Who is on the latest

    and greatest versions of both?
  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?
  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!
  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
  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
  7. Self-Help/Life Coaching Business is booming!

  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
  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?
  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?
  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...
  12. Let’s Talk about the CAP...Theorem

  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
  14. Let’s Talk about the CAP...Theorem • Help identify failure points

    • Increase overall responsiveness • Ensure longevity and stability of operations
  15. None
  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
  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
  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
  19. What can I do to speed up my Rails App?

  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
  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
  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
  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
  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
  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
  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
  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?
  28. None
  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
  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
  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?
  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
  33. EXPLAIN ANALYZE

  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)
  35. Interest Outlet: Our favorite Characters Performance: Optimizing Read Replicas •

    Things to look out for ◦ Sort Method ◦ Seq (Sequence) Scan ◦ Groupings ◦ Conditionals
  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
  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)
  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
  39. None
  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
  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
  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
  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
  44. None
  45. We can Partition the Quotes Table!

  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
  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');
  48. POSTGRES PARTITIONING: NEEDS ITS OWN TALK

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

  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?
  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
  52. Quotes TOTAL size 5 TB+ Characters are now 3 TB+

    Uh-oh, we have way more Characters & Quotes...
  53. How can we handle the data growth?

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

  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
  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
  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
  58. What’s happening to my Rails app?

  59. None
  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
  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
  62. None
  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)
  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
  65. Considerations

  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?
  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?
  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?
  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?
  70. • How many resources are left on the Rails app

    servers? ◦ Memory ◦ Connections ◦ CPU Consideration: Where are all my resources?
  71. • How many resources are there left to spare on

    the DBs? ◦ Memory ◦ I/O ◦ CPU ◦ Connections Consideration: Where are all my resources?
  72. None
  73. • Apps should have safety measures ◦ Maintenance Mode capabilities

    ◦ Feature Flagging ◦ Circuit Breakers for when too many alerts fire Food For Thought
  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
  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
  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
  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
  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
  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!
  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?
  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?
  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
  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
  84. Questions welcome! Come by the Booth!