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