Would RisingWave and PostgreSQL both be used in any scenarios?

[omnipresent07]

Does running risingwave in a ecosystem remove the need to have PostgreSQL running? Would there ever be scenarios where both are run simultaneously?

[yingjunwu]

Hi @omnipresent07 - we do see many people using both RisingWave and Postgres.

RisingWave is not built for transaction processing (see: https://docs.risingwave.com/docs/current/transactions/). So if you want to process transactions, you may want to use a PostgreSQL. RisingWave can consume PostgreSQL CDC and perform real-time streaming analytics.

The data stack may look like:

Your APP -> PostgreSQL (for transaction processing) -> RisingWave (for streaming analytics) -> Grafana/Metabase/Superset/whatever (for visualization)

But if you don't really need to do transaction processing, you may consider using RisingWave to replace PostgreSQL, especially for data streaming scenario. For example, when people use streaming processors like Flink/Samza/Storm, they typically want to use PostgreSQL/MySQL to serve queries. The data stack may look like:

Your APP -> Kafka -> Flink/Samza/Storm (for stream processing) -> PostgreSQL (for serving queries) -> Grafana/Metabase/Superset/whatever (for visualization)

or

Your APP -> PostgreSQL (for transaction processing) -> Flink/Samza/Storm (for stream processing) -> PostgreSQL (for serving queries) -> Grafana/Metabase/Superset/whatever (for visualization)

But using RisingWave, the data stack can be simplified to:

Your APP -> kafka -> RisingWave -> Grafana/Metabase/Superset/whatever (for visualization)

or

Your APP -> PostgreSQL (for transaction processing) -> RisingWave -> Grafana/Metabase/Superset/whatever (for visualization)

Please feel free to let me know if you have any questions.

[lmatz]

Thanks for the details, that's very helpful!

We do use the continuous view / continuous aggregate features a lot do do roll up for presenting analytic data to our end uses ( we run a SaaS product ).

Risingwave is born as a streaming engine.
Real-time data is ingested row by row, so to efficiently handle the incremental updates to the materialized view, the streaming engine must be used with the row-based storage system. All streaming engines on the market made such design choices.

As part of the reason, for building a materialized view or continuous aggregation query, RW does a better job than any time-series/OLAP databases.

However, the row-based storage system limits its OLAP query performance. That's why RW's read query performance is on par with Postgres. If the data volume of the read query is huge, e.g. > 10GB, RW is unable to compete with dedicated OLAP systems such as Clickhouse which equips a columnar storage system. It is just not what a row storage is designed for.

I'm just trying to understand where limitations and strengths are. There are some documents that suggest risingwave is not well suited as an OLAP database, and I'm struggling to understand why that might be.
But It appears that it should do quite well as an OLAP.

RW is going to use Iceberg as its columnar storage: #19418

RW will put the data of materialized views or tables into Iceberg (parquet format) to speed up its read query performance. Iceberg is the standard format of datalake. It is efficient and highly interoperable. The data is not vendor-locked in any proprietary column storage systems.

This is our highest priority at the moment. We estimate that at the end of Q1, the read performance of the query against Table/Materialized View in the Iceberg will be much closer to an OLAP system especially when the data column of the query is large

If the data volume of the read query is small, e.g. median 100 MB as shown in https://www.fivetran.com/blog/how-do-people-use-snowflake-and-redshift, the performance difference would be negligible. RW and other OLAP systems can both complete a reasonably complex query in sub-second ~ 1s.
Besides, if the memory is not the constraint, i.e. the tables and materialized views can mostly be cached, the difference would be even smaller.

[esatterwhite]

When the primary key is defined on a table or when an index is created, RW knows which essential part of data in the storage to read instead of blindly scanning all the unnecessary data. This applies to small and large data sets

Sure, but even with indexes, partitioning or sharding is eventually necessary with most all RDBMS. does rising wave not have the ability to partition or shard data? Or is it not necessary because of the object store backing?

[lmatz]

Or is it not necessary because of the object store backing?

Yes, all the data is stored on object store, which means any piece of data is visible to all the compute nodes and can be shared among all the compute nodes.
Each compute node does process and cach some partitions of the data. But it is merely caching, compute nodes do not store any data durably and are thus stateless.

Suppose we scale from 4 compute nodes to 5 compute nodes, without any downtime, the RW cluster can immediately process existing and new queries once the new compute node starts(e.g. start a new compute pod on kuberenetes). Compute nodes pull data from the object store when cache misses occur.

I feel like "partitioning or sharing" is a means to solve certain problems, and because of the difference in the design goal, architecture, and jargon among different DBs, I guess my answer could be more on point if I could directly address the objectives. I wonder if I missed any other objectives besides the following ones:

its just a lot of data that can logically be partitioned by time
partitioning or sharding is eventually necessary with most all RDBMS.

If the objective is to accelerate query performance by not reading unnecessary data that does not satisfy the "where" condition, then RW can do it.

Define the timestamp as the primary key, any query that selects a single point of time or a range of time will only read data that falles in this range.

the auto partitioning + retention features seemed very attractive.

https://docs.risingwave.com/processing/sql/temporal-filters
temporal filter can limit the data size by the time

If there is any objective that you worry RW cannot achieve, I am happy to answer and point to documentation.

[esatterwhite]

RW is going to use Iceberg as its columnar storage: #19418

@lmatz This looks promising and might actually be a necessity for adoption in our case. Is there a rough estimate on when it might be released, I see its been moved through a couple planned releases with 2.4 its current. And there looks to be work going on for 2.2, 2.3 and 2.4 concurrently. Its hard to gauge when things might land.

[taapkenz]

Hi @esatterwhite, we're expecting the feature to be available early March. It is the top priority for our team at the moment. Our CEO is pretty active on socials and you can get a sense from his posts how important and promising he feels it is.

If you think it'd be helpful, I'd be happy to arrange a call with Rayees Pasha, our Head of Product to learn more about your requirements and offer any help, if needed!

From what you've described so far, your use case aligns perfectly (well almost) with what RisingWave is built for!