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

161–170 of 222 posts

Re: SQL Anti-Patterns

#161

Post by lucyjojo »

sgarland wrote: post #144
sigwinch28 wrote: post #38

Earlier quoted context omitted.

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

Because a city/region/state can be uniquely identified with a postal code (hell, in Ireland, the entire address is encapsulated in the postal code), but the reverse is not true. At scale, repeated low-cardinality columns matter a great deal.

these kinds of things are almost never true in the real world.

Re: SQL Anti-Patterns

#162

Post by ddxv »

physicles wrote: post #154
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…

Why do you need a warm standby for production? Do you need >= 3 nines? Our staging environment has its own instance that is rebuilt from prod, with pii removed, every day outside working hours (this normally takes about 15 minutes). It’s fantastic for testing migrations, and is easy to support compared with a warm standby.

"Do you need >= 3 nines?" No, it's a one person project. I'd be happy with a single 9 or even no 9s just a 99.0 haha

I switched to warm standby to reduce stress on the production db which was in the cloud. There is just a single production server and having it constantly run the heavy data processing MVs + handle queries was CPU intensive and slowed everything down. The CPU was costly.

To fix those issues, especially the CPU, I run the primary on a home server where it can crank the CPU as much as it wants running the data processing MVs and then sends the processed WALs to the warm standby that just handles the queries.

This has fixed those CPU and slow queries (when an MV is updating a table that is being constantly read). But introduced headaches anytime I update postgres.

My understanding is the 'fix' is to move data processing to another postgresql DB or flow? My biggest reason for not using another DB is I didn't like the idea of losing direct relations for keys.

Anyways, I appreciate the input, it's been a thorny issue I hit once or twice a year and am always unsure if what I'm doing is 'normal' or what I should do to fix it.

Re: SQL Anti-Patterns

#163

Post by CuriousRose »

I don’t fully agree with the nested view argument. In our context (POS software) we use them heavily to have a single source of truth for a clean transaction view, joining common tables like product, category, etc and then using that as the backbone for all user reporting that might be more/less complex. Not doing this means that we need to accommodate for each where clause in each table on each report. For example eliminating voided lines, voided transactions, returned transactions, etc. Not having this means that a single logic change would need to update 20+ views/stored procs so for our case I think its valid to nest.

Re: SQL Anti-Patterns

#164

Post by naasking »

sql_nitpicker wrote: post #30
dgb23 wrote: post #19

If „select *“ breaks your code, then there‘s something wrong with your code. I think Rich Hickey talked about this. Providing more than is needed should never be a breaking change. Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

I don't see anything wrong with what the article is saying. If you have a view over a join of A and B, and the view uses "select *", then what is gonna happen when A adds a column with the same name as a column in B? In sqlite, the view definition will be automatically expanded and one of the columns in the output will automatically be distinguished with an alias. Which column name changes is dependent on the order o…

Seems like a database failure if it can't notify you that introduced a breaking change. All of the schema information is available to the database after all, so it should be able to tell you about the duplicate column breaking that view.

Re: SQL Anti-Patterns

#165

Post by chuliomartinez »

SQL makes it very hard to express real world requirements.

1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is greater than X. Obviously this will genereate duplicates for orders that have more than one such orderproduct. So you slap a distinct on it… but what if you need an aggregation?

The possible fixes are highly non-trivial: subqueries, window functions, or vendor specific: outer apply.

2. Or queries, that is when you group where conditions with OR are very hard (impossible) to optimize.

Apart from the trivial case where the conditions are all on the same column, you are better of leaving the declarative world and imperatively tell sql to do a union.

I wrote a bit about it here: https://www.inuko.net/blog/platform_sql_or_conditions_on_joi...

Re: SQL Anti-Patterns

#166

Post by dfe »

chuliomartinez wrote: post #165

SQL makes it very hard to express real world requirements. 1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is greater than X. Obviously this will genereate duplicates for orders that have more than one such orderproduct. So you slap a distinct on it… but what if you need an aggregation? The possible fixes are highly non-trivial: subqueries, window functions, or vendor s…

Your own article points out that exists handles the first case. Exists is not actually implemented as a subquery, it is merely syntactically a subquery.

Re: SQL Anti-Patterns

#167

Post by yxhuvud »

bts89 wrote: post #31
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.

That’s almost always my experience too. Though fairly recently I learned that even with all the correct joins in place, sometimes adding a DISTINCT within a CTE can dramatically increase performance. I assume there’s some optimizations the query planner can make when it’s been guaranteed record uniqueness.

I've seen similar effects when changing a bunch of left outer joins to lateral joins with a limit 1 tacked on. The limit do nothing to the end result, but speed up the query by a factor of 1000..

Re: SQL Anti-Patterns

#168

Post by procaryote »

chuliomartinez wrote: post #165

SQL makes it very hard to express real world requirements. 1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is greater than X. Obviously this will genereate duplicates for orders that have more than one such orderproduct. So you slap a distinct on it… but what if you need an aggregation? The possible fixes are highly non-trivial: subqueries, window functions, or vendor s…

I don't really understand the problem in 1

in 2, looking at your article, from your first query it looks like person_relationship contains both (A,B) and (B,A) for all related people A and B; otherwise the left join won't work. If you also make people related to themselves and store (A,A) and (B,B) there your query becomes much simpler:

    SELECT other.id, other.name
    FROM person p
      JOIN person_relationship r ON r.from_person_id = p.id
      JOIN person other ON r.to_person_id = other.id
    WHERE p.family_id = @familyId;

Re: SQL Anti-Patterns

#169

Post by gregjor »

aerzen wrote: post #79

These "anti-patterns" are just workarounds for bad language design of SQL (or lack of design actually). I'm working on a language that can run on SQL databases, so I hope it will do better with every one of these points. If anyone wants to check out a half-done lang with lacking documentation, I'd be happy to read your feedback: https://lutra-lang.org

"SQL database" doesn't describe anything. Variations of SQL have implementations on relational and non-relational databases. SQL and relational often get used interchangeably but given your goal you might want to use the terms more precisely.

Experts including Codd recognized the problems with SQL since that language got traction. Some alternatives got proposed, perhaps most notably Tutorial D by Chris Date and Hugh Darwen. No SQL replacement goes anywhere because of the vast quantity of SQL code and supporting tools dating back decades. Chris Date wrote the textbook on databases, and at least one book going through the problems with SQL and various implementations of the relational model.

SQL perfectly illustrates what Strostrup meant by "There are only two kinds of languages: the ones people complain about and the ones nobody uses." In some sense I would welcome a better query language. On the other hand I attribute decades of job security and steady income to knowing SQL and dealing with its problems.

Re: SQL Anti-Patterns

#170

Post by viraptor »

valiant55 wrote: post #94
sigwinch28 wrote: post #38

Earlier quoted context omitted.

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

It depends when you see it, but I agree that DISTINCT shouldn't be used in production. If I'm writing a one off query and DISTINCT gets me over the finish line sparing me a few minutes then that's fine.

Which categories did the user post in? Which projects did the user interact with in the last week? That's all normal DISTINCT usage.
Post reply on HN