5 ms·

> When querying the database, query the database. It’s almost always more efficient to get the database to do the work than to do it yourself. For instance, if

by bambax 1y ago

> When querying the database, query the database. It’s almost always more efficient to get the database to do the work than to do it yourself. For instance, if you need data from multiple tables, JOIN them instead of making separate queries and stitching them together in-memory. Oh yes! Never do a join in the application code! But also: use views! (and stored procedures if you can). A view is an abstraction about the underlying data, it's functional by nature, unlikely to break for random reasons in the future, and if done well the underlying SQL code is surprisingly readable and easy to reason about.

DanielHB 1y ago
Microservice achitecture promotes splitting data cross multiple databases making it impossible to do proper DB JOINs from application code. Then companies buy a solution to aggregate all the different databases in a single "data-lake" (or whatever buzzword is hot right now) so you can do OLAP queries. Without consistency guarantees of course. And I am not saying this is never the _right_ solution, but it should almost never be the _first_ solution
tossandthrow 1y ago
Views make good sense when you can check them in - and DB migrations are a poor way of doing it due to their immutable nature. Depending on the ecosystem the code base adopts a good orm might be a better choice to do joins.
CafeRacer 1y ago
I came here to say an exactly opposite things. There were a few instances where a relatively heavy join would not perform well, no matter what I tried. And it was faster to load/stitch data together with goroutines. So I just opted to doing it that way. Also SQL is easy, but figuring out what's up with indexes and planner is not.
deleted 1y ago
[deleted]
quietbritishjim 1y ago
I think it's ok to have this rule as a first approximation, but like all design rules you should understand it well enough to know when to break it. I worked on an application which joined across lots of tables, which made a few dozen records balloon to many thousands of result rows, with huge redundancy in the results. Think of something like a single conceptual result having details A, B, C from one table, X, Y from another table, and 1, 2, 3 from another table. Instead of having 8 result rows (or 9 if you include the top level one from the main table) you have 18 (AX1, AX2, AX3, AY1, ...). It gets exponentially worse with more tables. We moved to separate queries for the different tables. Importantly, we were able to filter them all on the same condition, so we were not making multiple queries to child tables when there were lots of top-level results. The result was much faster because the extra network overhead was overshadowed by the saving in query processing and quantity of data returned. And the application code was actually simpler, because it was a pain to pick out unique child results from the big JOIN. It was literally a win in every respect with no downsides. (Later, we just stuffed all the data into a single JSONB in a single table, which was even better. But even that is an example of breaking the old normalisation rule.)
- 9rx 1y ago
  > which made a few dozen records balloon to many thousands of result rows That doesn't really sound like a place where data is actually conceptually joined. I expect, as it is something commonly attempted, that you were abusing joins to try and work around the n+1 problem. As a corollary to the above, you also shouldn't de-join in application code.
  - kccqzy 1y ago
    It's a join. A join without any ON or USING clause or any filtering is a Cartesian product which is what's happening here.
- wongarsu 1y ago
  That reminds me of many cases of adhering to database normalisation rules even in views and queries, even in a case where you should break it. Aggregation functions like postgres's array_agg and jsonb_agg are incredibly powerful at preventing the number of rows from ballooning in situations like those
bob1029 1y ago
This is a big part of what makes ORMs a problem. Writing raw SQL views/queries per MVC view in SSR arrangements is one of the most elegant and performant ways to build complex web products. Let the RDBMS do the heavy lifting with the data. There are optimizations in play you can't even recall (because there's so many) if you're using something old and enterprisey like MSSQL or Oracle. The web server should be able to directly interpolate sql result sets into corresponding <table>s, etc. without having to round trip for each row or perform additional in memory join operations. The typical ORM implementation is the exact opposite of this - one strict object model that must be used everywhere. It's about as inflexible as you can get.
- tossandthrow 1y ago
  Have you ever build a complex app like this? In particular, have you have to do testing, security (eg. row level security), manage migrations, change management (eg. for SOC2 or other security frameworks), cache offloads (Redis, and friends), support for microservices, etc. Comments like this give me a vibe of young developers trying out Supabase for the first time feeling like that approach can scale indefinitely.
  - rbees 1y ago
    > Comments like this give me a vibe of young developers I don’t think so. The context is about avoiding joining in memory, which is fairly awful to do in a application, and should be avoided, along with uninformed use of ORMs, which often just add a layer of unwarranted complexity leading to things like the dreaded N+1 problem that most inexperienced Rails developers had when dealing with ActiveRecord. If anything, what you’re talking about sounds like development hell. I can understand a database developer having to bake in support for that level of security, but developing an app that actually uses it gets you so far in the weeds that you can barely make progress trying to do normal development. A developer with several years of experience or equivalent will have pride in developing complexity and using cool features that make them feel important. After a developer has maybe twice that many years experience or equivalent, they may develop frameworks with the intent to make code easier to develop and manage. And beyond that level of experience, developers just want code that’s easy to maintain and doesn’t make stupid decisions like excessive complexity. But, they know they have to let the younger devs make mistakes, because they don’t listen, so there is no choice but to watch hell burn. Then you retire or get a different job.
nurettin 1y ago
for me, it is orm -> schema bound views -> views -> table functions -> stored procedure as a last resort (hopefully it doesn't come to that)
hk1337 1y ago
You should be careful with how much you lean into “doing it in the database” as well with how you implement it. Lest, you get the situation where your application inserts as one value and it gets saved completely different.
- mattmanser 1y ago
  I'm not sure if this is what you mean, but I think a big thing missing from the article is how you should isolate you business logic. A great software design will separate all business logic into its own layer. That might be a distinct project, module, or namespace, depending on what your language supports. Keep business logic out of SQL and out of web server code (controllers, web helpers, middleware, etc.). Then you're treating SQL as the data store it is designed to be. When you embed application logic in SQL, you're hiding core functionality in a place where most developers won't expect to find it. This approach also creates tight coupling between your application and your database provider, making it hard to switch as needs change/the application grows.
  - dondraper36 1y ago
    That also depends on what you would consider "business logic in the database". What would you say about CHECK constraints, though? I don't think it's something few developers expect to see, and having these checks is very convenient. I know that there are even opponents of foreign keys (which makes sense sometimes), but in general, I don't understand why I would ever throw away the nice features of Postgres that can enforce correctness.
  - hk1337 1y ago
    Pretty much yeah. I think you need to stay somewhat vigilant because it can creep in under the guise of validating data. Triggers is something I was thinking about that is grossly misused.
    - porridgeraisin 1y ago
      I like using triggers for validation important for maintaining data integrity. Example: let's say you have a parent table P whose rows are soft deleted (update P set _deleted=1 where id=?) and a child table C (foreign key on P.id) On delete cascade isn't gonna work here obviously. So I use a trigger to emulate on delete cascade. Also, I've seen triggers to cancel transactions raise abort(..) if they violate some property that can't be retrofitted into a proper db constraint due to team politics at a previous workplace.
mdavid626 1y ago
I disagree. In modern highly scalable architectures I’d prefer doing joins in the layer front of the database (backend). The “backend” scales much easier than the database. Loading data by simple indexes, eg. user_id, and joining it on the backend, keeps the db fast. Spinning up another backend instance is easy - unlike db instance. If you think, your joins must happen in db, because data too big to be loaded to memory on backend, restructure it, so it’s possible. Bonus points for moving joins to the frontend. This makes data highly cacheable - fast to load, as you need to load less data and frees up resources on server side.
- AdrianB1 1y ago
  My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.
  - mdavid626 1y ago
    In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.
    - AdrianB1 1y ago
      The SQL database has more than a dozen semi-independent applications that treat different aspects of the manufacturing process, for example from recipes and batches to maintenance, scrap management and raw material inventory. The data is interlocked, the apps are independent as different people in very different roles are using it. No, it never starts in the front end, it started as a system and evolved by adding more data and more apps. Think SAP as another such example.
      - mdavid626 1y ago
torginus 1y ago
Are you sure about this? Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields. Let's say you have 10k customers, and 1m orders. A query performing a full join on this and getting all the data would result in 25 million fields transmitted, while 2 separate queries and a client side manual join would be just 5m for orders, and 200k for customers.
- dondraper36 1y ago
  What I particularly like about the comments in this thread is how it proves that everything is a trade-off :)
- digitalPhonix 1y ago
  What sort of application is regularly doing a query for “all data”?
  - aembleton 1y ago
    Client report generation.
    - tremon 1y ago
      1) As soon as reporting requirements get serious, you build a data warehouse. Because odds are, the client will want to combine data from multiple systems in their reports anyway. If not today, then they will tomorrow. 2) such reports never need all the data, it's mostly about top N volume queries or month-over-month performance data. When a reporting application does query all the data, it's because it's building its own data warehouse so the query usually happens only once per day, at a specific time, which means the load is entirely predictable.
    - digitalPhonix 1y ago
      Even then, DB side aggregation and joins would be beneficial. I’m asking specifically about “all data” unfiltered, un-aggregated, un-joined as the parent’s analysis was on.
tialaramex 1y ago
Stored procedures seem like a win but the big problem is that while I could write the rest of the software in a very nice modern language like Rust, or more practically in C# since my team all know C# if I write a stored procedure it will be in Transact-SQL because that's the only choice. T-SQL was not a good programming language last century when it was vaguely current, and so no I do not want to write any significant amount of code in T-SQL. For my sins I maintain a piece of software with huge T-SQL procedures (multi-page elaborations by somebody who really, really like this stuff) and they're a nightmare. The tooling doesn't really believe in version control, the diagnostics when you make a mistake are either non-existent or C++ style useless spew. We hire a lot of very junior developers. People who still need to be told not to comment out code in release, that variable numbers are for humans to read not machines, that sort of thing. We're not quite hiring physicists to write software (I have done that at a startup) but it's close. However, none of the poor "My first program" code I see in a merge request by a new hire is anywhere close to as unreadable as the T-SQL we already own and maintain.
- Yokohiii 1y ago
  I've only once tried to use stored procedures in mysql and it was almost impossible to debug back then. Very painful. Average devs already have issues being smart with their databases and stored procedures would add to that. Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone. So you have to add extra layers of complexity to manage versioning. I can see the advantage of extreme performance/efficiency gains, but it should be really big to be justified.
  - loglog 1y ago
    > Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone. This one is easily solved: never change a stored procedure. Every version should get a new name.
    - Yokohiii 1y ago
      That's what I meant when I've mentioned versioning.
luckylion 1y ago
I feel like the biggest question to ask is: how expensive is it exactly, how often do you need to do it, and how important is the speed of it? If you have some complex queries on every page load with a huge number of users, put it in the DB as much as possible. If you need to iterate over a bunch of records and do something based on some combination of values, and it's for a weekly reporting thing, I'd much rather see 3 nested foreach loops with a lot of early exits to skip the things you don't care about than a multi-kb SQL-statement that took two days to develop and nobody every dares to touch again because it's hard to handle.
scarface_74 1y ago
I have a very strict rule about any system I design or that I’m over against using stored procedures. When I use to interview to be a developer at a company, it was always an automatic no for me if a company kept business logic in stored procedures and had a separate team of “database developers”. As far as not doing joins in code, while I agree for the most part. GitHub itself has a rule against joining tables using sql that belong to different domains. https://github.blog/engineering/infrastructure/partitioning-githubs-relational-databases-scale/ https://github.blog/engineering/infrastructure/partitioning-...
victorbjorklund 1y ago
Not sure I agree. First of all it can be more performant. Say you fetch 1000 records. And we need to join on a table where these 1000 records just got 2 different foreign keys. Instead of joing in db and fetching a lot more data we can do two queries and join in app instead. Secondly, makes it easier to cache data. Lets say the thing we joing with almost never changes (like some country info) we can cache that and just join it with the data from the db. Not saying this should always be the case, but sometimes it is the right call.
- teraflop 1y ago
  But as a counterpoint to that, (a) the database has its own caching built in, which you don't have to implement, and (b) the database knows when to invalidate its cache. To quote Douglas Adams: "The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair." Likewise, if you cache a piece of data in your application because you assume that it won't change, that just makes it likely that if and when it does change, you'll have bugs. Moving the cache to the database layer so that it can be properly invalidated fixes this. It's true that an application-side join can still be more performant if the DB cache isn't good enough, but IMO you should only take that step after actually profiling your queries.
Ozzie_osman 1y ago
There are definitely examples of when you want to do joins in the application. For example, you may want to (or have the option to) vertically partition your database, or use different data stores. The app layer is usually stateless and can scale perpetually, but the database might be a bottleneck. Joining in the database over the application is a great default. But I wouldn't say "never join in the application code".
bencornia 1y ago
Views are great. Stored procedures are cursed.
- 0xDEAFBEAD 1y ago
  >Stored procedures are cursed. Elaborate?
  - bencornia 1y ago
    Stored procedures make sense if you are trying to make your backend less chatty with the database. The downsides are that they aren't version controlled. PLSQL and its variants can be inscrutable and difficult to debug. I guess you just have to decide what tradeoffs you are willing to make in your application. For me stored procedures take too much discipline for any potential upside. Sure you might have a wizard on your team that has it all in their head. But once they are gone it becomes a tar pit.
    - 0xDEAFBEAD 1y ago
      Thanks!

by bambax 1y ago

DanielHB 1y ago

Microservice achitecture promotes splitting data cross multiple databases making it impossible to do proper DB JOINs from application code. Then companies buy a solution to aggregate all the different databases in a single "data-lake" (or whatever buzzword is hot right now) so you can do OLAP queries. Without consistency guarantees of course. And I am not saying this is never the _right_ solution, but it should almost never be the _first_ solution

tossandthrow 1y ago

Views make good sense when you can check them in - and DB migrations are a poor way of doing it due to their immutable nature. Depending on the ecosystem the code base adopts a good orm might be a better choice to do joins.

CafeRacer 1y ago

I came here to say an exactly opposite things. There were a few instances where a relatively heavy join would not perform well, no matter what I tried. And it was faster to load/stitch data together with goroutines. So I just opted to doing it that way. Also SQL is easy, but figuring out what's up with indexes and planner is not.

deleted 1y ago

[deleted]

quietbritishjim 1y ago

I think it's ok to have this rule as a first approximation, but like all design rules you should understand it well enough to know when to break it. I worked on an application which joined across lots of tables, which made a few dozen records balloon to many thousands of result rows, with huge redundancy in the results. Think of something like a single conceptual result having details A, B, C from one table, X, Y from another table, and 1, 2, 3 from another table. Instead of having 8 result rows (or 9 if you include the top level one from the main table) you have 18 (AX1, AX2, AX3, AY1, ...). It gets exponentially worse with more tables. We moved to separate queries for the different tables. Importantly, we were able to filter them all on the same condition, so we were not making multiple queries to child tables when there were lots of top-level results. The result was much faster because the extra network overhead was overshadowed by the saving in query processing and quantity of data returned. And the application code was actually simpler, because it was a pain to pick out unique child results from the big JOIN. It was literally a win in every respect with no downsides. (Later, we just stuffed all the data into a single JSONB in a single table, which was even better. But even that is an example of breaking the old normalisation rule.)

9rx 1y ago
> which made a few dozen records balloon to many thousands of result rows That doesn't really sound like a place where data is actually conceptually joined. I expect, as it is something commonly attempted, that you were abusing joins to try and work around the n+1 problem. As a corollary to the above, you also shouldn't de-join in application code.
- kccqzy 1y ago
  It's a join. A join without any ON or USING clause or any filtering is a Cartesian product which is what's happening here.
wongarsu 1y ago
That reminds me of many cases of adhering to database normalisation rules even in views and queries, even in a case where you should break it. Aggregation functions like postgres's array_agg and jsonb_agg are incredibly powerful at preventing the number of rows from ballooning in situations like those

bob1029 1y ago

This is a big part of what makes ORMs a problem. Writing raw SQL views/queries per MVC view in SSR arrangements is one of the most elegant and performant ways to build complex web products. Let the RDBMS do the heavy lifting with the data. There are optimizations in play you can't even recall (because there's so many) if you're using something old and enterprisey like MSSQL or Oracle. The web server should be able to directly interpolate sql result sets into corresponding <table>s, etc. without having to round trip for each row or perform additional in memory join operations. The typical ORM implementation is the exact opposite of this - one strict object model that must be used everywhere. It's about as inflexible as you can get.

tossandthrow 1y ago
Have you ever build a complex app like this? In particular, have you have to do testing, security (eg. row level security), manage migrations, change management (eg. for SOC2 or other security frameworks), cache offloads (Redis, and friends), support for microservices, etc. Comments like this give me a vibe of young developers trying out Supabase for the first time feeling like that approach can scale indefinitely.
- rbees 1y ago
  > Comments like this give me a vibe of young developers I don’t think so. The context is about avoiding joining in memory, which is fairly awful to do in a application, and should be avoided, along with uninformed use of ORMs, which often just add a layer of unwarranted complexity leading to things like the dreaded N+1 problem that most inexperienced Rails developers had when dealing with ActiveRecord. If anything, what you’re talking about sounds like development hell. I can understand a database developer having to bake in support for that level of security, but developing an app that actually uses it gets you so far in the weeds that you can barely make progress trying to do normal development. A developer with several years of experience or equivalent will have pride in developing complexity and using cool features that make them feel important. After a developer has maybe twice that many years experience or equivalent, they may develop frameworks with the intent to make code easier to develop and manage. And beyond that level of experience, developers just want code that’s easy to maintain and doesn’t make stupid decisions like excessive complexity. But, they know they have to let the younger devs make mistakes, because they don’t listen, so there is no choice but to watch hell burn. Then you retire or get a different job.

nurettin 1y ago

for me, it is orm -> schema bound views -> views -> table functions -> stored procedure as a last resort (hopefully it doesn't come to that)

hk1337 1y ago

You should be careful with how much you lean into “doing it in the database” as well with how you implement it. Lest, you get the situation where your application inserts as one value and it gets saved completely different.

mattmanser 1y ago
I'm not sure if this is what you mean, but I think a big thing missing from the article is how you should isolate you business logic. A great software design will separate all business logic into its own layer. That might be a distinct project, module, or namespace, depending on what your language supports. Keep business logic out of SQL and out of web server code (controllers, web helpers, middleware, etc.). Then you're treating SQL as the data store it is designed to be. When you embed application logic in SQL, you're hiding core functionality in a place where most developers won't expect to find it. This approach also creates tight coupling between your application and your database provider, making it hard to switch as needs change/the application grows.
- dondraper36 1y ago
  That also depends on what you would consider "business logic in the database". What would you say about CHECK constraints, though? I don't think it's something few developers expect to see, and having these checks is very convenient. I know that there are even opponents of foreign keys (which makes sense sometimes), but in general, I don't understand why I would ever throw away the nice features of Postgres that can enforce correctness.
- hk1337 1y ago
  Pretty much yeah. I think you need to stay somewhat vigilant because it can creep in under the guise of validating data. Triggers is something I was thinking about that is grossly misused.
  - porridgeraisin 1y ago
    I like using triggers for validation important for maintaining data integrity. Example: let's say you have a parent table P whose rows are soft deleted (update P set _deleted=1 where id=?) and a child table C (foreign key on P.id) On delete cascade isn't gonna work here obviously. So I use a trigger to emulate on delete cascade. Also, I've seen triggers to cancel transactions raise abort(..) if they violate some property that can't be retrofitted into a proper db constraint due to team politics at a previous workplace.

mdavid626 1y ago

I disagree. In modern highly scalable architectures I’d prefer doing joins in the layer front of the database (backend). The “backend” scales much easier than the database. Loading data by simple indexes, eg. user_id, and joining it on the backend, keeps the db fast. Spinning up another backend instance is easy - unlike db instance. If you think, your joins must happen in db, because data too big to be loaded to memory on backend, restructure it, so it’s possible. Bonus points for moving joins to the frontend. This makes data highly cacheable - fast to load, as you need to load less data and frees up resources on server side.

AdrianB1 1y ago
My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.
- mdavid626 1y ago
  In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.
  - AdrianB1 1y ago
    The SQL database has more than a dozen semi-independent applications that treat different aspects of the manufacturing process, for example from recipes and batches to maintenance, scrap management and raw material inventory. The data is interlocked, the apps are independent as different people in very different roles are using it. No, it never starts in the front end, it started as a system and evolved by adding more data and more apps. Think SAP as another such example.
    - mdavid626 1y ago

torginus 1y ago

Are you sure about this? Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields. Let's say you have 10k customers, and 1m orders. A query performing a full join on this and getting all the data would result in 25 million fields transmitted, while 2 separate queries and a client side manual join would be just 5m for orders, and 200k for customers.

dondraper36 1y ago
What I particularly like about the comments in this thread is how it proves that everything is a trade-off :)
digitalPhonix 1y ago
What sort of application is regularly doing a query for “all data”?
- aembleton 1y ago
  Client report generation.
  - tremon 1y ago
    1) As soon as reporting requirements get serious, you build a data warehouse. Because odds are, the client will want to combine data from multiple systems in their reports anyway. If not today, then they will tomorrow. 2) such reports never need all the data, it's mostly about top N volume queries or month-over-month performance data. When a reporting application does query all the data, it's because it's building its own data warehouse so the query usually happens only once per day, at a specific time, which means the load is entirely predictable.
  - digitalPhonix 1y ago
    Even then, DB side aggregation and joins would be beneficial. I’m asking specifically about “all data” unfiltered, un-aggregated, un-joined as the parent’s analysis was on.

tialaramex 1y ago

Stored procedures seem like a win but the big problem is that while I could write the rest of the software in a very nice modern language like Rust, or more practically in C# since my team all know C# if I write a stored procedure it will be in Transact-SQL because that's the only choice. T-SQL was not a good programming language last century when it was vaguely current, and so no I do not want to write any significant amount of code in T-SQL. For my sins I maintain a piece of software with huge T-SQL procedures (multi-page elaborations by somebody who really, really like this stuff) and they're a nightmare. The tooling doesn't really believe in version control, the diagnostics when you make a mistake are either non-existent or C++ style useless spew. We hire a lot of very junior developers. People who still need to be told not to comment out code in release, that variable numbers are for humans to read not machines, that sort of thing. We're not quite hiring physicists to write software (I have done that at a startup) but it's close. However, none of the poor "My first program" code I see in a merge request by a new hire is anywhere close to as unreadable as the T-SQL we already own and maintain.

Yokohiii 1y ago
I've only once tried to use stored procedures in mysql and it was almost impossible to debug back then. Very painful. Average devs already have issues being smart with their databases and stored procedures would add to that. Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone. So you have to add extra layers of complexity to manage versioning. I can see the advantage of extreme performance/efficiency gains, but it should be really big to be justified.
- loglog 1y ago
  > Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone. This one is easily solved: never change a stored procedure. Every version should get a new name.
  - Yokohiii 1y ago
    That's what I meant when I've mentioned versioning.

luckylion 1y ago

I feel like the biggest question to ask is: how expensive is it exactly, how often do you need to do it, and how important is the speed of it? If you have some complex queries on every page load with a huge number of users, put it in the DB as much as possible. If you need to iterate over a bunch of records and do something based on some combination of values, and it's for a weekly reporting thing, I'd much rather see 3 nested foreach loops with a lot of early exits to skip the things you don't care about than a multi-kb SQL-statement that took two days to develop and nobody every dares to touch again because it's hard to handle.

scarface_74 1y ago

I have a very strict rule about any system I design or that I’m over against using stored procedures. When I use to interview to be a developer at a company, it was always an automatic no for me if a company kept business logic in stored procedures and had a separate team of “database developers”. As far as not doing joins in code, while I agree for the most part. GitHub itself has a rule against joining tables using sql that belong to different domains. https://github.blog/engineering/infrastructure/partitioning-githubs-relational-databases-scale/ https://github.blog/engineering/infrastructure/partitioning-...

victorbjorklund 1y ago

Not sure I agree. First of all it can be more performant. Say you fetch 1000 records. And we need to join on a table where these 1000 records just got 2 different foreign keys. Instead of joing in db and fetching a lot more data we can do two queries and join in app instead. Secondly, makes it easier to cache data. Lets say the thing we joing with almost never changes (like some country info) we can cache that and just join it with the data from the db. Not saying this should always be the case, but sometimes it is the right call.

teraflop 1y ago
But as a counterpoint to that, (a) the database has its own caching built in, which you don't have to implement, and (b) the database knows when to invalidate its cache. To quote Douglas Adams: "The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair." Likewise, if you cache a piece of data in your application because you assume that it won't change, that just makes it likely that if and when it does change, you'll have bugs. Moving the cache to the database layer so that it can be properly invalidated fixes this. It's true that an application-side join can still be more performant if the DB cache isn't good enough, but IMO you should only take that step after actually profiling your queries.

Ozzie_osman 1y ago

There are definitely examples of when you want to do joins in the application. For example, you may want to (or have the option to) vertically partition your database, or use different data stores. The app layer is usually stateless and can scale perpetually, but the database might be a bottleneck. Joining in the database over the application is a great default. But I wouldn't say "never join in the application code".

bencornia 1y ago

Views are great. Stored procedures are cursed.

0xDEAFBEAD 1y ago
>Stored procedures are cursed. Elaborate?
- bencornia 1y ago
  Stored procedures make sense if you are trying to make your backend less chatty with the database. The downsides are that they aren't version controlled. PLSQL and its variants can be inscrutable and difficult to debug. I guess you just have to decide what tradeoffs you are willing to make in your application. For me stored procedures take too much discipline for any potential upside. Sure you might have a wizard on your team that has it all in their head. But once they are gone it becomes a tar pit.
  - 0xDEAFBEAD 1y ago
    Thanks!