Optimize Database Performance in Ruby on Rails and ActiveRecord | Hacker News Reader

Optimize Database Performance in Ruby on Rails and ActiveRecord

blog.appsignal.com

83 pointsamalinovic58 comments

If you start hitting performance issues with Rails, switch to using Arel for queries and pump the data into lightweight classes. ActiveRecord optimization magic is a foot bazooka.

If you want to reuse the code between these objects and the corresponding ActiveRecord model, do it with a module.

The biggest problem with Rails is that people get so glued to the conventions that everything gets shoehorned into a model or controller instead of using a better abstraction.

> The biggest problem with Rails is that people get so glued to the conventions that everything gets shoehorned into a model or controller instead of using a better abstraction.

As a Rails fanboy, I couldn't agree more.

Rails' semi-insistence on doing everything convention based and rewarding you for it can be a double edged sword in that way. The idea of doing anything outside the bounds of a controller/model/job sounds scary, especially when you consider how flexible Ruby itself is and how off the rails (heh) you can go pretty quickly. But at the end of the day, we should not be afraid of creating new conventions - which is a demonstrably different idea than just devolving into a raw Ruby scripting bonanza.

objects are fundamentally a bad abstraction over sql. Thats why I love phoenix's ecto. its a very good elixir abstraction over sql that is more like a super powered Arel.

The rails core team would tell you NOT to use arel, as it's private API.

The conceptual compression of having activerecord be a database table mapper, entity class, form/input validation, and query builder, it's both its greatest strength and its greatest weakness, depending of the stage of the business. FWIW the ruby community has more suitable alternatives which self serve the kind of abstractions you describe, such as hanami.

Back when I wrote Rails apps, Arel was stable enough and its own gem. Things may have changed, and that’s incredibly depressing.

Sometimes you just need SQL not everything that AR gives you. Most apps are read heavy, so you need an escape hatch. For me, Arel was always that: a programmatic way to alter SQL.

Hanami is a complete Rails replacement. I wouldn’t advocate for that at all.

As someone who evangeline Arel where I used to work 10y ago, I can assure you that arel has *never* been considered stable (it also never followed semver), nor has it ever been considered recommended or publicly documented API to use with rails, neither in the time prior to the gem having been moved to the github rails org, nor after. And every rails core team member that has publicly mentioned arel since, afair has always prefaced it with the "private API" warning.

> Sometimes you just need SQL not everything that AR gives you.

I used to agree, until I took a step back and realised how much ruby code ceremony I had to write in order to write, p.ex. A multi insert statement With an expression. It was several character more, and didn't seamlessly trabslate to SQL. I eventually replaced the AREL craft with core sequel, where I'd use it to build the queries and pass the statements to the AR sql execution method. I could have gone with raw sequel too.

> Hanami is a complete Rails replacement. I wouldn’t advocate for that at all.

The top level comment mentioned abstractions that something like hanami already supports. Instead of bending rails, to something it was not built to support, you either pick an alternative tool that does, or you accept what rails does and move on.

> The biggest problem with Rails is that people get so glued to the conventions that everything gets shoehorned into a model or controller instead of using a better abstraction.

https://speakerdeck.com/cgriego/models-models-every-where?sl...

Another approach are materialized views, where you basically create a caching table with exactly the fields you need. Can be used for any query, but particularly valuable for rich reporting where you may be pulling data from all over your associations. It requires you to write the source query in raw SQL, but with the Scenic gem, pulls it into the Active Record ecosystem with models, migrations, etc.

I try to avoid materialized views when I can due to the problem of keeping them up to date.

If I see a materialized view I’d definitely go back to see if I could eke out any other optimisation in a straight view rather than use it.

With regard to Scenic, I like the way it separates out SQL but I’ve always found how it versions view files with an iteration in the file name a pain.

I wish it had the option to just pick up changes in the source file without needing a migration to bump that version number.

On larger projects it’s easy to run into version number clashes in different pull requests.

You can have multiple small materialised views, which get updated when appropriate and are composed by a regular view. It’s more convenient than a single large materialised view.

On projects I've used it, we're already running scheduled jobs anyways, so another job for refreshing wasn't a problem.

One problem (at least in Postgres) is that you can't partially refresh the view, so it inevitably takes more time as your database grows. There's pg_ivm extension but it limits what can be put in the view definition. Otherwise you'll have to be smart about composing it of smaller, independent views.

I wrote a fair bit of RoR app code in the past.

Now I'm on the JVM and Hibernate is the equivalent of AR there: an ORM.

We try to get rid of Hibernate were we can: it is slow, comes with huuuuuge conceptual overhead (detachment/lifecycle/lazy/etc), makes it easy to write bad code (e.g. queries in loops), and you still need to know SQL (for complex queries that dont fit in the ORM).

So we use jOOQ now. Much better. Somthing that's AR also offers (but without the type safety). LINQ also has a similar interface (the one w/o the fancy syntax).

Alternatively there are tools like SQLDelight (Kotlin) and Sqlx (Rust). That provide an alternative path to "just use SQL" without resorting to queries-in-strings.

As someone who's worked in both stacks, I dislike both ActiveRecord and Hibernate for different reasons. ActiveRecord because it pushes the database layer into the model layer (and because Rails pushes the model layer into the view, this often leads to N+1 queries) - and Hibernate because it's a mess of optimisations that nobody understands, weird lazy loading behaviour, it saves records in the background when it feels like, has inscrutable transactional behaviour and wants be in charge of your database schema (thankfully you can turn that off). ActiveRecord's saving graces are a decent-ish query API and that it popularised migrations - Hibernate's that it encourages a repository pattern.

As you write, jOOQ and similar are much better approaches. For Ruby, Sequel is a better library than AR for a number of reasons.

Nice post covering many of the main ways to improve efficiency.

Here’s a related post I wrote for AppSignal:

What's Coming in Ruby on Rails 7.2: Database Features in Active Record https://blog.appsignal.com/2024/07/24/whats-coming-in-ruby-o...

For folks interested in additional depth on optimizing Postgres for use with Active Record/Rails, please check out my book:

High Performance PostgreSQL for Rails https://andyatkinson.com/pgrailsbook

Thanks!

Are people using Rails for new startups?

It feels like I haven't heard anything new about Rails for the past five years, but suddenly in the last few weeks it's been a trending topic on HN.

People have forgotten how horribly it failed last time

What happened then?

Probably referring to twitter switching from Rails a long time ago, because it didn't scale for them. I can't think of anything else

Nothing, probably just not a fan…

Yeah seems like it. I’m still working with startups using Rails.

The changes coming with Rails 8 are really good for small teams working to tight budgets.

The new deployment strategy is amazing compared to some of the complex react, micro services, k8s and cloud stacks I’ve worked with.

They always involve a lot of engineering effort and a lot of expensive proprietary services.

If you want to get off the ground quick for little money a Rails monolith with the frontend using Hotwire still looks very hard to beat.

If Shopify, GitHub, etc can scale to be the largest vendors in their respective markets you know you can too.

I’ve been using Rails for tape.it to power cloud syncing for an app.

Upsides: hilariously easy deployment and development story. Migrations and secrets management is trivial. Everything works. No long searches for third party dependencies.

Downsides: speed (as in compared to a Java backend, for example), and activerecord has a learning curve on performance. Did some bad design decisions on two endpoints that my iOS client depends on and those design decisions hurt badly. They would’ve hurt in any language but hit me much much sooner with Rails.

Something with Rails 8 sparked a renaissance, it's gotten some attraction for new users again.

Source?

I have no direct source, that's just what I have noticed from reading blogs about switching to Rails and by people on Twitter embracing the new release. I've also noticed the engagement in posts regarding Rails has increased and been reading comments by users who've never tried Rails giving it a shot and found it pleasant to work with.

Having been in the Rails community for seven years or so, I can say that there's been a lot more excitement and activity after Rails 7 (Hotwire stack). It doesn't seem that long ago, but we're already on Rails 8, which is now bringing a new round of excitement of its own.

I've seen a ton of different "we're switching to Rails" posts floating around like these -

* https://www.reddit.com/r/rails/comments/1dkcegr/im_switching...

* https://www.reddit.com/r/rails/comments/1ah1b36/is_the_rails...

There's also the new Rails Foundation - https://rubyonrails.org/foundation and their new annual conference, Rails World, which has proved pretty popular - https://www.reddit.com/r/rails/comments/1fqizim/rails_world_...

So, Rails ain't dead yet :)

Nice, happy to see that

Personal anecdote, I recently got interested in rails again in the last 2 months after not touching it for 10 years.

Like other commenters have said, the Hotwire stack plus the new “solid” stack of database adapters is really cool. The general ethos of simplifying the web stack and “compressing the complexity” as DHH says is really appealing.

I’m migrating a personal project from python/svelte and it’s been a ton of fun so far.

As with any technology, there are its supporters and its detractors.

I would say that it's popularity has definitely waned in the last 10-15 years, probably in part due to Javascript gaining more ground, perceived performance problems with Ruby and the fact that many frameworks in other languages adopted some of Rails's best features (such as convention over configuration, DB migrations, unified project structures, etc.) and ignored its worst parts (such as monkeypatching).

We're using it for https://www.freeats.com/, an open source applicant tracking system we just launched.

You can check out the source here: https://github.com/freeats/freeats

I honestly do not understand ActiveRecord and ORMs in general. There is the obvious advantage of mapping, for which one merely needs microORMs. Even by reading this article one realizes that for anything complex, it is much simpler to directly write SQL queries rather than use byzantine techniques whose only raison d'etre is to coerce the ORM into writing those SQL queries. I mean -- why not write SQL directly? :-)

There's the not-so-common case of needing database independence as well, but at that point DB perf becomes a really hard problem to solve generally...

Yeah … for want of easy serializing to classes and what not I hate the ORM approach. The database is already a black box. Stats might be out of whack. The indexes might be bad. Etc etc. now add magic that the ORM does.

It’s ridiculous the lengths people go to avoid writing sql.

Sql is awesome and gives immediate access to the full raw power of your database.

It’s worth learning whilst learning some abstraction isn’t.

As a semi-counter-point, learning some data mapper libraries where you learn a DSL that seems better-structured than SQL definitely helped me understand SQL better.

SQL is ultimately a bit dysfunctional and takes a lot of getting used to. Not arguing it's worth learning but since most of my career I was not truly exposed to it, I preferred the bespoke DSL.

I'll agree using ORMs is indeed going to ridiculous lengths to avoid learning even a smidgen of SQL but there's a nice middle ground.

If you're encountering these problems at scale where scale is up to the reader, I can promise you that you're already writing SQL. You might have already started from the beginning by writing direct SQL, because that's something that rails also allows you to do against a model. Apart from juniors, I don't think I've ever met anyone who actually said that they wanted to avoid writing SQL in all cases. The reality is that it's far more useful and efficient to burn cpu time than dev time, and ORMs general tend towards the latter than the former in the favor of generally being a fair bit more legible (depending on the writer).

Say you have a SQL database on one side, and a Python/Ruby/Java/Golang webserver on the other. If you wrote raw SQL, you'd have to also write the ORM (the object-relational mapper) yourself as well to map database results into objects or whatever data structure your webserver expects.

Not true. There are plenty of libraries that make this pleasant. Ok JVM, for example: jdbi, mybatis, jooq.

those are ORMs

In the sense that in Java (almost) everything is an object, a jOOQ query must of course return an object (such as a Record2<String, String>), but unlike Hibernate or AR, jOOQ doesn't enforce a 1:1 mapping between domain objects and tables, especially if you turn off POJO generation - at my last company, we had some custom mapping from a table to several subclasses of a sealed class depending on certain fields in the table - and you can even split a single class into two tables etc.

> In the sense that in Java (almost) everything is an object,

no, this is misleading. There are different kinds of objects. if you use JDBC to run a query you get back something like a Row object (sorry I havent done JDBC since the 1990s) - the "Row" like object does not declaratively define the fields of the row, the fields and the data of each field are all data. however when you get back POJOs, as you say, these declaratively define the fields that are "mapped" to a column. if jOOQ does this, it's an ORM. ORM has nothing to do with writing SQL - that's called a "SQL builder". the ORM is about marshalling data from POJO-style objects to and from relational database rows.

There are different ways of using jOOQ, some of them being more like an ORM (but still more low-level than Hibernate) and others less so. You can use the API in such a way that it just returns a general tuple object holding the result from your query (called Record1<T>, Record2<T1, T2>, etc.). This is especially useful when fetching from multiple tables. You can also use codegen to auto-create POJOs and mapping code, but this is not required.

No, they're not. Hibernate is an ORM. These all let you map the results of sql queries to objects.

object

relational

mapping

_relational_ is the key word you're missing.

ORMs map _objects_ to _relations_ (i.e. tables).

"Unlike ORM frameworks, MyBatis does not map Java objects to database tables but Java methods to SQL statements." https://en.wikipedia.org/wiki/MyBatis

"Jdbi is not an ORM. It is a convenience library to make Java database operations simpler and more pleasant to program than raw JDBC." https://jdbi.org/

"While jOOQ is not a full fledged ORM (as in an object graph persistence framework), there is still some convenience available to avoid hand-writing boring SQL for every day CRUD. That's the UpdatableRecord API [which is only one part of it and you don't have to use it]" https://blog.jooq.org/how-to-use-jooqs-updatablerecord-for-c...

ORMs do not require SQL builders. Textual, hand constructed SQL to rows based on tables mapped to objects and you get objects back. "Why don't people learn SQL?" as a retort for ORMs is incorrect.

This is about ActiveRecord, which tries very hard to convince you that a relational database works like objects in memory, especially when it comes to associations, saving etc. Hibernate is similar in that regard (although it at least enforces repositories, but you can still have surprises when it comes to when it loads associated tables). Both allow you to drop down to SQL but then you lose syntax checking / type safety and the conveniences of the library.

With something like jOOQ, the query language is basically SQL, just made type-safe. You write a query that maps to an SQL query 1:1 and then you map the results to whatever you need. No implicit saving, auto-loading of associations in the background etc.

So it's not about "people should use the SQL syntax instead of query builders", it's "people should write relational queries explicitly instead of relying on the framework to save and load stuff from the database when it deems it necessary". Your domain objects do not need to know that they're persisted and they don't need to carry a DB connection around at all times (looking at you, ActiveRecord).

> I honestly do not understand ActiveRecord

ActiveRecord also does a terrible job of hiding the database or sql, even compared to other ORMs like Django's.

So the price you pay for the it doesn't even buy you that much benefit via abstraction in the first place.

It’s really handy to have a composable API for building SQL queries where different elements are contributed by different parts of the code. From example, having your authorisation code apply restrictions to queries through where clauses, joins, etc. to ensure a user only sees the records they are allowed to see.

I currently spend a large proportion of my time working in a Java code base that uses JDBC directly. There are many places where the complexity of the work to be done means code is being used to assemble the final SQL query based on conditionals and then the same conditional structure must be used to bind parameter values. Yes, in some places there are entire SQL statements as String literals, but that only really works for simple scenarios. There are also many bits of code that wrap up common query patterns, reimplementing some of what an ORM might bring.

I recently implemented soft deletion for one of the main entities in the system, and having to review every query for the table involved to see whether it needed the deleted_at field adding to the where clause took a significant amount of time. I think better architecture supported by a more structured query builder would have made this much easier. For me that’s the main benefit of an ORM.

> I honestly do not understand ActiveRecord

What's not to understand? ActiveRecord exists to make queries easier to write and more readable - the same reason any library or tool is created.

Why don't we skip using tools like Ruby or Rails and just write everything in machine code instead? /s

> ActiveRecord exists to make queries easier to write and more readable

It achieves only making queries much harder and much more opaque. It completely fails at that.

Speed to market. One person or a very small team can build a feature-rich app in Rails (or equivalent framework) faster than any other approach.

Plenty of companies end up building the wrong product altogether and need to pivot when they realize customers don't care about feature X and need feature Y instead. In cases like these you hope you get the right product out there and survive long enough to regret building with the fast framework instead of the finely tuned ultra performant version.

A well-designed ORM can help you with performance, for example, by reducing the amount of effort to do things like fetch related records in a single query. The really good ones also handle migrations. And finally, all of the popular ones I'm aware of allow developers to use them just for mapping results to objects. I would say that in general the further an ORM strays from SQL concepts the more troublesome it is likely to be. If the query syntax looks like Mongo, then you probably won't have a good time when doing more advanced things.

I have seen many projects where people use activerecord for their SQL interfacing. It is only the really simple queries that are okay, but as soon as there is some complexity the generated sql query are not so good quality to put it politely. Imho raw sql is always best, build your own query generator which you understand its logic fully and is tailored for your project.

Absolutely not the case, in my experience.

Like any advanced tool, it’s possible to shoot yourself in the foot if you don’t use it correctly. But once you know it, it makes simple queries easy, complex queries tolerable, and allows progressively dropping to raw SQL if needed.

I’d go even further and say ActiveRecord (particularly when using Arel for complex query construction) is the single best ORM I’ve ever used. I miss it hard whenever I interact with an SQL database in any other language.

I think I disagree with this.

Not about using raw SQL when needed. I would definitely do that for a hot, complex query if I felt I needed to but I think ActiveRecord is rock solid if you know how to use it and how to examine its output.

There are times where an ORM is a very useful tool and I've found ActiveRecord to be better than most home made query generators I’ve run into regardless of base language.

> Imho raw sql is always best, build your own query generator which you understand its logic fully and is tailored for your project.

This comes with the caveat that someone will have to work with bespoke-sql-codegen:0.0.1 when you go work in another company, with StackOverflow being of no help, as well as an unknown amount of vulnerabilities.

I once worked on a Java project that put like 90% of the logic in the DB and the application mostly called prepared statements and parsed the results with a bunch of low level logic. There was some dynamic SQL generation but the end result was the whole thing feeling like code archaeology because of course there was basically no documentation or good examples on how to do things, decoupled from the already complex codebase.

It was blazing fast but also an absolute nightmare to work with. I would be similarly guarded towards using anything bespoke without a community and knowledge base, regardless of the stack.

As for ORMs, there’s nothing preventing you from making an efficient DB view and mapping that into an entity.

Hard disagree. I have replaced hand written reports that were total nightmares to reason about and maintain with fairly trivial activerecord implementations that were quite literally hundreds of times faster.

Activerecord may not give optimal solutions but it can get close enough for a lot of workloads, and complicated sql can become a complete bear over time.

> It is only the really simple queries that are okay, but as soon as there is some complexity the generated sql query are not so good quality to put it politely.

ActiveRecord was specifically designed to work in that way—tools for most queries, which are simple, and making it easy to use SQL for more complicated cases.

Reply on news.ycombinator.com