5 ms·
Because Postgres and MySQL are more familiar and by the time they're falling over, you have raised 80M and can afford to hire folks like me who's entire job is
by erulabs 3y ago
Because Postgres and MySQL are more familiar and by the time they're falling over, you have raised 80M and can afford to hire folks like me who's entire job is pestering developers to stop reading-after-writing, to stop forcing queries to the primary (just incase there's lag!), to stop "select id from table" -> "select data_I_want from other_table where ID IN (list-of-a-million-ids"), to beg AWS for early access to the fastest possible machines, to systematically destroy the custom ORM you built during the first 5 years of the company, before you had customers.
Scylla/Cassandra is exactly the database you want when you're serving the entire planet.
Postgres is exactly the database you want when you're desperately searching for product market-fit and just need things to work and code to get written.
Turns out it's much better to start simple and face scaling problems maybe than start complex and face scaling problems maybe.
- gajus 3y agoWhat's wrong with "select id from table"? (presumably "where" skipped for brevity of the example)
- pritambaral 3y agoI read it as "`where` skipped at code time because the table is never going to be too large".
- CBLT 3y agoIt looks like what they're describing is implementing database joins in the application: 2 network round trips, first one for ids from a table and the second to get values from another table with foreign keys corresponding to the ids.
- WanderPanda 3y agoIsn‘t implementing joins in the application what microservices are all about?
- _jal 3y agoNo, microservices are all about senior management's ability to map applications to people to yell at.
- nicoburns 3y agoYou're getting confused with NoSQL ;)
- macintux 3y agoI have supported a microservices environment where each service had its own database, and indeed "foreign keys" were mostly application-level concerns. A real pain, and I don't think any of us would have chosen that approach in hindsight. I hope there are better ways to design microservices.
- cogman10 3y agoIDK if postgres has this problem but mssql does. If you do "Select * from table where ID in (blah)" it ends up with some pretty terrible performance. It's often a lot faster to do something like Insert into #tempTable ids select from table JOIN #tempTable t on t.id = table.id
- WanderPanda 3y agoI can‘t believe this is a thing in 2023! If this is faster, shouldn‘t the database do that transparently in the background?!
- cogman10 3y agoAt least in our case, it comes down to expectations. For the `IN` query the DB doesn't know how many elements are in the list. In MSSQL, it would default to assuming "well, probably a short list" which blows out performance when that's not the case. When you first insert into the temp table, the DB can reasonably say "Oh, this table has n elements" and switch the query plan accordingly. In addition, you can throw an index on the temp table which can also improve performance. Assuming the table you are querying against is indexed on ID, when you have another table with IDs that are indexed it doesn't have to assume random access as it pulls out each id. (Effectively, it just has to navigate the tree nodes in order rather than needing to do a full look into the tree).
- dragonwriter 3y agoWhat's wrong is replacing a single query with a join with multiple round trips implementing part of the join logic in application code because you are using an RDBMS as if it were a fairly dumb repository of dusconnected tables. SELECT id FROM table1 Is fine, if what your app wants is the ids from table1. Its not good if it is just a prelude to: SELECT * FROM table2 WHERE table1_id in (...ids from previous query...) instead of just doing: SELECT table2.* FROM table1 INNER JOIN table2 ON ( table2.table1_id == table1.id )
- muzaffarpur 3y agoMost of the query optimisers would automatically convert subqueries into join.
- stvltvs 3y agoNot if the dev has removed the context of which table the id values come from.
- muzaffarpur 3y agoCan you explain bit further. If the subquery do not have context where the value comes from, I would think query will error out?
- 10000truths 3y agoWhat you're saying is that: SELECT * FROM table2 WHERE table1_id in (SELECT ids FROM table1 WHERE ...) will be optimized to a join by the query planner (which may or may not be true, depending on how confident you are in the stability of the implementation details of your RDBMS's query planner). But in most circumstances, there is no subquery, it's more like: SELECT * FROM table2 WHERE table1_id in (452345, 529872, 120395, ...) Where the list of IDs are fetched from an API call to some microservice, or from a poorly used/designed ORM library.
- 3y ago
- WesleyJohnson 3y agoCurious how you approach business units asking for reports that require multiple joins and can "filter by any column" and "sort by any column" and also offer pagination and then complain about why it's so slow? This is MySQL by the way. The sorting by any column and pagination is really killer. Can't do cursor-based pagination so you get LIMIT/OFFSET which is terrible for performance, at least in this context. Indexes are often useless in this circumstance as well due to the filter by any column. We get lucky sometimes with a hard-coded WHERE clause as baseline filter for the entire report that hits an index, but not always. Just for fun, add in plenty of LIKE '%X%' because we limiting them to STARTS WITH is out of the question; it must be CONTAINS. It's a constant source of frustration for the business and for the dev team.
- pbowyer 3y agoIn our case we switched to holding that data in Elasticsearch. Now we have 2 problems: running Elasticsearch and keeping the data in Elasticsearch in sync with MySQL. We took an application-level approach to triggering record sync to ES but somewhere in our ORM it's doing some magic and doesn't sync in certain places. Database triggers are the next stop. If we could do away with ES and go back to MySQL for what are glorified data grids with 20+ column filtering, sorting, and no full text requirements, we would.
- WesleyJohnson 3y agoWe use ElasticSearch in a very limited capacity for keyword searches against consumer product metadata. We haven't ventured into using it for BI reporting, but I'm interested. I'm sure there is plenty of knowledge I can google, but any early insights or traps to avoid when moving from SQL-based reporting to something in ES?
- stvltvs 3y agoSounds like the job for a denormalized OLAP data structure (e.g. star schema) with a BI tool. This is the kind of use case they were made for. In other words, you've rediscovered the motivation for data warehouses.
- VirusNewbie 3y agoI think it depends on the problem. I've worked at a tiny telco (<100 people) where MySql absolutely wouldn't have worked. We ended up using Scylla DB. We had billions of rows in single tables. If you're launching an e-commerce site that sells shoes or something, yeah, you probably aren't going to sell a billion shoes every year so MySql is probably fine. Finally, I use this example a lot: Github thought they could scale out MySql by writing their own Operator system that sharded MySql. They put thousands of engineering hours in it, and they still had global database outages that lost customer data. You get that shit for free using Scylla/Cassandra (the ability to replicate across data centers and still fine grained control to do things like enforce local quorum to not impact write speed etc.) They probably made the wrong choice.
- krembo 3y agoOr just use RDS Aurora for same benefits out of the box at scale.
- pc86 3y agoYou know as I get closer and closer to official graybeard status I start having more of a hair trigger when someone says "just use __________" as if that solves everything and doesn't just create a list of a dozen or two other problems. Everything is tradeoffs, you need to look at what you're trying to do and see which problems you are okay with. Some of these will be show-stoppers at larger scale. Some might be show-stoppers now. But "just use __________" is never the right answer for any serious discussion.
- VirusNewbie 3y agoAurora won't work near as well for write heavy/time series type workloads. It's probably a good middle ground for a lot of applications though.
- tynorf 3y agoCassandra does not make you immune to database issues: https://monzo.com/blog/2019/09/08/why-monzo-wasnt-working-on-july-29th/ https://monzo.com/blog/2019/09/08/why-monzo-wasnt-working-on...
- isoprophlex 3y ago> systematically destroy the custom ORM you built during the first 5 years of the company, before you had customers. Ouch, haha, that hurt
- osigurdson 3y ago>> Because Postgres and MySQL are more familiar If familiarity is the primary problem, it should be relatively easy to fix.