9 ms·

Nobody ever got fired for using a struct

146 points by gz09 7mo ago

pmoati 6mo ago
Great writeup ! The bitmap trick is elegant and I've seen similar patterns in other contexts. The core insight resonates beyond Rust and SQL: the data structure that's "obvious" at design time can become a bottleneck when the real-world usage pattern diverges from your assumptions. Most fields exist vs most fields might not exist is a subtil but critical distinction. The fix being a simple layout change rather than a clever algorithm is also a good reminder. I've spent 20 years building apps and the most impactful optimizations were almost always about changing the shape of data, not adding complexity.
SoftTalker 7mo ago
> But SQL schemas often look like this. Columns are nullable by default, and wide tables are common. Hard disagree. That database table was a waving red flag. I don't know enough/any rust so don't really understand the rest of the article but I have never in my life worked with a database table that had 700 columns. Or even 100.
- xarope 7mo ago
  OLTP tables typically are normalized. But OLAP tables (data lake/warehouse stuff), for speed purposes, are intentionally denormalized and yes, you can have 100+ columns of nullable stuff.
- decremental 7mo ago
  [dead]
- gz09 7mo ago
  Hi, I'm the author of the article. As to your hard disagree, I guess it depends... While this particular user is on the higher end (in terms of columns), it's not our only user where column counts are huge. We see tables with 100+ columns on a fairly regular basis especially when dealing with larger enterprises.
  - sublinear 7mo ago
    Can you clarify which knowledge domains those enterprises fall under with examples of what problems they were trying to solve? If it's not obvious, I agree with the hard disagree. Every time I see a table with that many columns, I have a hard time believing there isn't some normalization possible. Schemas that stubbornly stick to high-level concepts and refuse to dig into the subfeatures of the data are often seen from inexperienced devs or dysfunctional/disorganized places too inflexible to care much. This isn't really negotiable. There will be issues with such a schema if it's meant to scale up or be migrated or maintained long term.
    - fiddlerwoaroof 7mo ago
      Normalization is possible but not practical in a lot of cases: nearly every “legacy” database I’ve seen has at least one table that just accumulates columns because that was the quickest way to ship something. Also, normalization solves a problem that’s present in OLTP applications: OLAP/Big Data applications generally have problems that are solved by denormalization.
      - gz09 7mo ago
        Yep, this comment sums it up well. We have many large enterprises from wildly different domains use feldera and from what I can tell there is no correlation between the domain and the amount of columns. As fiddlerwoaroof says, it seems to be more a function of how mature/big the company is and how much time it had to 'accumulate things' in their data model. And there might be very good reasons to design things the way they did, it's very hard to question it without being a domain expert in their field, I wouldn't dare :).
        locknitpicker 7mo ago
        > I can tell there is no correlation between the domain and the amount of columns. This is unbelievable. In purely architectural terms that would require your database design to be an amorphous big ball of everything, with no discernible design or modelling involved. This is completely unrealistic. Are queries done at random? In practical terms, your assertion is irrelevant. Look at the sparse columns. Figure out those with sparse rows. Then move half of the columns to a new table and keep the other half in the original table. Congratulations, you just cut down your column count by half, and sped up your queries. Even better: discover how your data is being used. Look at queries and check what fields are used in each case. Odds are, that's your table right there. Let's face it. There is absolutely no technical or architectural reason to reach this point. This problem is really not about structs.
- Mikhail_Edoshin 7mo ago
  I saw tables with more than a thousand columns. It was a law firm home-grown FileMaker tool. Didn't inspect it too closely, so don't know what was inside I remember a phrase from one of C. J. Date's books: every record is a logical statement. It really stood out for me and I keep returning to it. Such an understanding implies a rather small number of fields or the logical complexity will go through the roof.
  - p_v_doom 6mo ago
    > I saw tables with more than a thousand columns. I used to work in a company that had all the tags in their SCADA system feed into SQL tables. They had multiple tables (as in tens of tables), because they ran out of columns ...
- unclad5968 7mo ago
  It might not be common in typical software shops. I work in manufacturing and our database has multiple tables with hundreds of columns.
  - ambicapter 7mo ago
    What's in them?
    - jayanmn 7mo ago
      Property1 to 20 or more is an example. There are better ways to do it but I have seen columns for storing ‘anything’
      - Spivak 7mo ago
        Sounds like a generic form of single table inheritance. I don't honestly see any other way to do it (punting to a JSON field is effectively the same thing) when you have potentially thousands of parts all with their own super specific relevant attributes. I've worked on multiple products that have had a concept of "custom fields" who did it this way too.
    - unclad5968 7mo ago
      Data from measurement tools. Everything about the tool configuration, time of measurement, operator ID, usually a bunch of electrical data (we make laser diodes) like current, potential, power, and a bunch of emission related data.
      - arethuza 6mo ago
        I think I'd rather work with very wide tables than the entity-attribute-value approach - which seems like a good idea but rapidly becomes a mess...
  - pizza-wizard 7mo ago
    I’m working on migrating an IBM Maximo database from the late 90s to a SQL Server deployment on my current project. Also charged with updating the schema to a more maintainable and extensible design. Manufacturing and refurbishing domain - 200+ column tables is the norm. Very demoralizing.
- holden_nelson 7mo ago
  https://jimmyhmiller.com/ugliest-beautiful-codebase https://jimmyhmiller.com/ugliest-beautiful-codebase
  - linolevan 7mo ago
    This is awesome. Got completely lost reading this and was struggling to figure out where I got this link from. Amazing story.
  - roblh 7mo ago
    I kinda love this. That sounds like an incredibly entertaining place to work for between 1 and 2 years in your late 20s and not a second longer.
    - tdeck 7mo ago
      If you enjoyed this, you'd probably enjoy thedailywtf.com, which is full of stories like that.
  - bobson381 7mo ago
    This is like the functional ugly tier of buildings from "how buildings learn". Excellent stuff
  - lelanthran 7mo ago
    With AI "programmers", this will be the future: bugs galore and the things that do work, work by accident. I think this company was ahead of the curve.
  - locknitpicker 7mo ago
    The blog post is an entertaining read, but I was left with the impression the author might have tried do embellish, particularly in it's disbelief angle. Take this passage: > The app relied on a SOAP service, not to do any servicey things. No, the service was a pure function. It was the client that did all the side effects. In that client, I discovered a massive class hierarchy. 120 classes each with various methods, inheritance going 10 levels deep. The only problem? ALL THE METHODS WERE EMPTY. I do not exaggerate here. Not mostly empty. Empty. > That one stumped me for a while. Eventually, I learned this was in service of building a structure he could then use reflection on. That reflection would let him create a pipe-delimited string (whose structure was completely database-driven, but entirely static) that he would send over a socket. Classes with empty methods? Used reflection to create a pipe-delimited string? The string was sent over the wire? Why congratulations, you just rediscovered data transfer objects, specifically API models.
- woah 7mo ago
  No idea what these guys do exactly but their tagline says "Feldera's award-winning incremental compute engine runs SQL pipelines of any complexity" So it sounds like helping customers with databases full of red flags is their bread and butter
  - gz09 7mo ago
    > it sounds like helping customers with databases full of red flags is their bread and butter Yes that captures it well. Feldera is an incremental query engine. Loosely speaking: it computes answers to any of your SQL queries by doing work proportional to the incoming changes for your data (rather than the entire state of your database tables). If you have queries that take hours to compute in a traditional database like Spark/PostgreSQL/Snowflake (because of their complexity, or data size) and you want to always have the most up-to-date answer for your queries, feldera will give you that answer 'instantly' whenever your data changes (after you've back-filled your existing dataset into it). There is some more information about how it works under the hood here: https://docs.feldera.com/literature/papers https://docs.feldera.com/literature/papers
- nikhilsimha 7mo ago
  It is very common to find tables with 1000+ columns in machine learning training sets at e-commerce companies. The largest I have seen had over 10000 columns.
- bananamogul 7mo ago
  That statement jumped out at me as well. I've worked as a DBA on tons of databases backing a wide variety of ERPs, web apps, analytics, data warehouses...700 columns?!? No.
  - shakna 7mo ago
    You've never seen an SAP database where the business object had a couple hundred fields? Its pretty much required if you're touching international data.
- randallsquared 7mo ago
  I have seen tables (SQL and parquet, too) that have at least high hundreds of optional columns, but this was always understood to be a terrible hack, in those cases.
- wombatpm 7mo ago
  Not everyone understands normal form, much less 3rd normal form. I’ve seen people do worse with excel files where they ran out of columns and had to link across spreadsheets.
- vharuck 7mo ago
  https://apps.naaccr.org/data-dictionary/data-dictionary/version=26/chapter-view/data-descriptor-table/ https://apps.naaccr.org/data-dictionary/data-dictionary/vers... 771 columns (and I've read the definitions for them all, plus about 50 more that have been retired). In the database, these are split across at least 3 tables (registry, patient, tumor). But when working with the records, it's common to use one joined table. Luckily, even that usually fits in RAM.
  - deleted 7mo ago
    [deleted]
- orthoxerox 7mo ago
  It's OLAP, it very common for analytical tables to be denormalized. As an example, each UserAction row can include every field from Device and User to maximize the speed at which fraud detection works. You might even want to store multiple Devices in a single row: current, common 1, 2 and 3.
- shakna 7mo ago
  Salesforce by default comes with some where your tables have 50 columns before you start tweaking anything. 100s is not unusual. Thousands happens before you realise.
- locknitpicker 7mo ago
  > Hard disagree. That database table was a waving red flag. Exactly this. This article is not about structs or Rust. This article is about poor design of the whole persistence layer. I mean, hundreds of columns? Almost all of them optional? This is the kind of design that gets candidates to junior engineer positions kicked off a hiring round. Nobody gets fired for using a struct? If it's an organization that tolerates database tables with nearly 1k optional rows then that comes at no surprise.
  - grey-area 7mo ago
    The database table is someone else’s data. That’s why this company exists and is explained in the article. They don’t have the option to clean up the data.
    - gz09 7mo ago
      Yep pretty much, feldera is the engine we don't control what SQL people throw at us.
- adrianN 7mo ago
  If lots of columns are a red flag then red flags are quite common in many businesses. I’ve seen tables with tens of thousands of columns. Naturally those are not used by humans writing sql by hand, but there are many tools that have crazy data layouts and generate crazy sql to work with it.
- bob1029 7mo ago
  Some businesses are genuinely this complicated. Splitting those facts into additional tables isn't going to help very much unless it actually mirrors the shape of the business. If it doesn't align, you are forcing a lot of downstream joins for no good reason.
- magicalhippo 6mo ago
  > I have never in my life worked with a database table that had 700 columns Main table at work is about 600, though I suspect only 300-400 are actively used these days. A lot come from name and address fields, we have about 10 sets of those in the main table, and around 14 fields per. Back when this was created some 20+ years ago it was faster and easier to have it all in one row rather than to do 20+ joins. We probably would segment it a bit more if we did it from scratch, but only some.
arcrwlock 7mo ago
Why not use a struct of arrays? https://en.wikipedia.org/wiki/Data-oriented_design https://en.wikipedia.org/wiki/Data-oriented_design
- mustache_kimono 7mo ago
  > Why not use a struct of arrays? I would assume because then the shape of the data would be too different? SOAs is super effective when it suits the shape of the data. Here, the difference would be the difference between an OLTP and OLAP DB. And you wouldn't use an OLAP for an OLTP workload?
SigmundA 7mo ago
Looks like they just recreated a tuple layout in rust with null bit map and everything, next up would be storing them in pages and memmap the pages. https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-TUPLE-LAYOUT https://www.postgresql.org/docs/current/storage-page-layout....
- gz09 7mo ago
  Absolutely, it's a very common technique :) I wasn't sure about writing the article in the first place because of that, but I figured it may be interesting anyways because I was kind of happy with how simple it was to write this optimization when it was all done (when I started out with the task I wasn't sure if it would be hard because of how our code is structured, the libraries we use etc.). I originally posted this in the rust community, and it seems people enjoyed the post.
  - SigmundA 6mo ago
    I think its a good article and I enjoyed learning a little more about rust, but would have been nice to point out this is a common technique used for tuple storage in databases for those not familiar. It comes off as being a novel solution rather than connecting it to a long tradition of DB design. I believe PG for instance has used a null bitmap since the beginning 40 years ago.
    - gz09 6mo ago
      That would be surprising to me if anyone would think this is novel. Using bitmaps to indicate whether things are in-use or not is very common in systems programming. Like you said PG does it, but most other systems do this too. It's not specific to databases: in an operating system, one of the first thing it needs is an allocator, the allocator most likely will use some bitmap trick somewhere to indicate what's free vs. what's available etc.
      - SigmundA 6mo ago
        Interesting so you don't find it odd that an article about the storage engine of a SQL database system explains the solution to problem without once mentioning that it is the way most other sql database engines solve it? It is mentioned several times sql table are typically sparse, but not that this is how its typically solved, only this is how you solved it... >The fix is simple: we stop storing Option<T> and instead we store a bitmap that records which fields are None. Right here would have been the opportunity to let those not familiar with database internals know. Something like "This technique has been widely used in many RDBMS systems over the years, you can see the PG version of this in their documentation for page layout". Instead you go into detail on what a null bitmap is and how to implement it, calling it a "trick". Which is strange if you think your target audience is assumed to already know this common technique. I mean not one mention of the origin of the trick or even calling it a common solution to this problem...
astrostl 7mo ago
I have mixed feelings about it, but I'm going to fire somebody tomorrow for using a struct just to prove a point to the author.
- gz09 7mo ago
  Point them to us https://github.com/feldera/feldera https://github.com/feldera/feldera -- we are hiring ;)
  - adampunk 7mo ago
    You folks have too many structs already! I just finished reading about it!
deleted 7mo ago
[deleted]
dyauspitr 7mo ago
[flagged]
- jimbokun 7mo ago
  They’re pretty popular in Go?
  - kstrauser 7mo ago
    And pervasive in Rust.
- deleted 7mo ago
  [deleted]
duc_minh 7mo ago
> Sometimes the best optimization is not a clever algorithm. Sometimes it is just changing the shape of the data. This is basically Rob Pike's Rule 5: If you've chosen the right data structures and organized things well, the algorithms will almost always be self-evident.(https://users.ece.utexas.edu/~adnan/pike.html https://users.ece.utexas.edu/~adnan/pike.html)
- jeswin 7mo ago
  I wouldn't give too much credit to rules like this. Data structures are often created with an approach in mind. You can't design a data structure without knowing how you will use it. If anything it's the other way round, if you're not talking about business domain modeling (where data structures first is a valid approach).
  - sublinear 7mo ago
    If you don't know enough to design a data structure, requirements are missing and someone talking to the client is dropping the ball big time.
    - jeswin 7mo ago
      Where did I say any of that? I'm saying that if you care about performance, data structures should be designed with approach specific tradeoffs in mind. And like I've said above, in typical business apps, it's ok to start with data structures because (a) performance is usually not a problem, (b) staying close to the domain is cleaner.
      - reverius42 7mo ago
        You said: "You can't design a data structure without knowing how you will use it." But the whole discussion involves knowing how you will use it; the advocacy is for careful consideration of data structures (based on how you will use them) resulting in less pain when designing/choosing algorithms.
amluto 7mo ago
There are many systems that take a native data structure in your favorite language and, using some sort of reflection, makes an on-disk structure that resembles it. Python pickles and Java’s serialization system are infamous examples, and rkyv is a less alarming one. I am quite strongly of the opinion that one should essentially never use these for anything that needs to work well at any scale. If you need an industrial strength on-disk format, start with a tool for defining on-disk formats, and map back to your language. This gives you far better safety, portability across languages, and often performance as well. Depending on your needs, the right tool might be Parquet or Arrow or protobuf or Cap’n Proto or even JSON or XML or ASN.1. Note that there are zero programming languages in that list. The right choice is probably not C structs or pickles or some other language’s idea of pickles or even a really cool library that makes Rust do this. (OMG I just discovered rkyv_dyn. boggle. Did someone really attempt to reproduce the security catastrophe that is Java deserialization in Rust? Hint: Java is also memory-safe, and that has not saved users of Java deserialization from all the extremely high severity security holes that have shown up over the years. You can shoot yourself in the foot just fine when you point a cannon at your foot, even if the cannon has no undefined behavior.)
- neilyio 7mo ago
  Delightful metaphor, I'll be looking everywhere for a chance to use that now!
- gz09 7mo ago
  > Depending on your needs, the right tool might be Parquet or Arrow or protobuf or Cap’n Proto I think parquet and arrow are great formats, but ultimately they have to solve a similar problem that rkyv solves: for any given type that they support, what does the bit pattern look like in serialized form and in deserialized form (and how do I convert between the two). However, it is useful to point out that parquet/arrow on top of that solve many more problems needed to store data 'at scale' than rkyv (which is just a serialization framework after all): well defined data and file format, backward compatibility, bloom filters, run length encoding, compression, indexes, interoperability between languages, etc. etc.
- vlovich123 7mo ago
deleted 7mo ago
[deleted]
everyone 7mo ago
Just cus structs and classes work differently, and classes are much more common. I tend to make everything a class, unless there is a really good reason to make it a struct.
- bob1029 7mo ago
  Classes are a safe default even if you expect things to go very, very fast. The overhead of screwing up NUMA concerns vastly outstrips any kind of class vs struct differences. It's really one of the very last things you should be worrying about. Allocating an array of a class vs an array of struct might seem like you're getting a wildly different memory arrangement, but from the perspective of space & time this distinction is mostly pointless. Where the information resides at any given moment is the most important thing (L1/L2/L3/DRAM/SSD/GPU/AWS). Its shape is largely irrelevant.
- tialaramex 6mo ago
  > Just cus structs and classes work differently The most common programming language where "struct" and "class" are both kinds of user defined type is C++. In C++ they only "work differently" in that the default accessibility is different, a "struct" is the same as a "class" if you change the accessibility to public at the top with an access specifier. If you think you saw a bigger difference you're wrong.
saghm 7mo ago
I feel like I'm missing something, but the article started by talking about SQL tables, and then in-memory representations, and then on-disk representation, but...isn't storing it on a disk already what a SQL database is doing? It sounds like data is being read from a disk into memory in one format and then written back to a disk (maybe a different one?) in another format, and the second format was not as efficient as the first. I'm not sure I understand why a third format was even introduced in the first place.
- gz09 7mo ago
  Feldera is an incremental query engine, you can think of it as a specialized database. If you have a set of question you can express in SQL it will ingest all your data and build many sophisticated indexes for it (these get stored on disk). Whenever new data arrives feldera can instantly update the answers to all your questions. This is mostly useful when the data is much larger than what fits in memory because then the questions will be especially expensive to answer with a regular (batch) database. Feel free to try it out, it's open source: https://github.com/feldera/feldera/ https://github.com/feldera/feldera/
jim33442 7mo ago
I did read the rest, but I'm stuck on the first part where their SQL table has almost a thousand cols. Why so many?
deleted 7mo ago
[deleted]
logdahl 7mo ago
Strictly speaking, Isn't there still a way to express at least one Illegal string in ArchivedString? I'm not sure how to hint to the Rust compiler which values are illegal, but if the inline length (at most 15 characers) is aliased to the pointer string length (assume little-endian), wouldnt {ptr: null, len: 16} and {inline_data: {0...}, len: 16} both technically be an illegal value? I'm not saying this is better than your solution, just curious :^)
- gz09 7mo ago
  > Isn't there still a way to express at least one Illegal string in ArchivedString? There may be good reasons (I don't know any) why it wasn't done like this, but from a high-level it looks possible to me too yes.
- bombela 6mo ago
  In the code you will find union { {len, relptr}, [u8; 16] } The length is first. The pointer second. The inline string is terminated with 0xFF. The length is 62 bits out of 64 bits such that a specific pattern is placed in the first byte that utf8 doesn't collide with.
porise 7mo ago
Why is rust allowed to reorder fields? If I know that fields are going to be generally accessed together, this prevents me from ordering them so they fit in cache lines.
- Narishma 7mo ago
  You can tell it not to reorder them if you want but it's not the default.
- kzrdude 7mo ago
  It's allowed as an optimization, the order it uses will limit the space lost to field alignment.
- tialaramex 6mo ago
  You can choose in Rust to explain the representation you want for your data type. Unlike C or C++ that's not a non-portable vendor extension it's just part of the language, look at the repr documentation: https://doc.rust-lang.org/nomicon/other-reprs.html https://doc.rust-lang.org/nomicon/other-reprs.html So if you want "what C does" you can just repr(C) and that's what you get. For most people that's not a good trade unless they're doing FFI with a language that shares this representational choice.
  - porise 6mo ago
    Rusts tradeoff is awful. People organize struct members in the logical ways in which they will be accessed. Rust just decides to do things different so they can say they do things different and "better" than the old C. The featured article is an extreme case but other normal cases would have the same issue. Packing structs should be the exception.
jamesblonde 7mo ago
Here is an article I wrote this week with a section on Feldera - how it uses its incremental compute engine to compute "rolling aggregates" (the most important real-time feature for detecting changes in user behavior/pricing/anamalies). https://www.hopsworks.ai/post/rolling-aggregations-for-real-time-ai https://www.hopsworks.ai/post/rolling-aggregations-for-real-...
Ciantic 7mo ago
If I understand this problem was in rkyv, and solution is using rkyv with glue code. I hope they could integrate some sort of official derive macro `rkyv::Sparse` for this if it can't be done automatically in rkyv.
kleiba 7mo ago
> This struct we saw earlier had 700+ of optional fields. In Rust you would never design a struct like this. You would pick a different layout long before reaching 700 Options. But SQL schemas often look like this. Really? I've never had to do any serious db work in my career, but this is a surprise to me.
lsuresh 6mo ago
Feldera co-founder here. Great discussions here. Some folks pointed out that no one should design a SQL schema like this and I agree. We deal with large enterprise customers, and don't control the schemas that come our way. Trust me, we often ask customers if they have any leeway with changing their SQL and their hands are often tied. We're a query engine, so have to be able to ingest data from existing data sources (warehouse, lakehouse, kafka, etc.), so we have to be able to work with existing schemas. So what then follows is a big part of the value we add: which is, take your hideous SQL schema and queries, warts and all, run it on Feldera, and you'll get fully incremental execution at low latency and low cost. 700 isn't even the worst number that's come our way. A hyperscale prospect asked about supporting 4000 column schemas. I don't know what's in that table either. :)
- kwillets 6mo ago
  This site is underweighted on OLAP. Columnstores were invented for precisely this use case; nobody in the field wants to normalize everything. Which brings me to the question, why a rowstore? Are Z-sets hard to manage otherwise? Another aspect of wide tables is that they tend to have a lot of dependencies, ie different columns come from different aggregations, and the whole table gets held up if one of them is late. IVM seems like a good solution for that problem.
  - lsuresh 6mo ago
    Good questions! Feldera tries to be row- and column-oriented in the respective parts that matter. E.g. our LSM trees only store the set of columns that are needed, and we need to be able to pick up individual rows from within those columns for the different operators. I don't think we've converged on the best design yet here though. We're constantly experimenting with different layouts to see what performs best based on customer workloads.
kristianp 6mo ago
> A new use case processed about the same amount of data as their existing pipelines, but it ran much slower. Did I miss something? They didn't mention why the new use case was slower. I was expecting some callback to that new usecase in the article somewhere. Always enjoy reading about performance debugging, thanks for writing this. Edit: they didn't talk about profiling either. It was an enjoyable read of rust serialisation for non rusty people though.
- lsuresh 6mo ago
  We use an in-product profiler (discussed here: https://www.feldera.com/blog/introducing-feldera's-visual-profiler https://www.feldera.com/blog/introducing-feldera's-visual-pr...), along with CPU profiles to identify where in the code we're spending time.

146 points by gz09 7mo ago

Great writeup ! The bitmap trick is elegant and I've seen similar patterns in other contexts. The core insight resonates beyond Rust and SQL: the data structure that's "obvious" at design time can become a bottleneck when the real-world usage pattern diverges from your assumptions. Most fields exist vs most fields might not exist is a subtil but critical distinction. The fix being a simple layout change rather than a clever algorithm is also a good reminder. I've spent 20 years building apps and the most impactful optimizations were almost always about changing the shape of data, not adding complexity.

SoftTalker 7mo ago

> But SQL schemas often look like this. Columns are nullable by default, and wide tables are common. Hard disagree. That database table was a waving red flag. I don't know enough/any rust so don't really understand the rest of the article but I have never in my life worked with a database table that had 700 columns. Or even 100.

xarope 7mo ago
OLTP tables typically are normalized. But OLAP tables (data lake/warehouse stuff), for speed purposes, are intentionally denormalized and yes, you can have 100+ columns of nullable stuff.
decremental 7mo ago
[dead]
gz09 7mo ago
Hi, I'm the author of the article. As to your hard disagree, I guess it depends... While this particular user is on the higher end (in terms of columns), it's not our only user where column counts are huge. We see tables with 100+ columns on a fairly regular basis especially when dealing with larger enterprises.
- sublinear 7mo ago
  Can you clarify which knowledge domains those enterprises fall under with examples of what problems they were trying to solve? If it's not obvious, I agree with the hard disagree. Every time I see a table with that many columns, I have a hard time believing there isn't some normalization possible. Schemas that stubbornly stick to high-level concepts and refuse to dig into the subfeatures of the data are often seen from inexperienced devs or dysfunctional/disorganized places too inflexible to care much. This isn't really negotiable. There will be issues with such a schema if it's meant to scale up or be migrated or maintained long term.
  - fiddlerwoaroof 7mo ago
    Normalization is possible but not practical in a lot of cases: nearly every “legacy” database I’ve seen has at least one table that just accumulates columns because that was the quickest way to ship something. Also, normalization solves a problem that’s present in OLTP applications: OLAP/Big Data applications generally have problems that are solved by denormalization.
    - gz09 7mo ago
      Yep, this comment sums it up well. We have many large enterprises from wildly different domains use feldera and from what I can tell there is no correlation between the domain and the amount of columns. As fiddlerwoaroof says, it seems to be more a function of how mature/big the company is and how much time it had to 'accumulate things' in their data model. And there might be very good reasons to design things the way they did, it's very hard to question it without being a domain expert in their field, I wouldn't dare :).
      - locknitpicker 7mo ago
        > I can tell there is no correlation between the domain and the amount of columns. This is unbelievable. In purely architectural terms that would require your database design to be an amorphous big ball of everything, with no discernible design or modelling involved. This is completely unrealistic. Are queries done at random? In practical terms, your assertion is irrelevant. Look at the sparse columns. Figure out those with sparse rows. Then move half of the columns to a new table and keep the other half in the original table. Congratulations, you just cut down your column count by half, and sped up your queries. Even better: discover how your data is being used. Look at queries and check what fields are used in each case. Odds are, that's your table right there. Let's face it. There is absolutely no technical or architectural reason to reach this point. This problem is really not about structs.
Mikhail_Edoshin 7mo ago
I saw tables with more than a thousand columns. It was a law firm home-grown FileMaker tool. Didn't inspect it too closely, so don't know what was inside I remember a phrase from one of C. J. Date's books: every record is a logical statement. It really stood out for me and I keep returning to it. Such an understanding implies a rather small number of fields or the logical complexity will go through the roof.
- p_v_doom 6mo ago
  > I saw tables with more than a thousand columns. I used to work in a company that had all the tags in their SCADA system feed into SQL tables. They had multiple tables (as in tens of tables), because they ran out of columns ...
unclad5968 7mo ago
It might not be common in typical software shops. I work in manufacturing and our database has multiple tables with hundreds of columns.
- ambicapter 7mo ago
  What's in them?
  - jayanmn 7mo ago
    Property1 to 20 or more is an example. There are better ways to do it but I have seen columns for storing ‘anything’
    - Spivak 7mo ago
      Sounds like a generic form of single table inheritance. I don't honestly see any other way to do it (punting to a JSON field is effectively the same thing) when you have potentially thousands of parts all with their own super specific relevant attributes. I've worked on multiple products that have had a concept of "custom fields" who did it this way too.
  - unclad5968 7mo ago
    Data from measurement tools. Everything about the tool configuration, time of measurement, operator ID, usually a bunch of electrical data (we make laser diodes) like current, potential, power, and a bunch of emission related data.
    - arethuza 6mo ago
      I think I'd rather work with very wide tables than the entity-attribute-value approach - which seems like a good idea but rapidly becomes a mess...
- pizza-wizard 7mo ago
  I’m working on migrating an IBM Maximo database from the late 90s to a SQL Server deployment on my current project. Also charged with updating the schema to a more maintainable and extensible design. Manufacturing and refurbishing domain - 200+ column tables is the norm. Very demoralizing.
holden_nelson 7mo ago
https://jimmyhmiller.com/ugliest-beautiful-codebase https://jimmyhmiller.com/ugliest-beautiful-codebase
- linolevan 7mo ago
  This is awesome. Got completely lost reading this and was struggling to figure out where I got this link from. Amazing story.
- roblh 7mo ago
  I kinda love this. That sounds like an incredibly entertaining place to work for between 1 and 2 years in your late 20s and not a second longer.
  - tdeck 7mo ago
    If you enjoyed this, you'd probably enjoy thedailywtf.com, which is full of stories like that.
- bobson381 7mo ago
  This is like the functional ugly tier of buildings from "how buildings learn". Excellent stuff
- lelanthran 7mo ago
  With AI "programmers", this will be the future: bugs galore and the things that do work, work by accident. I think this company was ahead of the curve.
- locknitpicker 7mo ago
  The blog post is an entertaining read, but I was left with the impression the author might have tried do embellish, particularly in it's disbelief angle. Take this passage: > The app relied on a SOAP service, not to do any servicey things. No, the service was a pure function. It was the client that did all the side effects. In that client, I discovered a massive class hierarchy. 120 classes each with various methods, inheritance going 10 levels deep. The only problem? ALL THE METHODS WERE EMPTY. I do not exaggerate here. Not mostly empty. Empty. > That one stumped me for a while. Eventually, I learned this was in service of building a structure he could then use reflection on. That reflection would let him create a pipe-delimited string (whose structure was completely database-driven, but entirely static) that he would send over a socket. Classes with empty methods? Used reflection to create a pipe-delimited string? The string was sent over the wire? Why congratulations, you just rediscovered data transfer objects, specifically API models.
woah 7mo ago
No idea what these guys do exactly but their tagline says "Feldera's award-winning incremental compute engine runs SQL pipelines of any complexity" So it sounds like helping customers with databases full of red flags is their bread and butter
- gz09 7mo ago
  > it sounds like helping customers with databases full of red flags is their bread and butter Yes that captures it well. Feldera is an incremental query engine. Loosely speaking: it computes answers to any of your SQL queries by doing work proportional to the incoming changes for your data (rather than the entire state of your database tables). If you have queries that take hours to compute in a traditional database like Spark/PostgreSQL/Snowflake (because of their complexity, or data size) and you want to always have the most up-to-date answer for your queries, feldera will give you that answer 'instantly' whenever your data changes (after you've back-filled your existing dataset into it). There is some more information about how it works under the hood here: https://docs.feldera.com/literature/papers https://docs.feldera.com/literature/papers
nikhilsimha 7mo ago
It is very common to find tables with 1000+ columns in machine learning training sets at e-commerce companies. The largest I have seen had over 10000 columns.
bananamogul 7mo ago
That statement jumped out at me as well. I've worked as a DBA on tons of databases backing a wide variety of ERPs, web apps, analytics, data warehouses...700 columns?!? No.
- shakna 7mo ago
  You've never seen an SAP database where the business object had a couple hundred fields? Its pretty much required if you're touching international data.
randallsquared 7mo ago
I have seen tables (SQL and parquet, too) that have at least high hundreds of optional columns, but this was always understood to be a terrible hack, in those cases.
wombatpm 7mo ago
Not everyone understands normal form, much less 3rd normal form. I’ve seen people do worse with excel files where they ran out of columns and had to link across spreadsheets.
vharuck 7mo ago
https://apps.naaccr.org/data-dictionary/data-dictionary/version=26/chapter-view/data-descriptor-table/ https://apps.naaccr.org/data-dictionary/data-dictionary/vers... 771 columns (and I've read the definitions for them all, plus about 50 more that have been retired). In the database, these are split across at least 3 tables (registry, patient, tumor). But when working with the records, it's common to use one joined table. Luckily, even that usually fits in RAM.
- deleted 7mo ago
  [deleted]
orthoxerox 7mo ago
It's OLAP, it very common for analytical tables to be denormalized. As an example, each UserAction row can include every field from Device and User to maximize the speed at which fraud detection works. You might even want to store multiple Devices in a single row: current, common 1, 2 and 3.
shakna 7mo ago
Salesforce by default comes with some where your tables have 50 columns before you start tweaking anything. 100s is not unusual. Thousands happens before you realise.
locknitpicker 7mo ago
> Hard disagree. That database table was a waving red flag. Exactly this. This article is not about structs or Rust. This article is about poor design of the whole persistence layer. I mean, hundreds of columns? Almost all of them optional? This is the kind of design that gets candidates to junior engineer positions kicked off a hiring round. Nobody gets fired for using a struct? If it's an organization that tolerates database tables with nearly 1k optional rows then that comes at no surprise.
- grey-area 7mo ago
  The database table is someone else’s data. That’s why this company exists and is explained in the article. They don’t have the option to clean up the data.
  - gz09 7mo ago
    Yep pretty much, feldera is the engine we don't control what SQL people throw at us.
adrianN 7mo ago
If lots of columns are a red flag then red flags are quite common in many businesses. I’ve seen tables with tens of thousands of columns. Naturally those are not used by humans writing sql by hand, but there are many tools that have crazy data layouts and generate crazy sql to work with it.
bob1029 7mo ago
Some businesses are genuinely this complicated. Splitting those facts into additional tables isn't going to help very much unless it actually mirrors the shape of the business. If it doesn't align, you are forcing a lot of downstream joins for no good reason.
magicalhippo 6mo ago
> I have never in my life worked with a database table that had 700 columns Main table at work is about 600, though I suspect only 300-400 are actively used these days. A lot come from name and address fields, we have about 10 sets of those in the main table, and around 14 fields per. Back when this was created some 20+ years ago it was faster and easier to have it all in one row rather than to do 20+ joins. We probably would segment it a bit more if we did it from scratch, but only some.

arcrwlock 7mo ago

Why not use a struct of arrays? https://en.wikipedia.org/wiki/Data-oriented_design https://en.wikipedia.org/wiki/Data-oriented_design

mustache_kimono 7mo ago
> Why not use a struct of arrays? I would assume because then the shape of the data would be too different? SOAs is super effective when it suits the shape of the data. Here, the difference would be the difference between an OLTP and OLAP DB. And you wouldn't use an OLAP for an OLTP workload?

SigmundA 7mo ago

Looks like they just recreated a tuple layout in rust with null bit map and everything, next up would be storing them in pages and memmap the pages. https://www.postgresql.org/docs/current/storage-page-layout.html#STORAGE-TUPLE-LAYOUT https://www.postgresql.org/docs/current/storage-page-layout....

gz09 7mo ago
Absolutely, it's a very common technique :) I wasn't sure about writing the article in the first place because of that, but I figured it may be interesting anyways because I was kind of happy with how simple it was to write this optimization when it was all done (when I started out with the task I wasn't sure if it would be hard because of how our code is structured, the libraries we use etc.). I originally posted this in the rust community, and it seems people enjoyed the post.
- SigmundA 6mo ago
  I think its a good article and I enjoyed learning a little more about rust, but would have been nice to point out this is a common technique used for tuple storage in databases for those not familiar. It comes off as being a novel solution rather than connecting it to a long tradition of DB design. I believe PG for instance has used a null bitmap since the beginning 40 years ago.
  - gz09 6mo ago
    That would be surprising to me if anyone would think this is novel. Using bitmaps to indicate whether things are in-use or not is very common in systems programming. Like you said PG does it, but most other systems do this too. It's not specific to databases: in an operating system, one of the first thing it needs is an allocator, the allocator most likely will use some bitmap trick somewhere to indicate what's free vs. what's available etc.
    - SigmundA 6mo ago
      Interesting so you don't find it odd that an article about the storage engine of a SQL database system explains the solution to problem without once mentioning that it is the way most other sql database engines solve it? It is mentioned several times sql table are typically sparse, but not that this is how its typically solved, only this is how you solved it... >The fix is simple: we stop storing Option<T> and instead we store a bitmap that records which fields are None. Right here would have been the opportunity to let those not familiar with database internals know. Something like "This technique has been widely used in many RDBMS systems over the years, you can see the PG version of this in their documentation for page layout". Instead you go into detail on what a null bitmap is and how to implement it, calling it a "trick". Which is strange if you think your target audience is assumed to already know this common technique. I mean not one mention of the origin of the trick or even calling it a common solution to this problem...

astrostl 7mo ago

I have mixed feelings about it, but I'm going to fire somebody tomorrow for using a struct just to prove a point to the author.

gz09 7mo ago
Point them to us https://github.com/feldera/feldera https://github.com/feldera/feldera -- we are hiring ;)
- adampunk 7mo ago
  You folks have too many structs already! I just finished reading about it!

deleted 7mo ago

[deleted]

dyauspitr 7mo ago

[flagged]

jimbokun 7mo ago
They’re pretty popular in Go?
- kstrauser 7mo ago
  And pervasive in Rust.
deleted 7mo ago
[deleted]

duc_minh 7mo ago

> Sometimes the best optimization is not a clever algorithm. Sometimes it is just changing the shape of the data. This is basically Rob Pike's Rule 5: If you've chosen the right data structures and organized things well, the algorithms will almost always be self-evident.(https://users.ece.utexas.edu/~adnan/pike.html https://users.ece.utexas.edu/~adnan/pike.html)

jeswin 7mo ago
I wouldn't give too much credit to rules like this. Data structures are often created with an approach in mind. You can't design a data structure without knowing how you will use it. If anything it's the other way round, if you're not talking about business domain modeling (where data structures first is a valid approach).
- sublinear 7mo ago
  If you don't know enough to design a data structure, requirements are missing and someone talking to the client is dropping the ball big time.
  - jeswin 7mo ago
    Where did I say any of that? I'm saying that if you care about performance, data structures should be designed with approach specific tradeoffs in mind. And like I've said above, in typical business apps, it's ok to start with data structures because (a) performance is usually not a problem, (b) staying close to the domain is cleaner.
    - reverius42 7mo ago
      You said: "You can't design a data structure without knowing how you will use it." But the whole discussion involves knowing how you will use it; the advocacy is for careful consideration of data structures (based on how you will use them) resulting in less pain when designing/choosing algorithms.

amluto 7mo ago

There are many systems that take a native data structure in your favorite language and, using some sort of reflection, makes an on-disk structure that resembles it. Python pickles and Java’s serialization system are infamous examples, and rkyv is a less alarming one. I am quite strongly of the opinion that one should essentially never use these for anything that needs to work well at any scale. If you need an industrial strength on-disk format, start with a tool for defining on-disk formats, and map back to your language. This gives you far better safety, portability across languages, and often performance as well. Depending on your needs, the right tool might be Parquet or Arrow or protobuf or Cap’n Proto or even JSON or XML or ASN.1. Note that there are zero programming languages in that list. The right choice is probably not C structs or pickles or some other language’s idea of pickles or even a really cool library that makes Rust do this. (OMG I just discovered rkyv_dyn. boggle. Did someone really attempt to reproduce the security catastrophe that is Java deserialization in Rust? Hint: Java is also memory-safe, and that has not saved users of Java deserialization from all the extremely high severity security holes that have shown up over the years. You can shoot yourself in the foot just fine when you point a cannon at your foot, even if the cannon has no undefined behavior.)

neilyio 7mo ago
Delightful metaphor, I'll be looking everywhere for a chance to use that now!
gz09 7mo ago
> Depending on your needs, the right tool might be Parquet or Arrow or protobuf or Cap’n Proto I think parquet and arrow are great formats, but ultimately they have to solve a similar problem that rkyv solves: for any given type that they support, what does the bit pattern look like in serialized form and in deserialized form (and how do I convert between the two). However, it is useful to point out that parquet/arrow on top of that solve many more problems needed to store data 'at scale' than rkyv (which is just a serialization framework after all): well defined data and file format, backward compatibility, bloom filters, run length encoding, compression, indexes, interoperability between languages, etc. etc.
vlovich123 7mo ago

deleted 7mo ago

[deleted]

everyone 7mo ago

Just cus structs and classes work differently, and classes are much more common. I tend to make everything a class, unless there is a really good reason to make it a struct.

bob1029 7mo ago
Classes are a safe default even if you expect things to go very, very fast. The overhead of screwing up NUMA concerns vastly outstrips any kind of class vs struct differences. It's really one of the very last things you should be worrying about. Allocating an array of a class vs an array of struct might seem like you're getting a wildly different memory arrangement, but from the perspective of space & time this distinction is mostly pointless. Where the information resides at any given moment is the most important thing (L1/L2/L3/DRAM/SSD/GPU/AWS). Its shape is largely irrelevant.
tialaramex 6mo ago
> Just cus structs and classes work differently The most common programming language where "struct" and "class" are both kinds of user defined type is C++. In C++ they only "work differently" in that the default accessibility is different, a "struct" is the same as a "class" if you change the accessibility to public at the top with an access specifier. If you think you saw a bigger difference you're wrong.

saghm 7mo ago

I feel like I'm missing something, but the article started by talking about SQL tables, and then in-memory representations, and then on-disk representation, but...isn't storing it on a disk already what a SQL database is doing? It sounds like data is being read from a disk into memory in one format and then written back to a disk (maybe a different one?) in another format, and the second format was not as efficient as the first. I'm not sure I understand why a third format was even introduced in the first place.

gz09 7mo ago
Feldera is an incremental query engine, you can think of it as a specialized database. If you have a set of question you can express in SQL it will ingest all your data and build many sophisticated indexes for it (these get stored on disk). Whenever new data arrives feldera can instantly update the answers to all your questions. This is mostly useful when the data is much larger than what fits in memory because then the questions will be especially expensive to answer with a regular (batch) database. Feel free to try it out, it's open source: https://github.com/feldera/feldera/ https://github.com/feldera/feldera/

jim33442 7mo ago

I did read the rest, but I'm stuck on the first part where their SQL table has almost a thousand cols. Why so many?

deleted 7mo ago

[deleted]

logdahl 7mo ago

Strictly speaking, Isn't there still a way to express at least one Illegal string in ArchivedString? I'm not sure how to hint to the Rust compiler which values are illegal, but if the inline length (at most 15 characers) is aliased to the pointer string length (assume little-endian), wouldnt {ptr: null, len: 16} and {inline_data: {0...}, len: 16} both technically be an illegal value? I'm not saying this is better than your solution, just curious :^)

gz09 7mo ago
> Isn't there still a way to express at least one Illegal string in ArchivedString? There may be good reasons (I don't know any) why it wasn't done like this, but from a high-level it looks possible to me too yes.
bombela 6mo ago
In the code you will find union { {len, relptr}, [u8; 16] } The length is first. The pointer second. The inline string is terminated with 0xFF. The length is 62 bits out of 64 bits such that a specific pattern is placed in the first byte that utf8 doesn't collide with.

porise 7mo ago

Why is rust allowed to reorder fields? If I know that fields are going to be generally accessed together, this prevents me from ordering them so they fit in cache lines.

Narishma 7mo ago
You can tell it not to reorder them if you want but it's not the default.
kzrdude 7mo ago
It's allowed as an optimization, the order it uses will limit the space lost to field alignment.
tialaramex 6mo ago
You can choose in Rust to explain the representation you want for your data type. Unlike C or C++ that's not a non-portable vendor extension it's just part of the language, look at the repr documentation: https://doc.rust-lang.org/nomicon/other-reprs.html https://doc.rust-lang.org/nomicon/other-reprs.html So if you want "what C does" you can just repr(C) and that's what you get. For most people that's not a good trade unless they're doing FFI with a language that shares this representational choice.
- porise 6mo ago
  Rusts tradeoff is awful. People organize struct members in the logical ways in which they will be accessed. Rust just decides to do things different so they can say they do things different and "better" than the old C. The featured article is an extreme case but other normal cases would have the same issue. Packing structs should be the exception.

jamesblonde 7mo ago

Here is an article I wrote this week with a section on Feldera - how it uses its incremental compute engine to compute "rolling aggregates" (the most important real-time feature for detecting changes in user behavior/pricing/anamalies). https://www.hopsworks.ai/post/rolling-aggregations-for-real-time-ai https://www.hopsworks.ai/post/rolling-aggregations-for-real-...

Ciantic 7mo ago

If I understand this problem was in rkyv, and solution is using rkyv with glue code. I hope they could integrate some sort of official derive macro `rkyv::Sparse` for this if it can't be done automatically in rkyv.

kleiba 7mo ago

> This struct we saw earlier had 700+ of optional fields. In Rust you would never design a struct like this. You would pick a different layout long before reaching 700 Options. But SQL schemas often look like this. Really? I've never had to do any serious db work in my career, but this is a surprise to me.

lsuresh 6mo ago

Feldera co-founder here. Great discussions here. Some folks pointed out that no one should design a SQL schema like this and I agree. We deal with large enterprise customers, and don't control the schemas that come our way. Trust me, we often ask customers if they have any leeway with changing their SQL and their hands are often tied. We're a query engine, so have to be able to ingest data from existing data sources (warehouse, lakehouse, kafka, etc.), so we have to be able to work with existing schemas. So what then follows is a big part of the value we add: which is, take your hideous SQL schema and queries, warts and all, run it on Feldera, and you'll get fully incremental execution at low latency and low cost. 700 isn't even the worst number that's come our way. A hyperscale prospect asked about supporting 4000 column schemas. I don't know what's in that table either. :)

kwillets 6mo ago
This site is underweighted on OLAP. Columnstores were invented for precisely this use case; nobody in the field wants to normalize everything. Which brings me to the question, why a rowstore? Are Z-sets hard to manage otherwise? Another aspect of wide tables is that they tend to have a lot of dependencies, ie different columns come from different aggregations, and the whole table gets held up if one of them is late. IVM seems like a good solution for that problem.
- lsuresh 6mo ago
  Good questions! Feldera tries to be row- and column-oriented in the respective parts that matter. E.g. our LSM trees only store the set of columns that are needed, and we need to be able to pick up individual rows from within those columns for the different operators. I don't think we've converged on the best design yet here though. We're constantly experimenting with different layouts to see what performs best based on customer workloads.

kristianp 6mo ago

> A new use case processed about the same amount of data as their existing pipelines, but it ran much slower. Did I miss something? They didn't mention why the new use case was slower. I was expecting some callback to that new usecase in the article somewhere. Always enjoy reading about performance debugging, thanks for writing this. Edit: they didn't talk about profiling either. It was an enjoyable read of rust serialisation for non rusty people though.

lsuresh 6mo ago
We use an in-product profiler (discussed here: https://www.feldera.com/blog/introducing-feldera's-visual-profiler https://www.feldera.com/blog/introducing-feldera's-visual-pr...), along with CPU profiles to identify where in the code we're spending time.