4 ms·

> Shift logic to the application level to reduce database load. I’m not convinced by this. What type of logic are we talking about?

leetrout 3y ago
Some companies do large amounts of stored procedures for even routine tasks.
- JCharante 3y ago
  Ah I see. I thought it meant taking away some merge logic out of the database layer.
  - leetrout 3y ago
    Oh I think they mean that, too. I am just trying to be charitable by calling out times where it can be a lift to yank something out of the DB. This application side join stuff was real popular about 15 years ago with Mongo and similar stores that didnt support joins. It is one of the many things that separated RethinkDB from the document db crowd at that time.
wongarsu 3y ago
How to operate on a tiny database server: shift all work from the database server to the application servers. See also: webscale databases
northbymidwest 3y ago
This is a common pattern with NoSQL databases, but really, the author of this article should just spend more than $10 a month on their Postgres instance.
nijave 3y ago
The last place I worked had some JSON columns and doing lots of JSON operations tended to eat up all the Postgres CPU (the servers were generally 16-32 cores)
- ddorian43 3y ago
  Native columns are better/faster.
  - ahoka 3y ago
    Not everything fits into those.
    - sgarland 3y ago
      Not with that attitude.
- ttfkam 3y ago
  We had that too. Partial expression indexes mitigated most of the performance problems and CPU load. We then created consistently-named functions that made the appropriate JSON query. Then an expression index on the function. If the key was missing (NULL result), it wasn't added to the index, and we stripped all JSON nulls before storing. It wasn't necessarily pretty, but neither was the client's source data, which we had little to no control over. Best of a bad situation. Jsonb columns required more steps afforded us more flexibility and timeliness when the client kept asking for "one more feature" at the last minute.
winrid 3y ago
Easier to scale application CPU usage horizontally than a single instance DB.
- WJW 3y ago
  Even easier (and much less error prone when it comes to ACID properties) would be to scale the single instance DB vertically instead.
  - winrid 3y ago
    well, no. This is because there's a cap (albeit very high these days) scaling vertically - but the bigger advantage is you can also variably scale the application. It's a much bigger pain to scale up and down your DB instance with traffic. application pods can just be shut down etc. Also you can just wrap your queries in a session/transaction on the client, so no worries there. But yes, don't do this until you need to.
dkjaudyeqooe 3y ago
This makes no sense and is typically the opposite of what I'd build. The more business logic (various validation rules and processing) you have in the database (via stored procedures) the less problems you have, not least because all that processing becomes ACID. The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database. If you implement business logic in front end applications, you're asking for trouble. Many places I've worked didn't allow software outside the database to do anything more than call a stored procedure. Application servers did come into fashion at one point, with predictable results: subtle timing bugs, inconsistent processing with different results depending on which application server processed the data, a general difficulty in finding the source of bugs, security problems. Everyone would be a lot less stressed if they did everything in the database.
- count 3y ago
  Oracle, DB2 or SQLServer? >The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database. Ah, but by shifting the logic to the app, I can now query multiple databases (of disparate types even! vector, document, object, relational, etc.), each optimized for its particular data and query types and combine the result. That's very....not easy in stored procedures. I can also scale out different tiers of the application based on its needs (heavy compute, gpu-based, etc.). How do you do that when everything is a stored proc on a database?
  - dkjaudyeqooe 3y ago
    Well depends on the requirements, does it? I'm not suggesting you try to stick all your processing of all things inside a database, I'm saying put all critical processing of data stored in said database within it. Put all related data in the same database where is makes sense. Whatever data doesn't belong in that database can be merely referenced from it. > I can now query multiple databases This is another thing I disagree with. Wherever possible related data should be centralised to improve ACID properties and to create a reliable single "source of truth". I know this is unfashionable. with everyone claiming they need massive scale (which does cause some issues with this design), but much like microservices, it ends up being mostly busy work for programmers and related professions. Having said that, multiple databases, if largely orthogonal to each other present no real problems, and the same design concept is applied to each.
stiiv 3y ago
Here is a scenario that I've encountered a number of times. We needed to serve up some data for an API endpoint. Great! We'll write a SQL query for it. Just need a couple of joins and where-clause parameters -- it will be speedy, and easy to maintain. Before long, we are asked to make our query more robust, including a couple of optional parameters. Some null-coalescing in our join expressions and where-clauses is introduced. Performance immediately suffers. (I've seen the same kind of impact when introducing unions or function calls within such expressions -- the query plan can get very messy when you try to scale in this manner.) So what do we do? Restructuring our data (maybe with a materialized view or something) is one option. A reasonable alternative is to break up the single query into a few more specialized queries, and then use application logic to inspect parameters and choose the best query to use for each request. Plenty of trade-offs to consider in both cases.
- JCharante 3y ago
  I don't think there's any trade-offs in that situation. It doesn't make sense to have your queries handle optional parameters when you have that info already precent in the application layer and can choose the specialized query. I suppose if you were really rushed you could end up in that starting scenario.

leetrout 3y ago

Some companies do large amounts of stored procedures for even routine tasks.

JCharante 3y ago
Ah I see. I thought it meant taking away some merge logic out of the database layer.
- leetrout 3y ago
  Oh I think they mean that, too. I am just trying to be charitable by calling out times where it can be a lift to yank something out of the DB. This application side join stuff was real popular about 15 years ago with Mongo and similar stores that didnt support joins. It is one of the many things that separated RethinkDB from the document db crowd at that time.

wongarsu 3y ago

How to operate on a tiny database server: shift all work from the database server to the application servers. See also: webscale databases

northbymidwest 3y ago

This is a common pattern with NoSQL databases, but really, the author of this article should just spend more than $10 a month on their Postgres instance.

nijave 3y ago

The last place I worked had some JSON columns and doing lots of JSON operations tended to eat up all the Postgres CPU (the servers were generally 16-32 cores)

ddorian43 3y ago
Native columns are better/faster.
- ahoka 3y ago
  Not everything fits into those.
  - sgarland 3y ago
    Not with that attitude.
ttfkam 3y ago
We had that too. Partial expression indexes mitigated most of the performance problems and CPU load. We then created consistently-named functions that made the appropriate JSON query. Then an expression index on the function. If the key was missing (NULL result), it wasn't added to the index, and we stripped all JSON nulls before storing. It wasn't necessarily pretty, but neither was the client's source data, which we had little to no control over. Best of a bad situation. Jsonb columns required more steps afforded us more flexibility and timeliness when the client kept asking for "one more feature" at the last minute.

winrid 3y ago

Easier to scale application CPU usage horizontally than a single instance DB.

WJW 3y ago
Even easier (and much less error prone when it comes to ACID properties) would be to scale the single instance DB vertically instead.
- winrid 3y ago
  well, no. This is because there's a cap (albeit very high these days) scaling vertically - but the bigger advantage is you can also variably scale the application. It's a much bigger pain to scale up and down your DB instance with traffic. application pods can just be shut down etc. Also you can just wrap your queries in a session/transaction on the client, so no worries there. But yes, don't do this until you need to.

dkjaudyeqooe 3y ago

This makes no sense and is typically the opposite of what I'd build. The more business logic (various validation rules and processing) you have in the database (via stored procedures) the less problems you have, not least because all that processing becomes ACID. The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database. If you implement business logic in front end applications, you're asking for trouble. Many places I've worked didn't allow software outside the database to do anything more than call a stored procedure. Application servers did come into fashion at one point, with predictable results: subtle timing bugs, inconsistent processing with different results depending on which application server processed the data, a general difficulty in finding the source of bugs, security problems. Everyone would be a lot less stressed if they did everything in the database.

count 3y ago
Oracle, DB2 or SQLServer? >The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database. Ah, but by shifting the logic to the app, I can now query multiple databases (of disparate types even! vector, document, object, relational, etc.), each optimized for its particular data and query types and combine the result. That's very....not easy in stored procedures. I can also scale out different tiers of the application based on its needs (heavy compute, gpu-based, etc.). How do you do that when everything is a stored proc on a database?
- dkjaudyeqooe 3y ago
  Well depends on the requirements, does it? I'm not suggesting you try to stick all your processing of all things inside a database, I'm saying put all critical processing of data stored in said database within it. Put all related data in the same database where is makes sense. Whatever data doesn't belong in that database can be merely referenced from it. > I can now query multiple databases This is another thing I disagree with. Wherever possible related data should be centralised to improve ACID properties and to create a reliable single "source of truth". I know this is unfashionable. with everyone claiming they need massive scale (which does cause some issues with this design), but much like microservices, it ends up being mostly busy work for programmers and related professions. Having said that, multiple databases, if largely orthogonal to each other present no real problems, and the same design concept is applied to each.

stiiv 3y ago

Here is a scenario that I've encountered a number of times. We needed to serve up some data for an API endpoint. Great! We'll write a SQL query for it. Just need a couple of joins and where-clause parameters -- it will be speedy, and easy to maintain. Before long, we are asked to make our query more robust, including a couple of optional parameters. Some null-coalescing in our join expressions and where-clauses is introduced. Performance immediately suffers. (I've seen the same kind of impact when introducing unions or function calls within such expressions -- the query plan can get very messy when you try to scale in this manner.) So what do we do? Restructuring our data (maybe with a materialized view or something) is one option. A reasonable alternative is to break up the single query into a few more specialized queries, and then use application logic to inspect parameters and choose the best query to use for each request. Plenty of trade-offs to consider in both cases.

JCharante 3y ago
I don't think there's any trade-offs in that situation. It doesn't make sense to have your queries handle optional parameters when you have that info already precent in the application layer and can choose the specialized query. I suppose if you were really rushed you could end up in that starting scenario.