128-column limit workarounds

[Roardom]

With a 128-limit column limit, what workarounds do people use to tailor their database schema to diesel's requirement?

For example, on a FOSS project I maintain, the users table itself has 46 columns, and each user has 1-to-1 relations with their general site customization settings (18 columns), their notification settings (32 columns) and their privacy settings (43 columns). While none of these table have reached 128 columns yet, combined they are over 128 columns, require more joins than necessary, and it's still something that remains on my mind.

Or another example, storing global site config settings in the database, one column per setting, which can be anything from site title, logo, contact email, default css theme, registration enable/disable, outbound email rate limit, user comment posting rate limit, default chatroom, ip/port for external services, 3rd party api keys among hundreds of other site config settings. While some may suggest putting these settings in an .env, that prevents administrators from editing them from the website admin panel, makes backups harder, and requires writing upgrade notes for any changes in the .env that could have been handled automatically via a database migration. Another option would be using a table with one column for a key, another for the value, but that prevents e.g. adding a foreign key constraint between the default chatroom and the chatrooms table, or ensuring that a value is an int and not a string. Another option yet is having multiple site config tables, grouped into batches of 128 settings or less.

I'm interested in hearing what other people have done to work around this limitation.

[weiznich]

It's rather unusual to have tables/queries with more than 128 values. It normally indicates that your database is heavily denormalized, which is an antipattern for relational databases. The usual way to go there is to normalize your database schema, so for example for your settings table instead of having a column per setting have a table settings which defines which settings exists (the column names for your other table) and a table settings_values which has 3 columns (settings_id, settings_value and whatver_references_settings_id). If you have several different settings types (integer, string, etc) you will have just several different settings_value columns on the later table. You then can use a check constraint to verify that the actual correct value column is used.

Increasing the number of supported columns is not an option as that will lead to another explosion of compile times. The correct way forward there is to wait language level support of variadic generics.

[Roardom]

Thank you for the response. Saving different types on the settings_values table would indeed be another way of going about it (e.g. with columns id, settings_id, integer_value, string_value, bool_value, chatroom_id,etc.). Drawbacks I see with this solution are ensuring to use the correct type column when querying, and not being able to enforce the existence of a particular setting without using triggers or app level logic. To enforce non null for all but the correct column also requires a bunch of CHECK ((settings_id IN (<list of ids of int settings>) AND integer_value IS NOT NULL AND string_value IS NULL AND bool_value IS NULL) OR ...).

However, you've made me realize that in our current design, there's nothing preventing the entire current settings row to exist at all at the database level and we already fall back to default settings in such a scenario. That said, given that the type of the setting (int, string, bool) can't be determined and enforced from the individual setting name using this method (even if nullable) and becomes a runtime error (e.g. trying to insert an int value into the integer_value column of a setting that's defined to only accept a bool, or selecting the integer_value column of a setting when it's defined to be stored in bool_value), I'm not sure how much better it would actually be. It might end up leading to falling back to storing everything as a string and parsing it as needed, reducing the benefit of the type system. I will be looking forward to variadic generics should they ever be implemented in rust. Or perhaps there's an optimization to be made somewhere to convert the recursive generics in rust to be O(n) with the number of columns, rather than the exponential (?) it might currently be (Although I'm sure that's already been explored in depth).

[weiznich]

Or perhaps there's an optimization to be made somewhere to convert the recursive generics in rust to be O(n) with the number of columns, rather than the exponential (?) it might currently be (Although I'm sure that's already been explored in depth).

You would need to reach out to the compiler team for that. The last time I checked there was quite a few thinks that might be a good target for such improvements.

[marvin-hansen]

@Roardom

If you really want super wide columns, consider using clickhouse for that as it can handle up to 10k columns.

https://altinity.com/blog/too-wide-or-not-too-wide-that-is-the-clickhouse-question

You really only want that if you have to de-normalize multiple massive tables (> billions of rows) into one big wide table for performance reasons. This became a lot less common these days as materialized views get you a long way.

That said, if you can fix your table design, do it in the first place. In postgres, you can use a few tricks:

• Table inheritance. Youp. You can create multiple specialized tables from one general one and that allows you to have multiple
  different tables for different configs while still sharing the same core columns
• Array types for repeated custom values. That's a no brainer
• Tuples / composite types. Instead of having sub-configs flattened into columns, just group them into one composite type.

Array types, custom types, and composite types are all well supported in Diesel. I don't know about table inheritance, but if I were to guess, Diesel most likely treats the sub-tables as ordinary tables while ignoring the super-table as long as your don't write wrapper classes for it.

[Roardom]

Thanks for this advice.

The wide tables in our database don't reach beyond a few 10s of thousands of rows in the largest of production instances currently (so the "normalized" version would only be a few million rows), so clickhouse probably isn't the right direction. However a handful of user settings are fetched frequently (and cached for 30 seconds) on every http request to generate each user's site layout/navbar/stylesheet.

Currently we're on mysql, but migration to postgres should be relatively straight forward and might be the best long-term solution, especially with the composite type support (thanks for mentioning this alternative), although our large multi-million row tables are very critical and handle a few thousand upserts per second from our api and might fare differently.

Being able to maintain using the type system is definitely an advantage to some of the other offered alternatives. Using the EAV (entity-attribute-value) model which the normalized model resembles is usually seen as an anti-pattern and treating all settings as strings requires a bunch of .parse().unwrap() which is very unlike rust. It also makes much more intuitive sense to select the columns from the settings table of the settings needed for that http request, instead of selecting each row for each setting needed. Am I missing something here?

[marvin-hansen]

You might be able to reduce the number of columns using arrays of comosite types as you are dealing mostly with configuration. See this example repo. https://github.com/marvin-hansen/bazel-diesel-postgres This also nicely reducs the number of writes as you only upsert one array of composite types instead of multiple records. As you are dealing with only a few 10k's of rows, proper caching may do more for performance than the underlying database. Take a look at dragonfly and check if it fits your requirements. https://www.dragonflydb.io Diesel code is way more robust, easier to test, and better to maintain so you are definitely on the right track.

…

On Wed, Aug 21, 2024 at 9:24 AM Roardom ***@***.***> wrote: Thanks for this advice. The wide tables in our database don't reach beyond a few 10s of thousands of rows in the largest of production instances currently (so the "normalized" version would only be a few million rows), so clickhouse probably isn't the right direction. However a handful of user settings are fetched frequently (and cached for 30 seconds) on every http request to generate each user's site layout/navbar/stylesheet. Currently we're on mysql, but migration to postgres should be relatively straight forward and might be the best long-term solution, especially with the composite type support (thanks for mentioning this alternative), although our large multi-million row tables are very critical and handle a few thousand upserts per second from our api and might fare differently. Being able to maintain using the type system is definitely an advantage to some of the other offered alternatives. Using the EAV (entity-attribute-value) model which the normalized model resembles is usually seen as an anti-pattern and treating all settings as strings requires a bunch of .parse().unwrap() which is very unlike rust. It also makes much more intuitive sense to select the columns from the settings table of the settings needed for that http request, instead of selecting each row for each setting needed. Am I missing something here? — Reply to this email directly, view it on GitHub <#4160 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFYR7XD6SPORSLKDBIRBOWDZSPT6HAVCNFSM6AAAAABMLNQJZKVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANBQGE3DGNY> . You are receiving this because you commented.Message ID: ***@***.***>

[Roardom]

Fantastic project, I read the readme and it was very well thought out.

We currently cache the entire settings row keyed by user_id into redis permanently until the user saves their settings again or until an update is deployed. We haven't yet seen the need to scale redis beyond a single instance however and received worse performance last time we tested out dragonflydb. We also tried keydb in a prod instance as a test for a few weeks one time before dragonflydb was popular which improved performance slightly, but it wasn't stable enough (crashed too often).

Although, wouldn't only a single composite type (or multiple composite types grouped by related config values) be necessary per row rather than an array of them, when dealing with configuration values? I might be misunderstanding your proposed schema for defining a set pre-determined configuration names and their associated types. On the other hand, some specific configuration settings could be of the array type which would make this very relevant/useful.

[marvin-hansen]

@Roardom

Your welcome. That project is on the way to get merged into the example folder, but the PR is still under review.. In the meantime, you can use the alternate link from my repo and ignore the Bazel stuff since the Cargo build works as expected.

if Reddis caching works, keep it. It's worth noting that the Dragonfly benchmarks are clear that you don't see a difference on smaller deployments; their improvements only kick in on large multi-core and multi-node deployments. Either way, if a single redidis node works well, you are doing something really right.

As for the composite and array type, the the general idea is as following:

Assume a normal toml config file, just for illustration:

# First block
[workspace.package]
edition = "2021"
rust-version = "1.79.0"
readme = "README.md"

# Second block
[workspace.dependencies]
anyhow = { version = "1.0" }
arc-swap = { version = "1.7" }
binance-rs-async = { version = "1.3.3", default-features = false, features = ["rustls-tls"] }

The first block is quite simple just a bunch of typed fields.

The second block has a bunch of repeated entries and, in postgres you can design a Dependency type
with a bunch of optional fields (i.e. default-features bool, features: string array)
and then define the entire second block as an array of dependencies.

Also, sometimes you have repeated key value pairs as it is often the case when storing JSON configs.
in that case, you put them in an Array with JsonKV being basically a custom type of type (String String).
Usually, the web developers like it a lot when they get some JSON back from the DB so you can just read that array of JsonKV
and use Serde to turn it back into proper JSON.

Hope that illustrates the big idea.

The rest is up to you.