30 ms·
Show HN: I wrote a free eBook about many lesser-known/secret database tricks
- quickthrower2 4y agoNice. I presume this is for a flavour e.g. Postgres?
- thangngoc89 4y agoBoth MySql and Postgres
- throwaway81523 4y agoBook looks good, shovelling the subscription at me was annoying and of course I used a throwaway address and unsubscribed immediately. It's actually probably a newsletter I wouldn't mind getting but the way it was done feels too spammy. I have Markus Winand's book SQL Performance Explained, actually paid for the download, and came away feeling cleaner. I do hear something from him through his mailing list once in a great while, and that is fine. I hope he comes out with another book someday.
- a2800276 4y agoNot only that, but every other chapter of the "book" ends in: "I have written a more extensive text about this topic on my database focused website ..." pushing their blog. It's unclear why. If anything, I would expect the more extensive text in the "book". At any rate, it starts to feel like a wild goose chase after a while and my expectation is that I'll have to fill something else out or watch a ton of ads before being show fluff content if I click on a link in a book that I had to sign up for newsletter to download.... I wouldn't know though because I'd rather whine about it here than actually follow the link :)
- throwaway81523 4y agoYikes. Yeah I haven't looked at the pdf yet, but the idea of a book is to get all the info in one place, I'd hoped. If every chapter leads me to another web page, the book might as well not be there. Tpetry, I get the idea that you don't want to give away the book outright. That is fine. Some people do that, but not everyone, and it's ok if you don't. But please, don't put us through this nonsense. Just charge money for the book, and make it so that people who pay up get everything in a single download, no subscriptions, no upsells from individual chapters. I am interested in such a thing, enough to probably be willing to buy a copy. Thanks for your consideration.
- gaws 4y agoFor those who don't want to give their email, link to the PDF: https://file.io/5bJFEXNyPi8s https://file.io/5bJFEXNyPi8s
- ranger_danger 4y ago>The transfer you requested has been deleted.
- gaws 4y agomirror: https://files.catbox.moe/zmju2p.pdf https://files.catbox.moe/zmju2p.pdf
- WesleyJohnson 4y agoI'm fairly adept at writing queries and have been using MySQL for what seems like an eternity. The multiple aggregates per query thing kinda blew my mind. I've certainly done that by using something like SUM(CASE WHEN x = y then 1 ELSE 0), but did not know you could just COUNT(x=y). Awesome.
- RustLove 4y agoWow, I had no idea you could do multiple aggregates in a single query like that. I've always used the SUM(CASE...) method, but the COUNT(x=y) way is much cleaner and more efficient. Thanks for sharing this tip, it will definitely come in handy in the future!
- deleted 4y ago[deleted]
- timemachine 4y agoNice effort on an interesting topic. However, the book could use a better technical review. For example the first recipe will not work as expected with out a multi-column unique index (specifically the MySql example). I feel that the preexisting knowledge required for such examples does not align with the stated target audience for the book.
- Simon_O_Rourke 4y agoAwesome - thanks for sharing your hard work! Some very new tricks to me!
- tobyhinloopen 4y agoI must be an idiot: where can you get the book?
- hgrth6644fd23 4y agoThe overall mantra of this book is: Don't reimplement database features in your application that the dbms can do for you. I really strongly agree with this, and even if you don't recall any of the concrete recipes in this book then this mantra will hopefully stick.
- tgv 4y agoDon't make it a hard rule, though. There are exceptions to everything. Sometimes it's easier, faster or more flexible in the client.
- vincnetas 4y agoThis is gold. Thank you very much.
- djmips 4y agoI don't want to spam the comments but I also want to say thanks!
- px1999 4y agoIt might be worthwhile mentioning somewhere that this is for mysql and postgres. While it does look really handy and well put together, unfortunately none of the tips in the book that I read before giving up are useful to someone using mssql
- overtomanu 4y agosome more tips and tricks https://blog.jooq.org/sql/ https://blog.jooq.org/sql/ https://winand.at/sql-slides-for-developers https://winand.at/sql-slides-for-developers
- zasdffaa 4y agoI don't want to diss this, it's useful to some but to say "Your database knowledge is outdated" is presumptuous to the point of insulting. It's basic to intermediate level stuff, like using CTEs to break up queries (and you don't discuss how this can overload the optimiser), and doing multiple aggs in one select. I also can't C&P from your pages as they're images, but Ghost Conditions Against Unindexed Columns has '... AND type = in (3, 6, 11)' - is that right or did you mean 'type in (...)'. It also talks about multi-col indexes as being more useful in some cases, true, but very elementary. This is good and well done, but please don't oversell it.
- benji-york 4y agoI get an error when trying to unsubscribe from the mailing list.
- fefe23 4y agoMaybe it's me but I wasn't shown a free ebook. I was shown a "give me you PII so I can spam you" trap website. If you ask me to give you my PII then it's not free, is it? Are you not aware how many "free ebook" spams are being sent around, usually from disreputable SEO people begging you to link them from your site? The fact that you registered your own domain is another deep red flag. Clearly there are ulterior motives here aside from "I want to help the world by putting our a free ebook".
- ThisIsTheWay 4y agoIs it really that hard to use a burner email?
- fefe23 4y agoIt's not about being asked for an email. It's about being lied to. You wouldn't be OK with the New York Times writing that Putin died of a heart attack when it's not true. Why would you be OK with this kind of deception then? If I click on your link, I took a leap of faith on you. If I then find out that you lied to me, you made my day worse and innocent bystanders like the next guy who actually did have a free ebook will get take the damage. He could have truthfully said: Show HN: Ebook here but you'll have to enter an email address. Then I wouldn't have clicked and we wouldn't be having this discussion now. I suspect the link wouldn't have been upvoted enough to appear on the front page either. Also note that if I use a burner account from some spam catching service, I'm externalising my damage on to them. They might be OK with that but it's still shitty behavior on my part. So I don't use throwaway accounts. It's a moral thing. You don't litter in the park. You leave it cleaner than how you found it.
- ThisIsTheWay 4y agoFirst, the word free is most frequently interpreted as not costing money, which in this case is completely true. Second, your comparison to a false claim by the NYT is absurd. All you had to do was close the tab and move on. I can't imagine getting through life making this big a deal on such a minor inconvenience. Good luck out there.
- Ozzie_osman 4y agoThis was a great read, but I was surprised not to see window functions in here. They are so powerful and I'd estimate that less than 10% of developers I've worked with even know they exist.
- AdrianB1 4y agoThe tricks for MySQL and Postgres are very different than MS SQL; some exist in a different form, some have no equivalent. It would be useful to have a side by side for all major flavors of SQL, Oracle included. LE. Almost nothing is applicable for MS SQL; some stuff is built into T-SQL, so there is no need for tricks, others have completely different solutions.
- uwwuuwhshshhdh 4y agohttps://news.ycombinator.com/login https://news.ycombinator.com/login
- gofreddygo 4y agoWith all the "I'm not giving you my email" sentiment, author could do a quick thing. Red button: Give me $5 Blue button: Give me your email. Everyone's happy! Sharing the results would be an icing on the cake.
- auct 4y agofunny that if someone choose $5 he still need enter email to get book ;)
- benjy77 4y agoMight there be an error in "Simplified Inequality Checks With Nullable Columns" for mysql? NOT(column <> 'value') seems to be wrong Did you mean NOT(column <=> 'value') ?
- tpetry 4y agoIn isolation because of Corona last year, I started to share many lesser-known database tricks I know on Twitter (@tobias_petry). Although I thought that only a few people would be interested in something like this, it became a matter of course over the months. Meanwhile, thousands follow me to read my database tips or news. Since every message in the constant social media stream vanishes after a few days, I had to do something about it. Knowledge must be preserved. I sat down for a few weeks and reworked every example and every text to create an ebook that you can read in an evening and still impart tons of knowledge. And so "Next-Level Database Techniques for Developers" was born.
- dev_dude 4y agoThanks for all the valuable tips. It's amazing every time how much load a properly configured database can reduce. Especially the tips for optimizing indexes are very good. "Partial Indexes for Uniqueness Constraints" is my most favorite.
- elric 4y ago@tpetry: I enjoyed reading the book. Could you please have it added to the Goodreads database so I can leave a positive review?
- tuukkah 4y agoMay I suggest providing a sample chapter before requiring an email address? EDIT: There are 10 sample pages (images) but on Firefox they are so small you might not notice them. The pages are in a row flexbox container which on Chrome overflows with a scrollbar but on Firefox scales all the way down to fit everything in view at once. Please add "min-width: 45%" in addition to the "width: 45%". Accessibility would also require links to the textual versions.
- copperx 4y agoDon't we all have burner addresses? I mean, even Apple provides them.
- 4y ago
- f0e4c2f7 4y agoDatabase knowledge is something that has long been on my list of things to brush up on. Hard to beat free. I'll likely give this a read. Thanks for writing it!
- tpetry 4y agoYou are not alone. Databases are so easy to use, and that is very good! But due to that simplicity most devs never learn more than the basic statements, but there is so much more that can be used.
- danenania 4y agoThis looks great! Awesome that you made it free, but fyi I would have paid you $5-10 for this in a heartbeat.
- llaolleh 4y agoYes. Please add donation button.
- tpetry 4y agoThank you both. But correctly filling taxes for those donations would be more effort than it is worth. So i‘ll just keep it free :)
- ylg 4y agoIf you change your mind, it shouldn’t be hard to handle the taxes; I think the approach would be the same as for hobby income. If you’re in the US and filing taxes as a employee I think you’d just sum up the book sales and stick that under Other Income. If you’re already filing as a business or self employed, just roll it in no? Of course, other countries are available. Anyway, I’d happily pay a few bucks at the very least for this. If it’s useful, it’s going to help my own income. If I see people who maybe can’t afford a few bucks can get it for free, I’ll be even happier to pay.
- danenania 4y agoVery generous! Fwiw I bet you could make significant money (5 figures) if you made a longer version and charged in the $20-50 realm. It's hard to find quality up-to-date resources on these topics and the info is extremely valuable--the interest on HN attests to this. Also totally cool if that's not interesting to you or not your thing :)
- bastardoperator 4y agoLove it, great work!
- deltaseventhree 4y ago
- adamc 4y agoI have a very negative reaction to sites that require an email to get something of unknown value. Automatic no.
- qup 4y agoThat's an interesting take, given that the landing page demonstrates its value with some example pages, and the index of topics. I can skim the chapter titles and get a pretty good idea of exactly what value there is. I'm not sure what's holding you up.
- qiller 4y agoWell, for example, intro doesn’t say which SQL flavor the author is targeting. As a MSSQL user I wouldn’t want to share my email in exchange for MySQL tricks
- joe_guy 4y agoWhy not a series of blog posts? To me, the reader, what value does sharing my email or it being in The incredibly less flexible "ebook" format have?
- googlryas 4y agoYou, the reader, don't have a choice between a series of blog posts and an emailed pdf.
- deleted 4y ago[deleted]
- deleted 4y ago[deleted]
- _lqaf 4y ago> Why not a series of blog posts? It seems pretty self-evident that they wish to exchange something of value for something else of value. This is a pretty common activity among humans. Unfortunately, as we see here, "instead, why don't you do even more work to change it to my specifications and then give it to me for free" is a common counteroffer.
- btown 4y agoThis is amazing! Would definitely recommend you have a privacy policy though, as you're collecting personally identifiable information! (Also, one of my favorite techniques along the lines of your Lock Contention example: https://www.enterprisedb.com/blog/what-skip-locked-postgresql-95 https://www.enterprisedb.com/blog/what-skip-locked-postgresq... is an amazing way to have a foolproof work queue without introducing things like Redis!)
- andrewl 4y agoI agree on the privacy policy. (I also agree that this looks good, and I want to sign up.)
- auct 4y agoNice tips. I'm interested in https://sqlfordevs.com/sorted-table-faster-range-scan https://sqlfordevs.com/sorted-table-faster-range-scan Would you use this approach for something like: 1) movie_actors (movie_id, actor_id, order_index) 2) movie_watchlist (movie_id, user_id, created_at) 3) movie_ratings (movie_id, user_id, rating, created_at) I know strange question, but most guides suggest all these table should have autoincrement id column
- tpetry 4y agoThese index choices are solid. But I suggest for the second one to swap the movie_id and user_id because in most cases you want to get all the watchlist entries for a user and not all users having the movie on the watchlist. Therefore all movies on a users watchlist are close to each other and not spread through the whole table by the movie_id.
- pashabitz 4y agoA person gives you a whole book they wrote for free and you're complaining about asking for an email? WTF
- kretaceous 4y agoYeah, I was sad seeing some of the reactions. To each his own. For a student like me, I would gladly take a free resource and won't mind the newsletter. Maybe OP can also put a donate button for people who don't want to provide their E-Mail and get the book. Thanks a lot, tpetry!
- hgrth6644fd23 4y agoThe criticism is that it's not honest. "For free" is not correct. It's "book against harvesting your contact data". If that's what the first page would say, then it would be honest and nobody would be upset. But acting as if it's been "published" out of pure altruism is just misleading people, and they are righfully pointing that out.
- enraged_camel 4y agoI took it to mean “free as in beer”
- yakshaving_jgt 4y agoWould you pay money in lieu of providing your real email address? If so, how much?
- gbear605 4y agoI’d probably pay $10, that’s worth less to me than my contact info.
- ranger_danger 4y agoJust use a throwaway mail, what's the big deal? Companies already have tons of your private data without you ever even interacting with them. You're just crying into a 50 gallon drum of milk.
- midnightmonster 4y agoThis is cool! Re: multiple aggregates in one query, I wrote a Ruby gem that adds an even-more-powerful and multi-database-compatible version to ActiveRecord. (I have tested Postgres, MySQL, and SQLite; others probably work, too.) https://github.com/midnightmonster/activerecord-summarize https://github.com/midnightmonster/activerecord-summarize
- jmhnsn 4y agoThank you for making this available, and for free. Nice work.
- jrvarela56 4y agoHas anyone been able to get the ebook? I submitted my email and confirmed the subscription, but no PDF sent I can only see the Tips section in the website.
- sandreas 4y agoAwesome, thank you. If you are on Microsoft SQL Server I additionally recommend to take a look at Brent Ozars sp_blitz[1] script collection and the according youtube series. Many optimizations apply for other databases, too. [1]: https://www.brentozar.com/blitz/ https://www.brentozar.com/blitz/
- AdrianB1 4y agoOr even contribute with code: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit https://github.com/BrentOzarULTD/SQL-Server-First-Responder-...
- rendall 4y agoNo, it's not weird to not want to give up an email address before knowing the value proposition, qup and icedchai.
- courgette 4y agoI’m confused, I was able to see 10 pages each exposing a tricky SQL request with modern SQL features. Maybe it’s been added in the face or HN grumpyness? ( tbh : I would not have give anything without those extracts, so your comments in on point )
- icedchai 4y agoThe value proposition was clear to me from the landing page. Maybe it wasn't to you. I could read enough of it to say "hey, this sounds interesting, I wouldn't mind hearing more from this person." Also, SQL databases are one of my favorite tech topics. Postgres, especially, has tons of cool features (example: lateral joins.)
- rrgok 4y agoThis is amazing. I would gladly donate a small amount. I already learned lateral joins. I didn't know something like that existed. I'm fascinated by DBMS each day I use them.
- mariusmg 4y agoHow exactly do you obtain that ebook after giving away the email ? I've "activated" the subscription (sigh...) but i don't see any download link.
- tpetry 4y agoGmail started to mark the email with the PDF as spam. It worked perfectly before this trended on hn. I guess I triggered some rate-limiting rule. If you activate the subscription again you now get a download link. And I will look at the stats tomorrow and send everyone the PDF who didn't get it because of the false spam classification.
- mariusmg 4y agoGot the email now (after retrying the subscription). Thank you for the free book.
- mdip 4y agoI haven't read the whole thing but I really enjoyed the introduction and agree completely. The RDBMS I've spent the most time with over the years is Microsoft SQL (immediately when 2000 was released) and over the years I've been surprised by two things: (a) How many developers that work with databases every day yet haven't the foggiest idea how to do anything the GUI tooling doesn't handle "easily" and (b) How quickly a developer can go from zero to "able to keep things performing well for all but the ugliest scenarios" knowledge-wise. I've watched that last part unfold on many occasions. Usually I'd be brought in because the developers have done everything down to "copying the data to denormalized tables[0]" to try to sort out slowness. Most of the time, just "blindly investigating the schema" will turn up something frightening that the ORM did, or a mess of inappropriate indexes. Two indexes on a table used by nearly every query in the database caused 30 second requests to yield sub-second results in one case[1]. I'm never willing to walk in and promise that, but I can't think of a time it hasn't happened when similar circumstances were presented to me. So if you're dragging your feet about learning SQL, take this as my encouragement: it's one of those things where the rewards come quick and the effort is far less than you probably expect. [0] ... with a broken sync process that has to be run carefully b/c it hammers the already over-sized database every time it fires. [1] If memory serves, it was an account table ... used a GUID between the app and the database, but primary key was an integer auto-incrementing field which was used as the FK to other tables. All I remember was adding a unique index to the GUID field and including the e-mail address/name columns which were included in every query. Ran the fix in production and it felt like "the dam broke".
- rudasn 4y agoThe last few years I've been working on a fairly big django project written by someone learning the problem domain and python/django at the same time. I have a lot of similar stories and I you are absolutely right to encourage people to learn the basics, at least (looking at you, indexes and perf tools). We're talking about having our cpu hitting 80-90% and rising internal temperature to 70-80C, while executing a task thats normally performed tens or hundreds of times during the workday. A couple of carefully planned indexes, a bit of sql shuffling, is all it takes sometimes. The upside of working with cheap bare metal servers is that you catch these things early on (5 concurrent users and your server is toasted). Fun times.
- uoaei 4y agoAm I missing something on the website? The offering is called an eBook but I'm not seeing any way to download a local copy as PDF or otherwise.
- leke 4y agoHmm...I subscribed, but after clicking the activation link, nothing was sent. Anyone else experience this?
- zellyn 4y agoyep
- tpetry 4y agoGmail started to mark the email with the PDF as spam. It worked perfectly before this trended on hn. I guess I triggered some rate-limiting rule. If you activate the subscription again you now get a download link. And I will look at the stats tomorrow and send everyone the PDF who didn't get it because of the false spam classification.
- leke 4y agoYep. Working now. Thanks.
- mgkimsal 4y agoFWIW, the initial email is showing up in my gmail as 'promotion', not spam. Hope that helps some.
- nop_slide 4y agoThis is great! Really appreciate you giving this out for free. Only critique is that it would be super useful to include some example query results for each section so show the effect.
- handzhiev 4y agoI actually appreciate asking for email, otherwise the most likely outcome is I will download the book and forget it. If there's some kind of a follow-up, there is a better chance to be reminded of it and read it.
- comradesmith 4y agoThank you Tobias
- aloukissas 4y agoThank you! I've been following your Twitter feed for a while and having all this in a single ebook is amazing.
- chasil 4y agoI downloaded the book, and the size can be reduced slightly with pdfsizeopt. I don't have any of the extended filters, but it is a small size reduction with the default load: $ ./pdfsizeopt next-level-database-techniques-for-developers.pdf ... info: generated object stream of 7399 bytes in 224 objects (9%) info: generated 387518 bytes (89%) $ stat -c '%s %n' next*.pdf 435483 next-level-database-techniques-for-developers.pdf 387518 next-level-database-techniques-for-developers.pso.pdf
- kevstev 4y agoHonest question- why does one choose to harvest emails for something like this vs just putting ads on the page? I have seen this model used for several ebooks, but it seems like long term its much more viable to just make the content widely available and put adwords on it or something similar.
- ylg 4y agoYou’re on the right track, the immediate value of the email addresses is near nil. Instead consider these email addresses are highly self-targeted due to the nature of the sign up. That can then be sold for direct use, for resale, or most likely for inclusion in larger data sets that aggregate tracking information about individuals for ad targeting and browsing/results personalization. For example, consider the value to Amazon to be able to match this expressed interest with the email address of my Prime account; they could then push SQL-related content on my Amazon front-page with justifiable confidence I’d buy something vs the default of showing me canned kidney beans. Of course, that they show me canned kidney beans _because_ I just bought an SSD does show how this is mostly pie in the sky/skynet nonsense.
- martin-adams 4y agoIf you ever want to release an update or launch a new product or book, you can now communicate directly with those who are already interested in your work.
- tpetry 4y agoExactly, I constantly write full-length articles I share with subscribers. If you don‘t want it just unsubscribe, I am not mad at you.
- ren_engineer 4y agolong term value of an email is far greater than ads, especially for a developer audience that has probably 90% ad block usage rate
- nezaj 4y agoLearned a few things -- thanks for putting this together!
- cdrini 4y agoI get the frustration about the email, but that aside, I do wonder whether requiring the email before getting the book is the most effective mechanism for your end goal. What do you want to achieve? Do you want to maximize the number of people subscribed? Do you want to create a community? Why do you want these things? Do you want to share your knowledge? Having no email-wall would probably result in fewer registrations. But I do wonder if you had all the pages available -- meaning they would be indexed by Google and show up in search results -- with a subscribe to my newsletter button, if that would have a comparable number of registrations because you would have more people seeing the button? And they might be potentially higher quality registrations, too, if you want a community. If your end goal is to share your knowledge then making the entire book public is probably the better option. In short I'm just confused what the goal of the email-wall is!
- edmundsauto 4y ago"Create a list of people who want to receive database tips via email" is almost a tautology at this point, but seems like a reasonable objective. Also is a good way to weed out people who want just a 1-way relationship. I, for one, wouldn't care about not distributing my knowledge to people who aren't even willing to drop their email in exchange. Those people want something but aren't willing to give me anything - that feels really greedy. Building an audience is incredibly difficult. People who don't want to be in my audience - I'm totally fine if they don't get access to my stuff.
- randStr_ 4y agoI didn't put my email to get the book. I would probably have subscribed, if it was something like "if you enjoyed the content, do subscribe to get more". Instead, it advertised free, then there is the wall. Like many others, I feel like I've been drawn to a false promise. I totally understand the author can keep the book behind whatever wall he wants. The title could have been "I wrote a book about ...". And then in the site, "subscribe to get the book".
- revskill 4y agoThanks. One suggestion: Could you put all materials in github for contribution ?
- pachico 4y agoI know your work. Thanks for everything you do, mate!
- FredPret 4y agoThanks tpetry! Free content and all I have to do is get notified when there is more. If this ever gets printed, I’d pay for it.
- rendall 4y ago
- matthewwolfe 4y agoStep 1. Make a temp email Step 2. Get book download Step 3. Move on with your life instead of complaining
- rendall 4y agoWhat's the point of gathering burner emails?
- danparsonson 4y agoYou could just have one - it's a useful tool in the box.
- freebookthanks 4y agoThis is a great read, but for those who would rather not provide their email address until they've read the book, here's a download link: https://sqlfordevs.com/ebook-download?expires=1670195527&signature=5cad39b976d7e3040cc6220f2bf896277b5de0e095dc4035456da5b8071b7c62 https://sqlfordevs.com/ebook-download?expires=1670195527&sig... Archived copy: https://web.archive.org/web/20221202231259im_/https://sqlfordevs.com/ebook-download?expires=1670195527&signature=5cad39b976d7e3040cc6220f2bf896277b5de0e095dc4035456da5b8071b7c62 https://web.archive.org/web/20221202231259im_/https://sqlfor...
- cfeduke 4y agoThanks for this - I had provided my email address because I feel like I'd like the content of his mailing list regardless of the ebook offer, but the ebook never arrived via email (not even in spam).
- bachmeier 4y agoJust FYI, I use Fastmail to create a masked email. If that email address starts flooding my inbox, I can kill it.
- remram 4y ago-- MySQL SELECT * FROM example WHERE NOT(column <> 'value'); -- PostgreSQL SELECT * FROM example WHERE column IS DISTINCT FROM 'value'; That doesn't seem equivalent?