[HN Gopher] Optimize Database Performance in Ruby on Rails and A...
___________________________________________________________________
Optimize Database Performance in Ruby on Rails and ActiveRecord
Author : amalinovic
Score : 76 points
Date : 2024-11-06 10:40 UTC (3 days ago)
(HTM) web link (blog.appsignal.com)
(TXT) w3m dump (blog.appsignal.com)
| bdcravens wrote:
| 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.
| Lio wrote:
| 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.
| nesarkvechnep wrote:
| 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.
| bdcravens wrote:
| On projects I've used it, we're already running scheduled
| jobs anyways, so another job for refreshing wasn't a problem.
| pawelduda wrote:
| 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.
| mobilemidget wrote:
| 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.
| matthewmacleod wrote:
| 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.
| Lio wrote:
| 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.
| KronisLV wrote:
| > 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.
| lostapathy wrote:
| 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.
| mcphage wrote:
| > 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.
| echelon wrote:
| 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.
| chikere232 wrote:
| People have forgotten how horribly it failed last time
| Alifatisk wrote:
| What happened then?
| pawelduda wrote:
| 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
| boesboes wrote:
| Nothing, probably just not a fan...
| Lio wrote:
| 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.
| earthnail wrote:
| 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.
| Alifatisk wrote:
| Something with Rails 8 sparked a renaissance, it's gotten some
| attraction for new users again.
| pawelduda wrote:
| Source?
| Alifatisk wrote:
| 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.
| bluerooibos wrote:
| 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_switch
| ing...
|
| * https://www.reddit.com/r/rails/comments/1ah1b36/is_the_ra
| ils...
|
| 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 :)
| pawelduda wrote:
| Nice, happy to see that
| nop_slide wrote:
| 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.
| Tainnor wrote:
| 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).
| fny wrote:
| 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.
| rco8786 wrote:
| > 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.
| cultofmetatron wrote:
| 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.
| chucke wrote:
| 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.
| rdsubhas wrote:
| > 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...
| sklivvz1971 wrote:
| 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...
| gigatexal wrote:
| 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.
| andrewstuart wrote:
| 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.
| pdimitar wrote:
| 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.
| status_quo69 wrote:
| 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).
| yen223 wrote:
| 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.
| smallerfish wrote:
| Not true. There are plenty of libraries that make this
| pleasant. Ok JVM, for example: jdbi, mybatis, jooq.
| zzzeek wrote:
| those are ORMs
| Tainnor wrote:
| 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.
| zzzeek wrote:
| > 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.
| Tainnor wrote:
| 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.
| smallerfish wrote:
| No, they're not. Hibernate is an ORM. These all let you
| map the results of sql queries to objects.
| zzzeek wrote:
| object
|
| relational
|
| mapping
| smallerfish wrote:
| _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...
| thunky wrote:
| > 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.
| mhw wrote:
| 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.
| bluerooibos wrote:
| > 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
| sklivvz1971 wrote:
| > 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.
| aczerepinski wrote:
| 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.
| evantbyrne wrote:
| 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.
| cies wrote:
| 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.
| Tainnor wrote:
| 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.
| andatki wrote:
| 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!
___________________________________________________________________
(page generated 2024-11-09 23:02 UTC)