171–180 of 203 posts
I’m a Rails developer by trade, been doing it for over a decade. Bad indexing and lazy N+1 are 90% of the performance problems in a typical rails application. The other 10% is when people do aggregates or joins in application code instead of sql. I absolutely despise ActiveRecord because it makes non-trivial aggregates a pain to write. Sequel is a much better ORM but good luck getting a team on board.
Earlier quoted context omitted.
That really depends on the language - specifically, on whether it already has constructs that can map nicely (e.g. LINQ in C#), or macros to define them, or syntax that is generally amenable to DSLs even without macros in the picture (e.g. Lisps). SQL itself is also not a particularly well-designed query language. E.g. the order of the query doesn't reflect the natural data flow (SELECT .. FROM .. is reversed - compa…
SQL is powerful. A DSL that "fixes" things in this area getting all the other language feature interactions right isn't trivial, all the while users have to learn yet another language. Take PRQL for example: https://prql-lang.org . It looks nice, but the examples are very basic. What about window functions, grouping sets, lateral, DML, recursive SQL, pattern matching, pivot/unpivot etc. Might be doable, but perhaps,…
And yet in practice the fixes end up more portable. How many of the things on your list of non-basic SQL have consistent syntax across databases, yet alone consistent behaviour?
Earlier quoted context omitted.
Django has pretty great features for handling exactly this. Unfortunately it doesn't by default warn you if you're missing this optimization. I've had to write that warning system myself into iommi, but with that on it's super nice.
Well, Django has an ORM that makes this kind of feature necessary in the first place? ORM's are trying to hide the relational nature of your db, don't they?
This is kind of relevant now : Mastodon is seeing a lot of growth all the sudden, the popular server for it is in Rails, and scaling up quickly is a challenge for server operators used to the levels of traffic from two months ago. Twitter did rewrite their frontend to JVM languages because it was enough of a bottleneck to be worth it. If you can get Mastodon substantially further by serving some views using something…
Ugh, tell me about it. I had an interesting weekend dealing with traffic loads that grew by about 7x in a few days. I wrote about my misadventures at https://blog.freeradical.zone/post/surviving-thriving-throug... . The gist of it is that a RoR “Sidekiq” task queue gets CPU bound after about 25-30 worker threads doing things like making REST API calls to remote servers, querying a database, insert status updates, etc…
It lacks a frontend and admin UI, so not ready for prime-time, i guess. But good enough to glue onto a pinafore frontend. And to manage a small instance.
The most promising rust alternative, rustodon, is ground to a halt, it seems. A lean, sinatra-based backend could work, but wouldn't solve the complexity of hosting caused by Ruby runtime and deps. That also doesn't exist to my knowledge.
Earlier quoted context omitted.
I’ve found that all this does is make the query less readable. SQL is purpose made for writing queries, and avoids unnecessary syntax noise you get when trying to fit the query into a host language based dsl.
In a statically typed language, what you get from a good query builder is that "malformed SQL statements" blow up at compile-time instead of at run-time.
The biggest database performance problem that I have seen time and time again over the 20+ years I've been using sql databases now is caused by people not understanding that databases are relational (ie designed to perform efficiently over tables, not individual rows) and so running code that essentially causes hundreds/thousands of individual queries where they could just do one big query. Any time you are doing a s…
Sounds obvious... of course, sometimes ORM and other abstraction layers conspire to hide that you are doing queries in a loop.
When the DB VMs were migrated suddenly we had complaints about certain ETL jobs going from minutes to days.
I opened up a packet capture and took a look (thankfully at the time the queries weren’t encrypted). What I saw was that this big name ETL tool was loading data one row at a time using one query per row. This apparently was ok when the DB and app server were in the same building but adding a few additional milliseconds to each query totally screwed the jobs.
Thankfully there was a parameter that could be set in the DB driver to fix the issue and we just had to convince the ETL tool vendor to set that.
Earlier quoted context omitted.
Yeah antidotal, but I was doing some tests of an existing Rails app API compared to Hasura (erlang GraphQL server) talking to same DB, and querying same data was like 100x faster in Hasura..
Hasura is Haskell, not Erlang, I thought.
Earlier quoted context omitted.
Well, Django has an ORM that makes this kind of feature necessary in the first place? ORM's are trying to hide the relational nature of your db, don't they?
Not really... At least, not a good ORM. The actual idea behind a model based ORM system is to basically solve two problems. The first is SQL is old and its methodology doesn't directly map onto modern programming paradigms. This one is just apparent by seeing what you get back from raw queries; tables. Want to do something with the table? Go and iterate over the rows individually. Want to get something from a foreign…
Well, SQL is clunky. But relational ideas map very well with (some) modern programming.
Of course, that's for the kind of modern programming that's not OOP.
Earlier quoted context omitted.
A pure query builder is just writing SQL on syntax tree level, more or less. This makes sense for the same reason why you want your macros to operate on ASTs and not raw text. But I would argue that it's still much closer to plain text SQL in the code than to any ORM.
Right, but it lives with your application code and has the same syntax as the application code. That's probably preferable to SQL stored procedures (which often live outside source control).
Earlier quoted context omitted.
Ugh, tell me about it. I had an interesting weekend dealing with traffic loads that grew by about 7x in a few days. I wrote about my misadventures at https://blog.freeradical.zone/post/surviving-thriving-throug... . The gist of it is that a RoR “Sidekiq” task queue gets CPU bound after about 25-30 worker threads doing things like making REST API calls to remote servers, querying a database, insert status updates, etc…
I'm experimenting with some alternatives. I prefer Rust (because I speak that) or Ruby (it's my main language) but the Go option gotosocial is my favorite. It lacks a frontend and admin UI, so not ready for prime-time, i guess. But good enough to glue onto a pinafore frontend. And to manage a small instance. The most promising rust alternative, rustodon, is ground to a halt, it seems. A lean, sinatra-based backend co…