13 ms·
Operating on a minimal two-core Postgres instance: Query optimization insights
- KingOfCoders 3y agoThis gives me hope, my current idea I work on is osal planned for a two-core Postgres cloud instance from Hetzner.
- deleted 3y ago[deleted]
- znpy 3y ago> This gives me hope, my current idea I work on is osal planned for a two-core Postgres cloud instance from Hetzner. Keep in mind that years ago having two cpus (single core cpus, with incredibly worse performance) was the pinnacle of technology, and still people managed to handle a decent amount of data.
- sgarland 3y agoSome of the points mentioned are sensible, but others are horrible (shift joins to the application) or simply nonsensical, like this: > If suboptimal methods (Nested Loop, Hash Join, Merge Join, etc.) are chosen for joins, this can lead to unnecessarily high resource consumption. Pray tell, what join strategies are left to use? Also, none of those are suboptimal, given the right schema and settings. Nested loop is fine, provided one side of the join is small. What isn’t mentioned (perhaps in a future post it will be) is how table and column statistics can affect performance. That’s how you get nested loops when a merge join would actually be more performant.
- OJFord 3y agoMaybe it means (trying to read charitably) that those are the methods, and if a suboptimal one of them (for a given query, schema, etc.) is chosen, then ... Or it's just a continuation of the first point: any non-application join is suboptimal. (Erm..) Edit: in fact, re-reading TFA, I think it is the latter or something like it - because the shift to application logic is mentioned as a 'countermeasure' for the 'suboptimal join methods'. I think it means deal with sometimes getting a bad plan by taking control and doing it yourself in the application. (I don't agree, just trying to understand.)
- robertlagrant 3y agoIf you're joining in the application, why use a relational database?
- horse_dung 3y agoThis is so true. If you just want the data then use a different store (e.g. NoSQL) and everything will be quicker, lighter weight and slower to develop…
- paulddraper 3y agoWhat does "lighter weight" mean? Smaller install size? Smaller memory footprint? Schemaless?
- toolz 3y agoJust because you're joining in the application for one code path doesn't mean all of the code paths are doing the same thing. There are certainly valid use-cases for joining in the application because application layer can often times be horizontally scaled more cheaply/easily than the persistence layer. So in a scenario where you have plenty of network IO being under utilized for your database, it's not unimaginable where joining in the application layer would be preferable.
- pard68 3y agoFWIW the article never says to join at the app level. GP read wrong or something. The article says to shift logic to the application.
- robertlagrant 3y agoAh - fair enough. I knew I should've checked the article first before replying!
- OJFord 3y agoThis may start to depend on exactly what you're willing to call relational, but because you still want the schema & foreign key constraints for example enforced in the database, you're just going to do the actual mechanics of joining the data in the application? join_users_and_widgets( run_sql('select * from users where account_id = :1', req.account_id), run_sql('select * from widgets'), ) for example, where the db still enforces that `widgets.owner_id` is a fk to `users.id` say. And `join_users_and_widgets` only has to know the current version of the schema, all rows have the same columns as it were.
- paulddraper 3y agoIt just means PostgreSQL can choose the wrong one for a specific instance, not that those are universally wrong. "PostgreSQL chosing a suboptimal join methods (a 'join method' is Nested Join, Hash Join, Merge Join, etc., each of which is optimized for a specific situation) leads to unnecessarily high resource consumption. "[And unlike virtually every other DBMS, PostgreSQL doesn't support specifying which method is used for a particular join. So even if it happen relatively infrequently, it's very difficult to correct a situation where it does choose the wrong one.]"
- sgarland 3y ago> PostgreSQL doesn't support specifying which method is used for a particular join Not directly, no. You can make some of them possible / impossible depending on your indexing and server settings, as well as the query itself. For example, if the smaller side of the join can't fix into `work_mem` – which defaults to 4 MiB – you won't get a hash join. You can either create smaller tables, more restrictive join conditions, or increase the size of `work_mem` (but be careful not to blow up the DB, since that's per connection).
- deleted 3y ago[deleted]
- paulddraper 3y agoAnd you can use the enable_* sledgehammers as well.
- matthewaveryusa 3y agoSometimes it's better to do things in the application instead of caking it in an sql query. An example would be a subquery that yields a large temp table, only to be pruned down to a single row. Your choice is to either have understanding of the query optimizer and formulate a query that forces the subquery with a smaller temp table first, or you can bring it back to the application. Either approach works, but in once case you need to have deep-ish understanding of the optimizer and SQL, and in the other not so much (at the cost of an RTT.)
- sgarland 3y ago> but in once case you need to have deep-ish understanding of the optimizer and SQL I will never understand why devs think they need to understand their chosen application well, but not SQL. Your application relies on SQL, so why shouldn't you deeply understand it? Relational algebra isn't that complex compared to other things we take for granted as necessary.
- whynotmaybe 3y agoBringing more data to the app so that the app filters it instead of the DB server is always wrong. An app with such solution is a sign of mutliple issues that either the DB schema is wrong, or that the devs don't know enough Sql, or both. Sql has been there for decades, much less longer than many of the actual dev framework. PostgreSQL (and other DBMS) have also been there for decades, they have perfected the art of fetching data.
- jon-wood 3y agoNothing is ever "always wrong". It may be a sign that a little more thought can lead to a more maintainable/quick/efficient solution, but it may also be that under the circumstances an approach that would be the wrong one if applied widely actually is the correct tradeoff between those factors in this case.
- jmull 3y agoThe article says "Shift logic to the application level to reduce database load." (Emphasis mine.) It may have been edited since you commented (perhaps because of your comment), but it seems like they aren't actually advocating to do joins at the application level.
- 7thaccount 3y agoI worked at a company with an extremely complex optimization application with more business rules than any human being could ever comprehend. The logic has to go somewhere. Either the database or the interface or the application itself I guess. There are pro/cons to all. Some of the logic is hard to follow in SQL compared to a general purpose language. The vendor for us of course did some kind of blend where a lot of SQL packages do certain validations and so on. The optimization problem reads a bunch of .csv files from the database and then has its own rules it uses to build the math problem for the mathematical solver.
- sgarland 3y agoI could’ve sworn it said joins, but I’m not positive. Tbf this is what Prisma does, albeit it joins at its own layer, then passes results back to the caller. Still horrifying.
- dboreham 3y agoJust to push against the crowd here pooh poohing client-side joins: this is a standard technique for scaling when you are stuck (for the time being) with a RDB and is definitely not in general "horrible". It does not mean pulling entire tables to the client then joining there. It means splitting a query that in effect walks down a chain of pointers (FKs) between tables into separate client-initiated queries. There are several benefits to this: 1. makes the design "nosql-ready" for when you design out the RDB. 2. Makes all DB queries inherently indexed and quick, allowing easy perf analysis (any non-quick query must be a bug) and alleviating connection pool starvation issues. 3. Prevents pesky devs from adding expensive queries under the radar. 4. allows more ready deployment of caching solutions. 5. recognizes that client resources scale with the number of clients, and are paid for by the client while RDB resources generally scale with difficulty and $$$, and are paid for by you.
- JCharante 3y ago> 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 agoSome companies do large amounts of stored procedures for even routine tasks.
- JCharante 3y agoAh I see. I thought it meant taking away some merge logic out of the database layer.
- leetrout 3y agoOh 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 agoHow to operate on a tiny database server: shift all work from the database server to the application servers. See also: webscale databases
- northbymidwest 3y agoThis 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 agoThe 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)
- ranting-moth 3y agoI agree that most DB instances are grossly underutilized (or abused performance wise). But an "entire company" is a tad crude unit of measure when talking about optimization. Good point though. I've seen companies create a completely new replicated DB instance for every little pet project they start. Crazy. Nice if you're a cloud provider though.
- mrweasel 3y agoOne issue I frequently ponder is: What would happen if we gave a modern computer to a developer 30 or 40 years ago? They'd be overwhelmed by the instruction set I suppose, but how large a business could they run on a modern desktop?
- narag 3y agoYou know, some of us are still alive today :)
- throwawaaarrgh 3y agoI don't think most people today realize how wasteful modern software engineering is. They think, it's fine to be inefficient, I'll just get a bigger X. And we are now blessed with technology and solutions that make that a possibility, when for the longest time it wasn't. But you don't want to have to optimize when you don't expect to. It takes time away from your features and quality, and is risky. You want to predict when you'll need to grow and how much effort it might take, and control costs. That's why software development should be efficient from the start. By only using what you need, you don't run into situations where you have to optimize to survive. Anyone who says to me "I'll just put everything in PostgreSQL and optimize later" sounds just as naive as the person who says "let's build for scalability before we have any customers". Both are giving themselves problems they don't need, and trying to justify it by thinking it's going to save them time. The former is giving themselves problems in the long term, the latter is giving themselves problems in the short term. Even if these tradeoffs are acceptable to you, they are often unnecessary!
- api 3y ago... then they see the cloud bill. Cloud companies found a way to directly monetize developers' love of complexity and overengineering. It's genius.
- robocat 3y agoDevelopers often love simplicity and underengineering, which can also lead to performance problems. My personal definition of engineering is: choosing good compromises between competing needs.
- ecshafer 3y agoThis comment will be taught in business schools one day.
- api 3y ago1. Find pathology. 2. Attach toll booth. 3. Profit.
- alkonaut 3y agoYou have to love the revival of things like "Let's render the html on the server!", "Lets create the whole page with just one request!", "Let's use SQL, and perhaps just ONE server!", "Try using a server in your office to save on cloud bills", "Put all your microservices in one process so they are cheap to run and easy to deploy!".
- riku_iki 3y ago> "Try using a server in your office to save on cloud bills" and then company stops working when that server dies.
- nik736 3y agoYou could also say the same for the cloud when your internet is not working.
- riku_iki 3y agoIt is easy to setup fault tolerant, simple, low maintenance and cheap for small traffic and data infra in the cloud (e.g. AppEngine+CloudSQL) where provider will provide 99.99% availability. Internet not working is usually can be mitigated through multiple providers, cellular included
- jabart 3y agoWhatever is spent optimizing this to two cores is hours wasted when you could expand. Developer time is still cheaper than Cloud Cores or Ram. Yes you should write SQL queries with basic best practices and have a schema that is just right between performance and normalized. Not mentioned was table size. You can do a lot of odd things with < 100 million rows of data and get away with it.
- outside1234 3y agoYes - I think the overall headline of this is correct (most people should just use PostgreSQL) but they should NOT go into five zoom levels of detail on optimization or anything other than really understanding and utilizing knowledge of how a relational database works (utilizing indexes etc.)
- sgarland 3y agoNothing in the article was in the weeds, and you can do all of it in RDS as well if you’d like (pgstat is an available extension, I think enabled by default).
- auspiv 3y agoNot entirely clear from the article/blog - is this an RDS instance (equivalent) or EC2 instance (equivalent) in a cloud with storage attached? I didn't think it was possible to edit postgres.conf on a RDS type managed database. But the author also talks about scaling up quickly as performance walls are hit while they work on optimizing stuff.
- rob742 3y agoIn RDS this would be possible via Parameter/Option groups
- monlockandkey 3y agoDoes anyone have any experience with Pgtune, i.e has if made a difference in performance? https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/
- deleted 3y ago[deleted]
- paxys 3y agoThe optimizations are no doubt impressive, but looking at it from a costs perspective – they run their company on two DB instances, each with two CPU cores and 4 GB of ram. The annual cost of running such a machine on AWS (with SSD included) is ~$4000. Even if their DB infra was only half as efficient as it is today, their costs would increase by a whopping $8000/yr. How much do they pay an average software developer? How many hours have developers spent on query monitoring, optimization, removing joins, removing aggregations? What is the ongoing cost of learning and applying these techniques to all new code written at the company? Even with the most conservative estimates I'm confident that it costs them several orders of magnitude more in developer productivity than they are saving on hardware. Optimization is good, but every company needs to recognize where they are throwing away dollars to save cents. There is an optimal level of inefficiency that companies want to have.
- mcqueenjordan 3y agoHow did you arrive at $4000? I used https://calculator.aws/#/estimate https://calculator.aws/#/estimate and my number is over an order of magnitude less than $8000 for 2 instances with SSDs, 4GB RAM, and 2 cores.
- deleted 3y ago[deleted]
- wheaties 3y agoI/O, backups, and such. Just having the instances cost money. Using them costs even more!
- sgt 3y agoAnd egress at AWS.
- mcqueenjordan 3y agoDo you expect a database to do much egress?
- latchkey 3y agoI'm picturing that meme with the guy sweating over pressing a button. Add new features that generates revenue, or optimize the database. Hmmm. Of course the response is that you want to optimize the database so that when you do eventually add new features that generate revenue, you can scale to meet the demand. Or is it...
- Draiken 3y agoThis meme is a bit too misleading. In reality tons of startups have a shitty product with zillions of bugs because "move fast break things". Then later on they don't survive because the only reason people even pay for them is because the other startups are spending silly money on it. When the well dries up, it all comes crumbling down. Turns out that startup's software is too shitty and simply not good enough to survive a cost cutting round. People love to pretend that just adding more features always beats everything else, but it's not as clear cut as people make it seem.
- latchkey 3y agoWe're talking about premature optimization, not necessarily shitty/buggy software.
- lgkk 3y agoNice. I do something similar with low cost instances. I use Go and I get a great bang for my buck (performance to dx to costs). In my case I also leverage the cheap access to gobs of SSD or nvme. I use super cheap VMs for the app servers and they come with 10-20gb disk. I use this as KV embedded cache. For a few bucks a month I’m able to pump out 7-10k rps at load (synthetic but still). I have a low end pg instance that’s able to pump out 2k tps (synthetic) so with the little bit of caching I’m able to save some costs and also offer good performance. All under $100 a month. I hope when my business does take off I will have a good base to expand that I can get far without spending tons of money on inefficient compute.
- mannyv 3y agoIf you want a data store, just use a NoSQL server. If you want an RDBMS, learn how to use it. At least with NoSQL you know you're trading ignorance for ease of use.
- bhaak 3y ago> Improve database schemas, e.g., through normalization, to promote efficient filtering. Cries in ActiveRecord type columns. In less sad news, I recently learned that Postgres can have serious problems with joining a lot of tables. I looked at a join of several tables (table A 80MM, table B 30MM, table C 1MM rows) and instead of using the WHERE parameter to reduce the result set of table A to a few thousands, it fetched all rows from table C first. The applied heuristic in this case apparently being "join tables by order of lower row count". Setting "join_collapse_limit" to 1 forces the query planner to join the table in exactly the order as they appear in the query and that solved my performance issues neatly.
- nerdponx 3y agoNow that CTEs are no longer optimization fences in recent versions of Postgres, is there a way to somehow manually declare an optimization fence around a certain region of the query? I've had similar problems in Snowflake with the query planner taking a bad path, and no obvious way to hint otherwise.
- bhaak 3y agoFunctions are hard barriers for optimization and I don't see that changing any time soon. Which goes even so far that EXPLAIN doesn't work on them and you just get a black box of "Function call to ..." in the execution plan. Using auto_explain is the only workaround known to me for that.
- baq 3y ago