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.

[esatterwhite]

How does read query performance compare to standard postgres. Is data partitioned for large data sets?

[lmatz]

Hi @esatterwhite,

RW's read query performance is generally on par with Postgres. Risingwave wins some and Postgres wins some, depending on the specific query you are running. Could you share a few examples of queries?

Is data partitioned for large data sets?

If the underlying question is whether Risingwave always scans the full table, the answer is no.

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.

For example, let’s say we define:

create table t (
  v1 int,
  v2 varchar,
  v3 timestamp,
  primary key (v3)
)

then any query in the form of
select * from t where v3 = <timestamp X> [or v3 = <timestamp Y]
or
select * from t where v3 >= <timestamp X> and v3 <= <timestamp Y>
can both be accelerated based on the physical clustering of the table t.

RW handles the case just like it has both a hash partition and a range partition automatically without requiring the user to manually specify "partition by hash" or "partition by range" keywords in Postgres.

[esatterwhite]

Risingwave wins some and Postgres wins some, depending on the specific query you are running. Could you share a few examples of queries?

I think it is mostly a analytics use case. Mostly real time and/or real time processing. We have a postgres / Timescaledb setup that we are struggling with for a variety of reasons.

Namely

• storage space is constantly a problem. Volume of data, speed of data, cost of storage (ebs).
• postgres is hard to scale in a kubernetes world

The selection of timescale may have been for the wrong reasons. Its not really a time series specific problem, its just a lot of data that can logically be partitioned by time and the auto partitioning + retention features seemed very attractive. The retention was attractive mainly because of the storage cost problem. We don't particularly have a reason to discard the source data.

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 ).

Our current plan has been to separate this into multiple timesacledb instances, put kafka in front of it and have workers do ETL into postgres effectively as we are constantly running into scale problems with our write through put, number of connections, disk capacity, etc. We have been looking at starrocks as well, but risingwave seems to be much better, suited for our use cases internally.

Our queries can range from basic aggregations (sum, count) over finite time ranges, to some fairly complex operations in the streaming portion.

-- simple query over a timescale continuous view
  SELECT
    component_id,
    SUM(value)::BIGINT AS total
  FROM usage_metrics_byte_size_5_min_v1
  WHERE account_id = $1
    AND event_ts_bucket >= $2
    AND event_ts_bucket < $3
  GROUP BY component_id

-- more complex query over continuous agg
  WITH main_aggregation AS (
    SELECT 
      component_id,
      log_cluster_id,
      SUM(count)::BIGINT AS count,
      SUM(size)::BIGINT AS total_size
    FROM usage_metrics_log_cluster_10_min
    WHERE
      component_id = $2 AND 
      ts_bucket >= $3 AND ts_bucket < $4
    GROUP BY component_id, log_cluster_id
    ORDER BY total_size DESC
    LIMIT $5
  )
  SELECT
    main_aggregation.log_cluster_id,
    log_clusters.template,
    log_clusters.first_seen_at,
    log_clusters.apps,
    main_aggregation.count,
    main_aggregation.total_size
  FROM main_aggregation
  INNER JOIN (
    SELECT
      component_id,
      log_cluster_id,
      MIN(template) AS template,
      MIN(first_seen_at) AS first_seen_at,
      ARRAY_REMOVE(ARRAY_AGG(DISTINCT annotations->>'app' ORDER BY annotations->>'app'), NULL) AS apps
    FROM log_clusters
    WHERE
      log_cluster_id IN (SELECT log_cluster_id FROM main_aggregation) AND
      account_id = $1 AND component_id = $2 AND ts >= $3 AND ts < $4
    GROUP BY component_id, log_cluster_id
  ) log_clusters ON
    main_aggregation.component_id = log_clusters.component_id AND
    main_aggregation.log_cluster_id = log_clusters.log_cluster_id
  ORDER BY total_size DESC

   -- timescale materialize view
    CREATE MATERIALIZED VIEW ${aggregate}
    WITH (timescaledb.continuous, timescaledb.create_group_indexes=false) AS
    SELECT
      public.time_bucket(INTERVAL '10 minutes', ts) AS ts_bucket,
      account_id,
      component_id,
      annotations,
      SUM(count) as count,
      SUM(size) as size
    FROM ${hypertable}
    GROUP BY account_id, component_id, annotations, ts_bucket

some of the more complex things are trying to generate unique arrays or sets of values effectively over time, or deal with json objects during aggregations. Some of these could probably be separate views. It may not be the best design currently, but that is a different problem

[esatterwhite]

Another question I would have is how well suited is risingwave to do joins on static data. It seems to be optimized for joining streams, but if we were to create static fact / lookup tables, are joins just as efficient when combining streams and static data (potentially on large data sets) or even against a large materialized view.

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.

I can understand not being optimized for OLTP / live application databases that are maintaining state. But It appears that it should do quite well as an OLAP. In particular if your generating views to serve specific analytic queries. Am I missing something?

[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.