Hacker News new | past | comments | ask | show | jobs | submit login
Show HN: I wrote a free eBook about many lesser-known/secret database tricks (sqlfordevs.com)
865 points by tpetry on Dec 2, 2022 | hide | past | favorite | 174 comments

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.

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.


Really wonder how you "fixed" Gmail :).

Anyway thanks, it's great.

Worked flawlessly just now.

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.

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.

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 ;) )

It says you're subscribing to the newsletter

I‘ll send you an email when I have written more full-length articles like the ones under „Tips“ in the navigation.

Why not you publish them in a blog and provide a public RSS? Some people prefer those to newsletters.

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

Don't we all have burner addresses? I mean, even Apple provides them.

Mailinator, but the free plans have no attachments.

temp-mail.org/ supports attachments I find it often works where mailinator is blocked.

Thanks 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.

This looks awesome! Thanks for sharing this! Mind me asking what you used to write this ebook?

Decrease is misspelled on p48.

Columns is misspelled on p33.

You may wish to run a spellcheck to find the others I didn't.

My disposable email I used to sign up has already been nullrouted. Rather than tit-for-tat collecting PII, why not just publish it to the whole web without the extra hoops? I'd have already sent you a PR in that case with more copy edits.

It's not about PII, it's a comms channel to db pros. And yes, a good email list like that is quite valuable, and he wouldn't get that just sharing the resource as you suggest.

A whole bunch of burner addresses of people who are hoping to one day be db pros must be REALLY valuable.

@tpetry: I enjoyed reading the book. Could you please have it added to the Goodreads database so I can leave a positive review?

Thank you! Writing a book is hard work, esp a technical one. Thanks for sharing your knowledge.

A person gives you a whole book they wrote for free and you're complaining about asking for an email? WTF

Yeah, 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!

The 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.

I took it to mean “free as in beer”

Would you pay money in lieu of providing your real email address? If so, how much?

I’d probably pay $10, that’s worth less to me than my contact info.

Just 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.

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

Vocal minority.

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'.

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.

Nope, they can sell the list (or have it hacked), and then YOU are screwed.

They sell the list and then you can still filter out unwanted emails.

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.

Wildcard emails for the win.

It's not exactly free if they're asking something in return.

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.

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/07/13/156737801/the-...

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

Hackers from "hacker news" know how to install adblock, so they dont have to see ads on youtube.

You don't value your time = free

Other people do value their time, therefore it's not free for them.

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

Yes "nothing is free" yet we still often call these things free. It definitely wasn't free for him to make it.

Average person on here views those hours as billable if they were to do it themselves, when they make decisions about what to do, or alternatives (opportunity cost).

Maybe he should charge them consulting rates for this PDF (and not take their -- correction: an -- email. The horror).

It's only because this is HN that it's even a discussion.

The thing is that calling something free and then asking for something in exchange can trigger someone on autistic spectrum, where likely there is an overrepresentation of on HN.

In that sense for people who value their time, HN is not free.

I would rather use the term opportunity cost.

If you're not technical enough to have a junk email address then you probably aren't technical enough to learn SQL tricks.

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".

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.

The gist is:

1. Trace your application, so you know which query is running slowly in production.

2. Using this exact query, run `EXPLAIN ANALYZE` or whatever your RDBMS equivalent is.

3. Read the output, see which step is taking the slowest. Use google to help.

4. Google-fu until you find out which index might help you.

Over time, (3) and (4) requires less and less Google, because there's really only a few common cases that give you 100% of the speedup in 80% of cases.

Once you know this path to improvement exists, it's trivial to progress down it habitually. The `EXPLAIN ANALYZE` output looks like Greek at first, but quickly becomes as familiar to parse as a compiler error, etc.

DB 'expert' here - agree completely. It's about profiling then banging your head against it and learning more from books/the docs/web. That's really it.

(I am Not the OP) Not exactly from 0, maybe, but I strongly suggest this book: https://www.oreilly.com/library/view/the-art-of/0596008945/

If you're really starting out from square 1, I might read this book first: https://www.amazon.com/Database-Design-Mere-Mortals-Annivers... (although I read an earlier edition). The Art of SQL is a great book, but I wish I'd read something a bit...easier...first, lol. I struggled with it.

Yeah well, I am old school (or at least old) so I had already a good grasp of SQL, but I think the book is good because it explains overarching concepts very well.

The details depend heavily on what specific database you’re working with, but in broad strokes, you’ll want to know: indices, query plans, when and where to cache expensive computations, every f—- word of your DB manual’s chapter on concurrency control, and the fastest method(s) for yeeting data in and out from your language/toolkit of choice.

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.

This 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&sig...

Archived copy: https://web.archive.org/web/20221202231259im_/https://sqlfor...

Just FYI, I use Fastmail to create a masked email. If that email address starts flooding my inbox, I can kill it.

Thanks 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).

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!

I 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".

"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.

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!)

I agree on the privacy policy. (I also agree that this looks good, and I want to sign up.)

The 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.

Don't make it a hard rule, though. There are exceptions to everything. Sometimes it's easier, faster or more flexible in the client.

I have a very negative reaction to sites that require an email to get something of unknown value.

Automatic no.

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.

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.

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.

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?

> 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.

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.

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.

Take a shot every time a HN pedant types "Why don't you just X".

You, the reader, don't have a choice between a series of blog posts and an emailed pdf.

Well, 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

Your definition of value differs from that of others'.

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).

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.

The "privacy violation" is not the value of the book, that's the cost to you.

We're not discussing the values inherent in privacy concerns, we're discussing the value of the book, and whether or not you can guess at its value before you give the guy your email.

You're talking about something else.

No, you're talking about something else. Obviously OOP's concern is about the value of the book vis a vis what is demanded of the recipient in exchange for the book.

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!

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.

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.

Just make something up.

What about a throaway email?

can someone share the pdf here for folks who don't wanna share their email?

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

agree. Thanks for changing my perspective

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?

I've bounced back from so many walled articles that now I genuinely blame the OP (of that post) for not including a non-walled link.

HN should have a tag for walled contents.

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.

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.

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.

Sure, same as the total cost of a movie is how long it takes to type the name into a torrent search and click the magnet link. If the OP wanted a bunch of dead temp emails, why ask other people to input them. But, not my point.

I think on HN a good assumption is posters want to discuss, to get feedback. Also not a bad assumption the notion of temp email will be news to absolutely no one at all here.

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.

This looks great! Awesome that you made it free, but fyi I would have paid you $5-10 for this in a heartbeat.

Yes. Please add donation button.

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 :)

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 :)

If 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.

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.)


I'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.

Wow, 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!

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!

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.

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.

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.

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.

You’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.

long term value of an email is far greater than ads, especially for a developer audience that has probably 90% ad block usage rate

I 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.

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.

Maybe 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".

Is it really that hard to use a burner email?

It'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.

First, 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.

Book 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.

Not 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 :)

Yikes. 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.

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

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.

The 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.

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/

Nice 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.

Hmm...I subscribed, but after clicking the activation link, nothing was sent. Anyone else experience this?


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.

Yep. Working now. Thanks.

I get an error when trying to unsubscribe from the mailing list.

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.

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.

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.

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

Mine came in an email attachment after activating the newsletter sign up.

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.

No, it's not weird to not want to give up an email address before knowing the value proposition, qup and icedchai.

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 )

The 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.)

It 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

    -- MySQL
    SELECT * FROM example WHERE NOT(column <> 'value');
    -- PostgreSQL
    SELECT * FROM example WHERE column IS DISTINCT FROM 'value';
That doesn't seem equivalent?

This 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.

Thanks. One suggestion: Could you put all materials in github for contribution ?

Might 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')


With 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.

funny that if someone choose $5 he still need enter email to get book ;)

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.

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.

Got the email now (after retrying the subscription).

Thank you for the free book.

Thank you! I've been following your Twitter feed for a while and having all this in a single ebook is amazing.

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

Awesome - thanks for sharing your hard work! Some very new tricks to me!

I don't want to spam the comments but I also want to say thanks!

Thank you for making this available, and for free. Nice work.

Learned a few things -- thanks for putting this together!

Love it, great work!

I know your work. Thanks for everything you do, mate!

Nice. I presume this is for a flavour e.g. Postgres?

Both MySql and Postgres

I must be an idiot: where can you get the book?

Thank you Tobias

This is gold. Thank you very much.

FWIW, the initial email is showing up in my gmail as 'promotion', not spam. Hope that helps some.


Step 1. Make a temp email Step 2. Get book download Step 3. Move on with your life instead of complaining

What's the point of gathering burner emails?

You could just have one - it's a useful tool in the box.

For those who don't want to give their email, link to the PDF: https://file.io/5bJFEXNyPi8s

>The transfer you requested has been deleted.

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact