How I Reduced My DB Server Load by 80%

Partial functional indexes are amazing. You can create an index that not only elides execution of the function, but automatically only includes the subset where that function would be relevant. Makes the index smaller, to boot.

    create index repo_name_lower on repos(LOWER(name)) where name IS NOT NULL;

I believe the primary driver here is the overwhelming reliance upon ORMs when building anything with your average web framework—be it Python, Ruby, JavaScript, or whatever. Sure, these ORMs may, and usually do, offer ways to write and execute queries directly in SQL, but a vast majority of current developers seems to always reach for the ORM. Now, for simple CRUD apps, sure, that’ll help get you going quickly without having to think outside your chosen language. But this then snowballs into never thinking outside your chosen language. When ORMs are the average developer’s introduction and sole interface to using databases, we wind up with all the StackOverflow questions about how to write query X in ORM Y, when it could have been written as an SQL statement, sproc, or function, and executed directly (and probably more efficiently). It’s both amusing and sad when I see or hear of someone being unable to interact with their db directly. I can only suspect they actually believe the ORM is the way to interact with their db. Oh, they may know SQL exists, but that’s about it.

This problematic state of affairs is only compounded by the fact that ORMs don’t properly and easily handle taking constraint, validation, and other data-focused application logic and actually writing it into the schema (some ORMs are better/worse at this than others, of course). They all seem to just default to letting as much of it be application-level code as possible. This then leaves new developers with the impression this is where such code belongs, so they never experience anything telling them it should be anywhere else. It’d be great to see more education offered within ORM docs, but the really important stuff seems to often be there only if you already know what you’re looking for. I’ve seen so many models defined over the years without indexes on the most obvious columns that are queried a bajillion times. Or maybe you can set a unique constraint on certain fields without a bunch of effort—sqlalchemy is decent at this in the Python world—but then you have to have unique validators for forms, rather than relying on the db to complain about a violation of the the constraint, and the ORM catching it in a friendly way devs can bubble up when needed. Oh, and then some insist on adding client-side validation of that same constraint. So, you take a constraint that matters at the db level, and is already easily handled at the db level, and people are writing client and server validation to enforce a condition the db is already supposed to be enforcing.

There’s madness here. Maybe it’s just me, though.

You can certainly have more than one db.

  > any programming logic you put in the DB will be impossible to scale if needed.
  > You can only have one DB, but any number of Rails servers.

The difference in processing time between a database with no rules and one with all of them is usually negligible.

  > You should always put indexes on things you query by

I have put indexes on things, and PostgreSQL still decided it was more efficient to do a sequential scan. There are cases where they don't make a difference ("low cardinality" or something like that). Also, indexes slow down writes and eat disk space. Usually each is just a little. But it can add up sometimes.

The advice I see most is to run EXPLAIN and judge from that. Even after you've added the index and run ANALYZE, run EXPLAIN again to make sure that the database is using the index and that it makes a difference.

Indexes are useful for speeding up select statements but they run a significant cost on insert and update.

Sometimes it's better not to index, if you have a table that is updated often and queried in varying, complex, rarely repeated ways.

No way: you can just kill -9 it from another terminal.

If exiting VI takes years... I shudder to think how long it'll someone to learn the "Write with Sudo" trick.

Yes on before my first oracle project I did a weeks course on Pl/SQL and a week on Forms and that worked out fine

You can absolutely build a product on top of an SQL database, by just writing the SQL queries you need.

As a bonus, if you're thinking ahead, you can avoid writing queries that will perform poorly. If you're not thinking ahead, at least you can more easily fix the ones that do.

I've never really understood why people want to interact with their database outside of its native query language. There's just so much mismatch between an ORM and the underlying interface, it feels like fighting all the time. And I don't feel like it's really saving any time either. The one thing that's sort of nice is it's easier to write a general filtering function, but SQL filtering lets you write so many things that won't perform well, making that easier isn't really helping anybody.

> if you don't use an ORM, you'll invent one yourself

Not true. You may use a graph database, or write SQL statements & apply functional transformations. Or you might be using a platform like smalltalk & just save your image. There are other paradigms besides OOP & relational databases.

> new hires will end up taking a ton of time to learn this "custom" orm framework of yours.

Or your new hire might really know how to leverage the particular database you're using, but you force him to throw away a lot of his experience & to start over from scratch & instrument the database through an ORM layer. There are a lot of ORMs, their APIs may even differ, so someone who knows Propel still has to stop & read the docs if you're using Doctrine, for example.

> it showed a true lack of knowledge of the ORM at hand and even SQL in general.

Quite the opposite, anyone who can craft a query that complex obviously knows the tool well. Their real problem is knowing when to not use that tool. Or they just write bad code, regardless of what tool/langauge they're using, for whatever reason.

ORMs try and solve a hard problem, and the devil is in the detail.

https://martinfowler.com/bliki/OrmHate.html

>And new hires will end up taking a ton of time to learn this "custom" orm framework of yours

Or your new hire actually learns how to use the tech that makes their code work. I could use fewer devs who have no idea how the code they write actually works.

> Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly

There is always a trade-off. The code you wrote, even if it is poorly re-implentation, you know it inside out, since you wrote it. The third party code, may be better, but people usually make a lot of implicit assumptions and end up not using it correctly, so you end up in situations like this.

For DB layer, I usually prefer custom code so I know exactly what is going on under the hood.

I think you might be confusing 2 different ideas. An ORM is an Object Relational Mapper. Lots of companies build very big software without using an ORM. And the very concept of an ORM becomes meaningless if you use a language that rejects Object Oriented Programming. For instance, when using Clojure, some teams use Korma:

http://sqlkorma.com/

Korma is a DSL. And I think you are confusing the concepts of ORM and DSL. I'd agree that most teams, over time, tend to develop (or borrow) their own DSLs for interacting with the database. And I've seen teams that use the term DSL loosely to refer to any code they write to interact with the database, so long as they are doing more than concatenating strings of SQL. But they don't necessarily use ORMs.

While I agree with this, the quality of ORMs vastly differs. For instance, the ORM style that wraps sql in a dsl is strictly better than one that encourages model lifecycle callback hell, as activerecord does. The latter is never necessary to work with a database and is a terrible pattern long-term for composing database interactions: it is extremely difficult to reason about what will actually happen inside the transaction (or immediately after).

Tl;dr you aren’t wrong, but many home-rolled ORMs are simpler and much easier to use than, say, activerecord.

> if you don't use an ORM, you'll invent one yourself...only poorly.

My favourite SQL library is Clojure's HugSQL, which avoids this issue by letting you write SQL in .sql files and then generating functions for you that execute these queries. https://www.hugsql.org/

Just so. SQLAlchemy and many other ORMs allow you to simply define an index. ActiveRecord always reimplemented far too much of the database. I'm not sure whether that was due to the state of MySQL functionality at the time or what.

I don't really understand why people throw the baby out with the bathwater like that. What's the problem of using an ORM for 99% of your queries and doing the other 1% in raw SQL? Many ORMs (like Django's) even help you by mapping the results back into models automatically[1].

[1] https://docs.djangoproject.com/en/1.11/topics/db/sql/#mappin...

There is a middleground too - for instance sqlalchemy in python allows you use code to build queries. It's python, it does quoting right (lowering lots of sqli concerns) and it has an orm layer on top of the query builder.

You get to use the orm for a lot of braindead simple, and mild complexity stuff, and can drop down to the sql builder as it makes sense for complexity or performance reasons.

It also understands the various databases very well, so if you choose e.g. postgres, you can access most of the features without needing much (if any) "raw sql".

I adopt a idea (not remember the original library where I say it) where ALL the sql strings are in a single .sql file.

Exactly as with sql upgrade scripts.

It look like this:

    --name: post-event
    SELECT post_log(@theId, @ENTITY, @ACTION, @CHANGEBY, @DATA, @VERSION)

    GO

    --name: get-location
    SELECT * FROM "Location"
    WHERE
        id = @id

    GO

    --name: list-location
    SELECT * FROM "Location"
    ORDER BY country, state, city;

    GO

Then I just parse this file (note the names with --) once and use this alike (in F#):

    module Location =
    let ENTITY = "Location"
    let SQL_LIST = SQL_CMDS.["list-location"]

    type LocationRecord = {id:int64 option; address:string; country:string; state:string; city:string; version:int64}

    type LocationQuery =
        | All
        | ById of int64

    let query q =
        use con = openConn()

        let toRec = Db.toRecord<LocationRecord>

        match q with
        | All ->
            Db.select con SQL_LIST []
            |> Seq.map toRec

        | ById(theId) ->
            let id = [P("@id", theId)]
            Db.select con SQL_BYID id
            |> Seq.map toRec

And plug a micro-orm (mainly just a very thin layer over ADO.NET in .NET. I do similar over swift and python).

This take me like a few hours. Let me test easily the sql. I can build the sql exactly as I want.

The only cons is the repetition on the scripts - because SQL is a terrible language that not allow composability, like similar to CSS - . Probably I will later use a template parser (like mustache or similar) but I think this is the closest to the holy grail ;)

We are happily using JOOQ with Java + Postgres.

I strongly recommend it. Having statically typed representation of the schema has many pros and the trade-off is a little overhead when changing the db schema to regenerate classes.

Some of the benefits include knowing exactly what part of the code stops compiling after changing the db schema, easily map POJOs to-from query results.

Also, there's simply no pressure that you have to be absolutely certain what each and every knob on your ORM does else you have bad data in your database. Been burned before with Hibernate defaults, and the thought of a junior dev creating wrong conf is just not worth it.

The database is basically the place were you least want a risk of misuse to have any unseen side effects.

>No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source.

Tbh thats currently my preferred methodology, just because it's usually nicer to clean up a mistaken query in something like sequel-pro, and its generally easy to verify correct output visually

And I've had to do the same thing with ORMs like sqlalchemy, and the only real difference is I first have to get the ORM to tell me what it generated (which is a pain, sometimes).

Intellij tools are capable to parse, lint, and autocomplete sql in strings.

To my own surprise, it has become my favorite way to handle queries with multiple/variable joins.

Maybe I'm doing it wrong, but I like to keep simple queries in an ORM, and then execute anything complicated as a stored procedure.

> No one likes copying the SQL command to a SQL IDE, making some changes, running it to make sure it works, and then copying it back to source.

And how exactly are you making sure your ORM code works?

That sounds more like a tooling problem.

Actually, I started to put SQL directly into my source code. IntelliJ, for instance, has really good support for embedded SQL code (syntax highlighting, auto-completion, executing queries including placeholders etc.).

SQL strings should always be in the hands of a DBA, not a developer. The RDBMS should get your request in the form of a SP call and get the data you need in the form of cursors.

So, SQL strings belong to the metal, not the UI.

> I think ORMs are there to make developing easier

Only for object oriented apps, where you want to tightly link behavior to your objects. For other kinds of app architectures, there may be no relational/object oriented mismatch to smooth out.

No, you could write raw SQL (we do this alot in our Rails app) or perhaps push out your abstractions to stored procedures (not a good option IMO, but maybe better than letting my framework come up with the best SQL)