19 ms·
Things to know about databases
- googletron 4y agoThis is a quick rundown of database indexes and transactions. Excited to continue sharing these notes with community!
- mgrouchy 4y agoI have been really enjoying the content so far, any hits on whats coming up?
- googletron 4y agoWe have another couple of notes from a few companies like Temporal, Sentry, and Gadget.
- itsmemattchung 4y agoAwesome. Looking forward to additional content. If possible, would be great to get Mark Brooker (Principal at AWS) to provide some notes on bridging the gap between CAP theorem and how AWS relaxes constraints for building out Elastic Block Storage (EBS). https://brooker.co.za/blog/2014/07/16/pacelc.html https://brooker.co.za/blog/2014/07/16/pacelc.html
- googletron 4y agoI will definitely reach out! Thanks for the suggestion!
- yla92 4y agoGreat post. Also highly recommend Designing Data-Intensive Applications by Martin Kleppmann (https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321 https://www.amazon.com/Designing-Data-Intensive-Applications...). The sections on "Storage and Retrieval", "Replication", "Partitioning" and "Transactions" really opened up my eyes!
- itsmemattchung 4y agoSecond this. I really like how he (Martin Kelppman) in the book starts with a primitive data structure for constructing a database design, and then evolves the system slowly and describes the various trade offs with building a database from the ground up.
- lysecret 4y agoAbsolutely loved the book. Can someone recommend similar books?
- avinassh 4y agoDatabase Internals is also pretty good.
- skrtskrt 4y agoSeconding Database Internals - it's not just about "Internals of a database", as part 2 gets nitty gritty with the general problems of distributed systems, consensus, consistency, availability, etc. etc.
- dangets 4y agoI have not read it personally, but I've seen 'How Query Engines Work' highly recommended several times before. I have a procrasinatory tab open to check it out some day. https://leanpub.com/how-query-engines-work https://leanpub.com/how-query-engines-work
- wombatpm 4y agoDatabase Design for Mere Mortals by Ray Hernandez
- pixelmonkey 4y agoThere is a quite-nice interactive browser dataviz here that shows you books similar to the themes, categories, and topics discussed in DDIA: https://anvaka.github.io/greview/ddia/1/ https://anvaka.github.io/greview/ddia/1/
- otherflavors 4y agowhy is this tagged "MySQL" but not also "SQL"
- googletron 4y agoThanks! Added!
- bironran 4y agoNice post, though for the indexing "introduction-deep-dive" I would still recommend newbies to look at https://use-the-index-luke.com/ https://use-the-index-luke.com/ .
- googletron 4y agoGreat resource! I have it linked as a reference!
- konfusinomicon 4y agoalso check out rick james's mysql documents http://mysql.rjweb.org/ http://mysql.rjweb.org/ I send those 2 links to coworkers all the time
- wrs 4y agoFrom the SERIALIZABLE explanation: “The database runs the queries one by one … It is essential to have some retry mechanism since queries can fail.” I know they’re trying to simplify, but this is confusing. If the first part is true, the second part can’t be. In reality the database does execute the queries concurrently, but will try to make it seem like they were done one by one. If it can’t manage that, a query will fail and have to be retried by the application.
- googletron 4y agoI believe there was a caveat around this exact point later in the post. It was really tough striking a balance for people learning this for the first time and more knowledgeable audience without confusing them further. I do appreciate the feedback and will look to add some more color here! Thank you!
- blupbar123 4y agoIt's kind of saying something which isn't true. Optimally one would find a wording that doesn't confuse beginners but also is factual, IMHO.
- r0b05 4y agoNicely written and informative!
- googletron 4y agoThank you!
- ssd8991 4y ago[flagged]
- jwr 4y agoSome of the explanations are questionable: I think they were overly simplified, and while I applaud the goal, some things just aren't that simple. I highly recommend reading https://jepsen.io/consistency https://jepsen.io/consistency and clicking on each model on the map. This is the best resource I found so far for understanding databases, especially distributed ones.
- googletron 4y agoI would love the feedback, what was questionable? striking the balance is tough. jepsen's content is great.
- gumby 4y agoEveryone can disagree on what is the precise place to slice "this is beginner content" from "this is almost-beginner content". I could stick my own oar in in this regard but I won't. I think your level of abstraction is quite good for the absolute "what on earth are people talking about when they use that 'database' word?". With an extremely high level understanding, when they encounter more detail they'll have a "place to put it".
- Diggsey 4y agoOne thing that can be surprising is that for "REPEATABLE READ", not all "reads" are actually repeatable. There are at least two ways (that I'm aware of) that this can be violated. For example, if you run an update statement like this: UPDATE foo SET bar = bar + 1 Then the read of "bar" will always use the latest value, which may be different from the value other statements in the same transaction saw.
- kerblang 4y agoNot sure what you're claiming here... Repeatable read isolation creates read locks so that other transactions cannot write to those records. Of course our own transaction has to first wait for outstanding writes to those records to commit before starting. Best as I know the goal is not to prevent one's own transaction from updating the records we read; the read locks will just get upgraded to write locks.
- trhoad 4y agoAn interesting subject! The article could do with an edit, however. There are lots of grammatical errors.
- AtNightWeCode 4y agoNot sure how to use these recommendations in practice though even if the info is somewhat correct. SQL is a beast of tech and it is used because of battle history and since there is simply no other viable tech replacing it when it comes to transactions and aggregated queries. Indexes are a nightmare to get right. Often performance optimizations of SQL databases include removing indexes as much as adding indexes.
- vorpalhex 4y agoIt's not that SQL is all that beastly, it's that most tutorials fail to explain the internals and basics and so you just see all these features and interfaces of the system and can't build a mental model of how the system works.
- AtNightWeCode 4y agoWell, SQL does come with liberties. I worked with expensive commercial software that destroys the performance of databases by doing everything from complicated ad hoc queries to massive amounts of point reads.
- vorpalhex 4y agoAnd a hammer will let you smash your own fingers. All useful tools can be used incorrectly - and I agree SQL is one of the more frequently misused ones. I think a lot of that is that it's one of the more powerful tools.
- larrik 4y agoIndexes aren't a "make my DB faster" magic wand. They have benefits and costs. If you are seeing performance gains from removing indexes, then I'm assuming your workload is very heavy on writes/updates compared to reads.
- AtNightWeCode 4y agoMostly because of overlapping indexes. Then if there are include columns it may get out of hand. Not too difficult to achieve. Just blindly follow recommendations from a tool or a cloud service.
- manish_gill 4y agoWhat tool was used to create the visuals?
- praveenhm 4y agoI am guessing it was done on iPad
- tiffanyh 4y ago#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.
- googletron 4y agoGood point. Its often the problem space and other constraints that usually drive these decisions. Its important that you deal with problems when you have them.
- dobin 4y agoI just use files
- w0m 4y agoIs 'Not performance bound, and dot knowing the future shape of your data' a valid reason? Less overhead on initial rollout to just Toss it up there. > choosing something else is rarely the right decision I think this is a little bit of a 'We always did it this way' statement.
- dspillett 4y agoThere are circumstances where you really don't know the shape of the data, especially when prototyping for proof of concept purposes, but usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. More often than not it is worth sometime thinking and planning to work out at least the core requirements in that area, to save yourself a lot of refactoring (or throwing away and restarting) later, and potentially hitting bugs in production that a relational DB with well-defined constraints could have saved you from while still in dev. Programming is brilliant. Many weeks of it sometimes save you whole hours of up-front design work.
- spmurrayzzz 4y ago
- jrm4 4y agoTo go big picture; I'm kind of glad databases are largely like cars in this respect, in ways that other software tooling isn't. Which is to say they're frequently good enough such that the human working with them on whatever level can safely not know a lot of these details and get a LOT done. Kudos to whoever deserves them here.
- charcircuit 4y agoIsn't that true for almost all software? You only need to know the implementation of a small subset of parts. I would say databases are worse since you need to know how they are implemented else you will start making O(rows) queries or doing other inefficient stuff.
- jrm4 4y agoGoing broadly (which is all I can do because I teach this stuff and don't build in depth) -- "the database" is the part I can most easily "abstract" away as if it were walled off? As opposed to aspirationally discrete classifications that end up being porous, e.g. MVC, "Object Oriented" etc.
- mjb 4y agoIntroductory material is always welcome, but I suspect this isn't going to hit the target for most people. For example: > Therefore, if the price isn’t an issue, SSDs are a better option — especially since modern SSDs are just about as reliable as HDDs This needs a tiny extra bit of detail: if you're buying random IO (IOPS) or throughput (MB/s), SSDs are significantly (orders of magnitude!) cheaper than HDDs. HDDs are only cheaper on space, and only if your need for throughput or IO doesn't cause you to "strand" space. > Consistency can be understood after a successful write, update, or delete of a row. Any read request immediately receives the latest value of the row. This isn't the ACID definition of C, and is closer to the distributed systems (CAP) one. I can't fault the article for getting this wrong, though - it's super confusing!
- googletron 4y agoYou are absolutely right about the C being more inline with CAP one. I have a post in draft to discuss disk trade offs which digs into this aspect, its impossible to dig into everything in this level of a post.
- jandrewrogers 4y ago> "Scale of data often works against you, and balanced trees are the first tool in your arsenal against it." An ironic caveat to this is that balanced trees don't scale well, only offering good performance across a relatively narrow range of data size. This is a side-effect of being "balanced", which necessarily limits both compactness and concurrency. That said, concurrent B+trees are an absolute classic and provide important historical context for the tradeoffs inherent in indexing. Modern hardware has evolved to the point where B+trees will often offer disappointing results, so their use in indexing has dwindled with time.
- hashmash 4y agoWhat kinds of indexing structures are used instead, and how do they differ from B+trees? Do you have examples of which relational databases have replaced B+tree indexes?
- xmprt 4y agoI know Clickhouse uses MergeTrees which are different from B+trees. However it can't really be used as an RDBMS. It's especially bad at point reads. https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree
- mikeklaas 4y agoThere are projects that use LSMTs as the storage engine for RDBMS' (like RocksDB); I'm not sure it's accurate to say "they can't be use as an RDMBS".
- petergeoghegan 4y agoIt's definitely possible, and can make a lot of sense -- MyRocks/RocksDB for MySQL seems like an interesting and well designed system to me. It is fairly natural to compare MyRocks to InnoDB, since they're both MySQL storage engines. That kind of comparison is usually far more useful than an abstract comparison that ignores the practicalities of concurrency control and recovery. The fact that MyRocks doesn't use B+Trees seems like half the story. Less than half, even. The really important difference between MyRocks and InnoDB is that MyRocks uses log-structured storage (one LSM tree for everything), while InnoDB uses a traditional write-ahead log with checkpoints, and with logical UNDO. There are multiple dimensions to optimize here, not just a single dimension. Focusing only on time/speed is much too reductive. In fact, Facebook themselves have said that they didn't set out to improve performance as such by adopting MyRocks. The actual goal was price/performance, particularly better write amplification and space amplification.
- SulphurSmell 4y agoThis article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the design of a database, imagine years later what someone looking at it might want to know if having to rip all the data out into some other store. Having migrated many legacy systems, I tend to sleep better when I know the data is well-structured and easy to normalize. In those cases, I really don't care so much about the apps. If I can sort out (haha) the data, I worry less about the new apps I need to design. I have been known to bury documentation into for-purpose tables...that way I know that info won't be lost. Export the schema regularly, version it, check it in somewhere. And, if you can, please, limit the use of anything that can hold a NULL. Not every RDBMS handles NULL the same way. Big old databases live a looooong time.
- emerongi 4y ago> Realize that any reasonably used database will likely outlast the applications leveraging it. I love this statement. It's true too, having seen a decades-old database that needed to be converted to Postgres. The old application was going to be thrown away, but the data was still relevant :).
- Yhippa 4y agoI think this is and will continue to be a common use case. I'm very thankful for these applications that the data was still stuck in a crusty old relational database for me to work on top of as I built a new application. It's going to be interesting when this same problem occurs years from now when people are trying to reverse schemas from NoSQL databases or if they become difficult to extract. The only sticking point is when business logic is put into stored procedures. On one hand if you're building an app on top of it, there's a temptation to extract and optimize that logic in your new back-end. On the other hand, it is kind of nice to even have it at all should the legacy app go poof.
- thedougd 4y agoI have to plug the "Designing Data-Intensive Applications" book. It dives deep into the inner workings of various database architectures. https://dataintensive.net/ https://dataintensive.net/
- donatj 4y agoI still think about my first job out of college. Shopping cart application, we would add indexes exclusively when there was a problem rather than proactively based on expected usage patterns. It's genuinely a testament to MySQL that we got as far as we did without knowing anything about what we were doing. One of my most popular StackOverflow questions to this day is about how to handle one million rows in a single MySQL table (shudder). The product I work on now collects more rows than that a day in a number of tables.
- Linda703 4y ago[dead]
- dennalp 4y agoReally nice guide.
- sonofacorner 4y agoThis is great. Thanks for sharing!
- Merad 4y ago> a dirty read occurs when you perform a read, and another transaction updates the same row but doesn't commit the work, you perform another read, and you can access the uncommitted (dirty) value It's even worse than this with MS SQL Server. When using the READ UNCOMMITTED isolation level it's actually possible to read corrupted data, e.g. you might read a string while it's being updated, so the result row you get contains a mix of the old value and new value of the column. SQL Server essentially does the "we got a badass over here" Neil deGrasse Tyson meme and throws data at you as fast as it can. Unfortunately I've worked on several projects where someone apparently thought that READ UNCOMMITTED was a magic "go fast" button for SQL and used it all throughout the app.
- jiggawatts 4y agoI really wish SERIALIZABLE was the default transaction isolation level and anything lower was opt in… with warnings.
- hodgesrm 4y agoSERIALIZABLE is ridiculously slow if you have any level of concurrency in your app. READ COMMITTED is a reasonable default in general. The behavior GP is describing sounds like an out and out bug. Dirty reads incidentally weren't supported for quite some time in the Sybase architecture (which forked to MS SQL Server in 1992). There was a Sybase effort to add dirty read support around 1995 or so. The project name was "Lolita."
- throwaway787544 4y agoCan anyone give me a brief understanding of stored procedures and when I should use them?
- galaxyLogic 4y agohttps://github.com/prql/prql https://github.com/prql/prql : " Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it transpiles to SQL. "
- molly0 4y agoAnyone read this pdf/book https://sql-performance-explained.com https://sql-performance-explained.com and would recommend?