4 ms·

Could you say more about some of these times? I'm curious what it means in more concrete terms. I only say this because I am a UI engineer who has been in some

Could you say more about some of these times? I'm curious what it means in more concrete terms. I only say this because I am a UI engineer who has been in some scrappy situations, so obviously I don't get to change the data model. What kind of structures do you find that "hit the database too hard" or otherwise invoke performance penalties?

freehunter 6y ago
When I say "hit the database too hard" what I actually mean is making too many calls to the database. If I have to make three or four calls just to get all the data I need, performance is going to take a hit. And databases are often the worst-performing part of the tech stack, so you have to craft your queries around the performance bottleneck of the database. So you have to balance your table structure around getting the data you need in as few calls as possible while also only returning exactly as much data as you need and no more. Because pulling extra data out puts more load on the database and shipping more data across the network is slower. In general, relational databases (RBDs) like Postgres or MySQL are great for making sure your data follows the correct structure (this field is a string, this is an integer, this field in table1 has to be the same as this field in table2, etc) but make it hard to make changes to the data structure later. What happens if that int should actually be a float? Now you have to write a migration that makes fundamental changes to the structure of the data and hope there are no negative side effects. Someone else mentioned NoSQL databases, which offer a lot more flexibility at the cost of the data integrity that RDBs enforce. If you suddenly want to store a float instead of an int, go ahead. No one is stopping you, you just need to make sure your code is updated to handle the possibility of getting back an int or float (or coerce the value to the right type and pray). Basically a database is integral to almost all applications but they're complex monsters with their own structure and rules and performance implications. If you're building an application you really have to know the data structure of the final product before you even _start_ configuring the database.
- balfirevic 6y ago
  > but make it hard to make changes to the data structure later. What happens if that int should actually be a float? Now you have to write a migration that makes fundamental changes to the structure of the data and hope there are no negative side effects. This is often stated, but it's not true. RDBMS make it easier to make changes to the data structure precisely because you only have to write migration and after that you are guaranteed to only ever get the data out of database in the new format. Which means your application code doesn't accumulate the cruft of dealing with two possible database data formats.
  - freehunter 6y ago
    I strongly disagree that this pattern makes it easier to make changes. It makes it easier to use the data (because you know this data will always be this type), but changing the data is harder than if it did not enforce a type. I'm not arguing that un-typed data in a DB is easy to use or a good idea, just that it's harder to change the structure of RDBMS if you didn't plan properly from the beginning. Basically: I think data is hard and I wish there were better tools for interacting with it.
- nawgszy 6y ago
  Well, this was maybe a bit entry level description. Q: "databases are often the worst-performing part of the tech stack" - compared to what? nginx throughput? I find this to be a bit of a strange view, surely business logic is always the slowest part of the tech stack Q: "What happens if that int should actually be a float?" - how often do you actually need to run migrations versus just extending the data? From my end, I have a small idempotent database schema-maintaining tool, and if I need a new column or a new table there's no need for a migration, and you know your whole stack will interact with the new schema or old schema identically assuming you set sane defaults etc. I've built a lot of medium-quality low-traffic apps so I'm yet to encounter a real-world case where a migration wasn't just bad planning
  - freehunter 6y ago
    >I'm yet to encounter a real-world case where a migration wasn't just bad planning That's basically the root of the issue. Poor planning in your code means you re-write some code. Poor planning in your database means you have to start restructuring data, and if it's already running in production you have to hope you don't accidentally corrupt production data. It's a lot harder to restore corrupted data in production than it is to roll back a code deployment. And the answer to the problem is obviously just spending more time thinking about the proper data structure, which is the entirety of my complaint: I want my data to fit my application, I don't want to have to write my application to fit my data. I don't want to have to think "does this field belong in the Users table or the Accounts table or the [insert table here]". I'm not sure what you mean by "just extending the data"... if I'm writing a Rails app and I need to change an int to a float, the way I do that is by writing and executing a migration. As for the speed... a database typically stores its data on disk and is often not hosted on the same physical machine as the web server. Meanwhile the app and web server store a lot of things in-memory on the local server and even when it has to read from disk, it's a local disk attached to that machine. Check these numbers for how long it takes to read from memory (or even local disk) versus reading a remote disk over the network: https://gist.github.com/jboner/2841832 https://gist.github.com/jboner/2841832
    - nawgszy 6y ago

freehunter 6y ago

When I say "hit the database too hard" what I actually mean is making too many calls to the database. If I have to make three or four calls just to get all the data I need, performance is going to take a hit. And databases are often the worst-performing part of the tech stack, so you have to craft your queries around the performance bottleneck of the database. So you have to balance your table structure around getting the data you need in as few calls as possible while also only returning exactly as much data as you need and no more. Because pulling extra data out puts more load on the database and shipping more data across the network is slower. In general, relational databases (RBDs) like Postgres or MySQL are great for making sure your data follows the correct structure (this field is a string, this is an integer, this field in table1 has to be the same as this field in table2, etc) but make it hard to make changes to the data structure later. What happens if that int should actually be a float? Now you have to write a migration that makes fundamental changes to the structure of the data and hope there are no negative side effects. Someone else mentioned NoSQL databases, which offer a lot more flexibility at the cost of the data integrity that RDBs enforce. If you suddenly want to store a float instead of an int, go ahead. No one is stopping you, you just need to make sure your code is updated to handle the possibility of getting back an int or float (or coerce the value to the right type and pray). Basically a database is integral to almost all applications but they're complex monsters with their own structure and rules and performance implications. If you're building an application you really have to know the data structure of the final product before you even _start_ configuring the database.

balfirevic 6y ago
> but make it hard to make changes to the data structure later. What happens if that int should actually be a float? Now you have to write a migration that makes fundamental changes to the structure of the data and hope there are no negative side effects. This is often stated, but it's not true. RDBMS make it easier to make changes to the data structure precisely because you only have to write migration and after that you are guaranteed to only ever get the data out of database in the new format. Which means your application code doesn't accumulate the cruft of dealing with two possible database data formats.
- freehunter 6y ago
  I strongly disagree that this pattern makes it easier to make changes. It makes it easier to use the data (because you know this data will always be this type), but changing the data is harder than if it did not enforce a type. I'm not arguing that un-typed data in a DB is easy to use or a good idea, just that it's harder to change the structure of RDBMS if you didn't plan properly from the beginning. Basically: I think data is hard and I wish there were better tools for interacting with it.
nawgszy 6y ago
Well, this was maybe a bit entry level description. Q: "databases are often the worst-performing part of the tech stack" - compared to what? nginx throughput? I find this to be a bit of a strange view, surely business logic is always the slowest part of the tech stack Q: "What happens if that int should actually be a float?" - how often do you actually need to run migrations versus just extending the data? From my end, I have a small idempotent database schema-maintaining tool, and if I need a new column or a new table there's no need for a migration, and you know your whole stack will interact with the new schema or old schema identically assuming you set sane defaults etc. I've built a lot of medium-quality low-traffic apps so I'm yet to encounter a real-world case where a migration wasn't just bad planning
- freehunter 6y ago
  >I'm yet to encounter a real-world case where a migration wasn't just bad planning That's basically the root of the issue. Poor planning in your code means you re-write some code. Poor planning in your database means you have to start restructuring data, and if it's already running in production you have to hope you don't accidentally corrupt production data. It's a lot harder to restore corrupted data in production than it is to roll back a code deployment. And the answer to the problem is obviously just spending more time thinking about the proper data structure, which is the entirety of my complaint: I want my data to fit my application, I don't want to have to write my application to fit my data. I don't want to have to think "does this field belong in the Users table or the Accounts table or the [insert table here]". I'm not sure what you mean by "just extending the data"... if I'm writing a Rails app and I need to change an int to a float, the way I do that is by writing and executing a migration. As for the speed... a database typically stores its data on disk and is often not hosted on the same physical machine as the web server. Meanwhile the app and web server store a lot of things in-memory on the local server and even when it has to read from disk, it's a local disk attached to that machine. Check these numbers for how long it takes to read from memory (or even local disk) versus reading a remote disk over the network: https://gist.github.com/jboner/2841832 https://gist.github.com/jboner/2841832
  - nawgszy 6y ago