10 Costly Database Performance Mistakes (And How To Fix Them)

After working with countless Rails and Postgres applications as a consultant and backend engineer, I’ve seen firsthand how database mistakes can cause big costs and headaches. Poor data types, inefficient queries, and flawed schema designs slow down operations, and result in excessive costs through over-provisioned servers, downtime, lost users, and engineering hours spent restructuring features.

How do we prevent these pitfalls? Awareness is the first step. And if your database is already serving tons of woefully inefficient queries, where should you focus for the biggest wins?

In this talk, we’ll break down 10 real-world Rails database mistakes, including how they happened, the impact they had, and most importantly, how to fix them. Topics include query design, indexing, schema optimization, and how the CPU, memory, and IO resources tie into Active Record SQL performance.

Transcript

1. 10 Costly Database Performance Mistakes (And How To Fix Them) bit.ly/rc10m RailsConf 2025 - 10 Mistakes Slide 1 of 45
2. 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
3. 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
4. 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
5. 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
6. ❌ 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
7. ~/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
8. RailsConf 2025 - 10 Mistakes Slide 8 of 45
9. Mistake #9—DB Inexperience RailsConf 2025 - 10 Mistakes Slide 9 of 45 Forming Scaling Optimizing
10. 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
11. 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
12. ❌ 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
13. Row versions (Tuples), MVCC, transactions Which Spiderman is "live" and "dead"? RailsConf 2025 - 10 Mistakes Slide 13 of 45
14. 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
15. RailsConf 2025 - 10 Mistakes Slide 15 of 45
16. 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
17. 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
18. ❌ 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
19. -[ 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
20. database_consistency gem RailsConf 2025 - 10 Mistakes Slide 20 of 45
21. 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
22. ❌ 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
23. 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
24. 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
25. ❌ 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
26. 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
27. 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
28. ❌ 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
29. 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
30. 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
31. ❌ 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
32. 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
33. 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
34. RailsConf 2025 - 10 Mistakes Slide 34 of 45
35. ❌ 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
36. 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
37. RailsConf 2025 - 10 Mistakes Slide 37 of 45
38. ❌ 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
39. 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
40. 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
41. ❌ 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
42. # 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
43. 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
44. Thank you! bit.ly/rc10m Consulting Refined Pages, LLC Newsletter pgrailsbook.com @andyatkinson.com RailsConf 2025 - 10 Mistakes Slide 44 of 45
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