5 ms·

The difficulty with this advice is that it assumes that you have many small transactions. Yes, of course, you should try to build your application so queries a

by ccleve 6y ago

The difficulty with this advice is that it assumes that you have many small transactions. Yes, of course, you should try to build your application so queries and transactions are very short. That solves a great many problems. But sometimes you can't. Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. That's a huge burden on your server, and it will slow down the other tenants. In that case, the only answer is to distribute the queries across other boxes. I agree with the advice in general -- delay moving to a distributed system as long as possible -- but sometimes you just can't.

giantrobot 6y ago
Postgres has materialized views for the case of expensive queries. Instead of doing a view query in real-time it physically caches the results and has the same accessibility as a regular view. They don't solve all situations with expensive queries but they help a lot. The fact they behave like dynamic views means you can migrate a dynamic view to materialized if you run into performance issues without making changes to the client application. With views in general you can shuffle around tables and relationships without the client application knowing or caring.
mmacedo_en 6y ago
I've seen queries running for 1 minute 2 minutes raising user complaints. Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. Before thinking about distributed systems, there is an entire database optimization toolkit to make use of: primary key review, secondary index creation, profiling, view or stored procedure creation, temporary tables, memory tables and so on.
- CharlesW 6y ago
  > Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. This is exactly what the parent comment said: "you should try to build your application so queries and transactions are very short". If you're claiming that the parent is incorrect about "sometimes, the only answer is to distribute the queries across other boxes", my guess is that probably don't work at a scale where you've learned that query optimization can't solve every database performance problem.
  - btilly 6y ago
    Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved. This does NOT mean that there are no use cases for distributed - I've seen Google's internals and it would be impossible to do that any other way. But it does mean that when someone is telling you with a straight face that they needed horizontal scalability for performance, you should assume that they were probably wrong. (Though probably saying that is not the wisest thing - particularly if the person you're talking to is the architect whose beautiful diagrams you'd be criticizing.) So yes, there are problems that require a distributed architecture for performance problems. That doesn't contradict the point that every other option should be explored first.
    - CharlesW 6y ago
      > Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved. Oh, I'm absolutely positive you're right! And as you know, even in distributed architectures there's a huge range of solutions from "let's do these n reasonably simple things" to "let's just rebuild our system to solve every conceivable future problem". I don't know of a scenario where the latter has ever worked.
  - mmacedo_en 6y ago
    I'm referring to parent's point "Sometimes you just have to do joins across large tables.". Indexes and query hints can make a world of difference once you profile how the query is being executed. Another option is to create a stored procedure which first extracts a smaller amount of data from first table to a temporary table and then join it to the other large table. Measuring and testing different optimization methods takes a lot of effort and time. We should only do that when faced with a real problem of slowness. "The first rule of optimization is: Don't do it. The second rule of optimization (for experts only) is: Don't do it yet. Measure twice, optimize once."
- ozkatz 6y ago
  This. Using any database requires building up an expertise and understanding how to use its capabilities properly. If you hit a wall with a non-distributed database and your solution is to replace it with a distributed one - you will have a bad time. The surface area of what you need to know to use it properly still includes your basic data modeling, indices, troubleshooting that was required before, with a whole lot of networking, consensus protocols and consistency models to worry about (just to name a few).
- jeffbee 6y ago
  Indexes are not free, they take up space and they make mutations more costly. Also, building the index may not even be possible while your application is running, because postgresql and other RDBMS have inadequate facilities for throttling index construction such that it doesn't harm the online workload. You might have to build indexes at midnight on Sundays, or even take your whole system offline. It can be a nightmare. This isn't just a problem for SQL databases. Terrible-but-popular NoSQL systems like MongoDB also rely heavily on indexes while providing zero or few safety features that will prevent the index build from wrecking the online workload. I personally prefer databases that simply do not have indexes, like bigtable, because they require more forethought from data and application architects, leading to fundamentally better systems.
  - btilly 6y ago
    I've seen https://www.postgresql.org/docs/12/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY https://www.postgresql.org/docs/12/sql-createindex.html#SQL-... work well in practice on busy transactional databases. I'd be interested in knowing about cases where it doesn't work well. My experience on systems without indexes differs strongly from yours. Yes, they can work well. But if you have multiple use cases for how your data is being queried, they push you into keeping multiple copies of your data. And then it is very, very easy to lose consistency. And yes, I know about "eventual consistency" and all that - I've found that in practice it is a nightmare of special cases that winds up nowhere good.
    - jeffbee 6y ago
      Just from personal experience, if they can build gmail on top of a database (bigtable) having neither indexes nor consistency, then probably it will also be suitable for the purposes of my far smaller, much less demanding products. On the other hand I've seen, and am currently suffering through, products that have desperate performance problems with trivial amounts (tens of GB) of data in relational databases with indexes aplenty.
      - simcop2387 6y ago
cliftonk 6y ago
You could always start with postgresql and use the transaction log to materialize views for particular use cases (ie differential data flow). This post is clickbaity in that it doesn’t give any administration advice but right in the broad sense that postgres/mysql are still the best low-latency single sources of truth available
btilly 6y ago
Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance improvements left when it first looked like it was topping out. Add a caching layer, have better indexes, more targeted queries, appropriate use of window functions, etc. Give those a try before biting the bullet on distributed data. Point #3, here is a good rule of thumb for distributed systems. Whatever scale you hit on a single machine, you can probably gain 1-2 orders of magnitude of performance by switching to a faster language and carefully optimizing. If you switch to distributed, you'll LOSE at least an order of magnitude performance due to the overhead of RPCs, but are able to scale indefinitely afterwards. If you're distributing for reliability, great. But if you're distributing for performance and you have less than 20 machines in your system, either your problem is embarrassingly parallel or you likely aren't achieving a net win. I've seen a lot of people prematurely distribute, run into performance challenges, solve them, then pat themselves on the back for being smart enough to have distributed their code. While failing to recognize that they were addressing a self-inflicted injury.
- ajsharp 6y ago
  My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.
  - barrkel 6y ago
    The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster overall, but the single individual SQL statement will be slow. No simple rules. It depends on the application. (Yes, there are middle ways. Break up the giant UPDATE using some kind of batching strategy. Long-running update-heavy transactions aren't healthy, particularly for Postgres.)
mcherm 6y ago
> Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. While that is true, I would speculate (based on my own experience, not any actual research) that it is far more common that users have an unoptimized query or database schema (perhaps it just needs an index to be created) which is taking tens of seconds to run but doesn't NEED to, than it is that users have a need for a complex query that cannot run faster than that. So for MOST users, the best advice is to learn how to use analyze query and other database tools to optimize execution. Only if you ALREADY know that should you be considering moving to a read replica or some sort of distributed database.

by ccleve 6y ago

giantrobot 6y ago

Postgres has materialized views for the case of expensive queries. Instead of doing a view query in real-time it physically caches the results and has the same accessibility as a regular view. They don't solve all situations with expensive queries but they help a lot. The fact they behave like dynamic views means you can migrate a dynamic view to materialized if you run into performance issues without making changes to the client application. With views in general you can shuffle around tables and relationships without the client application knowing or caring.

mmacedo_en 6y ago

I've seen queries running for 1 minute 2 minutes raising user complaints. Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. Before thinking about distributed systems, there is an entire database optimization toolkit to make use of: primary key review, secondary index creation, profiling, view or stored procedure creation, temporary tables, memory tables and so on.

CharlesW 6y ago
> Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. This is exactly what the parent comment said: "you should try to build your application so queries and transactions are very short". If you're claiming that the parent is incorrect about "sometimes, the only answer is to distribute the queries across other boxes", my guess is that probably don't work at a scale where you've learned that query optimization can't solve every database performance problem.
- btilly 6y ago
  Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved. This does NOT mean that there are no use cases for distributed - I've seen Google's internals and it would be impossible to do that any other way. But it does mean that when someone is telling you with a straight face that they needed horizontal scalability for performance, you should assume that they were probably wrong. (Though probably saying that is not the wisest thing - particularly if the person you're talking to is the architect whose beautiful diagrams you'd be criticizing.) So yes, there are problems that require a distributed architecture for performance problems. That doesn't contradict the point that every other option should be explored first.
  - CharlesW 6y ago
    > Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved. Oh, I'm absolutely positive you're right! And as you know, even in distributed architectures there's a huge range of solutions from "let's do these n reasonably simple things" to "let's just rebuild our system to solve every conceivable future problem". I don't know of a scenario where the latter has ever worked.
- mmacedo_en 6y ago
  I'm referring to parent's point "Sometimes you just have to do joins across large tables.". Indexes and query hints can make a world of difference once you profile how the query is being executed. Another option is to create a stored procedure which first extracts a smaller amount of data from first table to a temporary table and then join it to the other large table. Measuring and testing different optimization methods takes a lot of effort and time. We should only do that when faced with a real problem of slowness. "The first rule of optimization is: Don't do it. The second rule of optimization (for experts only) is: Don't do it yet. Measure twice, optimize once."
ozkatz 6y ago
This. Using any database requires building up an expertise and understanding how to use its capabilities properly. If you hit a wall with a non-distributed database and your solution is to replace it with a distributed one - you will have a bad time. The surface area of what you need to know to use it properly still includes your basic data modeling, indices, troubleshooting that was required before, with a whole lot of networking, consensus protocols and consistency models to worry about (just to name a few).
jeffbee 6y ago
Indexes are not free, they take up space and they make mutations more costly. Also, building the index may not even be possible while your application is running, because postgresql and other RDBMS have inadequate facilities for throttling index construction such that it doesn't harm the online workload. You might have to build indexes at midnight on Sundays, or even take your whole system offline. It can be a nightmare. This isn't just a problem for SQL databases. Terrible-but-popular NoSQL systems like MongoDB also rely heavily on indexes while providing zero or few safety features that will prevent the index build from wrecking the online workload. I personally prefer databases that simply do not have indexes, like bigtable, because they require more forethought from data and application architects, leading to fundamentally better systems.
- btilly 6y ago
  I've seen https://www.postgresql.org/docs/12/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY https://www.postgresql.org/docs/12/sql-createindex.html#SQL-... work well in practice on busy transactional databases. I'd be interested in knowing about cases where it doesn't work well. My experience on systems without indexes differs strongly from yours. Yes, they can work well. But if you have multiple use cases for how your data is being queried, they push you into keeping multiple copies of your data. And then it is very, very easy to lose consistency. And yes, I know about "eventual consistency" and all that - I've found that in practice it is a nightmare of special cases that winds up nowhere good.
  - jeffbee 6y ago
    Just from personal experience, if they can build gmail on top of a database (bigtable) having neither indexes nor consistency, then probably it will also be suitable for the purposes of my far smaller, much less demanding products. On the other hand I've seen, and am currently suffering through, products that have desperate performance problems with trivial amounts (tens of GB) of data in relational databases with indexes aplenty.
    - simcop2387 6y ago

cliftonk 6y ago

You could always start with postgresql and use the transaction log to materialize views for particular use cases (ie differential data flow). This post is clickbaity in that it doesn’t give any administration advice but right in the broad sense that postgres/mysql are still the best low-latency single sources of truth available

btilly 6y ago

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance improvements left when it first looked like it was topping out. Add a caching layer, have better indexes, more targeted queries, appropriate use of window functions, etc. Give those a try before biting the bullet on distributed data. Point #3, here is a good rule of thumb for distributed systems. Whatever scale you hit on a single machine, you can probably gain 1-2 orders of magnitude of performance by switching to a faster language and carefully optimizing. If you switch to distributed, you'll LOSE at least an order of magnitude performance due to the overhead of RPCs, but are able to scale indefinitely afterwards. If you're distributing for reliability, great. But if you're distributing for performance and you have less than 20 machines in your system, either your problem is embarrassingly parallel or you likely aren't achieving a net win. I've seen a lot of people prematurely distribute, run into performance challenges, solve them, then pat themselves on the back for being smart enough to have distributed their code. While failing to recognize that they were addressing a self-inflicted injury.

ajsharp 6y ago
My thoughts exactly on point #1. Nothing in a hot path should take multiple seconds.
- barrkel 6y ago
  The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster overall, but the single individual SQL statement will be slow. No simple rules. It depends on the application. (Yes, there are middle ways. Break up the giant UPDATE using some kind of batching strategy. Long-running update-heavy transactions aren't healthy, particularly for Postgres.)

mcherm 6y ago

> Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. While that is true, I would speculate (based on my own experience, not any actual research) that it is far more common that users have an unoptimized query or database schema (perhaps it just needs an index to be created) which is taking tens of seconds to run but doesn't NEED to, than it is that users have a need for a complex query that cannot run faster than that. So for MOST users, the best advice is to learn how to use analyze query and other database tools to optimize execution. Only if you ALREADY know that should you be considering moving to a read replica or some sort of distributed database.