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

41–50 of 61 posts

Re: Domain Logic and SQL (2003)

#41

Post by bdcravens »

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?

MongoDB marketing?

Re: Domain Logic and SQL (2003)

#42

Post by jmnicolas »

stefano wrote: post #40
jmnicolas wrote: post #38

Earlier quoted context omitted.

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.

I thought about making a "shadow database" on a server since my work PC is only 8 GB ram but ultimately decided against it.

The first reason was GDPR compliance and the second is that we have an already complex architecture as it is (and we're only 2 to manage it) so I don't want to add one more brittle layer that will need debugging at the worst time.

Re: Domain Logic and SQL (2003)

#43

Post by bdcravens »

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…

Another alternative is that based on your workload, using an RDBMS that is column-based.

Re: Domain Logic and SQL (2003)

#44

Post by macdice »

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.

Retry logic should not be complex! Model transactions as functions, and call them again with the same inputs.

Re: Domain Logic and SQL (2003)

#45

Post by pocket_cheese »

slim wrote: post #28
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…

isn't soft delete an update?

One way I get around this is having an append only version of the main table, and then a "most current record" version of the table. My append only table will be a running record of all the data I've loaded unmodified. Then I run a create table as select statement against my append only table to generate my new version of the final table. So in essence, I'm "deleting" the old version of the record by omitting it when I create my new version of the final table.

Re: Domain Logic and SQL (2003)

#46

Post by paulddraper »

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.

> complex retry logic

READ COMMITTED and REPEATABLE READ benefit from retry logic as well, not just SERIALIZABLE.

For a long time, I sought to write deadlock free code.

But that is very hard.

For example in PostgreSQL every UPDATE must be ordered, every DELETE must be ordered. [1]

Finally, I did myself a favor and create application-level retires.

This is 100% cool so long as (1) your deadlocks aren't so frequent so as to reach a performance problem and (2) the action is "replayable" (e.g. no read-once streams). Fortunately, these are both frequently true.

[1] https://dba.stackexchange.com/questions/257587/is-select-for...

Re: Domain Logic and SQL (2003)

#47

Post by paulddraper »

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.

Indeed.

Doing data retrieval and manipulation with application code instead of SQL is like doing web layout with JS instead of CSS or doing graphics with canvas instead of HTML/SVG.

Sure it's more flexible, but it's also far less simple, readable, and in many circumstances less performant.

"Rule of least power" is a good principle. [1]

[1] https://en.wikipedia.org/wiki/Rule_of_least_power

Re: Domain Logic and SQL (2003)

#48

Post by paulddraper »

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…

Oh, this happens so much and it hurts so bad.

Being able to join data in SQL or GraphSQL is a 10x time saver.

As the sibling commented, I often create a shadow database though of course isn't perfect.

Re: Domain Logic and SQL (2003)

#49

Post by keanzu »

hmmmmm2 wrote: post #35
keanzu wrote: post #31

Earlier quoted context omitted.

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

> You may or may not know this

I didn't, but they do, surprising!

https://www.facebook.com/notes/facebook-engineering/tao-the-...

Re: Domain Logic and SQL (2003)

#50

Post by wwweston »

jmnicolas wrote: post #42
stefano wrote: post #40

Earlier quoted context omitted.

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

I thought about making a "shadow database" on a server since my work PC is only 8 GB ram but ultimately decided against it. The first reason was GDPR compliance and the second is that we have an already complex architecture as it is (and we're only 2 to manage it) so I don't want to add one more brittle layer that will need debugging at the worst time.

About GDPR: is it possible that holding data in a non-storage/volatile medium could be legally distinct from non-volatile storage, especially if it's essentially syncing with an authoriative data source that's responsible for managing GDPR? Because if not, it seems to me every proxy and persistence layer runs legal risks....
Post reply on HN