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.

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
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?
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
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
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?
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
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?
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!