Note that Postgres supports functional indexes, and the case in the post (a `lower(column)=`-clause not being able to utilise the column's index) is the example used in the documentation: <a href="https://www.postgresql.org/docs/current/static/indexes-expressional.html" rel="nofollow">https://www.postgresql.org/docs/current/static/indexes-expre...</a>
This story supports my growing theory that you should put as much of your app's rules in the database as you can.<p>There were three problems with having the rule in Rails:<p>1. The need for an index was easily overlooked.<p>2. The rule would be bypassed if a different app used the same database.<p>3. The rule wasn't even foolproof. Only a database constraint would guarantee uniqueness when two users are saving at the same instant.<p>The problem is, SQL is hard. We should not forget how much a programmer must learn. For example: Ruby, Rails, Linux command line, HTML, CSS, JavaScript, vi, how to exit vi, etc. Each of these takes years to master.<p>SQL is especially SQuirreLy. However, it can't possibly be worse than learning the myriad JavaScript frameworks and complicated server-build tools that are completely optional for 99% of us. My advice: Don't do a SPA. Spend your time on SQL instead :D
I've seen a litany of these kinds of posts and I'm always amazed by two things when I see them posted on HN:<p>1. A cadre of diehards can't wait to post how amazing Postgress is or would be for whatever it is the OP is doing (as an aside, why isn't Postgres more popular if it's so amazing?); and<p>2. How averse people are to actual SQL.<p>Years ago I dealt with this crap in the Java world back when Hibernate and the like were all the rage. I was always amazed at how much confirmation bias there seemed to be. People decided these ORMs were amazing and then completely ignored all the bugs introduced by this layer and effort spent trying to figure out what the ORM was doing and how to make it do the right thing.<p>Back in the day I always liked a Java data mapper framework called iBatis (now dead, replaced by Mybatis it seems), which was pretty simple. Write some SQL in an XML file and call that SQL from your Java code. It was parameterized (so no SQL injection issues) and you could still do some funky things with discriminated types and the like. Plus, analytics were super easy because you knew how often each query was called and how long it took. Also, you could easily EXPLAIN PLAN those queries if you even had to (usually needed indexes were obvious).<p>Compare this to the auto-generated SQL from the likes of Hibernate. ugh.<p>I've come to the conclusion that people have this tendency to decide X is bad and then go completely out of their way to avoid X. You see it with SQL and ORMs. It largely explains (IMHO) thing slike Javascript and GWT.<p>At least half the time "X is bad" really means "I don't understand X and I don't want to learn it".<p>Joel Spolsky's "leaky abstractions" is good and time-honoured advice.<p>Take the Hibernate example. Once you bought into that framework you had to do all your data access that way or you broke the caching. That's mostly bad.<p>People also overestimate their needs. They rush to create Hadoop clusters and distributed NoSQL solutions because, you know, relational DBs can't keep up with their "Big Data" (which means, millions of rows) when in fact you can dump billions of rows into a single MySQL instance.
Exactly why ORMs are a bad idea. I've always wondered whether ORMs help or harm. I feel like the one reason to use it is if you have a development team that isn't capable of writing SQL which in itself is bad.
> Rarely do we consider how one query or a series of queries could interact to slow down the whole site.<p>That doesn't seem right to me. It's almost always something to consider when designing the data model. Maybe I'm being uncharitable, but this seems to me to be equivalent to claiming we rarely consider the use of an algorithm or interactions between algorithms and data structures when writing some code. I mean, for toys that's fine, but I wouldn't defer this discussion for something I intended for public use.
> It turns out it was coming from this line in my model.
> This innocuous little line was responsible for 80% of
> my total database load. This validates call is Rails
> attempting to ensure that no two Repo records get
> created with the same username and name. Instead of
> enforcing the consistency in the database, it put a
> before commit hook onto the object and it’s querying
> the database before we create a new repo to make sure
> there aren’t any duplicates.<p>I still can't believe that Rails even attempts this. It's <i>simply not possible</i> to do this kind of enforcement in a race-free manner using SELECT statements with Postgres.
For all those ORM's are bad people:<p>`I’ve been seeing that stray 30s+ spike in request time daily for months, maybe years. I never bothered to dig in because I thought it would be too much trouble to track down. It also only happened once a day, so the impact to users was pretty minimal.`
Rails is a pretty popular framework and widely used. How come something like this was not caught earlier, presumably its affects all Rails applications.<p>Why would anyone code this kind of inefficiency instead of using inbuilt constraints. Has the code been reviewed, tested? Too many questions.<p>It's surprising given how popular Rails was and still is that something which should have been caught in the early days of Rails is discovered now years later. Aren't all the production apps seeing this? Didn't Twitter see this?<p>The real concern is a lot of highly promoted technologies in HN do not get the proper technical scrutiny that one should take for granted in a technical forum and increasingly hype is conflated to quality.
I do this everyday for the past 10 years.. This is my bread and butter. <a href="http://www.jonathanlevin.co.uk" rel="nofollow">http://www.jonathanlevin.co.uk</a>