HN Reader

I wrote a small tutorial (~9000 words in two parts) on how to design complicated queries so that they don't need DISTINCT and are basically correct by construction.

https://kb.databasedesignbook.com/posts/systematic-design-of...

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.

Using != or NOT IN (...) is almost always going to be inefficient (but can be OK if other predicates have narrowed down the result set already).

Also, understand how your DB handles nulls. Are nulls and empty strings the same? Does null == null? Not all databases do this the same way.

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

https://en.wikipedia.org/wiki/Sargable

https://www.brentozar.com/blitzcache/non-sargable-predicates...

Therefore you should create a consistent indentation style for SQL. See https://bentilly.blogspot.com/2011/02/sql-formatting-style.h... for mine. Second, you should try to group logical things together. This is why people should move subqueries into common table expressions. And finally, don't be afraid of commenting wisely.

Some of these have nothing to do with SQL the language itself, and more to do with database schema design. If you have to do a DISTINCT, it means your primary key design is likely not right. If you are layering too many views, something is broken in the base table design, requiring the creation of all these views.

A good database model goes a long way to avoiding all this.

Translating status codes into English or some other natural language? That's better done in the application, not the database. Maybe even leave it to the frontend if you have one. As a rule of thumb, any transformation that does not affect which rows are returned can be applied in another layer after those rows have been returned. Just because you know SQL doesn't mean you have to do everything in SQL.

Deeply nested subqueries? You might want to split that up into simpler queries. There's nothing shameful about throwing three stones to kill three birds, as long as you don't fall into the 1+N pattern. Whoever has to maintain your code will thank you for not trying to be too clever.

Also, a series of simple queries often run faster than a single large query, because there's a limit to how well the query planner can optimize an excessively complicated statement. With proper use of transactions, you shouldn't have to worry about the data changing under your feet as you make these queries.

User Defined Functions (UDFs) are another option to consolidate the logic in one place.

> Using Functions on Indexed Columns

In other words, the query is not sargable [0]

> Overusing DISTINCT to “Fix” Duplicates

Orthogonal to author's point about dealing with fanout from joins, I'm a fan of using something like this for 'de-duping' records that aren't exact matches in order to conform the output to the table grain:

    ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1

[0] https://en.wikipedia.org/wiki/Sargable

[1] https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_cla...

Unfortunately I learned this the hard way!

query WHERE name = ‘abc’

create an indexed UPPER(name) column"

Should there be an "or" between these 2 points, or am I missing something? Why create an UPPER index column and not use it?

Certain languages, formats and tools do this correctly by default. For the others you need a source of truth that you generate from.

Guilty as charged. I love to do this. Materialized views aren't really possible on sqlite, and so I find stacking views on top of one another very readable and manageable. But it's true other people find it a little obscure and weird.

In a better language, this would be a pipeline. Pipelines are conceptually simple but annoying to debug, compared to putting intermediate results in a variable or file. Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?

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

Why wouldn’t you store this information in a table and query it when you need it? What if you need to support other languages? With a table you can just add more columns for more languages!

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.

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

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 …

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 …

The funny thing is it's actually several of those languages. :-)

Is this code for 'use a lookup table' or am I falling behind on the terminology? The modern term should be 'sum table' or something similar surely.

was surprised to not see anything about dates/time.

If I have to work with one more "custom field" or "classifier" implementation, I am going to cry. Your business domain isn't too hard to model, if you need a 100 different "entities" as a part of it, then you should have at least 100 different tables, instead of putting everything into an ill fitting grab bag. Otherwise you can't figure out what is connected to what by just looking at the foreign keys pointing to and from a table, because those simply don't exist. Developers inevitably end up creating shitty polymorphic links with similarly inevitable data integrity issues and also end up coupling the schema to the back end, so you don't get like "table" and "table_id" but rather "section" and "entity_id" so you can't read the schema without reading the back end code either. Before you know it, you're not working with the business domain directly, but it's all "custom fields this" and "custom fields that" and people end up tacking on additional logic, like custom_field_uses, custom_field_use_ids, custom_field_periods, custom_field_sources and god knows what else. If I wanted to work with fields that much, I'd go and work on a farm. Oh, you're afraid of creating 100 tables? Use codegen, even your LLM of choice has no issues with that. Oh, you're too afraid that you're gonna need to do blanket changes across them and will forget something? Surely you're not above a basic ADR, literally putting a Markdown file in a folder in the repo. Oh, you're afraid that something will go wrong in those 100 migrations? How is that any different than you building literally most of your app around a small collection of tables and having fewer migrations that will affect pretty much everything? Don't even get me started on what it's like when the data integrity issues and refactoring gone bad starts. Worst of all, people love taking that pattern and putting it literally everywhere, feels like I'm taking crazy pills and nobody seems to have an issue what it's like when most of the logic in your app has something to do with CustomFieldService.

Fuck EAV/OTLT, thanks for coming to my rant. When it comes to bad patterns, it's very much up there, alongside using JSON in a relational database for the data that you can model and predict and put into regular columns, instead of just using JSON for highly dynamic data.

> Excessive View Layer Stacking

> In larger data environments, it’s easy to fall into the trap of layering views on top of views. At first, this seems modular and organized. But over time, as more teams build their own transformations on top of existing views, the dependency chain becomes unmanageable. Performance slows down because the database has to expand multiple layers of logic each time, and debugging turns into an archaeological dig through nested queries. The fix is to flatten transformations periodically and materialize heavy logic into clean, well-defined base views or tables.

I will say that this is nice to strive for, but at the same time, I much prefer having at least a lot of views instead of dynamically generated SQL by the application (see: myBatis XML mappers), because otherwise with complex logic it's impossible to predict exactly how your application will query the DB and you'll need to run the app locally with logging debug levels on so you see the actual SQL, but god forbid you have noisy DB querying or an N+1 problem somewhere, log spam for days, so unpleasant to work with. It's even more fun when people start nesting mappers and fucking around with aliases, just give me MongoDB at this point, it's web scale.