The potential migration to diesel

[drahnr]

Experience through #928 as a start and continuation of #921

What is diesel?

diesel or diesel-rs has been one of the long standing ORM tools in the rust ecosystem for SQL database. It allows to derive a schema from an existing database with a standalone tool, and from there on reference that schema via strongly typed queries.

tl;dr

The code is cleaner, the enforcement at the type level is helpful to catch errors early, but at the cost of an additional syntax to learn and interpretation of the error messages is required. After spending a few days with it, writing queries become natural, with autocompletion.
For me personally, there is a clear upside, it's not as significant as initially anticipated though.

🔼 Upsides

The upsides of diesel cover a few features:

• allows direct SQL query execution
• query expression with generated types
• generating plain SQL queries for validation
• builtin query caching
• given our limited use of SQL featurs, we can swap in/out the 3 supported backends
  The history of diesel is well supported.

🚧 Outstanding

Currently a few intermediate types are created when querying. diesel has a few ways of allowing the mapping of complex types. These should have compounding effects, but have not been implemented yet.

🕳️ Practical Pitfalls

Error Complexity

Particularly around selecting more complex types in join_left and join statements, the a mismatch of .select((schema::foo::bar, schema::foo::baz)) or using schema::foo::all_columns any mismatch between the schema and the provided column-selectors will generate type-mismatch-errors that are hard to diagnose. They oftentimes are so large, that they end up in external files.

Incomprehensible issues with some features

Using

#[derive(Selectable, QueryablByName)]
struct Bar { a: i64, b: i32 }

#[derive(Selectable, QueryablByName)]
struct Foo {
  #[diesel(embed)] // < won't work with `<T as SelectHelper>::as_select()`
  bar: Bar,
  y: i64,
}

will always result in one of the wall-sized error messages mentioned in the previous section. Unfortunately I have not found a way to make this work in left_join or join queries, other than flattening the struct manually or falling back to (schema::foo::bar, schema::foo::baz) specification. CustomType::as_select() was not working despite triple checking columns and ordering correctness, as well as trimming the complexity down. :h

Type resolution

Using rust 1.85 oftentimes multiple variants of a function exist and require to use fully qualifying syntax, i.e. schema::foo::table.select(Foo::as_select()) often needs to be expressed as SelectDsl::select(schema::foo::table, Foo::as_select()) , since there is an ambiguity with QueryDsl::select.

At other times, avoiding sub-queries rather than nested queries is a better and easier to understand approach. The error messages also become easier.

[bobbinth]

Thank you for such a detailed summary!

One question: do we have a sense of how/if it affects query performance?

[drahnr]

Currently we have perf measurements for 3 API calls using rusqlite, I'll add some data here once I have it, and analyze the delta, if any.

[drahnr]

⚠️ I only did a single run of both, so this could very well be outliers or overlaps with other CPU/IO tasks that skewed the values, it's an indicator, more numbers to follow ⚠️

With diesel:

Total time: 4754.078 seconds
Inserted 3922 blocks with avg insertion time 476 ms
Initial DB size: 112.0 KB
Average DB growth rate: 340.3 KB per block

Block metrics:
Block #    Insert Time (ms)     Get Block Inputs Time (ms)     Get Batch Inputs Time (ms)     Block Size (B)       DB Size (KB)        
---------------------------------------------------------------------------------------------------------------------------------------
0          68                   41                             1                              374138               409.8               
50         365                  14                             43                             431531               25114.6             
100        395                  14                             2                              431531               41890.6             
150        425                  15                             43                             431531               58847.2             
200        399                  15                             2                              431531               76253.6             
250        413                  15                             42                             431531               93805.1             
300        451                  25                             43                             431531               111195.3            
350        444                  15                             43                             431531               128232.1            
400        422                  17                             43                             431531               145609.9            
450        444                  58                             43                             431531               162526.6            
500        434                  18                             3                              431531               179932.1            
550        436                  18                             43                             431531               196816.9            
600        440                  17                             2                              431531               213892.9            
650        445                  18                             44                             431531               230954.4            
700        440                  19                             43                             431531               247950.4            
750        461                  19                             43                             431531               265114.9            
800        445                  19                             43                             431531               282143.5            
850        459                  18                             42                             431531               298867.7            
900        475                  18                             43                             431531               316176.6            
950        435                  61                             43                             431531               332964.6            
1000       461                  20                             3                              431531               350008.6            
1050       458                  27                             43                             431531               366984.6            
1100       441                  20                             43                             431531               383996.8            
1150       489                  19                             42                             431531               401221.9            
1200       479                  26                             43                             431531               418209.9            
1250       452                  20                             43                             431531               434885.9            
1300       455                  20                             2                              431531               451873.9            
1350       435                  21                             2                              431531               468885.9            
1400       485                  20                             43                             431531               486162.2            
1450       503                  24                             43                             431531               503174.2            
1500       502                  20                             43                             431531               519962.2            
1550       495                  20                             43                             431531               536810.2            
1600       474                  21                             43                             431531               553758.7            
1650       490                  21                             43                             431531               571027.0            
1700       465                  63                             44                             431531               588135.0            
1750       484                  60                             43                             431531               605147.0            
1800       481                  20                             43                             431531               621843.0            
1850       502                  21                             44                             431531               638631.0            
1900       470                  22                             43                             431531               655675.0            
1950       472                  62                             44                             431531               672659.0            
2000       491                  21                             43                             431531               689871.0            
2050       462                  21                             44                             431531               706835.0            
2100       494                  23                             43                             431531               723771.0            
2150       500                  21                             43                             431531               740763.0            
2200       471                  22                             44                             431531               757911.0            
2250       498                  23                             44                             431531               774647.0            
2300       514                  22                             3                              431531               791519.3            
2350       474                  22                             2                              431531               808355.7            
2400       484                  22                             43                             431531               825539.8            
2450       471                  22                             43                             431531               842539.8            
2500       491                  22                             43                             431531               859487.8            
2550       524                  23                             44                             431531               876447.8            
2600       501                  65                             44                             431531               893652.4            
2650       496                  64                             43                             431531               910488.4            
2700       518                  64                             42                             431531               927368.4            
2750       518                  22                             3                              431531               944288.4            
2800       497                  23                             44                             431531               961216.4            
2850       530                  23                             44                             431531               978048.4            
2900       500                  22                             43                             431531               995212.6            
2950       487                  22                             43                             431531               1012224.6           
3000       526                  22                             44                             431531               1029292.6           
3050       519                  23                             44                             431531               1046184.6           
3100       495                  23                             43                             431531               1063156.6           
3150       529                  22                             3                              431531               1079840.6           
3200       518                  23                             4                              431531               1096928.6           
3250       517                  22                             44                             431531               1114004.6           
3300       553                  24                             43                             431531               1131120.6           
3350       538                  22                             43                             431531               1148288.6           
3400       512                  24                             42                             431531               1165609.3           
3450       489                  24                             43                             431531               1182229.3           
3500       522                  63                             44                             431531               1199153.3           
3550       513                  28                             5                              431531               1216125.3           
3600       506                  23                             3                              431531               1233077.3           
3650       525                  25                             43                             431531               1250061.3           
3700       515                  23                             3                              431531               1266817.3           
3750       533                  23                             44                             431531               1283901.3           
3800       501                  22                             44                             431531               1300585.3           
3850       500                  23                             44                             431531               1317733.3           
3900       505                  22                             44                             431531               1334713.3           

Database stats:
Table                               Size (KB)       KB/Entry       
----------------------------------------------------------------------
account_deltas                      4.0             -              
block_headers                       1128.0          0.3            
account_fungible_asset_deltas       4.0             -              
notes                               802108.0        0.8            
account_non_fungible_asset_updates  4.0             -              
nullifiers                          47480.0         0.0            
account_storage_map_updates         4.0             -              
settings                            4.0             -              
account_storage_slot_updates        4.0             -              
transactions                        61324.0         0.1            
accounts                            63848.0         0.1            
note_scripts                        8.0             8.0            

---

Previously:

Total time: 4452.117 seconds
Inserted 3922 blocks with avg insertion time 448 ms
Initial DB size: 112.0 KB
Average DB growth rate: 340.8 KB per block

Block metrics:
Block #    Insert Time (ms)     Get Block Inputs Time (ms)     Get Batch Inputs Time (ms)     Block Size (B)       DB Size (KB)        
---------------------------------------------------------------------------------------------------------------------------------------
0          70                   40                             1                              374138               417.8               
50         373                  12                             42                             431531               25283.1             
100        363                  13                             42                             431531               42059.1             
150        370                  14                             43                             431531               59023.1             
200        385                  14                             2                              431531               76470.5             
250        412                  14                             42                             431531               93973.1             
300        421                  15                             43                             431531               111194.5            
350        376                  15                             2                              431531               128633.0            
400        378                  17                             43                             431531               145717.6            
450        395                  17                             43                             431531               162786.3            
500        442                  16                             43                             431531               180200.4            
550        384                  17                             43                             431531               197112.4            
600        426                  16                             42                             431531               214152.8            
650        408                  17                             2                              431531               231213.6            
700        435                  17                             42                             431531               248406.4            
750        433                  19                             43                             431531               265350.6            
800        416                  18                             43                             431531               282275.0            
850        405                  19                             43                             431531               299475.3            
900        415                  18                             2                              431531               316407.5            
950        400                  24                             43                             431531               333820.4            
1000       408                  18                             43                             431531               350728.4            
1050       416                  21                             43                             431531               367568.4            
1100       402                  19                             42                             431531               384552.7            
1150       448                  19                             42                             431531               401777.0            
1200       414                  19                             3                              431531               418797.1            
1250       471                  20                             43                             431531               435617.1            
1300       433                  20                             43                             431531               452517.1            
1350       464                  20                             43                             431531               469521.6            
1400       449                  20                             2                              431531               486841.6            
1450       419                  20                             43                             431531               503765.6            
1500       463                  19                             43                             431531               520565.6            
1550       443                  27                             43                             431531               537669.6            
1600       453                  21                             43                             431531               554870.1            
1650       454                  20                             44                             431531               572010.3            
1700       416                  20                             43                             431531               588978.3            
1750       449                  20                             44                             431531               605902.9            
1800       480                  20                             44                             431531               622714.9            
1850       442                  23                             3                              431531               639938.9            
1900       430                  21                             3                              431531               656826.9            
1950       442                  22                             43                             431531               673931.7            
2000       487                  21                             44                             431531               691159.7            
2050       419                  21                             42                             431531               708119.7            
2100       460                  22                             43                             431531               725131.7            
2150       455                  21                             43                             431531               742007.7            
2200       486                  21                             2                              431531               759119.7            
2250       455                  21                             3                              431531               776195.7            
2300       471                  21                             42                             431531               792983.7            
2350       511                  22                             43                             431531               809979.7            
2400       484                  21                             42                             431531               826963.7            
2450       482                  22                             43                             431531               843891.7            
2500       445                  22                             44                             431531               860919.7            
2550       441                  22                             43                             431531               877863.7            
2600       456                  23                             43                             431531               894787.7            
2650       458                  23                             3                              431531               911728.4            
2700       501                  23                             43                             431531               928868.4            
2750       511                  21                             43                             431531               945824.4            
2800       467                  64                             45                             431531               963012.4            
2850       474                  22                             44                             431531               980040.4            
2900       485                  22                             43                             431531               997004.4            
2950       448                  22                             44                             431531               1014152.6           
3000       484                  22                             43                             431531               1031188.6           
3050       496                  22                             44                             431531               1048068.7           
3100       499                  22                             44                             431531               1065212.7           
3150       520                  22                             43                             431531               1082172.7           
3200       513                  22                             3                              431531               1098960.7           
3250       505                  22                             43                             431531               1116024.7           
3300       474                  22                             47                             431531               1132888.7           
3350       497                  23                             44                             431531               1149900.7           
3400       511                  22                             43                             431531               1166900.7           
3450       511                  22                             43                             431531               1183976.7           
3500       476                  22                             45                             431531               1200916.7           
3550       481                  23                             3                              431531               1217968.7           
3600       512                  23                             43                             431531               1234956.7           
3650       498                  23                             44                             431531               1252044.7           
3700       465                  23                             4                              431531               1268756.7           
3750       466                  24                             44                             431531               1285872.7           
3800       485                  23                             44                             431531               1302736.7           
3850       506                  23                             44                             431531               1319564.7           
3900       488                  23                             3                              431531               1336688.7           

Database stats:
Table                               Size (KB)       KB/Entry       
----------------------------------------------------------------------
account_deltas                      4.0             -              
block_headers                       1128.0          0.3            
account_fungible_asset_deltas       4.0             -              
notes                               802108.0        0.8            
account_non_fungible_asset_updates  4.0             -              
nullifiers                          47472.0         0.0            
account_storage_map_updates         4.0             -              
settings                            4.0             2.0            
account_storage_slot_updates        4.0             -              
transactions                        61392.0         0.1            
accounts                            63676.0         0.1            
note_scripts                        8.0             8.0            

[drahnr]

sync-state

/w diesel

     Running `target/release/miden-node-stress-test benchmark-store --data-directory ./data --iterations 10000 --concurrency 16 sync-state`
Applying migrations: 0
Average request latency: 1.28313057s
P95 request latency: 43.080789ms
Average notes per response: 1.4004

---

status quo

     Running `target/release/miden-node-stress-test benchmark-store --data-directory ./data --iterations 10000 --concurrency 16 sync-state`
Average request latency: 1.289619302s
P95 request latency: 51.923238ms
Average notes per response: 1.389

[Mirko-von-Leipzig]

I imagine its the domain/model indirection which is an extra layer so to speak?

[drahnr]

I find the above inconclusive, I haven't looked into the queries specifically yet, that's next on my list, after finishing all runs

[drahnr]

sync-notes

/w diesel

     Running `target/release/miden-node-stress-test benchmark-store --data-directory ./data --iterations 10000 --concurrency 16 sync-notes`
Applying migrations: 0
Average request latency: 1.266225305s
P95 request latency: 42.750652ms

---

status quo

     Running `target/release/miden-node-stress-test benchmark-store --data-directory ./data --iterations 10000 --concurrency 16 sync-notes`
Average request latency: 1.258993018s
P95 request latency: 50.938912ms

[drahnr]

The last run will take some more time, it seems a 🐞 snuck in

[drahnr]

Tackling the eq_any Problem: A Deep Dive into SQL Variable Limits

During the stress test, an issue with the function eq_any equiv SQL syntax when dealing with large slices of data. The function was generating SQL queries with an excessive number of variables (represented by ? in the query): (?,?,?,?,?,?) correlating directly with the input length of the slice. Currently, we’re using the RARRAY extension in rusqlite which avoids the need for the excessive number of variable bindings, but it ties us to the RARRAY extension which is specific to rusqlite and is not available in the diesel-rs framework intended to use.

This post outlines the problem, the options we considered, and the path we're taking forward.

The Core of the Issue

The eq_any(large_slice) function allows us to check if a value exists within a potentially large set of values. Internally, this translates to a SQL WHERE (((notes.tag IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)). Database systems have limits on the number of variables a single query can handle, for sqlite this is compile time defined to 999. Exceeding this limit leads to a runtime error.

Exploring Our Options

1. Halt the transition to Diesel

Consider it a learning effort, but stop pursuing the transition.

2. Migrate rusqlite's RARRAY Extension

This involves porting the RARRAY extension (which allows passing arrays directly to SQL) to work within diesel-rs.
The steps involved would be:

1. Port the RARRAY sqlite extension.
2. Extend diesel-rs to allow loading extensions.
3. Implement fn rarray within diesel-rs’s type DSL system.

This doesn't appear to be a sane path forward, particularly because we'd bind ourselves to sqlites specific extension prohibiting one of the upsides of diesel-rs - migrating easily to postgres.

3. Limit Input Size

This approach focuses on reducing the size of the input slice to eq_any.

• Upsides: We should be doing this regardless, as unbounded inputs can always be problematic.
• Downsides: Even with limits, there will still be a hard cap on the number of tags or accounts we can efficiently query.

4. Implement Pagination

Pagination involves breaking down the large input into smaller, more manageable chunks.

• Downsides: This would expose pagination logic to the user, potentially impacting the user experience.
• Upsdies: We would avoid the 4GB protobuf message size limit as well, if that ever becomes an issue.

5. Leverage Multi-Queries

This involves crafting multiple SQL queries instead of a single one, effectively reducing the number of variables in each individual query. This addresses the original issue directly.

• Downsides: Likely an additional performance impact.
• Upsides: Invisible to the user

Next Steps

3. is a must of have regardless of the issue at hand, as discussed, we don't anticipate excessive numbers for either accounts or tags, which are the arguments to today's eq_any calls.
4. is a lower impact compared to 4. and can be achieve by calling queries::* multiple times instead of once, which moves the complexity outwards where possible. However, there is also a limitation by protobuf to 4GB messages.

[drahnr]

We moved to diesel, after using it for a few months, it shows its limitations and the DSL complexity adds non-zero developer overhead.