13 ms·

Show HN: BemiDB – Postgres read replica optimized for analytics

209 points by exAspArk 2y ago

Hi HN! We're Evgeny and Arjun, and we’re building a better way to do analytics with Postgres. We love Postgres for its simplicity, power, and rich ecosystem. But engineers have to still get bogged down with heavyweight and expensive OLAP systems when connecting an analytics data stack. Postgres is amazing at OLTP queries, but not for OLAP queries (large data scans and aggregations). Even in this case, we’ve still heard from countless scaling startups that they still try to use only a read replica to run analytics workloads since they don’t want to deal with the data engineering complexity of the alternative. This actually works surprising well initially, but starts to break for them as they scale or when integrating multiple data sources. Adding lots of indexes to support analytics also slows down their transactional write performance. When growing out of “just use Postgres”, companies have to understand and wrangle complex ETL pipelines, CDC processes, and data warehouses — adding layers of complexity that defeat the simplicity that undermines their initial choice for Postgres as their data storage in the first place. We thought there had to be a better way, so we’re building BemiDB. It’s designed to handle complex analytical queries at scale without the usual overhead. It’s a single binary that automatically syncs with Postgres data and is Postgres-compatible, so it’s like querying standard Postgres and works with all existing tools. Under the hood, we use Apache Iceberg (with Parquet data files) stored in S3. This allows for bottomless inexpensive storage, compressed data in columnar files, and an open format that guarantees compatibility with other data tools. We embed DuckDB as the query engine for in-memory analytics that work for complex queries. With efficient columnar storage and vectorized execution, we’re aiming for faster results without heavy infra. BemiDB communicates over the Postgres wire protocol to make all querying Postgres-compatible. We want to simplify data stacks for companies that use Postgres by reducing complexity (single binary and S3), using non-proprietary data formats (Iceberg open tables), and removing vendor lock-in (open source). We'd love to hear your feedback! What do you think?

deleted 2y ago
[deleted]
oulipo 2y ago
Really cool! I have an IoT use-case where I ingest data, I want to keep like the last 3 months or so in Postgresql, and then store the old data as parquet files on S3 I planned initially to do chunks on S3 and do the analytical queries using duckdb, I'm wondering if your tool would be a good replacement? For now I don't have that many analytical queries, I'm mostly doing visualization of the data points by querying a range (eg last 2 weeks of data for a device) Does it then make sense to use columnar storage or am I better off with "regular Postgres"? Or in my case does your approach provide "best of both worlds" in the sense that I could do some occasional analytical queries on past data stored on S3, and regularly access "last 3 months" data for visualization using the data stored in the regular Postgres?
- exAspArk 2y ago
  Thank you! Yes, absolutely! 1) You could use BemiDB to sync your Postgres data (e.g., partition time-series tables) to S3 in Iceberg format. Iceberg is essentially a "table" abstraction on top of columnar Parquet data files with a schema, history, etc. 2) If you don't need strong consistency and fine with delayed data (the main trade-off), you can use just BemiDB to query and visualize all data directly from S3. From a query perspective, it's like DuckDB that talks Postgres (wire protocol). Feel free to give it a try! And although it's a new project, we plan to keep building and improving it based on user feedback.
  - oulipo 2y ago
    Thanks! - Can you give me more info about the strong consistency and delayed data, so I can better picture it with a few examples? - Also, is it possible to do the sync with the columnar data in "more-or-less real-time" (eg do a NOTIFY on a new write in my IoT events table, and push in the storage?) - Would your system also be suited for a kind of "audit-log" data? Eg. if I want to have some kind of audit-table of all the changes in my database, but only want to keep a few weeks worth at hand, and then push the rest on S3, or it doesn't make much sense with that kind of data?
    - exAspArk 2y ago
hoerzu 2y ago
Can you give an example if I have 5gig (2 million rows) How will it be created differently for columnar access?
- exAspArk 2y ago
  We ran some benchmarks (TPC-H, designed for OLAP) with ~10M records https://github.com/BemiHQ/BemiDB#benchmark https://github.com/BemiHQ/BemiDB#benchmark The BemiDB storage layer produced ~300MB columnar Parquet files (with ZSTD compression) vs 1.6GB of data in Postgres.
  - Sesse__ 2y ago
    Does TPC-H SF1 really take _one and a half hours_ for you on regular Postgres? Last time I tried (in the form of DBT-3), it was 22 queries and most of them ran in a couple seconds.
    - exAspArk 2y ago
      Interesting. I haven't used the DBT-3 kit, does it add any indexes? I manually added these Postgres indexes https://github.com/BemiHQ/BemiDB/blob/main/benchmark/data/create-indexes.ddl https://github.com/BemiHQ/BemiDB/blob/main/benchmark/data/cr... to reduce the main bottlenecks on SF0.1 and reduce the total time from 1h23m13s to 1.5s. But SF1 still took more than 1h
      - Sesse__ 2y ago
        It adds a bunch of indexes, yes. I don't think anyone really runs TPC-H unindexed unless they are using a database that plain doesn't support it; it wouldn't really give much meaningful information. Edit: I seemingly don't have these benchmarks anymore, and I'm not going to re-run them now, but I found a very (_very_) roughly similar SF10 run clocking in around seven minutes total. So that's the order of magnitude I would be expecting, given ten times as much data.
        2y ago
gigatexal 2y ago
Query Engine: embeds the DuckDB query engine to run analytical queries. Storage Layer: uses the Iceberg table format to store data in columnar compressed Parquet files. Smart. Imma test this out for sure.
- arjunlol 2y ago
  Thanks! Give it a try and let us know any feedback :)
paurora 2y ago
[dead]
- exAspArk 2y ago
  Thanks! We love the pg_moooncake extension (and pg_duckdb used under the hood). Although our approaches are slightly different. Long-term, we want to allow anyone to use BemiDB by using native Postgres logical replication without installing any extensions (many Postgres hosting providers impose their restrictions, upgrading versions might be challenging, OLAP queries may affect OLTP performance if within the same database, etc.)
- shayonj 2y ago
  Absolutely stoked for pg_mooncake. I really want to see some of these things happening inside PG and taking advantage of the PG internals + native storage. Only bummer is adoption by places where users are currently, say Aurora. But thats probably a problem for another day :) P.S The integration with something like Neon is really cool to see.
neeleshs 2y ago
Congratulations! I was looking and pg_analytics from ParadeDB hoping this use case would be solved (the dump from pg to parquet part), but it doesnt yet do it. How does it handle updates?
- exAspArk 2y ago
  Thank you! The pg_analytics Postgres extension partially supports different file formats. We bet big on Iceberg open table format, which uses Parquet data files under the hood. Our initial approach is to do periodic full table resyncing. The next step is to support incremental Iceberg operations like updates. This will involve creating a new "diff" Parquet file and using the Iceberg metadata to point to the new file version that changes some rows. Later this will enable time travel queries, schema evolution, etc.
  - neeleshs 2y ago
    Fantastic!
winddude 2y ago
difference to something like duckdb?
- polskibus 2y ago
  How can you setup automatic replication from Postgresql to a duckdb instance?
  - exAspArk 2y ago
    The most common approach is to read Postgres data in DuckDB https://duckdb.org/docs/extensions/postgres.html https://duckdb.org/docs/extensions/postgres.html
- simlevesque 2y ago
  > > Alternatives > DuckDB: > - Designed for OLAP use cases. Easy to run with a single binary. > - Limited support in the data ecosystem (notebooks, BI tools, etc.). Requires manual data syncing and schema mapping for best performance.
  - exAspArk 2y ago
    ^ This! Here is the link that briefly describes pros and cons of different alternatives for analytics https://github.com/BemiHQ/BemiDB#alternatives https://github.com/BemiHQ/BemiDB#alternatives
canadiantim 2y ago
How does this compare to ParadeDB? Seems to occupy the same space
- exAspArk 2y ago
  We love ParadeDB and their team. Their primary focus is search (Elasticsearch on Postgres), but they also have the pg_analytics Postgres extension (foreign data wrappers and embedded DuckDB). The biggest difference is in a Postgres extension vs a separate OLAP process. We want to allow anyone with just Postgres to be able to perform analytics queries without affecting resources in the transactional database, building and installing extensions (might not be possible with some hosting providers), dealing with dependencies and their versions when upgrading Postgres, manually syncing data from Postgres to S3, etc.
dangoodmanUT 2y ago
This is probably the most streamlined/all-inclusive solution out of all that I've seen, but this has definitely been an extremely saturated space in 2024
- dangoodmanUT 2y ago
  mostly everyone riding on duckdb's tailcoat
- dirtbag__dad 2y ago
  What are the other viable players? As mentioned in another thread to this post duckdb is not “production-ready.” That has been a non-starter for us at work.
  - exAspArk 2y ago
    That's why our current approach is to build missing or not fully functional features ourselves to move fast. For example, DuckDB performs reads from Iceberg tables not according to the spec, can't perform writes, etc.
levkk 2y ago
Moving data between systems is problematic. Where this product is actually needed (multi-TB databases under load) is where logical replication won't be able to sync your tables in time. Conversely, small databases where this will work don't really need columnar storage optimizations.
- exAspArk 2y ago
  Fair point. We think that BemiDB currently can be useful when used with small and medium Postgres databases. Running complex analytics queries on Postgres can work, but it usually requires tuning it and adding indexes tailored to these queries, which may negatively impact the write performance on the OLTP side or may not be possible if these are ad-hoc queries. > (multi-TB databases under load) is where logical replication won't be able to sync your tables in time I think the ceiling for logical replication (and optimization techniques around it) is quite high. But I wonder what people do when it doesn't work and scale?
  - delive 2y ago
    What would you consider to be small or medium? I have a use case for analytics on ~1 billion rows that are about 1TB in postgres. Have you tried on that volume?
    - exAspArk 2y ago
      We haven't tested this with 1TB Postgres databases yet, assuming that most companies operating at this scale already built analytics data pipelines :) I'm curious if you currently move the data from this Postgres to somewhere else, or not yet?
      - delive 2y ago
        Not yet, mostly just kicked the can down the road due to costs. Like you said in another post, careful indexes on postgres get you quite far, but not nearly as flexible as a columnar DB. I think your project is great. I suspect incremental updates will be a big feature for most uptake (one we would need to try this out at least).
woodhull 2y ago
As much as DuckDB is cute I've mostly come to believe that Clickhouse is the perfect thing to pair Postgres with. This is especially true now that they've acquired PeerDB and are integrating it into the Clickpipes cloud product. DuckDB is neat, and I understand why a company like BemiDB would build their product on top of it, but as a prospective customer embedded databases are a weird choice for serious workloads when there are other good open-source solutions like Clickhouse available.
- exAspArk 2y ago
 ClickHouse is definitely a popular choice nowadays. I'm curious whether you self-host ClickHouse or use their Cloud? We wanted to make BemiDB as simple to run as possible with a single binary and object storage (vs large machines, big disks, clustering, running Temporal for CDC, etc.)
 - Onavo 2y ago
 Clickhouse has an embedded version (https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb), the issue with duck is that it's too buggy for production loads. You can see a nice list of the issues here: https://news.ycombinator.com/item?id=41490707 https://news.ycombinator.com/item?id=41490707
- maxmcd 2y ago
 Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet). I can imagine this product is a very elegant solution for many types of companies/teams/workloads.
 - zX41ZdbW 2y ago
 You can mount read replicas on object storage in ClickHouse. Example: CREATE DATABASE test; USE test; CREATE TABLE hackernews_history UUID '66491946-56e3-4790-a112-d2dc3963e68a' ( `update_time` DateTime DEFAULT now(), `id` UInt32, `deleted` UInt8, `type` Enum8('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5), `by` LowCardinality(String), `time` DateTime, `text` String, `dead` UInt8, `parent` UInt32, `poll` UInt32, `kids` Array(UInt32), `url` String, `score` Int32, `title` String, `parts` Array(UInt32), `descendants` Int32 ) ENGINE = ReplacingMergeTree(update_time) ORDER BY id SETTINGS disk = disk(readonly = true, type = 's3_plain_rewritable', endpoint = 'https://clicklake-test-2.s3.eu-central-1.amazonaws.com/', use_environment_credentials = false); And you can try it right now. Install ClickHouse: curl https://clickhouse.com/ | sh ./clickhouse local Run the query above to attach the table. The table is updated in real time. For example, here is your comment: :) SELECT * FROM hackernews_history WHERE text LIKE '%Clickhouse is amazing%' ORDER BY update_time \G Row 1: ────── update_time: 2024-04-06 16:35:28 id: 39785472 deleted: 0 type: comment by: mightybyte time: 2024-03-21 22:59:20 text: I'll second this. Clickhouse is amazing. I was actually using it today to query some CSV files. I had to refresh my memory on the syntax so if anyone is interested:<pre><code> clickhouse local -q "SELECT foo, sum(bar) FROM file('foobar.csv', CSV) GROUP BY foo FORMAT Pretty" </code></pre> Way easier than opening in Excel and creating a pivot table which was my previous workflow.Here's a list of the different input and output formats that it supports.<a href="https://clickhouse.com/docs/en/interfaces/formats" rel="nofollow">https://clickhouse.com/docs/en/interfaces/formats</a> dead: 0 parent: 39784942 poll: 0 kids: [39788575] url: score: 0 title: parts: [] descendants: 0 Row 2: ────── update_time: 2024-04-06 18:07:34 id: 31334599 deleted: 0 type: comment by: richieartoul time: 2022-05-11 00:54:31 text: Not really. Clickhouse is amazing, but if you want to run it at massive scale you’ll have to invest a lot into sharding and clustering and all that. Druid is more distributed by default, but doesn’t support as sophisticated of queries as Clickhouse does.Neither Clickhouse nor Druid can hold a candle to what Snowflake can do in terms of query capabilities, as well as the flexibility and richness of their product.That’s just scratching the surface. They’re completely different product categories IMO, although they have a lot of technical / architectural overlap depending on how much you squint.Devil is in the details basically. dead: 0 parent: 31334527 poll: 0 kids: [31334736] url: score: 0 title: parts: [] descendants: 0 Row 3: ────── update_time: 2024-11-07 22:29:09 id: 42081672 deleted: 0 type: comment by: maxmcd time: 2024-11-07 22:13:12 text: Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet).I can imagine this product is a very elegant solution for many types of companies/teams/workloads. dead: 0 parent: 42080385 poll: 0 kids: [] url: score: 0 title: parts: [] descendants: 0 3 rows in set. Elapsed: 3.981 sec. Processed 42.27 million rows, 14.45 GB (10.62 million rows/s., 3.63 GB/s.) Peak memory usage: 579.26 MiB.
partdavid 2y ago
How does this replicate Postgres data? I glanced at the code and saw that it exports to a CSV file then writes out an Iceberg table for an initial snapshot--does it use Postgres logical replication?
- exAspArk 2y ago
  Full table re-syncing is our initial solution. Using Postgres logical replication is next on our roadmap!
  - oulipo 2y ago
    So if we have a very large table, it means each time I replicate (how often does that happen?) it needs to rewrite the entire table and upload it to S3, right?
leighleighleigh 2y ago
Definitely checking this out today! I use postgres for ~30 GB of machine learning data (object detection) and have a couple workflows which go through the Postgres->Parquet->DuckDB processing route. A couple questions, if you have time: 1. How do you guys handle multi-dimensional arrays? I've had issues with a few postgres-facing interfaces (libraries or middleware) where they believe everything is a 1D array! 2. I saw you are using pg_duckdb/duckdb under the hood. I've had issues calling plain-SQL functions defined on the postgres server, when duckdb is involved. Does BemiDB support them? Thanks for sharing, and good luck with it!
- exAspArk 2y ago
  Thank you, please give it a try! Great questions: 1. We currently don't support multi-dimensional arrays, but we plan to add support for such complex data structures. 2. Would you be able to share what type of user-defined functions are these, do they do modify the data or read it?
  - leighleighleigh 2y ago
    1. good to hear! 2. The bulk of them are convenience wrappers which resolve UUIDs into other values, so most are read-only with only a single table lookup.
VoxPelli 2y ago
The AGPL license is a no-go for me. While it’s technically true that it’s an OSI license it’s mostly used to scare away competing cloud vendors from hosting the software, which isn’t in spirit of OSS. Have you looked into the more modern choices? Like the Business Source License that MariaDB created and uses or the Functional Source License that Sentry created as an improvement over the Business Source License? https://fsl.software/ https://fsl.software/ Both those licenses have a fair source phase that automatically resolves into an open source phase over time. Thus one gets the best of two worlds: An honest descriptive license for protecting one’s business model + a normal permissive OSS license that ensures longevity and prevents lock-in.
- exAspArk 2y ago
  Our philosophy in general is to go to a more open license over time (vs the other direction). So we might consider other more permissive OSI-approved licenses. Would you be able to share why AGPL license is a no-go for you? I'm genuinely curious about your use case. In simple words, it'd require a company to open source their BemiDB code only if they made modifications and were distributing it to other users (allowing modifications and using it internally without any restrictions)
  - senorrib 2y ago
    Please, don’t. AGPL is great and you’re fine using it.
  - VoxPelli 2y ago
    So you have Contributor License Agreements that enable you to relicense to something like GPL or MIT whenever you want to? Because AGPL itself can not be relicensed and it even needed special wording when created to become GPL-compliant. Since you’re a startup I believe that you use AGPL to achieve the “fair source” idea (https://fair.io/ https://fair.io/) – where you yourself can provide a hosted service without providing all your source code while hoping others won’t be as they will need to provide all theirs. In simplified terms: It’s an anti-AWS defense. Helping you avoid being outcompeted by a big cloud vendor using your project without paying anything for it. And AGPL is a poor “fair source” license, especially as it was never designed to be a “fair source” license but also since it doesn’t give the impression of “fair source” but rather the impression of “open source”, giving an almost deceptive and dishonest look. And since AGPL is perpetual, unlike eg BSL and FSL, one is stuck with the “fair source” license forever, rather than having it be converted into a mainstream OSS license over time. It can eg make it hard for someone else to eventually, if your company goes away and the project gets abandoned (which is sadly the most likely outcome of most startups), pick up the project and build a company around that while using similar “fair source” principles like you. If you are doing like what SourceHut is doing (https://sourcehut.org/blog/2022-10-09-ip-assignment-or-lack-thereof/ https://sourcehut.org/blog/2022-10-09-ip-assignment-or-lack-...) and going all in on AGPL and playing by its rules yourself as well and treating it as “open source” rather than “fair source”, then well done! I still would likely want to avoid the legalese complexity of AGPL in my stack though and try to generally stick to permissive licenses and the occasional GPL-licensed projects, like eg Linux. And eg Google has similar guidelines when using OSS-code internally.
gregw2 2y ago
So it looks like you don't use postgres extensions so you can run this on an EC2 against an Aurora Postgres instance and dump files to S3 Iceberg right? And can you then have Glue Catalog auto-crawl them and expose them in Athena? Or are they DuckDB-managed Iceberg tables essentially?
- exAspArk 2y ago
  Exactly! You can run it on any server connecting to any Postgres, without installing custom extensions (AWS Aurora supports only a limited number of extensions https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQ...). The Iceberg tables are created separately from the DuckDB query engine. So you should be able to read these Iceberg tables by using any other Iceberg-compatible tools and services like AWS Athena.
jakozaur 2y ago
Cool. Every database or data source (e.g. CRM) should produce Iceberg format for you. Though a little sceptical of embedding DuckDB. It is easy and better to isolate Read/Write paths, and it has a lot of other benefits.
- exAspArk 2y ago
  Iceberg for the win! We actually separate Read/Write paths. BemiDB reads by levering DuckDB as a query engine. And it writes to Iceberg completely separately from DuckDB. I'm curious if that's what you imagined.
  - jakozaur 2y ago
    Ideally, I would love many places writing to the Iceberg catalogue and then using it as a data lake. In the data lake, I would have a gateway provisioning DuckDB on demand for each user.
    - exAspArk 2y ago
      Oh, interesting, thanks for sharing it!
mrbluecoat 2y ago
I hadn't heard of Devbox before, so thanks for sharing
- exAspArk 2y ago
  Haha, it's awesome for isolating project environments (languages, databases, etc.) without using docker
globular-toast 2y ago
I don't get how this would do away with the need for some kind of ETL. Most apps use highly normalised schemas that are completely unsuitable for analytical users. Not to mention you wouldn't want to couple your app schema to your warehouse schema. Am I missing something? How would this replace traditional data warehousing?
- exAspArk 2y ago
  Good point. For more complex scenarios, people would still be able to implement, for example, a Medallion Architecture to progressively improve data quality and structure. Because it is Postgres- and Iceberg-compatible (db and data), it's possible to bring more other advanced data tools when it's needed to perform data transformation and movement. Currently, we see it as a Postgres read replica for analytics. But it's easy to imagine that in the future it could be used as a standalone OSS database on top of a data lakehouse with an open format in S3.
  - globular-toast 2y ago
    Cool, I can definitely see this smoothing the path towards a full DW solution, assuming that is ever needed. Could you see it working with something like dbt, say doing transformations in a dedicated pg database then serving the transformed data to users via the read replica? Out of interest, do you know any good resources covering the current state of data engineering? I find the area quite impenetrable compared to software engineering. Almost like much of it is trade secrets and passed down knowledge and none of it written down.
    - exAspArk 2y ago
      Our plan is to make BemiDB work with dbt by leveraging the Postgres-compatibility (supported dbt adapters https://docs.getdbt.com/docs/trusted-adapters https://docs.getdbt.com/docs/trusted-adapters). So it should be possible to transform data from Postgres or directly from BemiDB, which may actually perform better. You're right, the data engineering world is complex, constantly evolving, and has many various solutions. I'd also like to know about any good resources that people use :) For us, we mostly talked to many potential users asking about their data setups and challenges, and had many conversations with friends and experts in this field. I also read a few weekly newsletters, substracks, and follow people in this space on X (many recently started posting on Bluesky). For a deeper research, reading docs and specs, experimenting, watching talks, listening to podcasts, reading subreddits, etc.
lucasfcosta 2y ago
Amazing work, guys! Looking forward to seeing where BemiDB is going.
- arjunlol 2y ago
  Thank you Lucas!
anentropic 2y ago
I'm looking for low latency queries over not-very-big data (40-100M rows) in user-facing dashboards How does the latency of Iceberg-on-S3 compare to say an EBS volume?
- exAspArk 2y ago
  I'd say that querying data from S3 is not ideal when low-latency queries are required. Generally, there could be a few roundtrip requests to fetch metadata (JSON, Avro) and data (Parquet) files, which may lead to around 1s or so latency. However, we have caching on our roadmap (it could be just a simple TTL for the fetched data or some more sophisticated caching depending on the synced & queried data)
  - anentropic 2y ago
    How tied is it to S3? Could I easily point it to Iceberg tables on another storage target?
    - exAspArk 2y ago
      Yes! BemiDB natively supports two storage layers, a local disk and S3 (we assumed that most people would choose this in production environments to simplify management). When I query Iceberg tables stored on SSD, it works superfast.
      - anentropic 2y ago
        awesome, thanks
hipadev23 2y ago
I don’t totally understand the fascination with storing analytical data on S3. It’s not fast, and if you’re in a write heavy environment it’s definitely not cheap either. What’s with the avoidance of clickhouse or duckdb paired with insanely fast EBS or even physically attached storage? You can still backup to s3, but using s3 for live analytics queries is missing out on so much of the speed.
- nine_k 2y ago
  S3 is a protocol understood by "everyone". If you're on AWS, as many are, it's basically the only natural choice. But a number of cloud providers, and a bunch of self-hostable software offer an S3 interface. Clickhouse on local NVMe is one possible solution, but then you are married to that solution. An S3 interface is more universal and allows you to mix and match your tools, even though this comes at some expense.
- exAspArk 2y ago
  My few cents: - Compute and storage separation simplifies managing a system making compute "ephemeral" - Compute resources can be scaled separately without worrying about scaling storage - Object storage provides much higher durability (99.999999999% on S3) compared to disks - Open table formats on S3 become a universal interface in the data space allowing to bring many other data tools if necessary - Costs at scale can actually be lower since there is no data transfer cost within the same region. For example, you can check out WarpStream (Kafka on object storage) case studies that claim saving 5-10x
- potamic 2y ago
  Would love the authors to pitch in with their use cases, but I think most people simply do not need sub millisecond analytics. This is mostly replacing typical spark pipelines where you're okay with sub second latencies. S3 is the cheapest, fully managed storage you can get that can scale infinitely. When you're already archiving to S3, doubling it for analytics saves cost and simplifies data management.
  - hipadev23 2y ago
dantodor 2y ago
Question: is it possible to use BemiDB in ... don't know how to spell it, maybe read-only mode? And by that I mean one Bemi instance that is connected to postgres source, and others that use the produced Iceberg tables to answer queries? Poor man's scalability of the query engine :) ... I would also imagine having an instance that is write-only (reads from postgres and produces the Iceberg tables) and one or many query-only engines. Other than that, great work, will definitely start using it!
- exAspArk 2y ago
  Great ideas! We'll keep this suggestion related to read/write separation in mind. We started with a simple unified solution, but we'll keep iterating, listening and addressing any feedback :)
  - dantodor 2y ago
    FWIW, I'm using ro-api atm for that.
nitinreddy88 2y ago
How does update or continuous inserts get written/updated to parquet files? Architecture doesn't show nor anything in docs. 1. All the benchmarks/most of the companies, show one time data exists and try querying/compressing in different formats which is far from reality 2. Do you rewrite parquet data every time new data comes? Or partitioned by something? No examples 3. How does update/delete works. Update might be niche case. But deletion/data retention/truncation is must and I don't see how you support that
- exAspArk 2y ago
  Our initial approach is to do full table re-syncs periodically. Our next step is to enable incremental data syncing by supporting insert/update/delete according to the Iceberg spec. In short, it'd produce "diff" Parquet files and "stitch" them using metadata (enabling time travel queries, schema evolution, etc.)
cocoflunchy 2y ago
What I would really love is a dead simple way to: 1) connect to my transactional Postgres db 2) define my materialized views 3) have these views update in realtime 4) query these views with a fast engine And ideally have the whole thing open source and be able to run it in CI We tried peerdb + clickhouse but Clickhouse materialized views are not refreshed when joining tables. Right now we’re back to standard materialized views inside Postgres refreshed once a day but the full refreshes are pretty slow… the operational side is great though, a single db to manage.
- benpacker 2y ago
  You can do this with Materialize or Feldera. The keyword to look for is “incremental view maintenance”
- capkutay 2y ago
  that's been supported in striim since 2016 https://dl.acm.org/doi/10.1145/3129292.3129294 https://dl.acm.org/doi/10.1145/3129292.3129294
- lsuresh 2y ago
  Yeah you definitely need something like Feldera: https://github.com/feldera/feldera https://github.com/feldera/feldera
- deleted 2y ago
  [deleted]
- make_it_sure 2y ago
  we have the exact same problem. We tried Clickhouse but their materialized view limitations stopped us.
nwhnwh 2y ago
Any benchmarks against ClickHouse?
- exAspArk 2y ago
  Sorry, we haven't benchmarked it against ClickHouse yet. Our initial point of reference was just Postgres
banditelol 2y ago
Looking at the syncer it seems like copying data to csv from the whole table everytime (?) Code: https://github.com/BemiHQ/BemiDB/blob/6d6689b392ce6192fe521ad02b4b11cce150fb44/src/syncer.go#L173 https://github.com/BemiHQ/BemiDB/blob/6d6689b392ce6192fe521a... I cant imagine until at what scale can you do this and is there anything better we can do before using debezium to sync the data via cdc? Edit: add code permalink
- exAspArk 2y ago
  Our initial approach was to implement periodic full table re-syncing. We're starting to work on CDC with logical replication for incremental syncing. Here is our roadmap https://github.com/BemiHQ/BemiDB#future-roadmap https://github.com/BemiHQ/BemiDB#future-roadmap
fanyang01 2y ago
[flagged]
- deleted 2y ago
  [deleted]
darkbatman 2y ago
We do quite similar but through Debezium/Kafka CDC pipeline to clickhouse. This way primary database is protected. Directly querying from files from postgres might get slower eventually IMO compare.
- exAspArk 2y ago
  This is a great DIY setup. We're hoping to compress this stack and simplify it down to a single binary
mun763272dfuwhz 2y ago
[flagged]

209 points by exAspArk 2y ago

deleted 2y ago

[deleted]

oulipo 2y ago

Really cool! I have an IoT use-case where I ingest data, I want to keep like the last 3 months or so in Postgresql, and then store the old data as parquet files on S3 I planned initially to do chunks on S3 and do the analytical queries using duckdb, I'm wondering if your tool would be a good replacement? For now I don't have that many analytical queries, I'm mostly doing visualization of the data points by querying a range (eg last 2 weeks of data for a device) Does it then make sense to use columnar storage or am I better off with "regular Postgres"? Or in my case does your approach provide "best of both worlds" in the sense that I could do some occasional analytical queries on past data stored on S3, and regularly access "last 3 months" data for visualization using the data stored in the regular Postgres?

exAspArk 2y ago
Thank you! Yes, absolutely! 1) You could use BemiDB to sync your Postgres data (e.g., partition time-series tables) to S3 in Iceberg format. Iceberg is essentially a "table" abstraction on top of columnar Parquet data files with a schema, history, etc. 2) If you don't need strong consistency and fine with delayed data (the main trade-off), you can use just BemiDB to query and visualize all data directly from S3. From a query perspective, it's like DuckDB that talks Postgres (wire protocol). Feel free to give it a try! And although it's a new project, we plan to keep building and improving it based on user feedback.
- oulipo 2y ago
  Thanks! - Can you give me more info about the strong consistency and delayed data, so I can better picture it with a few examples? - Also, is it possible to do the sync with the columnar data in "more-or-less real-time" (eg do a NOTIFY on a new write in my IoT events table, and push in the storage?) - Would your system also be suited for a kind of "audit-log" data? Eg. if I want to have some kind of audit-table of all the changes in my database, but only want to keep a few weeks worth at hand, and then push the rest on S3, or it doesn't make much sense with that kind of data?
  - exAspArk 2y ago

hoerzu 2y ago

Can you give an example if I have 5gig (2 million rows) How will it be created differently for columnar access?

exAspArk 2y ago
We ran some benchmarks (TPC-H, designed for OLAP) with ~10M records https://github.com/BemiHQ/BemiDB#benchmark https://github.com/BemiHQ/BemiDB#benchmark The BemiDB storage layer produced ~300MB columnar Parquet files (with ZSTD compression) vs 1.6GB of data in Postgres.
- Sesse__ 2y ago
  Does TPC-H SF1 really take _one and a half hours_ for you on regular Postgres? Last time I tried (in the form of DBT-3), it was 22 queries and most of them ran in a couple seconds.
  - exAspArk 2y ago
    Interesting. I haven't used the DBT-3 kit, does it add any indexes? I manually added these Postgres indexes https://github.com/BemiHQ/BemiDB/blob/main/benchmark/data/create-indexes.ddl https://github.com/BemiHQ/BemiDB/blob/main/benchmark/data/cr... to reduce the main bottlenecks on SF0.1 and reduce the total time from 1h23m13s to 1.5s. But SF1 still took more than 1h
    - Sesse__ 2y ago
      It adds a bunch of indexes, yes. I don't think anyone really runs TPC-H unindexed unless they are using a database that plain doesn't support it; it wouldn't really give much meaningful information. Edit: I seemingly don't have these benchmarks anymore, and I'm not going to re-run them now, but I found a very (_very_) roughly similar SF10 run clocking in around seven minutes total. So that's the order of magnitude I would be expecting, given ten times as much data.
      - 2y ago

gigatexal 2y ago

Query Engine: embeds the DuckDB query engine to run analytical queries. Storage Layer: uses the Iceberg table format to store data in columnar compressed Parquet files. Smart. Imma test this out for sure.

arjunlol 2y ago
Thanks! Give it a try and let us know any feedback :)

paurora 2y ago

[dead]

exAspArk 2y ago
Thanks! We love the pg_moooncake extension (and pg_duckdb used under the hood). Although our approaches are slightly different. Long-term, we want to allow anyone to use BemiDB by using native Postgres logical replication without installing any extensions (many Postgres hosting providers impose their restrictions, upgrading versions might be challenging, OLAP queries may affect OLTP performance if within the same database, etc.)
shayonj 2y ago
Absolutely stoked for pg_mooncake. I really want to see some of these things happening inside PG and taking advantage of the PG internals + native storage. Only bummer is adoption by places where users are currently, say Aurora. But thats probably a problem for another day :) P.S The integration with something like Neon is really cool to see.

neeleshs 2y ago

Congratulations! I was looking and pg_analytics from ParadeDB hoping this use case would be solved (the dump from pg to parquet part), but it doesnt yet do it. How does it handle updates?

exAspArk 2y ago
Thank you! The pg_analytics Postgres extension partially supports different file formats. We bet big on Iceberg open table format, which uses Parquet data files under the hood. Our initial approach is to do periodic full table resyncing. The next step is to support incremental Iceberg operations like updates. This will involve creating a new "diff" Parquet file and using the Iceberg metadata to point to the new file version that changes some rows. Later this will enable time travel queries, schema evolution, etc.
- neeleshs 2y ago
  Fantastic!

winddude 2y ago

difference to something like duckdb?

polskibus 2y ago
How can you setup automatic replication from Postgresql to a duckdb instance?
- exAspArk 2y ago
  The most common approach is to read Postgres data in DuckDB https://duckdb.org/docs/extensions/postgres.html https://duckdb.org/docs/extensions/postgres.html
simlevesque 2y ago
> > Alternatives > DuckDB: > - Designed for OLAP use cases. Easy to run with a single binary. > - Limited support in the data ecosystem (notebooks, BI tools, etc.). Requires manual data syncing and schema mapping for best performance.
- exAspArk 2y ago
  ^ This! Here is the link that briefly describes pros and cons of different alternatives for analytics https://github.com/BemiHQ/BemiDB#alternatives https://github.com/BemiHQ/BemiDB#alternatives

canadiantim 2y ago

How does this compare to ParadeDB? Seems to occupy the same space

exAspArk 2y ago
We love ParadeDB and their team. Their primary focus is search (Elasticsearch on Postgres), but they also have the pg_analytics Postgres extension (foreign data wrappers and embedded DuckDB). The biggest difference is in a Postgres extension vs a separate OLAP process. We want to allow anyone with just Postgres to be able to perform analytics queries without affecting resources in the transactional database, building and installing extensions (might not be possible with some hosting providers), dealing with dependencies and their versions when upgrading Postgres, manually syncing data from Postgres to S3, etc.

dangoodmanUT 2y ago

This is probably the most streamlined/all-inclusive solution out of all that I've seen, but this has definitely been an extremely saturated space in 2024

dangoodmanUT 2y ago
mostly everyone riding on duckdb's tailcoat
dirtbag__dad 2y ago
What are the other viable players? As mentioned in another thread to this post duckdb is not “production-ready.” That has been a non-starter for us at work.
- exAspArk 2y ago
  That's why our current approach is to build missing or not fully functional features ourselves to move fast. For example, DuckDB performs reads from Iceberg tables not according to the spec, can't perform writes, etc.

levkk 2y ago

Moving data between systems is problematic. Where this product is actually needed (multi-TB databases under load) is where logical replication won't be able to sync your tables in time. Conversely, small databases where this will work don't really need columnar storage optimizations.

exAspArk 2y ago
Fair point. We think that BemiDB currently can be useful when used with small and medium Postgres databases. Running complex analytics queries on Postgres can work, but it usually requires tuning it and adding indexes tailored to these queries, which may negatively impact the write performance on the OLTP side or may not be possible if these are ad-hoc queries. > (multi-TB databases under load) is where logical replication won't be able to sync your tables in time I think the ceiling for logical replication (and optimization techniques around it) is quite high. But I wonder what people do when it doesn't work and scale?
- delive 2y ago
  What would you consider to be small or medium? I have a use case for analytics on ~1 billion rows that are about 1TB in postgres. Have you tried on that volume?
  - exAspArk 2y ago
    We haven't tested this with 1TB Postgres databases yet, assuming that most companies operating at this scale already built analytics data pipelines :) I'm curious if you currently move the data from this Postgres to somewhere else, or not yet?
    - delive 2y ago
      Not yet, mostly just kicked the can down the road due to costs. Like you said in another post, careful indexes on postgres get you quite far, but not nearly as flexible as a columnar DB. I think your project is great. I suspect incremental updates will be a big feature for most uptake (one we would need to try this out at least).

woodhull 2y ago

As much as DuckDB is cute I've mostly come to believe that Clickhouse is the perfect thing to pair Postgres with. This is especially true now that they've acquired PeerDB and are integrating it into the Clickpipes cloud product. DuckDB is neat, and I understand why a company like BemiDB would build their product on top of it, but as a prospective customer embedded databases are a weird choice for serious workloads when there are other good open-source solutions like Clickhouse available.

exAspArk 2y ago
ClickHouse is definitely a popular choice nowadays. I'm curious whether you self-host ClickHouse or use their Cloud? We wanted to make BemiDB as simple to run as possible with a single binary and object storage (vs large machines, big disks, clustering, running Temporal for CDC, etc.)
- Onavo 2y ago
  Clickhouse has an embedded version (https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb), the issue with duck is that it's too buggy for production loads. You can see a nice list of the issues here: https://news.ycombinator.com/item?id=41490707 https://news.ycombinator.com/item?id=41490707
maxmcd 2y ago
Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet). I can imagine this product is a very elegant solution for many types of companies/teams/workloads.
- zX41ZdbW 2y ago
 You can mount read replicas on object storage in ClickHouse. Example: CREATE DATABASE test; USE test; CREATE TABLE hackernews_history UUID '66491946-56e3-4790-a112-d2dc3963e68a' ( `update_time` DateTime DEFAULT now(), `id` UInt32, `deleted` UInt8, `type` Enum8('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5), `by` LowCardinality(String), `time` DateTime, `text` String, `dead` UInt8, `parent` UInt32, `poll` UInt32, `kids` Array(UInt32), `url` String, `score` Int32, `title` String, `parts` Array(UInt32), `descendants` Int32 ) ENGINE = ReplacingMergeTree(update_time) ORDER BY id SETTINGS disk = disk(readonly = true, type = 's3_plain_rewritable', endpoint = 'https://clicklake-test-2.s3.eu-central-1.amazonaws.com/', use_environment_credentials = false); And you can try it right now. Install ClickHouse: curl https://clickhouse.com/ | sh ./clickhouse local Run the query above to attach the table. The table is updated in real time. For example, here is your comment: :) SELECT * FROM hackernews_history WHERE text LIKE '%Clickhouse is amazing%' ORDER BY update_time \G Row 1: ────── update_time: 2024-04-06 16:35:28 id: 39785472 deleted: 0 type: comment by: mightybyte time: 2024-03-21 22:59:20 text: I'll second this. Clickhouse is amazing. I was actually using it today to query some CSV files. I had to refresh my memory on the syntax so if anyone is interested:<pre><code> clickhouse local -q "SELECT foo, sum(bar) FROM file('foobar.csv', CSV) GROUP BY foo FORMAT Pretty" </code></pre> Way easier than opening in Excel and creating a pivot table which was my previous workflow.Here's a list of the different input and output formats that it supports.<a href="https://clickhouse.com/docs/en/interfaces/formats" rel="nofollow">https://clickhouse.com/docs/en/interfaces/formats</a> dead: 0 parent: 39784942 poll: 0 kids: [39788575] url: score: 0 title: parts: [] descendants: 0 Row 2: ────── update_time: 2024-04-06 18:07:34 id: 31334599 deleted: 0 type: comment by: richieartoul time: 2022-05-11 00:54:31 text: Not really. Clickhouse is amazing, but if you want to run it at massive scale you’ll have to invest a lot into sharding and clustering and all that. Druid is more distributed by default, but doesn’t support as sophisticated of queries as Clickhouse does.Neither Clickhouse nor Druid can hold a candle to what Snowflake can do in terms of query capabilities, as well as the flexibility and richness of their product.That’s just scratching the surface. They’re completely different product categories IMO, although they have a lot of technical / architectural overlap depending on how much you squint.Devil is in the details basically. dead: 0 parent: 31334527 poll: 0 kids: [31334736] url: score: 0 title: parts: [] descendants: 0 Row 3: ────── update_time: 2024-11-07 22:29:09 id: 42081672 deleted: 0 type: comment by: maxmcd time: 2024-11-07 22:13:12 text: Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet).I can imagine this product is a very elegant solution for many types of companies/teams/workloads. dead: 0 parent: 42080385 poll: 0 kids: [] url: score: 0 title: parts: [] descendants: 0 3 rows in set. Elapsed: 3.981 sec. Processed 42.27 million rows, 14.45 GB (10.62 million rows/s., 3.63 GB/s.) Peak memory usage: 579.26 MiB.

partdavid 2y ago

How does this replicate Postgres data? I glanced at the code and saw that it exports to a CSV file then writes out an Iceberg table for an initial snapshot--does it use Postgres logical replication?

exAspArk 2y ago
Full table re-syncing is our initial solution. Using Postgres logical replication is next on our roadmap!
- oulipo 2y ago
  So if we have a very large table, it means each time I replicate (how often does that happen?) it needs to rewrite the entire table and upload it to S3, right?

leighleighleigh 2y ago

Definitely checking this out today! I use postgres for ~30 GB of machine learning data (object detection) and have a couple workflows which go through the Postgres->Parquet->DuckDB processing route. A couple questions, if you have time: 1. How do you guys handle multi-dimensional arrays? I've had issues with a few postgres-facing interfaces (libraries or middleware) where they believe everything is a 1D array! 2. I saw you are using pg_duckdb/duckdb under the hood. I've had issues calling plain-SQL functions defined on the postgres server, when duckdb is involved. Does BemiDB support them? Thanks for sharing, and good luck with it!

exAspArk 2y ago
Thank you, please give it a try! Great questions: 1. We currently don't support multi-dimensional arrays, but we plan to add support for such complex data structures. 2. Would you be able to share what type of user-defined functions are these, do they do modify the data or read it?
- leighleighleigh 2y ago
  1. good to hear! 2. The bulk of them are convenience wrappers which resolve UUIDs into other values, so most are read-only with only a single table lookup.

VoxPelli 2y ago

The AGPL license is a no-go for me. While it’s technically true that it’s an OSI license it’s mostly used to scare away competing cloud vendors from hosting the software, which isn’t in spirit of OSS. Have you looked into the more modern choices? Like the Business Source License that MariaDB created and uses or the Functional Source License that Sentry created as an improvement over the Business Source License? https://fsl.software/ https://fsl.software/ Both those licenses have a fair source phase that automatically resolves into an open source phase over time. Thus one gets the best of two worlds: An honest descriptive license for protecting one’s business model + a normal permissive OSS license that ensures longevity and prevents lock-in.

exAspArk 2y ago
Our philosophy in general is to go to a more open license over time (vs the other direction). So we might consider other more permissive OSI-approved licenses. Would you be able to share why AGPL license is a no-go for you? I'm genuinely curious about your use case. In simple words, it'd require a company to open source their BemiDB code only if they made modifications and were distributing it to other users (allowing modifications and using it internally without any restrictions)
- senorrib 2y ago
  Please, don’t. AGPL is great and you’re fine using it.
- VoxPelli 2y ago
  So you have Contributor License Agreements that enable you to relicense to something like GPL or MIT whenever you want to? Because AGPL itself can not be relicensed and it even needed special wording when created to become GPL-compliant. Since you’re a startup I believe that you use AGPL to achieve the “fair source” idea (https://fair.io/ https://fair.io/) – where you yourself can provide a hosted service without providing all your source code while hoping others won’t be as they will need to provide all theirs. In simplified terms: It’s an anti-AWS defense. Helping you avoid being outcompeted by a big cloud vendor using your project without paying anything for it. And AGPL is a poor “fair source” license, especially as it was never designed to be a “fair source” license but also since it doesn’t give the impression of “fair source” but rather the impression of “open source”, giving an almost deceptive and dishonest look. And since AGPL is perpetual, unlike eg BSL and FSL, one is stuck with the “fair source” license forever, rather than having it be converted into a mainstream OSS license over time. It can eg make it hard for someone else to eventually, if your company goes away and the project gets abandoned (which is sadly the most likely outcome of most startups), pick up the project and build a company around that while using similar “fair source” principles like you. If you are doing like what SourceHut is doing (https://sourcehut.org/blog/2022-10-09-ip-assignment-or-lack-thereof/ https://sourcehut.org/blog/2022-10-09-ip-assignment-or-lack-...) and going all in on AGPL and playing by its rules yourself as well and treating it as “open source” rather than “fair source”, then well done! I still would likely want to avoid the legalese complexity of AGPL in my stack though and try to generally stick to permissive licenses and the occasional GPL-licensed projects, like eg Linux. And eg Google has similar guidelines when using OSS-code internally.

gregw2 2y ago

So it looks like you don't use postgres extensions so you can run this on an EC2 against an Aurora Postgres instance and dump files to S3 Iceberg right? And can you then have Glue Catalog auto-crawl them and expose them in Athena? Or are they DuckDB-managed Iceberg tables essentially?

exAspArk 2y ago
Exactly! You can run it on any server connecting to any Postgres, without installing custom extensions (AWS Aurora supports only a limited number of extensions https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQ...). The Iceberg tables are created separately from the DuckDB query engine. So you should be able to read these Iceberg tables by using any other Iceberg-compatible tools and services like AWS Athena.

jakozaur 2y ago

Cool. Every database or data source (e.g. CRM) should produce Iceberg format for you. Though a little sceptical of embedding DuckDB. It is easy and better to isolate Read/Write paths, and it has a lot of other benefits.

exAspArk 2y ago
Iceberg for the win! We actually separate Read/Write paths. BemiDB reads by levering DuckDB as a query engine. And it writes to Iceberg completely separately from DuckDB. I'm curious if that's what you imagined.
- jakozaur 2y ago
  Ideally, I would love many places writing to the Iceberg catalogue and then using it as a data lake. In the data lake, I would have a gateway provisioning DuckDB on demand for each user.
  - exAspArk 2y ago
    Oh, interesting, thanks for sharing it!

mrbluecoat 2y ago

I hadn't heard of Devbox before, so thanks for sharing

exAspArk 2y ago
Haha, it's awesome for isolating project environments (languages, databases, etc.) without using docker

globular-toast 2y ago

I don't get how this would do away with the need for some kind of ETL. Most apps use highly normalised schemas that are completely unsuitable for analytical users. Not to mention you wouldn't want to couple your app schema to your warehouse schema. Am I missing something? How would this replace traditional data warehousing?

exAspArk 2y ago
Good point. For more complex scenarios, people would still be able to implement, for example, a Medallion Architecture to progressively improve data quality and structure. Because it is Postgres- and Iceberg-compatible (db and data), it's possible to bring more other advanced data tools when it's needed to perform data transformation and movement. Currently, we see it as a Postgres read replica for analytics. But it's easy to imagine that in the future it could be used as a standalone OSS database on top of a data lakehouse with an open format in S3.
- globular-toast 2y ago
  Cool, I can definitely see this smoothing the path towards a full DW solution, assuming that is ever needed. Could you see it working with something like dbt, say doing transformations in a dedicated pg database then serving the transformed data to users via the read replica? Out of interest, do you know any good resources covering the current state of data engineering? I find the area quite impenetrable compared to software engineering. Almost like much of it is trade secrets and passed down knowledge and none of it written down.
  - exAspArk 2y ago
    Our plan is to make BemiDB work with dbt by leveraging the Postgres-compatibility (supported dbt adapters https://docs.getdbt.com/docs/trusted-adapters https://docs.getdbt.com/docs/trusted-adapters). So it should be possible to transform data from Postgres or directly from BemiDB, which may actually perform better. You're right, the data engineering world is complex, constantly evolving, and has many various solutions. I'd also like to know about any good resources that people use :) For us, we mostly talked to many potential users asking about their data setups and challenges, and had many conversations with friends and experts in this field. I also read a few weekly newsletters, substracks, and follow people in this space on X (many recently started posting on Bluesky). For a deeper research, reading docs and specs, experimenting, watching talks, listening to podcasts, reading subreddits, etc.

lucasfcosta 2y ago

Amazing work, guys! Looking forward to seeing where BemiDB is going.

arjunlol 2y ago
Thank you Lucas!

anentropic 2y ago

I'm looking for low latency queries over not-very-big data (40-100M rows) in user-facing dashboards How does the latency of Iceberg-on-S3 compare to say an EBS volume?

exAspArk 2y ago
I'd say that querying data from S3 is not ideal when low-latency queries are required. Generally, there could be a few roundtrip requests to fetch metadata (JSON, Avro) and data (Parquet) files, which may lead to around 1s or so latency. However, we have caching on our roadmap (it could be just a simple TTL for the fetched data or some more sophisticated caching depending on the synced & queried data)
- anentropic 2y ago
  How tied is it to S3? Could I easily point it to Iceberg tables on another storage target?
  - exAspArk 2y ago
    Yes! BemiDB natively supports two storage layers, a local disk and S3 (we assumed that most people would choose this in production environments to simplify management). When I query Iceberg tables stored on SSD, it works superfast.
    - anentropic 2y ago
      awesome, thanks

hipadev23 2y ago

I don’t totally understand the fascination with storing analytical data on S3. It’s not fast, and if you’re in a write heavy environment it’s definitely not cheap either. What’s with the avoidance of clickhouse or duckdb paired with insanely fast EBS or even physically attached storage? You can still backup to s3, but using s3 for live analytics queries is missing out on so much of the speed.

nine_k 2y ago
S3 is a protocol understood by "everyone". If you're on AWS, as many are, it's basically the only natural choice. But a number of cloud providers, and a bunch of self-hostable software offer an S3 interface. Clickhouse on local NVMe is one possible solution, but then you are married to that solution. An S3 interface is more universal and allows you to mix and match your tools, even though this comes at some expense.
exAspArk 2y ago
My few cents: - Compute and storage separation simplifies managing a system making compute "ephemeral" - Compute resources can be scaled separately without worrying about scaling storage - Object storage provides much higher durability (99.999999999% on S3) compared to disks - Open table formats on S3 become a universal interface in the data space allowing to bring many other data tools if necessary - Costs at scale can actually be lower since there is no data transfer cost within the same region. For example, you can check out WarpStream (Kafka on object storage) case studies that claim saving 5-10x
potamic 2y ago
Would love the authors to pitch in with their use cases, but I think most people simply do not need sub millisecond analytics. This is mostly replacing typical spark pipelines where you're okay with sub second latencies. S3 is the cheapest, fully managed storage you can get that can scale infinitely. When you're already archiving to S3, doubling it for analytics saves cost and simplifies data management.
- hipadev23 2y ago

dantodor 2y ago

Question: is it possible to use BemiDB in ... don't know how to spell it, maybe read-only mode? And by that I mean one Bemi instance that is connected to postgres source, and others that use the produced Iceberg tables to answer queries? Poor man's scalability of the query engine :) ... I would also imagine having an instance that is write-only (reads from postgres and produces the Iceberg tables) and one or many query-only engines. Other than that, great work, will definitely start using it!

exAspArk 2y ago
Great ideas! We'll keep this suggestion related to read/write separation in mind. We started with a simple unified solution, but we'll keep iterating, listening and addressing any feedback :)
- dantodor 2y ago
  FWIW, I'm using ro-api atm for that.

nitinreddy88 2y ago

How does update or continuous inserts get written/updated to parquet files? Architecture doesn't show nor anything in docs. 1. All the benchmarks/most of the companies, show one time data exists and try querying/compressing in different formats which is far from reality 2. Do you rewrite parquet data every time new data comes? Or partitioned by something? No examples 3. How does update/delete works. Update might be niche case. But deletion/data retention/truncation is must and I don't see how you support that

exAspArk 2y ago
Our initial approach is to do full table re-syncs periodically. Our next step is to enable incremental data syncing by supporting insert/update/delete according to the Iceberg spec. In short, it'd produce "diff" Parquet files and "stitch" them using metadata (enabling time travel queries, schema evolution, etc.)

cocoflunchy 2y ago

What I would really love is a dead simple way to: 1) connect to my transactional Postgres db 2) define my materialized views 3) have these views update in realtime 4) query these views with a fast engine And ideally have the whole thing open source and be able to run it in CI We tried peerdb + clickhouse but Clickhouse materialized views are not refreshed when joining tables. Right now we’re back to standard materialized views inside Postgres refreshed once a day but the full refreshes are pretty slow… the operational side is great though, a single db to manage.

benpacker 2y ago
You can do this with Materialize or Feldera. The keyword to look for is “incremental view maintenance”
capkutay 2y ago
that's been supported in striim since 2016 https://dl.acm.org/doi/10.1145/3129292.3129294 https://dl.acm.org/doi/10.1145/3129292.3129294
lsuresh 2y ago
Yeah you definitely need something like Feldera: https://github.com/feldera/feldera https://github.com/feldera/feldera
deleted 2y ago
[deleted]
make_it_sure 2y ago
we have the exact same problem. We tried Clickhouse but their materialized view limitations stopped us.

nwhnwh 2y ago

Any benchmarks against ClickHouse?

exAspArk 2y ago
Sorry, we haven't benchmarked it against ClickHouse yet. Our initial point of reference was just Postgres

banditelol 2y ago

Looking at the syncer it seems like copying data to csv from the whole table everytime (?) Code: https://github.com/BemiHQ/BemiDB/blob/6d6689b392ce6192fe521ad02b4b11cce150fb44/src/syncer.go#L173 https://github.com/BemiHQ/BemiDB/blob/6d6689b392ce6192fe521a... I cant imagine until at what scale can you do this and is there anything better we can do before using debezium to sync the data via cdc? Edit: add code permalink

exAspArk 2y ago
Our initial approach was to implement periodic full table re-syncing. We're starting to work on CDC with logical replication for incremental syncing. Here is our roadmap https://github.com/BemiHQ/BemiDB#future-roadmap https://github.com/BemiHQ/BemiDB#future-roadmap

fanyang01 2y ago

[flagged]

deleted 2y ago
[deleted]

darkbatman 2y ago

We do quite similar but through Debezium/Kafka CDC pipeline to clickhouse. This way primary database is protected. Directly querying from files from postgres might get slower eventually IMO compare.

exAspArk 2y ago
This is a great DIY setup. We're hoping to compress this stack and simplify it down to a single binary

mun763272dfuwhz 2y ago

[flagged]