author here. I would actually second you.
My core belief is "it is possible to build true database-like functionalities on top of iceberg", but it is definitely not 'easier' than building them directly in a db (in fact, doing them while keep iceberg-compatible is tricky. yep, that's the cost of being open and general purpose)
I've been reading up on lakehouse stuff, and everything seems to agree with the idea that data warehouse functionality deteriorates as they try to accommodate raw data access.
The databricks people suggest that a better "cache format" is needed, but I don't see how that's different from ETLing the data into a regular warehouse.
Using SQL as catalog is not new (iceberg supports JDBC catalog from the very beginning).
The main difference is to store metadata and stats also directly in SQL databases, which makes perfect sense for smaller scale data. In fact we were doing something similar in https://github.com/Mooncake-Labs/pg_mooncake, metadata are stored in pg tables and only periodically flush to actual formats like iceberg.
DataFile(parquet) is not enough for table with update/delete, (they are part of iceberg "metadata").
for CDC from OLTP use-cases, the pattern involves rapidly marking rows as deleted/ insert new rows and optimizing small files. This is required for minutes-latency replication.
And for second latency replication, it is more involving, you actually need to build layer on top of iceberg to track pk/ apply deletion.
Glab to see more 'postgres-native' full-text search implementation.
Alternative solutions (lucene/ tantivy) are both designed for 'immutable segments' (indexing immutable files), so marrying them with postgres heap table would results in a worse solution.
The segments themselves being immutable doesn't mean that Tantivy is incompatible with Postgres - it just means that Tantivy needs to be made compatible with Postgres' concurrency control mechanisms (MVCC) and storage format (block storage). This blog post explains the latter: https://www.paradedb.com/blog/block_storage_part_one
the fundamental mismatch i saw is "creating a new segment for each individual dml", it is possible to alleviate but i don't think there's a good general solution.
Yea this is indeed a repeated pattern we saw people requesting (filter on many columns) and we are trying to solve with pg_mooncake. If you are interested, feel free to join mooncake-devs.slack.com to chat more about your use case.
Thanks for the comment but you are mixing some terminologies.
The core idea of mooncake is to built upon open columnar format + substitutable vectorized engine, while natively integrate with Postgres.
So it is indeed closer to BigQuery (especially the newer bigquery with iceberg tables) than a 'standard SQL database'. It has all the nice properties of BigQuery (ObjectStore-native, ColumnStore, Vectorized execution...) and scaling is also not impossible.
hydra/pg_duckdb embeds duckdb to query existing data on S3. So it is kind of targeting a completely different use case (someone already prepares and shared a dataset and you just want to query it).
pg_mooncake (&crunchyData) is implementing columnstore tables in postgres, so you can actually use postgres as a data-warehouse (to ingest/ update and run analytics queries)
Good point! Normally for postgres extension it won't be solvable, but for mooncake it is actually not the case!
The core idea of mooncake is to built upon open columnar format + substitutable vectorized engine, while natively integrate with Postgres.
So right now it is using duckdb within postgres to run the query, but we can and we will support ad-hoc using other 'stateless engines' like Athena, StarRocks or even spark to run a big query.
