db=# EXPLAIN (format yaml) SELECT count(*) FROM errors;
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Aggregate" +
Total Cost: 49971.71 +
Plan Rows: 1 +
Plans: +
- Node Type: "Seq Scan" +
Relation Name: “errors" +
Total Cost: 48172.96 +
Plan Rows: 719496 +
Slide 13
Slide 13 text
$ rails console
> user.errors.page(100)
Slide 14
Slide 14 text
db=# EXPLAIN ANALYZE SELECT id FROM errors OFFSET 500000
LIMIT 100;
!
QUERY PLAN
--------------------------------------
- Plan: +
Node Type: "Limit" +
Actual Rows: 100 +
Plans: +
- Node Type: "Seq Scan" +
Relation Name: "errors" +
Actual Rows: 500100 +
Slide 15
Slide 15 text
db=# EXPLAIN ANALYZE SELECT msg FROM errors WHERE
id >= 500000 AND id < 500100;
!
QUERY PLAN
--------------------------------------------------------
- Plan: +
Node Type: "Bitmap Heap Scan" +
Relation Name: "errors" +
Actual Rows: 100 +
Plans: +
- Node Type: "Bitmap Index Scan" +
Index Name: "errors_pkey" +
Total Cost: 5.42 +
Actual Rows: 100 +
Index Cond: "((id >= 500000) AND (id <=
500100))"
Slide 16
Slide 16 text
Long-running Migrations
The only constant in life is change
Slide 17
Slide 17 text
class AddIndexToAsksActive < ActiveRecord::Migration
disable_ddl_transaction!
!
def change
add_index :asks, :active, algorithm: :concurrently
end
end
*Rails >= 4
Slide 18
Slide 18 text
Deadlock? Wha?
Slide 19
Slide 19 text
Too Many Locks
wiki.postgresql.org/wiki/Lock_Monitoring
Slide 20
Slide 20 text
Intensive DB Queries
www.postgresql.org/docs/current/static/warm-standby.html
Slide 21
Slide 21 text
Too Many DB Connections
www.craigkerstiens.com/2014/05/22/on-connection-pooling