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!
Keiko Oda
keiko713
reverentgeek
rmw
skipperchong
carmenhchung
keathley
productmarketing
clairelew
thoeni
jcasabona
