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

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

View Slide

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

View Slide

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

Rails 6 Multi-DB
Part 1: https://github.com/rails/rails/pull/32274
Parts ongoing: too many to list
Thank you to the Rails contributors!

View Slide

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 trafﬁc and data growth

View Slide

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

Self-Help/Life Coaching Business is booming!

View Slide

Super Secret Social interest platform revisited
● Customers have proﬁles
○ 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

Why do proﬁle 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

- The Characters table is huge (750 GB+)
- I can’t wait for all the queries on to ﬁnish 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

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

Let’s Talk about the CAP...Theorem

View Slide

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

● Help identify failure points
● Increase overall responsiveness
● Ensure longevity and stability of operations

View Slide

View Slide

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

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


View Slide

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

View Slide

What tools/changes do I need to make to set up or
recognize environments?
How do I deﬁne different databases?

View Slide

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

How does my Character class understand multi-DB?
What does the ActiveRecord look like for inheritance?

View Slide

class Character < ApplicationRecord
connects_to database: { writing: :characters, reading: :characters_replica }
end
class TvShowCharacter < Character
end
class MovieCharacter < Character
end

View Slide

Having the Base model deﬁne 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

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

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

View Slide

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

What is follower lag really?
● Postgres sends Write-Ahead Log (WAL) to replay changes
● The more trafﬁc to primary, the more WAL replicas replay
● Other factors on read replicas can affect replay

View Slide

Are we missing any indexes for the read replica?
What’s our connection usage look like?
How are other database resources?

View Slide

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

EXPLAIN ANALYZE

View Slide

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

● Things to look out for
○ Sort Method
○ Seq (Sequence) Scan
○ Groupings
○ Conditionals

View Slide

● Types of scans to favor
○ Bitmap Index Scan
○ Bitmap Heap Scan
○ Index or Index Only scans

View Slide

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

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

View Slide

View Slide

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

● 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

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

View Slide

● 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 signiﬁcantly
● 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

View Slide

We can Partition the Quotes Table!

View Slide

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

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

POSTGRES PARTITIONING:
NEEDS ITS OWN TALK

View Slide

But what about
User-sourced Characters
AND Quotes?

View Slide

User-sourced Content: Famous Character catchphrases
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

Hang on a second; even getting last week’s posts are
super slow though, they’re so many quotes...

View Slide

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

View Slide

How can we handle the data
growth?

View Slide

We can shard the Quotes to its own DB!

View Slide

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 speciﬁc Quotes DB workload

View Slide

Performance: Sharding the write database for Quotes
● Moving Quotes to its own DB relieves the rest of the
Rails App
● Characters are ﬂowing in a consistent fashion
● Other tables are performing as expected now

View Slide

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

View Slide

What’s happening to my
Rails app?

View Slide

View Slide

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

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

View Slide

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

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

Considerations

View Slide

● 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

● 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

● 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

● 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 overﬂow
● 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

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

View Slide

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

View Slide

View Slide

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

View Slide

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

View Slide

● 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

● 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

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

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

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

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

● Better Visibility on connection statistics
● Exposes guard rails to protect your database
● Excellent for high-throughput asynchronous workloads
Why use Connection pooling?

View Slide

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

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

Questions welcome!
Come by the Booth!

View Slide

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

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

Gabe Enslein

More Decks by Gabe Enslein

Other Decks in Programming

Featured

Transcript