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 Background Ruby on Rails developer, Postgres Specialist, Author, Consultant 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 for software delivery Performing DDL changes exclusively using ORM Migrations Not tracking DevOps metrics Not using Feature Flags 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 coverage (Simplecov), increase test speed and reliability Lint ORM and SQL (Squawk ) migrations for safe DDL Enhance Rails migrations with Anchor Migrations, safety-linted, non-blocking, idempotent & consistent. 2 3 4 5 6 7 8 9 10 11 12 RailsConf 2025 - 10 Mistakes Slide 6 of 45 Forming Scaling Optimizing
7. ~/P/rideshare (chore/anchor_migrations)> cat anchor_migrations/20250623173850_anchor_migration.s -- 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 bloc 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. Storage and retrieval. 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 able to read and interpret query execution plans Not learning how to use cardinality, selectivity, or execution plan BUFFERS info Adding indexes haphazardly (over-indexing) Choosing schema designs with poor performance Generating AI solutions but not being able to verify them 14 💵 Server costs, Developer time RailsConf 2025 - 10 Mistakes Slide 11 of 45 Forming Scaling Optimizing
12. ❌ Mistake #9—DB Inexperience ✅ 🛠️ Fixes Hire for experience: DB specialists, DBAs, and consultants Grow experience internally with books, courses, conferences, communities. Provide a production-like database instance and data for experimenting. Maintain it and use it in your workflow. Learn to use pages, identify latency sources, selectivity, cardinality, correlation, and locality to improve your designs Avoid performance-unfriendly schema designs like random UUID primary keys 15 RailsConf 2025 - 10 Mistakes Slide 12 of 45 Forming Scaling Optimizing
13. Tuples & MVCC Which Spiderman is the live tuple? RailsConf 2025 - 10 Mistakes Slide 13 of 45
14. Fixed-size 8KB Pages How do I fit all my data into these small boxes? 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: Ludicrous Speed RailsConf 2025 - 10 Mistakes Slide 16 of 45
17. Mistake #8—Speculative DB Design Avoiding beneficial database constraints due to speculation about the future Casting doubt about the ability to evolve the schema design Not using data normalization practices by default, avoiding duplication Avoiding all denormalization, even for 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. Validate using database_consistency gem. Normalize by default. Design for today, but anticipate growth in data and query volume. Use denormalization sometimes, for example with multi-tenancy. 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 Review BUFFERS counts from execution plans to improve designs Add DB observability. 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 "users" \ 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 from my unlaunched Postgres App performance tool RailsConf 2025 - 10 Mistakes Slide 23 of 45
24. Mistake #6—ORM Pitfalls Allowing inefficient queries that are ORM generated Never restricting column access, always using SELECT * Using non-scalable query patterns like huge IN lists Not removing unnecessary COUNT(*) , ORDER BY queries from ORM defaults Using ORM LIMIT / OFFSET pagination over alternatives Not using ORM counter caches or the prepared statement cache 24 25 💵 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 ) Load only needed columns: select() , pluck() , for 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 ORM prepared statement cache to skip repeated parsing/planning Skip unnecessary count queries with a counter cache Use size() over count() and length() Use EXISTS , set implicit_order_column 26 19 25 26 28 29 30 31 RailsConf 2025 - 10 Mistakes Slide 25 of 45 Forming Scaling Optimizing
26. books = Book.includes(:author).limit(10) SELECT books.* FROM books LIMIT 10; SELECT authors.* FROM authors WHERE authors.id IN (1,2,3,4,5,6,7,8,9,10); 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 No safety linting for DDL migrations Not using a production-like instance for practice big DDL changes Not using safety timeouts for Postgres, MySQL, SQLite Not learning the underlying locking mechanisms, or safer, multi-step alternatives 💵 Longer cycles, maintainability RailsConf 2025 - 10 Mistakes Slide 27 of 45 Forming Scaling Optimizing
28. ❌ Mistake #5—DDL Fear ✅ 🛠️ Fixes Practice DDL changes on a production-like instance. Collect timing. Study lock behavior. Use multi-step safe alternatives. ignored_columns , . INVALID CHECK constraint before NOT NULL Lint DDL in Active Record (PostgreSQL) strong_migrations (MySQL/MariaDB) online_migrations , Squawk for SQL Learn lock types for operations, tables, rows using pglocks.org Use a low lock_timeout for DDL changes with retries 32 9 33 1 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 Operating in huge sets of 10K+ rows, causing seconds of wait time for users Ineffective filtering and indexing on low cardinality columns Missing indexes for high cardinality filter columns, foreign keys Not using advanced indexing like multicolumn or partial (Postgres) Using only default B-Tree indexes, not other types like GIN, GiST Performing slow aggregate queries ( SUM , COUNT ) on huge tables For huge tables of 100GB or more in size, avoiding partitioning 💵 Server costs, user experience RailsConf 2025 - 10 Mistakes Slide 30 of 45 Forming Scaling Optimizing
31. ❌ Mistake #4—Excessive Data Access ✅ 🛠️ Fixes Work on 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. Improve UX by pre-calculating aggregates with rollup gem , or with materialized views of denormalize data, managed with scenic Migrate time-based data into a partitioned table 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","Tolerance":0.1}, {"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 Using high growth data gems like public_activity, papertrail, audited, or ahoy , and not archiving 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. ❌ 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 using time-partitioned tables, and efficient DETACH CONCURRENTLY 46 47 48 49 RailsConf 2025 - 10 Mistakes Slide 34 of 45 Forming Scaling Optimizing
35. RailsConf 2025 - 10 Mistakes Slide 35 of 45
36. Mistake #2—Missing DB Maintenance Running unsupported versions of Postgres, MySQL, or SQLite Keeping unneeded tables, columns, constraints, indexes, functions, triggers, or extensions in your database Not monitoring or fixing heavily fragmented tables and indexes Leaving Autovacuum and other maintenance parameters untuned 💵 Poor performance, security risk, UX RailsConf 2025 - 10 Mistakes Slide 36 of 45 Forming Scaling Optimizing
37. ❌ Mistake #2—Missing DB Maintenance ✅ 🛠️ Fixes Upgrade your database. Postgres why upgrade? 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 , logical replication, or copy swap drop ) Reindex fragmented indexes ( REINDEX CONCURRENTLY ) Maintain your database like your application code. Maintainable...Databases? podcast 50 51 52 53 46 53 RailsConf 2025 - 10 Mistakes Slide 37 of 45 Forming Scaling Optimizing
38. RailsConf 2025 - 10 Mistakes Slide 38 of 45
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 excessive CPU, memory, and disk IO by writing inefficient ORM queries Allowing inefficient queries from libraries like jsonapi-resources, graphql-ruby, , ActiveAdmin Using lazy loading and allowing N+1s Not auto-cancelling excessively long queries, idle transactions, or schema migrations Using designs for Postgres that don't work well with immutable row versions (tuples), MVCC, and Autovacuum 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() ). Improve efficiency, reduce the use of CPU, memory, and data access. Avoid high update churn designs, replacing in-place updates with "append- mostly", e.g. slotted counters, Increase HOT updates. Prevent lazy loading by using Strict Loading for queries, records, models, or apps Improve resiliency by setting upper limits on time spent in queries, idle transactions, connections, and schema migrations. 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. Cultivate 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/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