Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Anti-Patterns

datamethods.substack.com

Post reply on HN
Print view View original

121–130 of 222 posts

Re: SQL Anti-Patterns

#121

Post by DavidWoof »

bandrami wrote: post #36
EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.

IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know

In OP's defense, "becoming suspicious" doesn't mean it's always wrong. I would definitely suggest an explaining comment if someone is using DISTINCT in a multi-column query.

Re: SQL Anti-Patterns

#122

Post by Little_Kitty »

Sesse__ wrote: post #87
wvbdmp wrote: post #86

Earlier quoted context omitted.

No, sorry, you’re certainly correct, I just meant that any subqueries are generally crazy verbose. And then you usually want additional Where clauses or even Joins in there, and it starts to stop looking like a Where clause, so I’m often happy when I can push that logic into From.

Yes, I would certainly prefer if you could write SELECT * FROM t1 SEMIJOIN t2 USING (x); although it creates some extra problems for the join optimizer.

It's great being able to use an any join (and the counterpart anti join) in Clickhouse to deal with these operations.

Re: SQL Anti-Patterns

#123

Post by seanhunter »

I can’t take any article like this seriously if it doesn’t lead with the #1 sql antipattern which kills performance all the time - doing things row-by-row instead of understanding that databases operate on relations, so you need to do operations over whole relations.

Very often I have seen this problem buried in code design and it always sucks. Sometimes an orm obscures this but the basic antipattern looks like

   Select some stuff
   For each row in stuff:
      … do some important things …
      Select a thing to do with this row
      … maybe do some other things …
Early on in my career an old-hand sql guru said to me “any time you are doing sql in a loop, you are probably doing it wrong”.

The non-sucky version of the code above is

   Select some stuff, joining on all the things you need for the rows because databases are great
   For each row in stuff:
      … do some important things …
      … maybe do some other things …

Re: SQL Anti-Patterns

#124

Post by ddxv »

I've built myself a few problems that I haven't fixed yet:

Many materialized views that rely on materialized views. When one at the bottom, or a table, needs a changed all views need to be dropped and recreated.

Using a warm standby for production. I love having a read only production database, but since it's not the primary, it always feels like it's on the losing end of the system. Recently upgraded to Postgres 18 and forgot that means I need to rm rf the standby and pg_basebackup to rebuild... That wasn't fun.

Re: SQL Anti-Patterns

#125

Post by crazygringo »

SoftTalker wrote: post #72
magicalhippo wrote: post #66

Earlier quoted context omitted.

> Also, understand how your DB handles nulls. Also in regards to indexing. The DBs I've used have not indexed nulls, so a "WHERE col IS NULL" is inefficient even though "col" is indexed. If that is the case and you really need it, have a computed column with a char(1) or bit indicating if "col" is NULL or not, and index that.

NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.

Not sure what you mean.

If you have a table of customers and someone of them don't have addresses, it's standard to leave the address fields NULL. If some of them don't belong to a company, it's standard to leave the company_id field NULL.

This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common.

If you're suggesting mandatory additional has_address and has_customer_id fields, I would disagree. You'd be reinventing a database tool that already exists precisely for that purpose.

Re: SQL Anti-Patterns

#126

Post by MichaelNolan »

Arch-TK wrote: post #83

Forgot to add (all seen in production): * Don't store UUIDs as strings. * Don't use random UUID variants for your primary key (or don't use UUIDs for your primary key). * Don't use a random column in your clustered index.

I guess things are DB dependent. Spanner for instance not only recommends using uuidv4 as a PK, it also stores it as string(36). Uuidv4 as a PK works fine on Postgres as well.

Re: SQL Anti-Patterns

#127

Post by MaxBarraclough »

JohnHaugeland wrote: post #100
readthenotes1 wrote: post #16

Earlier quoted context omitted.

If a pattern is a common problem (e.g., becoming accustomed to a spectacular view) and generally-useful solution to that problem (blocking the view so that effort is required to obtain it), then an anti-pattern is what? I think most people think an anti-pattern is an aberration in the "solution" section that creates more problems. So here, the anti-pattern is that people use a term so casually (e.g., DevOps) that no…

> If a pattern is a common problem it isn't, is the thing. if you read the book design patterns, they spell out what a pattern is. if you read the book anti-patterns, he spells out what an anti-pattern is. people have gotten the wrong idea by learning the phrases from casual usage.

Pointing to books isn't very helpful here. Please just state the definition you are advocating.

Re: SQL Anti-Patterns

#128

Post by echelon »

sigwinch28 wrote: post #38
EvanAnderson wrote: post #14

> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.

Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)

DISTINCT, as well as the other aggregation functions, are fantastic for offline analytics queries. I find a lot of use for them in reporting, non-production code.

Re: SQL Anti-Patterns

#129

Post by MaxBarraclough »

crazygringo wrote: post #125
SoftTalker wrote: post #72

Earlier quoted context omitted.

NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.

Not sure what you mean. If you have a table of customers and someone of them don't have addresses, it's standard to leave the address fields NULL. If some of them don't belong to a company, it's standard to leave the company_id field NULL. This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common. If you're suggesting mandatory additional has_address and has_c…

> This is literally what NULL is for. It's a special value precisely because missing data or a N/A field is so common.

Kinda. You need null for outer joins, but you could have a relational DBMS that prohibits nullable columns in tables. Christopher Date thought that in properly normalised designs, tables should never use nullable columns. Codd disagreed. [0]

> If you're suggesting mandatory additional has_address and has_customer_id fields, I would disagree. You'd be reinventing a database tool that already exists precisely for that purpose.

The way to do it without using a nullable column is to introduce another table for the 'optional' data, and use a left outer join.

[0] https://en.wikipedia.org/wiki/First_normal_form#Christopher_...

Re: SQL Anti-Patterns

#130

Post by echelon »

ddxv wrote: post #124

I've built myself a few problems that I haven't fixed yet: Many materialized views that rely on materialized views. When one at the bottom, or a table, needs a changed all views need to be dropped and recreated. Using a warm standby for production. I love having a read only production database, but since it's not the primary, it always feels like it's on the losing end of the system. Recently upgraded to Postgres 18…

I'd like to call views, triggers, and integrity constraints antipatterns.

Your code should handle the data model and never allow bad states to enter the database.

There's too much performance loss and too many footguns from these "features".

Post reply on HN