Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Domain Logic and SQL (2003)

martinfowler.com

Post reply on HN
Print view View original

31–40 of 61 posts

Re: Domain Logic and SQL (2003)

#31

Post by keanzu »

ak39 wrote: post #5
nizmow wrote: post #2

One thing this article doesn't mention is that in many cases databases scale poorly, and only vertically. You can throw a bunch of domain logic in complex SQL queries and see significant performance benefits, but at some point you may find that your application has grown and your SQL server is running into IOPS and CPU limits that are difficult to fix. At that point your answer could be, pull logic out of those compl…

Most (if not all) modern RDBMSs allow for reliable horizontal and vertical scaling. SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers (for horizontal scaling) in geographically distributed servers. PostgreSQL has this and it's absolutely free. Where is this meme that RDBMSs…

> Where is this meme that RDBMSs do not scale coming from?

They don't scale to Google or Facebook operational sizes. Once you get to a billion customers or so the ol' RDBMS tends to struggle. Because everyone wants to be Google they imagine they have Google's problems. I've been in a meeting where the client was talking about their severe scaling issues for their "big data" which could only possibly be resolved by state of the art cloud solutions. I pressed them on the numbers - they had 400GB. You can buy an iPhone with 512GB.

Oracle's Exadata X8-2 is ~1PB per full rack.

Re: Domain Logic and SQL (2003)

#32

Post by [deleted] »

keanzu wrote: post #31
ak39 wrote: post #5

Earlier quoted context omitted.

Most (if not all) modern RDBMSs allow for reliable horizontal and vertical scaling. SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers (for horizontal scaling) in geographically distributed servers. PostgreSQL has this and it's absolutely free. Where is this meme that RDBMSs…

> Where is this meme that RDBMSs do not scale coming from? They don't scale to Google or Facebook operational sizes. Once you get to a billion customers or so the ol' RDBMS tends to struggle. Because everyone wants to be Google they imagine they have Google's problems. I've been in a meeting where the client was talking about their severe scaling issues for their "big data" which could only possibly be resolved by st…

[deleted]

Re: Domain Logic and SQL (2003)

#33

Post by chrisjc »

mumblemumble wrote: post #21
jasonhansel wrote: post #18

One thing this leaves out is the danger of concurrency issues, which can sometimes be worsened by using an ORM. Writing correct, race-free SQL can be very hard (with READ COMMITTED transactions) or require complex retry logic (with SERIALIZABLE). Preventing deadlocks can also be a concern. ORMs tend to hide the underlying SQL operations, making it even harder to verify whether operations are concurrency safe.

I think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model. A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems…

Definitely an advantage on the other side of your traditional "CRUD" application... The data warehouse and BI stack.

Re: Domain Logic and SQL (2003)

#34

Post by [deleted] »

chrisjc wrote: post #33
mumblemumble wrote: post #21

Earlier quoted context omitted.

I think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model. A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems…

Definitely an advantage on the other side of your traditional "CRUD" application... The data warehouse and BI stack.

[deleted]

Re: Domain Logic and SQL (2003)

#35

Post by hmmmmm2 »

keanzu wrote: post #31
ak39 wrote: post #5

Earlier quoted context omitted.

Most (if not all) modern RDBMSs allow for reliable horizontal and vertical scaling. SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers (for horizontal scaling) in geographically distributed servers. PostgreSQL has this and it's absolutely free. Where is this meme that RDBMSs…

> Where is this meme that RDBMSs do not scale coming from? They don't scale to Google or Facebook operational sizes. Once you get to a billion customers or so the ol' RDBMS tends to struggle. Because everyone wants to be Google they imagine they have Google's problems. I've been in a meeting where the client was talking about their severe scaling issues for their "big data" which could only possibly be resolved by st…

> They don't scale to Google or Facebook operational sizes.

You may or may not know this, but the primary datastore used at both Google and FB is MySQL. Sure, they use replication and sharding, but I would strongly argue that MySQL with sharding scales better than some multi master NoSQL thing like Cassandra.

Related, you should check out https://github.com/vitessio/vitess if you haven't seen it. It's what Youtube and others use for their primary data store in production.

Re: Domain Logic and SQL (2003)

#36

Post by mhd »

ak39 wrote: post #5
nizmow wrote: post #2

One thing this article doesn't mention is that in many cases databases scale poorly, and only vertically. You can throw a bunch of domain logic in complex SQL queries and see significant performance benefits, but at some point you may find that your application has grown and your SQL server is running into IOPS and CPU limits that are difficult to fix. At that point your answer could be, pull logic out of those compl…

Most (if not all) modern RDBMSs allow for reliable horizontal and vertical scaling. SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers (for horizontal scaling) in geographically distributed servers. PostgreSQL has this and it's absolutely free. Where is this meme that RDBMSs…

> Where is this meme that RDBMSs do not scale coming from?

In my days it was more like "You can't afford RDBMSs at that scale". Reach a certain point and Larry gets a new yacht. Cheaper/open source offerings have moved that goal post by quite a bit.

Although it sometimes scares me how them poor databases get treated when performance is dropping. Little Jimmy JOIN is the first one to be put down, often way before there's a need for it.

Re: Domain Logic and SQL (2003)

#38

Post by jmnicolas »

tylerjwilk00 wrote: post #37

I wince every time I use application code to do what I know SQL can do faster and easier. ORMs are nice but it's often impossible to translate performant SQL into the ORM's API. SQL is just so concise.

We recently changed one of our supplier. The previous one allowed us a full read access to their database (400 tables) but the new one insists that we use their SOAP webservices.

So now when someone ask me for some data, I need to code, manually joining objects instead of crafting a quick SQL query and using the export to CSV function.

Dictionaries (C# key value collection) have fast become a staple diet since Linq is way too slow when you try to 'join' hundred of thousands of objects together.

It feels backward (and I'm saying this as a dev, I'm certainly no DBA or SQL expert).

Re: Domain Logic and SQL (2003)

#39

Post by jmnicolas »

BrentOzar wrote: post #13
ak39 wrote: post #5

Earlier quoted context omitted.

Most (if not all) modern RDBMSs allow for reliable horizontal and vertical scaling. SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers (for horizontal scaling) in geographically distributed servers. PostgreSQL has this and it's absolutely free. Where is this meme that RDBMSs…

> SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers Additional CPUs are $7,000 USD per core, and replication is labor intensive. Transactional replication has a nasty habit of breaking as the source tables are changed, and Availability Groups have a ton of bugs (as evidenced…

> PostgreSQL has this and it's absolutely free.

Literally the second sentence ...

Re: Domain Logic and SQL (2003)

#40

Post by stefano »

jmnicolas wrote: post #38
tylerjwilk00 wrote: post #37

I wince every time I use application code to do what I know SQL can do faster and easier. ORMs are nice but it's often impossible to translate performant SQL into the ORM's API. SQL is just so concise.

We recently changed one of our supplier. The previous one allowed us a full read access to their database (400 tables) but the new one insists that we use their SOAP webservices. So now when someone ask me for some data, I need to code, manually joining objects instead of crafting a quick SQL query and using the export to CSV function. Dictionaries (C# key value collection) have fast become a staple diet since Linq i…

If the data fits in RAM, you could try loading it into an in-memory sqlite database, and run SQL queries there.
Post reply on HN