7 ms·

I don't think, as the article claims, data denormalization is broken. I think most programmer's knowledge of SQL is scrawny --- unexpectedly so for a language t

I don't think, as the article claims, data denormalization is broken. I think most programmer's knowledge of SQL is scrawny --- unexpectedly so for a language that's 30 years old and underpins most applications. SQL was obviously meant to be simple, almost declarative, and resembling plain-English sentences. And yet it's the first thing that developers abstract away with an Object Relational Mapping, much to their loss. The article's example is one-to-one chat with millions of users. It says that counting unread messages is too slow unless you denormalize it. First of all, the Rooms aren't a core table. If each Room has just two people, as the article says, then all you need is one table, messages: a | b | sent | body | read -----+-------+---------------------+----------------+--------------------- ed | tom | 09/29/2016 14:22:10 | Hi | 09/30/2016 02:02:15 tom | ed | 09/30/2016 02:03:15 | What up? | 09/30/2016 02:04:03 sue | sally | 09/30/2016 08:19:20 | Catch ya later | So the count of unread rooms is: select count(1) from (select distinct a from messages where b = :user and read is null) t; If the columns are indexed, I would think PostgreSQL could handle such queries in a split second, even with millions of rows. At Facebook's scale, where you have hundreds of millions of users, then you will have hundreds of billions or rows, maybe trillions. Okay, so it might start to get slow that this point. But let's stop for a second and remember that 99.999% of web applications will never reach Facebook's scale. In Facebook's case, I read a long time ago that they make a database for each user. If so, the aforementioned table and query would still work --- in fact without the need for the b column.

Tenhundfeld 10y ago
I agree with most of what you said, but one thing bothers me: > And yet [SQL]'s the first thing that developers abstract away with an Object Relational Mapping, much to their loss. I see that sentiment repeated often, but I really don't understand it. A decent ORM does two things: 1) It makes writing simple queries better – more maintainable, easier to compose, easier to secure against injection, smoother to write without constantly translating between your domain language and SQL. 2) It gets out of your way when you need to take the reins. If it is difficult to make your ORM run raw SQL queries and map the results back into domain models and primitives like array, hash, whatevs – it's time to find a better ORM. For me and many decent devs I've worked with, an ORM is not about hiding SQL. It's about automating the simple, routine stuff and letting me focus on the important, complicated parts – including highly-optimized SQL queries, at times.
- barrkel 10y ago
  If someone's primary perspective of the database is through the lens of an OO graph, they will write code that performs abysmally. Graph navigation from row to row is antithetical to all the power relational algebra gives you. That's the biggest risk: viewing the DB primarily as an object store. For the app I work on, the DB is only viewed as an object store for very small sets of data - configuration, specifically. It's impossible to map it to objects in any realistically performant way for our user's data, not least because that would require the data round-tripping through the application. It's much, much more efficient to execute my multi-million row updates and inserts on the database server directly rather than drag the data down to the app, tweak it slightly, then persist it back.
  - ak39 10y ago
    This is true. ORMs force the thinking "objects first" (ie. work with objects, the SQL will be handled for you). This translates into a design of "per object" architecture. Business logic is abstracted for handling single rows only - one at a time - encapsulation being the design idiom. However, this is a trap that immediately becomes apparent when you realise that you've forgone the powerful set-based capabilities of SQL. SQL doesn't "need to see" a single row of a table as anything special to warrant ring-fencing in the name of encapsulation. The whole (related) data model is a single algebraic representation of state.
  - chris_wot 10y ago
    I've never quite worked out why graphs and relations can't be unified in some way. They both rely on first order logic, after all.
    - DougBTX 10y ago
      The select N+1 problem is a problem because of the overhead on each select, if that was free the graph navigation and relational set operations would match. Reminds me of, "in theory, there's no difference between theory and practice, in practice there is".
  - 10y ago
deleted 10y ago
[deleted]

Tenhundfeld 10y ago

I agree with most of what you said, but one thing bothers me: > And yet [SQL]'s the first thing that developers abstract away with an Object Relational Mapping, much to their loss. I see that sentiment repeated often, but I really don't understand it. A decent ORM does two things: 1) It makes writing simple queries better – more maintainable, easier to compose, easier to secure against injection, smoother to write without constantly translating between your domain language and SQL. 2) It gets out of your way when you need to take the reins. If it is difficult to make your ORM run raw SQL queries and map the results back into domain models and primitives like array, hash, whatevs – it's time to find a better ORM. For me and many decent devs I've worked with, an ORM is not about hiding SQL. It's about automating the simple, routine stuff and letting me focus on the important, complicated parts – including highly-optimized SQL queries, at times.

barrkel 10y ago
If someone's primary perspective of the database is through the lens of an OO graph, they will write code that performs abysmally. Graph navigation from row to row is antithetical to all the power relational algebra gives you. That's the biggest risk: viewing the DB primarily as an object store. For the app I work on, the DB is only viewed as an object store for very small sets of data - configuration, specifically. It's impossible to map it to objects in any realistically performant way for our user's data, not least because that would require the data round-tripping through the application. It's much, much more efficient to execute my multi-million row updates and inserts on the database server directly rather than drag the data down to the app, tweak it slightly, then persist it back.
- ak39 10y ago
  This is true. ORMs force the thinking "objects first" (ie. work with objects, the SQL will be handled for you). This translates into a design of "per object" architecture. Business logic is abstracted for handling single rows only - one at a time - encapsulation being the design idiom. However, this is a trap that immediately becomes apparent when you realise that you've forgone the powerful set-based capabilities of SQL. SQL doesn't "need to see" a single row of a table as anything special to warrant ring-fencing in the name of encapsulation. The whole (related) data model is a single algebraic representation of state.
- chris_wot 10y ago
  I've never quite worked out why graphs and relations can't be unified in some way. They both rely on first order logic, after all.
  - DougBTX 10y ago
    The select N+1 problem is a problem because of the overhead on each select, if that was free the graph navigation and relational set operations would match. Reminds me of, "in theory, there's no difference between theory and practice, in practice there is".
- 10y ago

deleted 10y ago

[deleted]