HNHacker News
TopNewBestAskShowJobs

BrentOzar

3,773 karma · joined May 21, 2011

I make databases go faster. I love teaching, travel, cars, and laughing. Las Vegas. He/him, pan. Email: Brento at BrentOzar.com.
submissionscomments
BrentOzar··on MangaDex infrastructure overview
> what and how should things be cached?

If something is read much more frequently than it changes, store it client-side, or store it temporarily in an in-memory-only, not-persisted-to-disk "persistence" layer like Redis.

For example, if you're running an online store, your product list doesn't change all that often, but it's queried constantly. The single source of truth lives in a relational database, but when your app needs to fetch the list of products, it should first check the caching layer to see if it's available there. If not, fetch it from the database, but then write it into the cache so that it's available more quickly the next time you need it.

> When and how to denormalize, why is it needed?

When you need to join several tables together in order to retrieve a result set, and especially when you need to do grouping to get the result set, and the retrieval & grouping is presenting a performance problem, then pre-bake that data on a regular basis, flattening it out into a table optimized for read performance.

Again with the online store example, let's say you want to show the 10 most popular products, with the average review score for each product. As your store grows and you have millions of reviews, you don't really want to calculate that data every time the web page renders. You would build a simpler table that just has the top 10 products, names, IDs, average rating, etc. Rendering the page becomes much more simple because you can just fetch that list from the table. If the average review counts are slightly out of date by a day or two, it doesn't really matter.

> Why append-only and how?

If you have a lot of users fighting over the same row, trying to update it, you can run into blocking problems. Consider just storing new versions of rows.

But now we're starting to get into the much more challenging things that require big application code changes - that's why the grandparent post listed 'em in this order. If you do the first two things I cover above there, you can go a long, long, long way.

BrentOzar··on Connection Strings Reference
It is! Flashbacks. And it still does a better job than most database vendor sites, gets straight to the point.
BrentOzar··on Jabs mostly fend off the Delta strain but breakthroughs are infectious
> What's the mix of vaccinated and unvaccinated people in the hospital?

Iceland is fairly transparent with those numbers, but the only easily-accessible metrics are in the Icelandic version of the news:

https://www-mbl-is.translate.goog/frettir/innlent/2021/08/22...

"17 patients are in the emergency department of the hospital and seven of them are unvaccinated. There are seven in intensive care and three are unvaccinated. Five are on a ventilator and two of them are unvaccinated."

Those numbers are typical of the daily news: most of the hospitalized are usually vaccinated. You're going to have all kinds of followup questions like which vaccine did they get, what percentage of the population got which vaccine versus got hospitalized, etc. I'm just sharing this so you can see how you can't possibly gather all of those details quickly, especially when the situation is in flux.

Plus, given that this is peak tourism season for Iceland and a lot of folks here are from out of the country right now, you'd probably want a breakdown of what percentage are foreigners versus residents, and that's not available as far as I'm aware.

BrentOzar··on Good Riddance, TurboTax. Americans Need a Real ‘Free File’ Program
I own my own business, and I sell online training. I'm not working in the US - I'm working in Iceland, selling to people globally, and still paying income tax in the US.
BrentOzar··on Good Riddance, TurboTax. Americans Need a Real ‘Free File’ Program
> Has anyone personally had to pay taxes to the US while living abroad?

Yes, I'm living in Iceland at the moment and paying income taxes in the US.

Iceland has a teleworker visa that lets you live here tax-free while working remotely for your current employer. That's a fantastic deal for most countries, but I'm not saving any money while we're here. (We're not doing it to save money, just doing it to social distance and enjoy the scenery.)

BrentOzar··on Bring back the station wagon
> What I find odd is there's essentially zero collector interest in station wagons

There's a very strong interest in a few, but they have to be special: older Benzes, early BMW 3-series Touring, Roadmasters, Cadillac CTS-Vs with the manual transmission, AMG wagons, early woodies.

> meaning they have utterly vanished.

Most were used for their purpose - they weren't treasured the way sports cars were, nor were they weekend cars. As a result, it's hard (read: expensive) to find a collector-quality wagon. Prices on BringATrailer and CarsAndBids reflect that.

BrentOzar··on Microsoft is shutting down its Azure Blockchain Service
> Azure has now made it clear that they can and will shutdown services with relatively short notice.

This isn't a new thing - there's an entire Twitter feed dedicated to things Azure discontinues: https://twitter.com/azureendoflife

BrentOzar··on Amazon Sidewalk
"when you share your Bridge’s connection with Sidewalk, total monthly data used by Sidewalk, per account, is capped at 500MB"

Given that it's US-only, where most internet plans have pretty high caps, it's hard for me to get upset about this. I would have expected my Echoes alone to use more than that during normal monthly operation, playing music, fetching news, etc.

BrentOzar··on Internal Combustion Engine
Your fuel tank was only allowed to hold a certain amount of fuel because if you had more, you could go farther between pit stops, thereby covering more laps while the other drivers were stopped for gas.

He would temporarily meet the small tank regulations during inspection, but under race conditions, the ball would burst, allowing for more space in the tank, which would get filled up with more fuel than his competitors at the first pit stop.

BrentOzar··on FFmpeg and a Thousand Fixes (2014)
Hahaha, yes, this is indeed me! I love the eDiscovery business. Such a fun problem to solve.
BrentOzar··on Ask HN: Is there a way to efficiently subscribe to an SQL query for changes?
Microsoft SQL Server has a rarely-used feature to do exactly this: [Query Notifications][1].

It sounded great in theory when it came out in 2005, but the drawback was that every open subscription was an open connection to the database server, from every application server. The more web servers you have, and the more queries you want to subscribe to, the more open connections you end up with, which at large scale can cause performance issues even when the connections aren't doing anything.

You can mitigate this by using less subscriptions to monitor more data - but when anything changes in the underlying result set, you end up fetching the entire result set. With a lot of web/app servers, this means an avalanche of queries running at exactly the same time, trying to hit the same data to refresh their cache.

In a perfect world, I'd try to mitigate this by offloading these kinds of queries to read-only replicas. That's a feature limited to the expensive Enterprise Edition ($7K USD per CPU core), and when you scale out to a lot of database servers to leverage this, then it becomes prohibitively expensive.

Another way to mitigate the many-app-servers, many-subscriptions problem is to have a central app server manage the caching for everyone else.

[1]: https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/ena...

BrentOzar··on FFmpeg and a Thousand Fixes (2014)
Guessing this is related to the recent documentation by Signal about how Cellebrite is using a 2012 version of FFmpeg, long before these code fixes went in: https://news.ycombinator.com/item?id=26891811
BrentOzar··on Live feed from Iceland erupting volcano [video]
I'm in Iceland for 6 months on their new teleworker visa. It took a couple of months to get, but it's pretty straightforward: https://ozar.me/2021/01/whew-we-got-iceland-teleworker-visas...
BrentOzar··on Ideas to improve the user and developer experiences of databases
> I’d like a tool that automatically suggests which indexes to add/remove, including use of different types of indexes and partial indexes, based on the queries being performed

The data to do this is built into Microsoft SQL Server, and the open source sp_BlitzIndex does exactly what you're asking for.

BrentOzar··on Generalists vs specialists – Who has a greater chance of success? (2019)
In an immature market, be a generalist. Be good at switching tools quickly because they'll come and go, and be good at the general problem-solving process because you'll face a lot of problems with new tools.

In a mature market, be a specialist. Be good at one very popular tool, and specifically solving one problem with it, and you'll find a ton of companies willing to pay you a lot in order to solve that one particular problem quickly.

BrentOzar··on The Downside to Life in a Supertall Tower: Leaks, Creaks, Breaks
Quick rundown of the advantages (I usually live in high-rises):

High rises usually have spectacular shared amenities: grand pools, exercise rooms, party rooms, theaters, etc. One of mine had outdoor grilling areas that you didn't even have to clean up: the apartment had cleaning staff who would scour the grills every day.

High rises usually have 24/7 door staff to sign for & lock up packages, get taxis, handle dry cleaning, store grocery deliveries in a fridge, and let you into your unit if you get locked out. The sheer number of units makes this cost-effective for them because the costs are shared across all tenants.

High rises usually have maintenance staff during business hours and on call after hours to fix issues. Sometimes you can also hire them for handyman duties, too - I've had them install TVs, hang green screens, or paint a wall.

High rises are often the only cost-effective way to live in the core of a really vibrant city like Chicago: we couldn't have afforded a town home, let alone a single family home.

BrentOzar··on Whew. We Got Iceland Teleworker Visas
Thanks, but I don’t really have an interest in visiting Estonia at this time.
BrentOzar··on Drawbridge: What SQL Server on Linux is built on
> I for one hope that Azure Data Studio becomes the VSCode of SQL Editors and supports more database servers.

It already supports MySQL, PostgreSQL, and Microsoft SQL Server.

BrentOzar··on Drawbridge: What SQL Server on Linux is built on
A little background info:

For the last couple of decades, Microsoft SQL Server only ran on Windows.

Microsoft aspires to take licensing money from shops that run Oracle (which is much more expensive), and those shops often prefer to run Linux on their servers. To help win over those customers, they needed to run SQL Server on Linux.

Drawbridge is the layer that enables that to happen.

There hasn't been a lot of public material about it, and SQL Server on Linux didn't get much of an adoption rate, so there hasn't been much incentive for the community to dig into it, either.

Adoption's probably going up on developer workstations now that SQL Server runs in containers and now that Microsoft's put a lot of work into Azure Data Studio, a database development tool that runs on Linux & Mac & Windows.

BrentOzar··on Major Incident at Fastly
Affected live streaming on both Mux.com and Vimeo.com (the two streaming companies I use for my training classes, hahaha - so much for redundancy with paid companies. Had to switch to YouTube.)
BrentOzar··on SolarWinds hackers were able to access Microsoft source code
Here's the updated Microsoft post that contains the admission that the hackers viewed source code:

https://msrc-blog.microsoft.com/2020/12/31/microsoft-interna...

Drives me crazy that Reuters could write an entire post about a Microsoft blog post, yet not link to the post itself.

BrentOzar··on Babelfish: SQL Server-to-Postgres Translation Layer
"Babelfish is written in C, which is the same programming language used to develop PostgreSQL. Some parts of Babelfish are developed using procedural language in PL/pgSQL. Many test cases are written in PL/pgSQL and T-SQL."
BrentOzar··on Babelfish: SQL Server-to-Postgres Translation Layer
> I have to wonder if they’ve found it not worth the time to make it fully production ready.

It sounds like it's not compatible with all T-SQL commands and data types. It would be tough to get full feature completeness: Microsoft is constantly adding new stuff in each version, like the ability to run Java stored procedures, R & Python in the database, etc. Open sourcing it would let companies expand it to get specific features coded that they need - that otherwise might not get attention from the general public.

BrentOzar··on AT&T is selling your phone calls and text messages to marketers, how to opt out
Took less than a minute just now to opt out for all of my phone numbers. They don't give you a single yes/no switch to opt out - you have to set each phone number to "no" for each of several services - but all things considered, it was pretty quick.
BrentOzar··on Facebook prohibits music or music listening experience on Live
> Seems like a model could exist for an entrepreneur to create a subscription based licensing program for creators to use music in their streams.

There are a few - I use Pretzel.rocks. (No affiliation.)

BrentOzar··on Barbados introduces plan to allow visitors to work remotely there for a year
> you apply for FEIE and won't have to pay federal income tax as a US citizen.

The Foreign Earned Income Exclusion only applies to the first $107,600 you earn in 2020. You still have to pay taxes on the rest.

BrentOzar··on Developer won’t get hit by a bus, they’ll get hired by Netflix
> Sure, but even someone retiring after winning the lottery might deign to write a few things down for you.

Might, or they might not. Just as if someone were hit by a bus, they might be able to communicate from their hospital bed...or they might not. Hope is not a strategy.

BrentOzar··on Developer won’t get hit by a bus, they’ll get hired by Netflix
Instead of "hit by a bus," I prefer the term "win the lotto."

Any member of your team could win a life-changing amount of money in the lottery, inherit it, win a gambling bet, etc. Frame it as a good thing rather than a death or a change of hire - somebody might just flat out retire because they don't financially need your employment anymore.

BrentOzar··on Migrating a 40TB SQL Server Database
> That would have been worth the expense in DBA time alone, right?

You're assuming the DBA did nothing else during the project's timeline, and that's not likely the case. A lot of work in projects like this is just letting the batch job run while you keep an eye on it.

BrentOzar··on Migrating a 40TB SQL Server Database
> Maybe i'm missing it, but there doesn't seem to be any discussion of the result.

Sometimes you have to stop writing after 9,000 words. ;-)

← PreviousPage 5 of 16Next →