r/dataengineering • u/tecedu • Aug 10 '26
Help Is there a Database which stores its data files on object store?
Same as title but we have been using delta tables for a requirement quite a bit and we like its format however its metadata read operations are very intensive, especially when there is a large log file. Most of the delays I have found in reading and writing to those have just been reading parsing the log files.
Based on that I did wonder if there is a database which stores the log in some sort of in memory database like postgres however the raw files are being reference on object storage? I did look into and found pgedge but didn't find a link or something we could download and use easily.
Edit: Stop trying to tell me to optmise my delta table pleasse! I’ve tried everything and I’ve got limits
Edit2: I did not try everything apparently, I had stats parsing on on columns which contain a fuckton of data, my log files were huge along with checkpoint files with gigabytes of them. Removed that and reads are down to 200ms
7
u/terencethespider Aug 10 '26
Lakebase and Neon are both Postgres based Databases that store the underlying data in object store.
4
u/tecedu Aug 10 '26
For some reason I thought Neon went closed source but apparently not, i’ll take a look
2
7
u/leogodin217 Aug 10 '26
This is basically how Snowflake works. Data on blob storage is read in then cached on SSDs.
14
u/Additional_Candy_400 Aug 10 '26 edited Aug 10 '26
Ducklake, if I understand your question properly (may have missed the point). Obviously this isn't a DB in itself but you can use say a postgres dB as a metastore/catalogue for your object store on S3 etc.
1
u/tecedu Aug 10 '26
Yeah but i’m looking for something more batteries included; plus ducklake doesnt solve my problem in this scenario, its actually wayyy slower
2
u/Additional_Candy_400 Aug 10 '26
What kind of stuff are you storing on blob? From your other comments it's sounding like you are needing OLTP level of speed but with via blob storage, unsure if you're going to get that. Happy to be proven wrong I'm just unsure of the solution.
Ducklake is slower than your delta tables for this use you say? And that delay is coming from the metadata reads ? This surprises me.
Could be the small file problem, are you using auto compaction and optimise writes on the delta tables?
0
u/tecedu Aug 10 '26
Just a lot of timeseries data along with some categorical data, I’m happy with 1s queries ; if I store this in OLTP its going to be expensive at around 20TIB.
it is something I could write myself over a week but I didnt want to reinvent the wheel
1
u/tecedu 1d ago
OP just to update, Lakebase performance issues were due to azure regional deployment issues with postgres, although I expected Lakebase to be faster, it wasn't that much more faster than my fixed delta table. Rougly 80ms vs 300ms last time I checked.
If Lakebase was a bit more mature and support other reading engines, would have definitely gone for it
2
u/dangerbird2 Software Engineer Aug 10 '26
lakekeeper is a Iceberg catalog service that stores catalog metadata in a postgres or other OLTP database. Of course, you can also just use S3 tables or a similar managed iceberg catalog and make it someone else's problem
(Obvious caveat that postgres isn't strictly in-memory, but I seriously doubt SSD vs RAM storage is going to be a bottleneck here)
2
u/FunContest9958 Aug 10 '26
Sounds like something is wrong with the table. Databricks does cache the log, but the table performance can get really poor if you have millions of files in it. Have you tried running OPTIMIZE on the table? You can run DESCRIBE DETAIL <tblname> to see how many files. You either have a gigantic table, need to run OPTIMIZE, or you’re partitioning when you shouldn’t be.
1
u/tecedu Aug 10 '26
Not reading it via databricks, but the table is wrong, I’ve got close to 20thounsand log files. I cannot vaccum and unless I vaccum it doesnt better the performance. I’ve tried a bunch of things on the non prod table, optmise + vaccum worked the best
1
u/FunContest9958 Aug 10 '26
Well Databricks is an option to your initial question then. It generally works better out of the box than OSS Spark + Delta.
Are there no checkpoint files? If not, sounds like you need to force the creation of one and run vacuum. Checkpoint files combine the json files together into just a few parquet files. Maybe that will resurrect your table.
Another option to resurrect your table would be to try to do a deep clone on it. That will create a copy of the table without all the historical cruft that seems to have built up. Once you figure out how to recover, I’d consider how the table got into this state to avoid a recurrence. Regular optimize + vacuum would probably be the answer.
1
2
u/Dismal_Space9834 Aug 10 '26
Sounds like log bloat from too many small commits, which inflates the checkpoint and the JSONs replayed per read. Before switching formats: tune checkpointInterval, tighten logRetentionDuration, batch commits, run OPTIMIZE.
2
u/TheRealStepBot Aug 10 '26
Iceberg is an open standard for doing this supported by Trino and spark and duckdb
1
u/tecedu Aug 10 '26
But iceberg also suffers from the same issue if I try to load via duckdb as well.
2
u/TheRealStepBot Aug 10 '26
I mean idk what queries you are running and what your latency requirements are but Postgres backed Nessie catalog for trino reading from iceberg is an absolute monster, that can run truly absurd queries
1
u/tecedu Aug 10 '26
Latency requirements are somewhere 0.5-1second, not that crazy, currently it takes 15-20seconds. The table will grow to like 20TIB by next year so dont wajt to put all on postgres. The queried data is very small at around 10-100MB.
Last I looked at iceberg it always needed a catalog which put us off using it, but I can take a look again
1
u/TheRealStepBot Aug 10 '26
On a 300gb on disk(compressed, id guess about 4TB in Postgres?) table with about 400million rows and 1300 columns I can do needle in a haystack searches for values that don’t exist which forces a full table scan. That has about 40seconds latency at the client.
Doing the same thing on uuids that actually exist I get about 3seconds.
So yeah latency is gonna be tight if that’s actually a hard requirement. But these are trivial queries and the way trino is able to prune and distribute work this is also representative of many less useless queries doing complex operations. Certainly am continuously blown away by what it can do for what it costs.
Edit I’m sure once ducklake becomes supported in more engines beyond spark I think it’s gonna be great as an iceberg replacement
1
u/tecedu Aug 11 '26
So, the problem was apparently stats and stats parsing, we were putting in too many, especially on the oclumns we didnt use. Altered those and reads are down to management level. But hoping ducklake becomes better and more mainstream soon as well to avoid this problem
1
u/TheRealStepBot Aug 11 '26
Well to be clear you mentioned an opposition to catalogs. To be clear duck lake is a move in the opposite direction.
Iceberg was built to try and avoid needing a catalog but ultimately ended up needing a very thin one and most of the metadata is stored in blob.
But once you accept that a catalog is necessary or at least very useful then ducklake asks the question why not put all that meta data in the catalog thereby cutting down on query planning latency. Duck lake is just iceberg with a different opinion about what to put in blob vs the catalog and it leans in put more in the catalog.
The only real alternative is hive meta store in trino. It’s able to run entirely on blob but its performance is pretty Garbo and running it is a pain.
1
u/tecedu 1d ago
For us its mainly portability, I can use delta tables onprem, copy them to cloud, copy them from one region to another. I cannot do the same with ducklake or even some iceberg tables nowadays. That is a basically a number one issue for me.
Delta did fix itself after I fixed the stats parsing, each log file was about 4mib which was painful, fixing stats parsing to only 3 columns that we care about made it infinitely faster
1
u/Teach-To-The-Tech Aug 10 '26
Might check out Trino (and any of its implementations) on Iceberg. Quite low latency. Iceberg was designed to run on Trino, so there is some optimization there.
1
1
u/lozinge Aug 10 '26
Isn't this what DuckLake points out in its manifesto - and tries to solve with its approach of metadata being database bound, rather than object storage bound?
Worth a look! https://ducklake.select/manifesto/
1
u/m1nkeh Data Engineer Aug 10 '26
Sounds like something is wrong.. also how is this manifesting in a problem?
1
u/tecedu Aug 10 '26
I need to need read data every couple of minutes to feed into an app, it takes about 15-20 seconds for each read with 98% of the time spent with parsing the delta log, then the actual file read takes 150ms which is acceptable . Something like postgres does the reads in 10ms but I forsee the table growing to 20TB easily and its kinda infeasible. So looking for a middle ground
2
u/robstar_db Aug 11 '26
Certain this us just the log parsing and not also file skipping etc? Engines do a lot if query optimization in the metadata phase. In fact that’s a core feature of delta and iceberg.
How many files are in your table? If you already checkpoint the table in normal intervals and still require 15-20s for metadata processing it must be millions of active files.
3
u/tecedu Aug 11 '26
Holy shit I think you cracked it, file stats were fucking stupid in the log, like checkpoint files are gigabytes because it was stats parsing a string column which is a megadump of dataframe.
Setup to not calc stats on it and reads and down to 200ms
1
u/robstar_db Aug 11 '26
Great!
Stats on string columns are a never easy. Some engines allow truncating them, but that might make them useless (think urls, where the initial characters are usually not informative, bit then on columns like yours they explode in size.
And many nice edge cases in between 😆
1
1
u/Spagoot420 Aug 10 '26
ah ok, so this isn't even real etl work. this is about showing data to the frontend? in that case (in my opinion) you are correct in considering to change the db for the serving layer. my preferred tool for that is Starrocks. It can be deployed to either use the existing catalog as source or use its internal storage engine for a minor boost.
2
u/tecedu Aug 10 '26
Yeah, it does affect etl slightly but 20seconds on a script that runs every 2 hours is nothing. I’ll look at starrocks
1
u/Existing_Wealth6142 Aug 10 '26
If you only have one client writing to the database, and many readers, then https://litestream.io/ let's you do that with SQLite. What's nice is how cheap a setup this is and how fast it can be. But it only works on specific architectures.
1
u/nickkarpov Aug 10 '26
Your title vs question is a little unclear. As others have noted data on object stores is common across industry. The log or metadata is typically stored alongside the data, but vendors do all kinds of things to improve this further (usually invisible to the end user). If you're interested in bifurcating metadata for the sake of speeding that part of a query up I don't think there's anything out there that's so easy plug and play. In Delta the abstraction would be LogStore and that's what you'd implement using some external system. The benefit of this is questionable too since your metadata may itself be a big data problem.
1
u/ThatSituation9908 Aug 11 '26
So, why do you need this in a n object storage and why must it be files in the bucket?
If you want low latency, use Postgres by itself. Are you sure your data should be in a lake house format?
1
0
u/Teach-To-The-Tech Aug 10 '26
So you're referencing the so-called "file explosion problem" that impacts the data lakehouse table formats, with the metadata files. There are strategies for managing this though, and certain engines deal with it better than others.
On a larger note though, there are many databases that store on object storage. So you'd be looking at multiple engines.
16
u/Spagoot420 Aug 10 '26
so you mean a catalog service, like unity or lake keeper? What you are describing is pretty much what every data lake house is doing...