Queries ➤ Understanding Locks ➤ Let the database focus on its main job ➤ Other tips ➤ Heroku Postgres what’s new & what’s coming ➤ Heroku PGSettings ➤ Heroku Postgres Credentials ➤ Server Side PGBouncer
cause expensive queries ➤ Expensive queries are very good at making other healthy/simple queries running slow (e.g. high resource usage, locks, etc.) ➤ Always think about adding/revisiting indexes when you create new tables, introducing and/or modifying new columns, queries ➤ Watch out unused indexes, but also keep it in your mind that these indexes may be used in followers ➤ the cost of maintaining the index v.s. the benefits the index provides for queries ➤ I/O is expensive (make sure cache hit rate is high!), good indexes will help reducing disk I/O and keep good cache
changed one look up query (add one select condition) 20:15 Added one index, run create index concurrently Connection counts Database load HTTP response time HTTP status code
pg:outliers or in the log, or Heroku Expensive Queries dashboard ➤ high sync_io_time is a good culprit (likely seq scan) ➤ Outside of Heroku ➤ install pg_stat_statements extension and run a query ➤ tweak log_min_duration_statement setting to see long running queries ➤ EXPLAIN the query (EXPLAIN (ANALYZE, BUFFERS)) ➤ Check out any avoidable seq scan, non ideal index usage ➤ Run ANALYZE to update the query plan
towns_code_index ON towns USING btree (code); Using Index Scan! 1.7ms to run (even faster second time, because it’s easily put on the cache and most of 1.7ms was spent on I/O)
➤ https://devcenter.heroku.com/articles/postgresql-indexes ➤ Partial Indexes ➤ covers just a subset of a table’s data (e.g. deleted_at = NULL, flagged = TRUE) ➤ smaller size, easier to maintain, faster to scan ➤ Expression Indexes ➤ matches on some function of the data (e.g. lower(email)) ➤ Multi-column Indexes ➤ e.g. WHERE a = x AND b = y ➤ covers WHERE a = x, but doesn’t cover WHERE b = y (if you use both of them, create two individual indexes) ADDING INDEXES TO FIX EXPENSIVE QUERIES
bad shape ➤ Simple queries can take forever if they’re waiting for acquiring a lock ➤ Typical lock issues ➤ ALTER TABLE is taking forever ➤ It may be just waiting for a lock (needs ACCESS EXCLUSIVE lock) ➤ ALTER TABLE itself becomes a lock, causing other queries to wait for a lock (ACCESS EXCLUSIVE lock conflicts with ALL queries) ➤ https://www.postgresql.org/docs/current/static/explicit-locking.html ➤ How to find locks? ➤ pg_locks table, heroku pg:locks or heroku pg:blocking
│ Type │ Modifiers ────────────┼───────────────────────┼─────────── id │ integer │ first_name │ character varying(50) │ last_name │ character varying(50) │ email │ character varying(50) │ BEGIN; SELECT * FROM users where id = 1; ALTER TABLE users ADD COLUMN nickname varchar(50); SELECT count(*) FROM users; ACCESS SHARE (holding a lock because of open transaction) ACCESS EXCLUSIVE (waiting for ACCESS SHARE to release the lock) ACCESS SHARE (waiting for ACCESS EXCLUSIVE) waiting queue SELECT count(*) FROM users; ACCESS SHARE ACCESS SHARE doesn’t conflict with ACCESS SHARE
analytics/dataclips with it? ➤ Use followers ➤ Are you running a logical backup (pg_dump) with it? ➤ Nope, stop it ➤ When you need to take a backup, use fork database ➤ Are you making connections super often? Do you have many open connections? ➤ Use connection pooler, PGBouncer ➤ Are you having lots of logs? ➤ Reduce logs
to show the things like… ➤ Connection Count ➤ Long Queries ➤ Idle in Transaction: very bad (unnecessary locks), do not have ➤ Indexes ➤ Bloat: haven’t touched in this talk but another important thing to monitor ➤ Hit Rate ➤ Blocking Queries ➤ Load ➤ Sequences: don’t run out of integer! ➤ https://devcenter.heroku.com/articles/heroku-postgresql#pg-diagnose
setting tweaks ➤ log-lock-waits (default: on for 1000ms+ lock wait) ➤ reduce noisy logs temporary ➤ log-min-duration-statement (default: 2000ms+ to execute) ➤ get more “long running queries” like 1000ms+, or reduce noisy logs ➤ log-statement (default: ddl e.g. CREATE, ALTER and DROP) ➤ all/mod for debugging, auditing ➤ none for reducing noisy logs (e.g. create temp tables) ➤ More to come (also feature requests are welcome!)
➤ Allow you to create Postgres roles so that you can control access permissions ➤ Common use case ➤ read-only user for analytics team ➤ with a shared database between micro services, give different users (roles) for each app db User table name, password User view name role (credential) analytics: read-only + only has an access to User view
helps get around database connection limits ➤ Have been helping lots of customers with connection limits Postgres app app app dyno dyno dyno Heroku Postgres Server Clients Client side (in-dyno) PGBouncer
Uniform across dynos/apps, less app side setup ➤ Simple concept (one bouncer needs to think about the connection limit) Postgres app app app dyno dyno dyno Heroku Postgres Server Clients Server side PGBouncer NEW!
keiko713
mktakuya
gundama01
line_devday2020
tsukubachallenge
keithpitt
rmw
jensimmons
lara
myddelton
stephaniewalter
jcasabona
jonyablonski
carmenhchung
reverentgeek
jponch
danielanewman
