[HN Gopher] Show HN: I wrote a free eBook about many lesser-know... ___________________________________________________________________ Show HN: I wrote a free eBook about many lesser-known/secret database tricks Author : tpetry Score : 309 points Date : 2022-12-02 17:43 UTC (5 hours ago) (HTM) web link (sqlfordevs.com) (TXT) w3m dump (sqlfordevs.com) | aloukissas wrote: | Thank you! I've been following your Twitter feed for a while and | having all this in a single ebook is amazing. | chasil wrote: | I 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 | danenania wrote: | This looks great! Awesome that you made it free, but fyi I would | have paid you $5-10 for this in a heartbeat. | llaolleh wrote: | Yes. Please add donation button. | tpetry wrote: | Thank you both. But correctly filling taxes for those | donations would be more effort than it is worth. So i'll just | keep it free :) | danenania wrote: | Very 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 :) | rendall wrote: | Who are these flying monkeys gaslighting about very standard | hesitation about handing over email? No. Not doing it. | mdip wrote: | I 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". | proto-n wrote: | Can you recommend some resources to achieve (b)? Just basic | stuff without going full guru on every little detail. | | Or do you just mean using indexes basically? I'm just not sure | how many unknown unknowns I have wrt sql. | rudasn wrote: | The 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. | jmhnsn wrote: | Thank you for making this available, and for free. Nice work. | btown wrote: | This 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-postgresq... is an amazing way to have a foolproof work | queue without introducing things like Redis!) | andrewl wrote: | I agree on the privacy policy. (I also agree that this looks | good, and I want to sign up.) | mgkimsal wrote: | FWIW, the initial email is showing up in my gmail as 'promotion', | not spam. Hope that helps some. | jrvarela56 wrote: | Has 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. | dandruff wrote: | I haven't either, but my best guess is that there's a | bottleneck going on due to the site currently being on HN's | frontpage | jmhnsn wrote: | Mine came in an email attachment after activating the | newsletter sign up. | cdrini wrote: | I 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! | nop_slide wrote: | This 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. | uoaei wrote: | Am 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. | bastardoperator wrote: | Love it, great work! | handzhiev wrote: | I 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. | tpetry wrote: | In 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. | tpetry wrote: | Gmail now started to classify the email with the ebook | attachment as spam. After having worked without any problems | before. I am now changing my code, give me a few minutes. | | _Fixed_ | tomrod wrote: | Worked flawlessly just now. | [deleted] | tuukkah wrote: | May 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 wrote: | Don't we all have burner addresses? I mean, even Apple | provides them. | qup wrote: | It's a 50-page book. If you actually look at what's presented | on the landing page, three of the topics have the entire page | shown, so you can see exactly what topics will look like. | courgette wrote: | I agree, I had a good idea of what you proposed just | sliding on my phone. | | Let me give you an email in exchange of that bag of tricks. | | Out of curiosity: what's happening with the email? ( out of | honesty: you will get my spam ridden gmail ;) ) | tpetry wrote: | I'll send you an email when I have written more full- | length articles like the ones under ,,Tips" in the | navigation. | qup wrote: | I'm not the guy, so I don't know. But if I don't like | what he does with it, I'll set a filter in about 25 | seconds to send the email to the pits of /dev/null | sakopov wrote: | This looks awesome! Thanks for sharing this! Mind me asking | what you used to write this ebook? | tpetry wrote: | I've used ibis https://github.com/themsaid/ibis | mooreds wrote: | Thank you! Writing a book is hard work, esp a technical one. | Thanks for sharing your knowledge. | sandreas wrote: | Awesome, 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/ | revskill wrote: | Thanks. One suggestion: Could you put all materials in github for | contribution ? | auct wrote: | Nice tips. I'm interested in 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 wrote: | These 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 wrote: | A person gives you a whole book they wrote for free and you're | complaining about asking for an email? WTF | pubby wrote: | It's not exactly free if they're asking something in return. | selykg wrote: | I only have like a dozen email addresses. Several of them are | for junk mail and subscription type stuff. Do you... not have | a junk email address? I would strongly encourage you to start | on that, because, even just a stupid gmail account you never | use for anything important but use for junk is probably a | life changing situation for you if you've never heard of | doing something like this. | | Edit: Super power here is setting up a 1Password Identity (or | whichever tool you use, hell, even a snippet) to auto fill | junk email in when signing up for stuff vs not-junk email. I | have three identities. One for work, one for personal, one | for junk. Makes this stuff super easy and fast. | pubby wrote: | I wasn't trying to make a point about price (e-mails are | practically free). The book could cost half a penny and my | comment would still stand. People just view items that cost | something differently than genuinely free items, and not in | a rational, economic way. | | For example, see: https://www.npr.org/sections/money/2012/0 | 7/13/156737801/the-... | nabakin wrote: | YouTube asks you to watch ads, but we still consider it to be | free. Many mobile games make you create an account (and | provide your email) before you can play but we still consider | them free because you don't have to give any money. That's | the definition here: don't have to pay money = free. This | ebook is free | varispeed wrote: | You don't value your time = free | | Other people do value their time, therefore it's not free | for them. | nabakin wrote: | The question here is whether this ebook meets the | definition of free as it is commonly known/accepted. As | can be proven by the general population calling YouTube | free or a mobile game that requires an email free, this | ebook can be called free | giraffe_lady wrote: | scrubs wrote: | Agree. The guy provides engineering know how and we're pigeon | dropping all over it for bureaucratic dorkish nonsense? You | gotta have an attention span that separates noise from value | evilduck wrote: | Because I weigh the potential of being marketed to by email as | being a negative value greater than the potential positive | value of the information provided in exchange. 'Free (with the | potential to spam you later)' is a world apart from 'Free, | gratis, enjoy'. | FooBarWidget wrote: | The author loses much more than you. You can always | unsubscribe, or -- if you don't trust unsubscribe -- filter | away his emails. From the beginning you were always in full | control. Giving your email grants the author no power over | you whatsoever. | marktangotango wrote: | You are being down voted because this is a shallow view. | The expression of interest tied to an identity has value. | For proof see Google, Meta, Amazon, and Facebook earnings. | christophilus wrote: | Wildcard emails for the win. | rendall wrote: | No, it's not weird to not want to give up an email address before | knowing the value proposition, qup and icedchai. | courgette wrote: | I'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 ) | pachico wrote: | I know your work. Thanks for everything you do, mate! | f0e4c2f7 wrote: | Database 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 wrote: | You 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. | deltaseventhree wrote: | rrgok wrote: | This 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. | kevstev wrote: | Honest 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. | ren_engineer wrote: | long term value of an email is far greater than ads, especially | for a developer audience that has probably 90% ad block usage | rate | martin-adams wrote: | If 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 wrote: | Exactly, I constantly write full-length articles I share with | subscribers. If you don't want it just unsubscribe, I am not | mad at you. | nezaj wrote: | Learned a few things -- thanks for putting this together! | FredPret wrote: | Thanks 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. | leke wrote: | Hmm...I subscribed, but after clicking the activation link, | nothing was sent. Anyone else experience this? | zellyn wrote: | yep | tpetry wrote: | Gmail 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 wrote: | Yep. Working now. Thanks. | mariusmg wrote: | How 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 wrote: | Gmail 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 wrote: | Got the email now (after retrying the subscription). | | Thank you for the free book. | midnightmonster wrote: | This 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 | comradesmith wrote: | Thank you Tobias | adamc wrote: | I have a very negative reaction to sites that require an email to | get something of unknown value. | | Automatic no. | icedchai wrote: | The value is clear if you look at the landing page. There's | some interesting techniques in this book, for sure. (I've | worked with SQL databases for over 20 years. Several of them I | hadn't seen.) | | Providing an email address seemed like a fair trade. | layer8 wrote: | I'm okay with an email address (can just use a throwaway), but | I get annoyed when they want you to fill out name, occupation, | etc. | tr33house wrote: | can someone share the pdf here for folks who don't wanna share | their email? | NikolaNovak wrote: | I wouldn't. | | Author is offering their work at a given price. That price is | email. It may be worth it to you or not. Requesting a pirated | copy of it without paying the price is not something I | encourage,given so many of us here are well to do knowledge | workers whose live being depends on people paying for our | work. | | (Not to mention, it's same or less work for you to create a | temp email account, vs somebody else doing the work of | signing up, sharing their email, and uploading and hosting | content for your convenience). Yes yes information wants to | be free and all that, but this is just lazy :-D | remram wrote: | You say that, yet every paywalled news article posted on HN | quickly gets a web.archive.org or archive.is link as a top | comment. What is the fundamental difference with this? | tr33house wrote: | agree. Thanks for changing my perspective | tpetry wrote: | To be honest. Just subscribe and the next time I wrote a free | article and share it with you unsubscribe. I don't spam you. | Ans I don't mind when you only want to grab the ebook. | ylg wrote: | The problem is not unknown value. It's unknown cost. Until | I know everything you will ever do to me with my email I | don't know the true cost. What I can guess--given the | thousands of precedents I've encountered over the years--is | that the total cost will be time taken from my short life | to fend of yet more marketing and yet more marketers. For | comparison, if you ask me for currency, I know the total | cost of ownership. Usually, for me, a few units of currency | are a rounding error compared to the value I place on time. | ibdf wrote: | The total cost is how much time it takes to google "temp | email" and then click on the first link and use that temp | email to get the book. It should take about 2 minutes, | maybe even less time than writing out your comment. | jrvarela56 wrote: | I have a negative reaction to sites that require email. | | In this case, the value was made pretty clear in the homepage. | | I was happy to only have to exchange my email for what appears | to be a very high quality resource - the author is either | extremely generous or undervaluing their knowledge! | qup wrote: | That'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. | joe_guy wrote: | Why 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? | _jal wrote: | > 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. | peruvian wrote: | Take a shot every time a HN pedant types "Why don't you | just X". | tpetry wrote: | I am already writing every tip as a blog post. But those | blog posts take a full day to write as they are much more | detailled compared to the short summary in the ebook. My | time is limited like yours, and the short ebook is the best | way to spread the knowledge: You get the information now, | and I'll notify you when those full articles are written. | qup wrote: | Hey man, thanks for the book. | | Don't forget you don't owe these guys what they're asking | for. You've done the work to share your knowledge, they | can do the work to gather it. | rendall wrote: | tpetry, I suggest you call off qup. He comes off as a | sockpuppet, and I'm sure you don't want to insult your | potential audience the way he is doing. | googlryas wrote: | You, the reader, don't have a choice between a series of | blog posts and an emailed pdf. | 411111111111111 wrote: | I think that's true, but I'm also one of the people that's | going to bounce from that. | | While several chapters have mildly interesting titles, what | I've come used to from these kinds of offers are mostly just | rehashed blog articles from other sources. I remember one | extra special case about an Elixir "book" that pretty much | just copy-pasted the official getting started guide. | | Generally speaking, these kind of hurdles don't inspire | confidence in the quality of the content. | qup wrote: | But you can see the actual content. 6% of the book is on | display on the landing page. I think you should just judge | the quality of that instead of inventing ways to make | assumptions based on other factors. | uoaei wrote: | Your definition of value differs from that of others'. | qup wrote: | Yeah, of course it does. Fully agree. I don't understand | the point you're making. | | I haven't offered my definition of value. I just said that | you can clearly determine the value to yourself by reading | the landing page, it has the relevant information required | (besides actually reading all the content). | uoaei wrote: | And what of values inherent in privacy concerns? | | It just seems like you're applying a very narrow view of | what can be considered "value" here. | quijoteuniv wrote: | What about a throaway email? ___________________________________________________________________ (page generated 2022-12-02 23:00 UTC)