Domain Logic and SQL (2003) • Hacker News Board

Re: Domain Logic and SQL (2003)

#11

Post by nickserv » Fri, Feb 21, 2020, 1:11 PM UTC

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…

It's certainly possible, but more complex than just putting up a bunch of stateless application servers all accessing the same database. With no local cache. Then saying the DB is slow ;-)

It's also the kind of thing many developers don't like doing: thinking about operational concerns.

As an aside, horizontal scaling of sorts can be achieved by using microservices, it's actually one of the few really valid reasons for this type of architecture. If the microservice databases are not independent, you're doing it wrong.

Re: Domain Logic and SQL (2003)

#12

Post by sgift » Fri, Feb 21, 2020, 1:15 PM UTC

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…

> (...) 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.

It's not unique to SQL/databases that at some point your user base may outgrow your current architecture and you may have to invest in something else.

It's dangerous to think you can skip this and just employ whatever architecture is "good" for bigger user bases from the start. There are different trade-offs in different systems and the trade-offs of most alternatives are not as well understood as the trade-offs of databases. I have seen more than one project implode when something which was simple with a database was far more complicated with the alternative.

Re: Domain Logic and SQL (2003)

#13

Post by BrentOzar » Fri, Feb 21, 2020, 1:19 PM UTC

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…

> 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 by any recent Cumulative Update.)

Saying that SQL Server scales is like saying your wallet scales to hold any amount of money. Sure, it might, but it’s up to you to put the coin in, and it’s gonna for a lot of coin - compared to scaling out app servers, who have generally near no license cost, and code is synchronized at deploy time.

As to recommending you a place to read, I hate to say this, but you could start with my blog. Pretty much every week, I’ve got real life examples turned into abstract tutorials on there from companies who hit scaling walls and had to hire me for help. (Past client examples: Stack Overflow, Google.)

Re: Domain Logic and SQL (2003)

#14

Post by fr1tkot » Fri, Feb 21, 2020, 1:20 PM UTC

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? (I see lots of people saying this as though it is a given. Has there been any published data on this for me to read?).

It mostly comes from people who don't know how and when to create an index.

Re: Domain Logic and SQL (2003)

#15

Post by golergka » Fri, Feb 21, 2020, 1:40 PM UTC

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…

What's the limit of vertical SQL scaling and what kind of applications (I'm talking not only about the functionality, but also realistic level of popularity) can end up hitting it, in your experience?

Re: Domain Logic and SQL (2003)

#16

Post by taffer » Fri, Feb 21, 2020, 1:41 PM UTC

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…

> Additional CPUs are $7,000 USD per core, and replication is labor intensive

Probably still cheaper than trying to impement scalable transactions in higher layers.

Re: Domain Logic and SQL (2003)

#17

Post by BrentOzar » Fri, Feb 21, 2020, 1:51 PM UTC

taffer wrote: post #16

BrentOzar wrote: post #13

Earlier quoted context omitted.

> 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…

> Additional CPUs are $7,000 USD per core, and replication is labor intensive Probably still cheaper than trying to impement scalable transactions in higher layers.

> Probably still cheaper than trying to impement scalable transactions in higher layers.

Transactions, yes, I totally agree. That's what databases are for: reliably storing and retrieving data. It's where you start doing domain logic that things get tougher, like (and I wish I was joking) calling cross-continent web services from the database and building HTML inside SQL.

Re: Domain Logic and SQL (2003)

#18

Post by jasonhansel » Fri, Feb 21, 2020, 2:22 PM UTC

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.

Re: Domain Logic and SQL (2003)

#19

Post by crazygringo » Fri, Feb 21, 2020, 2:25 PM UTC

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…

I don't think I've ever seen performance improvements from taking logic in a complex SQL query and re-implementing it at the application layer.

The bottleneck with databases is virtually never calculations on results (CPU), it's disk access and network latency and bandwidth.

And generally, if you do have crazy complex CPU-bound calculations you need to do on data (e.g. scientific stuff)... SQL doesn't provide the necessary functions/control anyways, so the database isn't even an option for that.

The only issue I can imagine you might have run into at some point is complex queries badly written (e.g. recursive subqueries without indices) that you could speed up with application logic -- but then the solution is to optimize the query.

Re: Domain Logic and SQL (2003)

#20

Post by mumblemumble » Fri, Feb 21, 2020, 2:28 PM UTC

smoyer wrote: post #9

tkyjonathan wrote: post #6

It is worth mentioning that there are some data-process heavy work that can be done much faster and more efficiently in SQL. Real life example for a regulatory batch job: 6 tomcat servers + 1 RDS. 30X lines of code + UTs in java. 30+ minutes time. In SQL No tomcat servers + 1 RDS instance. 1X lines of code(SQL) + UTs (in java). 3+ minutes Here is a good book on it: https://www.amazon.co.uk/Relational-Database-Program…

"But that's not going to be database agnostic!" Haha ... I'll admit I used to say that but rarely did an internal system (what I work on most) ever actually have that requirement. Systems that are sold to customers to run on their premises are another story but don't worry about using PostgreSQL-only (e.g.) features to speed up both your processing and development times. I have however seen the horrors of 600 line st…

There seem to be two ways that interacting with the database works out in practice: You can embrace the specific DBMS you have, get the most out of it, and end up tightly coupled to it. Or you can try to be database agnostic, increase your development costs and limit your performance and data expressivity in the service of that goal, and still end up tightly coupled to the DBMS.

My pet hypothesis is that "database agnostic" isn't really about being able to switch databases on a whim. It's more expressing a developer-centric anxiety. A dog whistle way of saying, "I don't want to have to RTFM of a new DBMS." Which I get, most DBMSes have awful manuals. Especially SQL databases. SQL dialects tend to mix syntax, semantics, and subtle implementation details all together into one big ball of mud and tangled hair. But this is one spot where I think it really is worthwhile to hold your nose and get through it.