10 Costly Database Performance Mistakes (And How To Fix Them) bit.ly/rc10m RailsConf 2025 - 10 Mistakes Slide 1 of 45 

My RailsConf History 2010 & 2011 Baltimore 2017 Phoenix 2022 Portland 2024 Detroit 2025 Philly Thank you organizers and attendees! We will miss RailsConf! RailsConf 2025 - 10 Mistakes Slide 2 of 45 

Bill: Costly DB Mistakes July 8, 2025 Item Qty Price Total Bigger server costs 12 $500.00 $6,000.00 Customer downgrades, churn (5K ARR) 5 $5,000 $25,000.00 Dev time triage, resolution, 5 devs, 5 hours/month, $150/hr 300 $150.00 $30,000.00 Total $61,000.00 🍬 Thanks for your business! RailsConf 2025 - 10 Mistakes Slide 3 of 45 

10. Infrequent Releases 9. DB Inexperience 8. Speculative DB Design 7. Missing DB Monitoring 6. ORM Pitfalls 5. DDL Fear 4. Excessive Data Access 3. Missing Data Archival 2. Missing DB Maintenance 1. Rejecting Mechanical Sympathy Costs 👀 are here 💵 💵 💵 RailsConf 2025 - 10 Mistakes Slide 4 of 45 Forming Scaling Optimizing 

Mistake #10—Infrequent Releases Using Gitflow or similar legacy software delivery processes Not using Feature Flags to decoupling releases and feature visibility Not tracking or improving DevOps metrics Performing DDL ( create index , alter table , etc.) exclusively as Rails Migrations in releases 1 💵 Cycle time, incident response RailsConf 2025 - 10 Mistakes Slide 5 of 45 Forming Scaling Optimizing 

❌ Mistake #10—Infrequent Releases ✅ 🛠️ Fixes Use Trunk-based development (TBD) and feature flags. 2024 Rails Survey : 20% (500+) "multiple/month", 2% (50+) "multiple/quarter" Track DevOps metrics. DORA, SPACE, Accelerate, 2-Minute DORA Quick Check Raise test suite coverage (Simplecov), increase speed & reliability Supplement DDL releases using Anchor Migrations, safety-linted SQL, non- blocking, idempotent, maintain consistency with Active Record Migrations 2 3 4 5 6 7 8 11 12 RailsConf 2025 - 10 Mistakes Slide 6 of 45 Forming Scaling Optimizing 

~/P/rideshare (chore/anchor_migrations)> cat anchor_migrations/20250623173850_anchor_migrat -- Generated by anchor_migrations 0.1.0 -- -- Examples: CREATE INDEX IF NOT EXISTS 👈 idx_trips_created_at ON trips (created_at); ~/P/rideshare (chore/anchor_migrations)> bundle exec anchor lint warning[require-concurrent-index-creation]: During normal index creation, table updates are but reads are still allowed. --> anchor_migrations/20250623173850_anchor_migration.sql:4:1 | 4 | / CREATE INDEX IF NOT EXISTS 5 | | idx_trips_created_at ON trips (created_at); | |__________________________________________ | = help: Use `CONCURRENTLY` to avoid blocking writes. 👈 Find detailed examples and solutions for each rule at https://squawkhq.com/docs/rules Found 1 issue in 1 file (checked 1 source file) RailsConf 2025 - 10 Mistakes Slide 7 of 45 

RailsConf 2025 - 10 Mistakes Slide 8 of 45 

Mistake #9—DB Inexperience RailsConf 2025 - 10 Mistakes Slide 9 of 45 Forming Scaling Optimizing 

Object–relational mismatch Active Record ORM Object-orientation, inheritance, classes, methods, Ruby code ORM, query generation gems. Abstraction, reusability, portability. Ruby on Rails 13 Relational Database Data access, SQL, relations, indexes, execution plans, normalization, caches Pages, buffers, locks, MVCC & bloat in PostgreSQL Database RailsConf 2025 - 10 Mistakes Slide 10 of 45 

Mistake #9—DB Inexperience Not hiring DB specialists or DBAs Not using SQL in application code or business intelligence Not reading and interpreting query execution plans Not using cardinality, selectivity, or execution plan BUFFERS info in designs Adding indexes haphazardly (over-indexing) Choosing schema designs with poor performance 14 💵 Server costs, Developer time RailsConf 2025 - 10 Mistakes Slide 11 of 45 Forming Scaling Optimizing 

❌ Mistake #9—DB Inexperience ✅ 🛠️ Fixes Hire experience: DB specialists, DBAs, and consultants Grow experience: books, courses, conferences, communities Create a production clone instance for experimentation. Use it in your workflow. Use concepts of pages, buffers, latency, selectivity, cardinality, correlation, and locality to improve your designs Avoid performance-unfriendly designs like random UUID primary keys 15 RailsConf 2025 - 10 Mistakes Slide 12 of 45 Forming Scaling Optimizing 

Row versions (Tuples), MVCC, transactions Which Spiderman is "live" and "dead"? RailsConf 2025 - 10 Mistakes Slide 13 of 45 

Fixed-size 8KB Pages How is table and index data stored and how does that affect latency? RailsConf 2025 - 10 Mistakes Slide 14 of 45 

RailsConf 2025 - 10 Mistakes Slide 15 of 45 

Mastering Postgres High Performance SQLite Scaling Postgres PostgreSQL Performance Starter Kit (Free) Postgres Performance Demystified (Free) Ludicrous Speed RailsConf 2025 - 10 Mistakes Slide 16 of 45 

Mistake #8—Speculative DB Design Avoiding beneficial database constraints today due to speculation about tomorrow Doubting ability to evolve the schema design in the future Not using third normal form normalization (3NF) by default Avoiding all forms of denormalization, even for use cases like multi-tenancy16 💵 Data bugs, high maintenance costs RailsConf 2025 - 10 Mistakes Slide 17 of 45 Forming Scaling Optimizing 

❌ Mistake #8—Speculative DB Design ✅ 🛠️ Fixes Use all available constraints for data consistency, integrity, quality (CORE: constraint-driven ) Create matching DB constraints for code validation. Match PK/FK types. Use database_consistency gem. Normalize by default. Eliminate duplication. Design for today, but anticipate growth in data and query volume. Use denormalization sometimes, for example tenant identifier columns 17 18 RailsConf 2025 - 10 Mistakes Slide 18 of 45 Forming Scaling Optimizing 

-[ RECORD 1 ]----------------+---------- table_schema | rideshare table_count | 10 column_count | 65 not_null_count | 54 pk_count | 10 fk_count | 8 unique_count | 0 check_count | 57 not_null_ratio | 0.83 pk_per_table | 1.00 fk_per_table | 0.80 👈 check_per_column | 0.88 total_constraints_per_column | 1.15 table_and_constraints_stats_ratios.sql RailsConf 2025 - 10 Mistakes Slide 19 of 45 

database_consistency gem RailsConf 2025 - 10 Mistakes Slide 20 of 45 

Mistake #7—Missing DB Monitoring Not logging slow queries or collecting query statistics and execution plans Not using the BUFFERS information in PostgreSQL execution plans Spending time finding application source code locations for SQL queries Not monitoring critical background processes like Autovacuum 💵 Triage, incident resolution RailsConf 2025 - 10 Mistakes Slide 21 of 45 Forming Scaling Optimizing 

❌ Mistake #7—Missing DB Monitoring ✅ 🛠️ Fixes Log and store SQL query source code line numbers, using Query Logs (SQLCommenter formatted), visibile in Rails log Collect query execution plans, manually or automatically with auto_explain Reduce BUFFERS counts in execution plans to reduce latency Observe database processes. Postgres: pg_stat_statements, PgHero, PgAnalyze, PgBadger MySQL: Percona Monitoring and Management (PMM), Oracle Enterprise Manager for MySQL, SQLite: SQLite Database Analyzer 19 20 21 22 23 RailsConf 2025 - 10 Mistakes Slide 22 of 45 Forming Scaling Optimizing 

ID: 7 Account: my-rideshare-account Database: rideshare_development Query ID: 3517660050859089705 Query Text w/o annotations: SELECT "users".* FROM "use WHERE "users"."id" = $1 LIMIT $2 Annotations: controller=trip_requests action=create application=Rideshare source_location=app/models/trip.rb:6:in `rider' 👈 Main Command: SELECT FROM table: "users" WHERE clause: "users"."id" = $1 RailsConf 2025 - 10 Mistakes Slide 23 of 45 

Mistake #6—ORM Pitfalls Performing unnecessary, costly ORM queries like COUNT(*) , ORDER BY Using non-scalable query patterns like huge IN lists Not restricting column access, always using SELECT * Using inefficient ORM pagination Not using ORM caches 25 24 💵 Overprovisioned, inefficient queries RailsConf 2025 - 10 Mistakes Slide 24 of 45 Forming Scaling Optimizing 

❌ Mistake #6—ORM Pitfalls ✅ 🛠️ Fixes Put your app on a SQL Query Diet (find sources ) Limit columns to what's needed: select() , pluck() , better use of indexes Refactor huge IN lists. Use a join, VALUES , or ANY + ARRAY (Postgres) Use endless (keyset) pagination (pagy gem ) over ORM LIMIT / OFFSET Use the prepared statement cache to skip repeated parsing/planning Skip unnecessary count queries by using the counter cache Use size() over count() and length() 26 19 25 26 28 29 RailsConf 2025 - 10 Mistakes Slide 25 of 45 Forming Scaling Optimizing 

books = Book.includes(:author).limit(10) -- Generated SQL from Active Record "includes" SELECT books.* FROM books LIMIT 10; SELECT authors.* FROM authors WHERE authors.id IN (1,2,3,4,5,6,7,8,9,10); -- Alternative SQL using ANY + ARRAY SELECT books.* FROM books WHERE author_id = ANY( SELECT UNNEST(ARRAY( SELECT id FROM authors WHERE id <= 10 )) ); RailsConf 2025 - 10 Mistakes Slide 26 of 45 

Mistake #5—DDL Fear Creating code workarounds to avoid schema evolution and data backfills Using blocking DDL due to not understanding exclusive locks and queueing Not linting DDL migrations for safety Not practicing big DDL changes on a production DB clone Not auto-canceling contending DDL operations (Postgres, MySQL, SQLite) 💵 Longer cycles, high maintenance RailsConf 2025 - 10 Mistakes Slide 27 of 45 Forming Scaling Optimizing 

❌ Mistake #5—DDL Fear ✅ 🛠️ Fixes Practice DDL changes on a production clone with timing. Understand locks taken and access patterns. Use multi-step non-blocking DDL. ignored_columns . INVALID CHECK constraint before NOT NULL Safety-lint DDL. Active Record & PostgreSQL strong_migrations, (MySQL/MariaDB) online_migrations, Squawk for SQL Learn about locks and conflicts using pglocks.org Auto-cancel DDLs that fail lock acquisition with a low lock_timeout . Retry. 32 9 33 10 RailsConf 2025 - 10 Mistakes Slide 28 of 45 Forming Scaling Optimizing 

Mistake #4—Excessive Data Access 8 grocery bags in 1 trip 2 trips of 4 bags each RailsConf 2025 - 10 Mistakes Slide 29 of 45 

Mistake #4—Excessive Data Access Querying and retrieving huge sets, 10K+ rows, making users wait Ineffective filtering and indexing on low cardinality columns Missing indexes on high cardinality columns or foreign keys for filtering Not using advanced indexing strategies or index types Performing slow aggregate queries ( SUM , COUNT ) causing users to wait Not breaking up big tables using table partitioning 💵 Server costs, user experience RailsConf 2025 - 10 Mistakes Slide 30 of 45 Forming Scaling Optimizing 

❌ Mistake #4—Excessive Data Access ✅ 🛠️ Fixes Work with small sets of data. Restructure queries to select fewer rows, columns, and perform fewer joins. Add "missing indexes" on high cardinality columns, try out pganalyze_lint (and hypopg ) Use advanced indexing like multicolumn, partial indexes, GIN, GiST. Pre-calculate aggregates using rollup gem, create denormalized materialized views, manage using scenic gem Migrate huge tables to partitioned tables for improved performance and maintenance 34 35 38 36 37 39 40 41 RailsConf 2025 - 10 Mistakes Slide 31 of 45 Forming Scaling Optimizing 

pganalyze_lint --dbname rideshare_development \ --host localhost --username andy -v \ check -t rideshare.users Index Selection Settings: {"Options":{"Goals":[{"Name":"Minimize Total Cost","To {"Name":"Minimize Number of Indexes"}]}} Missing indexes found: 👈 CREATE INDEX ON rideshare.users USING btree (type) 👈 RailsConf 2025 - 10 Mistakes Slide 32 of 45 

Mistake #3—Missing Data Archival Storing a significant proportion of data in tables and indexes that's never queried Capturing high growth data using gems like public_activity, papertrail, audited, or ahoy, and not archiving unneeded data Not archiving app data from churned customers, retired features, or soft deleted rows Performing resource-intensive massive DELETE operations 42 43 44 45 💵 Server costs, user experience RailsConf 2025 - 10 Mistakes Slide 33 of 45 Forming Scaling Optimizing 

RailsConf 2025 - 10 Mistakes Slide 34 of 45 

❌ Mistake #3—Missing Data Archival ✅ 🛠️ Fixes Archive ALL data that's not regularly queried! Shrink a table using copy swap drop Use partition-friendly gems like logidze gem or partition your big tables, making necessary Rails compatibility changes Archive app data from churned customers, soft deleted rows, and retired features (discover with Coverband ) Replace massive DELETE operations by migrating to a partitioned table, unlock ability to DETACH CONCURRENTLY instead of DELETE 46 47 48 49 RailsConf 2025 - 10 Mistakes Slide 35 of 45 Forming Scaling Optimizing 

Mistake #2—Missing DB Maintenance Running unsupported versions of Postgres, MySQL, or SQLite Not monitoring or fixing heavily fragmented tables and indexes Leaving Autovacuum and other maintenance parameters untuned Not removing unneeded database objects 💵 Poor performance, security risk, UX RailsConf 2025 - 10 Mistakes Slide 36 of 45 Forming Scaling Optimizing 

RailsConf 2025 - 10 Mistakes Slide 37 of 45 

❌ Mistake #2—Missing DB Maintenance ✅ 🛠️ Fixes Upgrade your database. Postgres why upgrade? Tune Autovacuum for your workload. Prune and Tune indexes, use pg_dba for psql, rails_best_practices gem Drop unneeded tables, columns, constraints, indexes, functions, triggers, and extensions Rebuild fragmented tables (pg_repack, pg_squeeze, VACUUM FULL ) Reindex fragmented indexes ( REINDEX CONCURRENTLY ) Maintainable...Databases? podcast 50 51 52 53 53 RailsConf 2025 - 10 Mistakes Slide 38 of 45 Forming Scaling Optimizing 

Mechanical Sympathy Mechanical sympathy is when you use a tool or system with an understanding of how it operates best.55 RailsConf 2025 - 10 Mistakes Slide 39 of 45 Forming Scaling Optimizing 

Mistake #1—Rejecting Mechanical Sympathy Using high-churn designs (updates and deletes) for Postgres that don't work well with tuples, MVCC, and Autovacuum Over-using limited CPU, memory, and IO from inefficient reads and writes Inefficient generated queries from gems like jsonapi-resources, graphql-ruby, ActiveAdmin Allowing lazy loading and N+1s Not preventing excessively long queries, idle transactions 56 57 58 💵 ALL the costs RailsConf 2025 - 10 Mistakes Slide 40 of 45 Forming Scaling Optimizing 

❌ Mistake #1—Rejecting Mechanical Sympathy ✅ 🛠️ Fixes Take control of your SQL ( to_sql ) and execution plans ( .explain() ) Replace high update churn designs with "append-mostly", e.g. slotted counters, Increase HOT updates. Prevent lazy loading by enabling Strict Loading. Start by logging violations. Preserve stability by setting upper limits on allowed durations for queries & idle transactions, number of connections 59 60 61 62 63 RailsConf 2025 - 10 Mistakes Slide 41 of 45 Forming Scaling Optimizing 

# config/application.rb config.active_record.action_on_strict_loading_violation = :log 👈 Trip.limit(10).order(created_at: :desc). to_sql 👈 rideshare(dev)> Trip.limit(10).order(created_at: :desc). explain(:analyze, :buffers) 👈 Trip Load (14.2ms) SELECT "trips".* FROM "trips" ORDER BY "trips"."created_at" DESC LIMI => EXPLAIN (ANALYZE, BUFFERS) SELECT "trips".* FROM "trips" ORDER BY "trips"."created_at" DESC QUERY PLAN ------------------------------------------------------------------------------------------- Limit (cost=0.28..0.57 rows=10 width=80) (actual time=0.021..0.022 rows=10 loops=1) Buffers: shared hit=3 -> Index Scan Backward using idx_trips_created_at on trips (cost=0.28..30.27 rows=1000 Buffers: shared hit=3 Planning Time: 0.063 ms Execution Time: 0.047 ms (6 rows) RailsConf 2025 - 10 Mistakes Slide 42 of 45 

Embrace Mechanical Sympathy When you understand how a system is designed to be used, you can align with the design to gain optimal performance. RailsConf 2025 - 10 Mistakes Slide 43 of 45 

Thank you! bit.ly/rc10m Consulting Refined Pages, LLC Newsletter pgrailsbook.com @andyatkinson.com RailsConf 2025 - 10 Mistakes Slide 44 of 45 

1. atlassian.com/git/tutorials/comparing-workflows/gitflow-workflow 2. atlassian.com/continuous-delivery/continuous-integration/trunk-based-development 3. railsdeveloper.com/survey/2024/#deployment-devops 4. dora.dev/guides/dora-metrics-four-keys 5. octopus.com/devops/metrics/space-framework 6. a.co/d/0Sk81B9 7. dora.dev/quickcheck 8. github.com/simplecov-ruby/simplecov 9. github.com/ankane/strong_migrations 10. github.com/sbdchd/squawk 11. github.com/andyatkinson/anchor_migrations 12. github.com/andyatkinson/rideshare/pull/230 13. en.wikipedia.org/wiki/Object–relational_impedance_mismatch 14. postgres.fm/episodes/over-indexing 15. andyatkinson.com/generating-short-alphanumeric-public-id-postgres 16. andyatkinson.com/presentations/blob/main/pass2024/README.md 17. andyatkinson.com/constraint-driven-optimized-responsive-efficient-core-db-design 18. github.com/djezzzl/database_consistency 19. andyatkinson.com/source-code-line-numbers-ruby-on-rails-marginalia-query-logs 20. postgresql.org/docs/current/auto-explain.html 21. postgres.ai/blog/20220106-explain-analyze-needs-buffers-to-improve-the-postgres-query-optimization- process 22. mysql.com/products/enterprise/em.html 23. sqlite.org/sqlanalyze.html 24. andyatkinson.com/blog/2024/05/28/top-5-postgresql-surprises-from-rails-developers 25. andyatkinson.com/big-problems-big-in-clauses-postgresql-ruby-on-rails 26. andyatkinson.com/tip-track-sql-queries-quantity-ruby-rails-postgresql 27. ddnexus.github.io/pagy/docs/api/keyset/ 28. island94.org/2024/03/rails-active-record-will-it-bind 29. blog.appsignal.com/2018/06/19/activerecords-counter-cache.html 30. depesz.com/2024/12/01/sql-best-practices-dont-compare-count-with-0 31. bigbinary.com/blog/rails-6-adds-implicit_order_column 32. andycroll.com/ruby/safely-remove-a-column-field-from-active-record 33. github.com/fatkodima/online_migrations 34. github.com/andyatkinson/pg_scripts/pull/18 35. github.com/andyatkinson/pg_scripts/blob/main/find_missing_indexes.sql 36. github.com/pganalyze/lint 37. github.com/HypoPG/hypopg 38. github.com/andyatkinson/pg_scripts/pull/19 39. github.com/andyatkinson/rideshare/pull/232 40. github.com/scenic-views/scenic 41. andyatkinson.com/blog/2023/07/27/partitioning-growing-practice 42. github.com/public-activity/public_activity 43. github.com/paper-trail-gem/paper_trail 44. github.com/collectiveidea/audited 45. github.com/ankane/ahoy 46. andyatkinson.com/copy-swap-drop-postgres-table-shrink 47. github.com/palkan/logidze 48. andyatkinson.com/blog/2023/08/17/postgresql-sfpug-table-partitioning-presentation 49. github.com/danmayer/coverband 50. why-upgrade.depesz.com 51. andyatkinson.com/blog/2021/07/30/postgresql-index-maintenance 52. github.com/NikolayS/postgres_dba 53. cybertec-postgresql.com/en/products/pg_squeeze 54. maintainable.fm/episodes/andrew-atkinson-maintainable-databases 55. wa.aws.amazon.com/wellarchitected/2020-07-02T19-33-23/wat.concept.mechanical-sympathy.en.html 56. github.com/cerebris/jsonapi-resources 57. github.com/rmosolgo/graphql-ruby 58. github.com/activeadmin/activeadmin 59. boringrails.com/tips/active-record-to-sql 60. github.com/andyatkinson/rideshare/pull/233 61. cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance 62. andyatkinson.com/blog/2022/10/07/pgsqlphriday-2-truths-lie 63. jordanhollinger.com/2023/11/11/rails-strict-loading RailsConf 2025 - 10 Mistakes Slide 45 of 45