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

211–220 of 222 posts

Re: SQL Anti-Patterns

#211

Post by crazygringo »

naasking wrote: post #208
crazygringo wrote: post #203

Earlier quoted context omitted.

That seems like a logical nightmare to me. When thinking about which fields get returned, which fields do you index over, etc. It's a nice idea in theory, but would add an astronomical level of complexity in practice.

If the database supports algebraic data types, you wouldn't have to think about which fields get returned, it understand the needed semantics and handles that for you. Like I said, these are SQL limitations.

You'd need to handle it in your code. The last thing I want is a database that returns different columns per row.

No, these aren't SQL limitations, it's design that is super complex. Figuring out how to index over these multi-type fields isn't a SQL limitation, it's a hard engineering problem.

Re: SQL Anti-Patterns

#212

Post by crazygringo »

naasking wrote: post #207
magicalhippo wrote: post #205

Earlier quoted context omitted.

> I remember working on ERP systems with 40+ column tables, most of which were null. Those are rookie numbers. Add a zero to that number and we're talking. And for us, a good portion of the data, a considerable fraction of those fields will have data, and which fields will vary between customers. All except some key fields are NULL-able since the user can save and resume their work. Just to display our main screen wo…

400 columns all of which are nullable screams "dynamic field set" to me. Why have all of those as columns rather than something like: table Entity_Fields (ParentEntityId : int not null, FieldId : int not null, IntValue : int, TextValue : varchar(MAX), DateValue : datetime, ...) Or per the OP's suggestion, a table per field type: table Entity_IntFields (ParentEntityId : int not null, FieldId : int not null, Value : in…

At that point why not abandon databases entirely and use a key-value store?

The answer, obviously, is because traditional tables make lots of things really easy. SQL is designed for that use case, and it's performant.

You're not even talking about traditional relational databases anymore. You're trying to construct tables inside of tables, which means abandoning performance, indexes, etc.

Re: SQL Anti-Patterns

#213

Post by naasking »

magicalhippo wrote: post #209
naasking wrote: post #207

Earlier quoted context omitted.

400 columns all of which are nullable screams "dynamic field set" to me. Why have all of those as columns rather than something like: table Entity_Fields (ParentEntityId : int not null, FieldId : int not null, IntValue : int, TextValue : varchar(MAX), DateValue : datetime, ...) Or per the OP's suggestion, a table per field type: table Entity_IntFields (ParentEntityId : int not null, FieldId : int not null, Value : in…

> 400 columns all of which are nullable screams "dynamic field set" to me Could have been, but no. And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we…

> And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries.

Only if you insist on loading the data into a flat record type with all of those fields. You have a dynamically evolving datatype, so your class should also be dynamic:

    public enum FieldId
    {
        Field1,
        Field2,
        //...
    }

    public class YourRecord
    {
        public Dictionary TextValues { get; set; }
        public Dictionary IntValues { get; set; }
        public Dictionary DateValues { get; set; }
        // ...
    }
At most the number of subqueries increases by the number types your class needs. SQL has a fixed number of datatypes, and most apps use a small subset of these (dates, text, integral, floating point and decimal types, that's typically 5 at most). You could collapse these all into a single table with optional columns for each value too and then it's only one query.

> And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we get 100k inserts per hour once a day or so.

That's a fairly small amount of data, I don't really see an issue here.

Also, you're neglecting the fact that since the fields are independent, adding a field value doesn't involve locking and rewriting a large 400 column row to disk, but only appending a tiny 4 column row to a separate distinct table.

Re: SQL Anti-Patterns

#214

Post by naasking »

crazygringo wrote: post #212
naasking wrote: post #207

Earlier quoted context omitted.

400 columns all of which are nullable screams "dynamic field set" to me. Why have all of those as columns rather than something like: table Entity_Fields (ParentEntityId : int not null, FieldId : int not null, IntValue : int, TextValue : varchar(MAX), DateValue : datetime, ...) Or per the OP's suggestion, a table per field type: table Entity_IntFields (ParentEntityId : int not null, FieldId : int not null, Value : in…

At that point why not abandon databases entirely and use a key-value store? The answer, obviously, is because traditional tables make lots of things really easy. SQL is designed for that use case, and it's performant. You're not even talking about traditional relational databases anymore. You're trying to construct tables inside of tables, which means abandoning performance, indexes, etc.

> At that point why not abandon databases entirely and use a key-value store?

Because there is a schema, it's just a dynamically evolving one, and also, presumably the rest of your system depends on the relational DB, so why multiply your dependencies unnecessarily?

> You're not even talking about traditional relational databases anymore.

There's nothing non-relational about the schema I've outlined, it matches what the domain requires with less noise than 400 nullable columns.

Re: SQL Anti-Patterns

#215

Post by magicalhippo »

naasking wrote: post #213
magicalhippo wrote: post #209

Earlier quoted context omitted.

> 400 columns all of which are nullable screams "dynamic field set" to me Could have been, but no. And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. And insertion performance would crater I assume, since the DB now needs to do hundreds of inserts per record rather than a single row. We do have some cases where we…

> And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. Only if you insist on loading the data into a flat record type with all of those fields. You have a dynamically evolving datatype, so your class should also be dynamic: public enum FieldId { Field1, Field2, //... } public class YourRecord { public Dictionary Text…

> At most the number of subqueries increases by the number tables.

Fair enough. Application logic will want to access the data as a flat record, but that could be handled through getters. We have views which are also used by customers and our reporting tool, but the customers we're moving to API access and reporting could probably be done with something better if starting from scratch.

> That's a fairly small amount of data, I don't really see an issue here.

Well it turns 100k inserts into tens of millions. On tables where users need to work without slowdown while this is going on.

It probably works if you throw enough hardware at it, but currently we get by with quite modest DB hardware.

That said, how do you create compound indexes over these fields? Say you need to index one date and one varchar column? Such demands can arise down the line, often hard to predict up front.

Re: SQL Anti-Patterns

#216

Post by naasking »

crazygringo wrote: post #211
naasking wrote: post #208

Earlier quoted context omitted.

If the database supports algebraic data types, you wouldn't have to think about which fields get returned, it understand the needed semantics and handles that for you. Like I said, these are SQL limitations.

You'd need to handle it in your code . The last thing I want is a database that returns different columns per row. No, these aren't SQL limitations, it's design that is super complex. Figuring out how to index over these multi-type fields isn't a SQL limitation, it's a hard engineering problem.

> The last thing I want is a database that returns different columns per row.

Actually that's exactly what you'd want, because it saves you from running two different queries in those cases with properly normalized disjoint data sets, and moves more of the domain's constraints into the database schema where it belongs.

Re: SQL Anti-Patterns

#217

Post by naasking »

magicalhippo wrote: post #215
naasking wrote: post #213

Earlier quoted context omitted.

> And doing it like you suggest would mean overview grids would have to do 50+ subqueries for each row, and loading a record would mean hundreds of queries. Only if you insist on loading the data into a flat record type with all of those fields. You have a dynamically evolving datatype, so your class should also be dynamic: public enum FieldId { Field1, Field2, //... } public class YourRecord { public Dictionary Text…

> At most the number of subqueries increases by the number tables. Fair enough. Application logic will want to access the data as a flat record, but that could be handled through getters. We have views which are also used by customers and our reporting tool, but the customers we're moving to API access and reporting could probably be done with something better if starting from scratch. > That's a fairly small amount…

> Well it turns 100k inserts into tens of millions.

I don't think "number of inserts" is the right metric because the total amount of bytes being written is almost the same, it's just written in different areas and still mostly contiguously. I think "number of distinct tables being written" is a better metric. Assuming all 400 columns become 400 records in the data type tables, say evenly divided among the 4 most common data types (int, decimal, text, date), that would be more like (4 or 5) x 100k = 400k-500k. I would still hesitate to naively compare it this way without a benchmark though, because with 4 or 5 tables being written there's also less contention than there is when everyone is writing to 1 table.

Regarding indexes, you can index the field tables but obviously this applies to the whole table. Without more understanding of your domain I can't really say if this breaks down. There's also the possibility that this one table is serving two competing goals, eg. perhaps the user should be able to add data incrementally (so this pattern applies), but then at some point when all of the data is filled it should be migrated to an actual table with all non-null columns that can be indexed and queried/processed as you usually do.

In any case, what I've sketched out isn't really new, it's been used at least since the 90s under "Dynamic Object Model" and "Entity-Attribute-Value" [1,2], so if they were using on hardware in the 90s I can't imagine the pattern would be unusable on modern hardware.

[1] https://www.cs.sjsu.edu/~pearce/oom/patterns/new/Riehle.pdf

[2] https://softwarepatternslexicon.com/patterns-sql/4/5/

Re: SQL Anti-Patterns

#218

Post by crazygringo »

naasking wrote: post #216
crazygringo wrote: post #211

Earlier quoted context omitted.

You'd need to handle it in your code . The last thing I want is a database that returns different columns per row. No, these aren't SQL limitations, it's design that is super complex. Figuring out how to index over these multi-type fields isn't a SQL limitation, it's a hard engineering problem.

> The last thing I want is a database that returns different columns per row. Actually that's exactly what you'd want, because it saves you from running two different queries in those cases with properly normalized disjoint data sets, and moves more of the domain's constraints into the database schema where it belongs.

Definitely not what I want. I'm perfectly happy getting both fields at the same time in a single query the way I do now.

> and moves more of the domain's constraints into the database schema where it belongs.

No, I prefer to keep my domain constraints at the application level where they're far more flexible.

The database is for storing information, not for validating my business logic.

I mean, I realize some people want to build some of that logic into the database, especially when many applications interact with it. But it's not a superior design pattern. If you have a single application, it's perfectly valid and desirable to put all business logic in the application, not the database.

Re: SQL Anti-Patterns

#219

Post by Breza »

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.

This assumption got me in trouble as a junior analyst years ago. I was asked to analyze our customer base and wrote something like the below. Management congratulated me on finding thousands more customers than we'd ever had before.

SELECT zipcode.rural_urban_code, COUNT(*) AS n_customer FROM customer INNER JOIN zipcode USING(zipcode) GROUP BY 1;

Re: SQL Anti-Patterns

#220

Post by Breza »

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 agree with you. I also find that adding DISTINCT can sometimes make it easier for my colleagues to understand code, especially when I'm using multiple CTEs and it might be easy to miss a one-to-many join.
Post reply on HN