The equality delete problem in Apache Iceberg
blog.dataengineerthings.org
blog.dataengineerthings.org
> In streaming CDC scenarios, however, you’d need to query Iceberg for the location on every delete: introducing random reads, latency, and drastically lowering throughput under high concurrency. On large tables, real-time performance is essentially impossible.
Let's consider the actual situation. There's a Postgres table that fits on whatever Postgres server is in use. It gets mirrored to Iceberg. Postgres is a full-fledged relational database and has indexes and such. Iceberg is not, although it can be scanned much faster than Postgres and queried by fancy Big Data tools (which, I agree, are really cool!). And, notably, there is no index mapping Postgres rows to Iceberg row positions.
But why isn't there? CDC is inherently stateful -- unless someone is going to build Merkle trees or similar to allow efficiently diffing table states (which would be awesome), the CDC process need to keep enough state to know where it is. Maybe this is O(1) in current implementations. But why not keep the entire mapping from Postgres rows to Iceberg positions? The Postgres database table is about N rows times however wide a row is, and it fits on a Postgres server. The mapping needed would be about the size of a single index on the table. Why not store it somewhere? Updates to it will be faster than updates to the source Postgres table, so it will keep up. Is the problem that this is awkward to do in a "serverless" manner?
For extra fun, someone could rig up Postgres (via an extension or just some clever tables) so that the mapping is stored in Postgres itself. It would be, roughly, one small table with CDC state and one moderate size table per mirrored table storing the row position mapping. It could be on the same server instance or a different one.
I think most people who need very near real-time queries also tend to need them to be transactional. The use case where you can accept inconsistent reads but something will break if you're 3 minutes out of date is very rare.
But the 3 minute thing seems somewhat immaterial to me. If I have a table with one billion rows, and I do an every-three-minute batch job that need to sync an average of one modified row to Iceberg, that job still needs write the correct deletion record to Iceberg. If there’s no index, then either the job writes a delete-by-key or the job need to scan 1B Iceberg rows. Sure, that’s doable in 3 minutes, but it’s far from free.
Replying again to add: cost. Just because you can do a batch update every few minutes by doing a full scan of the primary key column of your Iceberg table and joining against your list of modified or deleted primary keys does not mean you should. That table scan costs actual money if the Iceberg table is hosted somewhere like AWS or uses a provider like Databricks, and running a full column scan every three minutes could be quite pricey.
It's kinda funny to not mention that Databricks acquired Tabular, the Iceberg company, for a billion dollars: https://www.databricks.com/company/newsroom/press-releases/d...
Apache Iceberg as mature? I mean, there's a lot of activity around it, but I remember a year ago the rust library didn't even have write capabilities. And it's not like the library is a client and there's an iceberg server - the library literally is the whole product, interacting with the files in s3
I'm not even sure if I'm joking. :)
SELECT * FROM X, into a C# list, Filter it with LINQ, and then use C# to do calculations
"Why not EF?" "Someone told me it was slower"
They do work, but they have some sharp and rough edges.
They are rarely supported by real business value.
Save the data as soon as it comes in and transform it in batch mode.
Whenever I implement CDC I always try to implement some kind of integrity check that runs at some reasonable interval that can detect and try to fix any discrepancies. CDC is by far the most efficient form of data synchronization in most situations.
It’s always shocking to me how many FAANG people will say, “we want an event driven solution with a message bus, that the right way to do it, we don’t want batch, that can’t scale”, and then need to bolt on a validation/reconciliation step for it to be reliable. Which of course is a batch job.
Unless you control a system end to end (which is rare, there’s usually some data from a system you don’t control the schema of), or are highly incentivized to make sync happen reliably (e.g. bitcoin), you’re always limited by a batch job somewhere in the system.
You could even say the batch job is often doing the heavy lifting, and the message bus is just an optimization (that’s often not adding the value needed to justify the complexity).
I'm not sure exactly what you mean by batch job but if n bytes change at the source you shouldn't have to copy more than n bytes to the destination.
Inevitably, even if you achieve this at some point, it never lasts. Your company acquires another, or someone pushes for a different HR/CRM/whatever system and gets it.
You mention if n bytes change in the source, but many systems have no mechanism of determining that n bytes have changed without scanning the entire data set. So we’re back to a batch job (cron, or similar).
This is so insanely inefficient it can't scale to very large amounts of data. If you can't do data syncing at the application layer you can do it at the storage layer with high end storage arrays that duplicate all writes to a second storage array, either synchronously or asynchronously. Or duplicate snapshots to a remote array. They work really well.
Iceberg is optimized for fact data in very large tables with relatively rare changes and likewise rare change to schema. It does that well and will continue to do so for the foreseeable future.
PostgreSQL databases typically don't generate huge amounts of data; that data can also be highly mutable in many cases. Not only that, the schema can change substantially. Both types of changes are hard to manage in replication, especially if the target is a system, like Iceberg, that does not handle change very well in the first place.
So that leaves the case where you have an lot of data in PostgreSQL that's creating bad economics. In that case, why not just skip PostgreSQL and put it in an analytic database to begin with?
p.s., I'm pretty familiar with trading systems that do archive transaction data to data lakes using Parquet for long-term analytics and compliance. That is a different problem. The data is for all intents and purposes immutable.
Edit: clarity
The live data set may not be huge, but the entire trail of all changes of all current and all previously existing data may easily exceed the volume of data you can reasonably process with Postgres.
In addition, its row based storage format doesn't make it an ideal fit for typical analytical queries on large amounts of data.
Replicating the data from Postgres to Iceberg addresses these issues. But, of course, it's not without its own challenges, as demonstrated by the article.
postgres is for relational data, ok
CDC is meant to capture changes and process the changes only (in isolation from all previous changes), not to recover the snapshot of the original table by reimplementing the logic inside postgres of merge-on-read
iceberg is columnar storage for large historical data for analytics, its not meant for relational data, and certainly not for realtime
it looks like they need to use time-series oriented db, like timescale, influxdb, etc
This is not to say that this architecture isn't salvageable - if the only consumer of the Iceberg table copy is a e.g. view that downstream consumers must use, then it's easier to change the Postgres schema, as only the view must be adjusted. My experience with copying tables directly to a data warehouse using CDC, though, suggests it's hard to prevent erosion of the architecture as high-urgency projects start taking direct dependencies to save time.
I have spent way too much life maintaining consumer shield views and answering hairy schema translation questions for use cases so unimportant the downstream business user forgot they even had the view.
Important downstream data consumers almost always have monitoring/alerting set up (if it's not important enough to have those, it's not important) and usually the business user cares about integrity enough to help data teams set up CI. Even in these cases, where the business user cares a lot, I've still found shield views to be of limited utility versus just letting the schema change hit the downstream system and letting them handle it as they see fit, as long as they're prepared for it.
> it's hard to prevent erosion of the architecture as high-urgency projects start taking direct dependencies to save time.
IME, it feels wrong, but it mostly does end up saving time with few consequences. Worse is better.
With a query engine that supports federation, we can write SELECT * FROM PG_Table or SELECT * FROM Iceberg_File just the same.
let's say e-shop customer changes his home address from WA state to CA, if you replicate this new address and delete old address in compaction process, all past transactions may now be associated with the new address, which can lead to wrong conclusions or distortions of past historical reports (WA sales from past month will not be attributed to CA sales)
It's becoming clear that merge trees and compaction need to be addressed next, after delete vectors brought them onstage.
Vertica will actually look up the equality keys in a relevant projection if it exists, and then use the column values in the matching rows to equality-delete from the other projections; it's fairly good at avoiding table scans.
As an industry, we keep forgetting these things and reinventing the wheel because there is more money to be made squeezing enterprises than providing widely available sustainable software for a fair price and than losing mindshare to the next generation of tool and eventually getting sold for parts. It's a sad dynamic
It's true that Vertica sales are optimized for large enterprises -- they just don't have the VC cash to hire 3000 sales people to sell it to the low end, so it doesn't appear on many people's radar.