I'm currently working on a project called Tabularis, which is an open-source SQL client built using Tauri, Rust, and React, and it supports MySQL, PostgreSQL, and SQLite. I'm facing a significant challenge with how to manage BLOB columns within my result grid.
The issue arises when I try to fetch rows; the method I use (row.try_get::<Vec, _>(index)) loads the entire BLOB into memory just to determine its size and generate a 4KB preview for display. This means that if I have a table with 50 rows containing 20MB images, it results in over 1GB of memory being allocated just to render the grid.
Another concern is that since the frontend only retains the 4KB preview, when a user modifies a different column and saves it, the update can inadvertently overwrite the original BLOB data with just the 4KB preview, leading to corruption of the stored files.
I'm weighing a few options:
1. **Rewrite the projection at query time** - I could use a SQL statement that retrieves the length of the BLOB without loading it entirely, but it would involve parsing user queries, which could be complicated.
2. **Sentinel on write** - This approach would involve sending a specific identifier for untouched columns during an update to prevent them from being modified unintentionally. This would help avoid corruption, although it wouldn't address the initial memory usage issue.
3. **Lazy loading** - Another idea is to display a placeholder in the grid and load the preview only when the user clicks on a cell. However, this would still allow for the full BLOB to be requested via a SELECT statement.
I'm curious how tools like DBeaver or DataGrip manage similar situations. Do they use query rewriting, lazy loading, or some other method? Additionally, is there a protocol within databases that allows streaming only a portion of a BLOB without requiring the entire data to be fetched? Lastly, is the practice of excluding BLOB columns from updates unless they are explicitly changed a recognized standard for preventing write-back errors?
3 Answers
I would suggest going with the "load on click" strategy. It's a common method used by various SQL clients and it seems practical.
Also, many DB systems offer ways to retrieve column types directly, like using pg_type() in PostgreSQL. You might also explore querying a single row into a temporary table to ascertain metadata.
For Java-based apps, JDBC offers a getBinaryStream() method for BYTEA columns, allowing you to stream the data, which can help manage memory more efficiently.
For the second question about updating only the modified columns, that's generally the way to go! It reduces overhead and prevents unnecessary data load. Just make sure your logic clearly defines what's been changed before executing an update.
DBeaver and DataGrip typically combine lazy loading with some server-side strategies. For a more efficient approach, you might want to consider using PostgreSQL's lo_get function, which would let you stream BLOBs in chunks as needed, helping with both memory management and update safety.
Related Questions
How To: Running Codex CLI on Windows with Azure OpenAI
Set Wordpress Featured Image Using Javascript
How To Fix PHP Random Being The Same
Why no WebP Support with Wordpress
Replace Wordpress Cron With Linux Cron
Customize Yoast Canonical URL Programmatically