PostgreSQL Compatibility

PostgreSQL Compatibility for a database system like RisingWave means that it is designed to understand and interact using the protocols, SQL dialect, and data types commonly associated with PostgreSQL. This significantly lowers the barrier to adoption for users already familiar with PostgreSQL and allows integration with the vast ecosystem of tools and applications built for it.

Key Aspects of PostgreSQL Compatibility

1. Wire Protocol Compatibility:

   • RisingWave implements the PostgreSQL wire protocol (version 3.0).
   • Benefit: This allows standard PostgreSQL clients (like psql, pgAdmin), database drivers (JDBC, ODBC, Python's psycopg2, Node.js pg, etc.), and BI tools (Tableau, Metabase, Grafana with PostgreSQL data source) to connect to and interact with RisingWave as if it were a PostgreSQL server. Users can often use their existing tools with minimal to no changes.

2. SQL Dialect Resemblance:

   • RisingWave's SQL syntax for defining sources, sinks, materialized views, and querying data is heavily inspired by and largely compatible with PostgreSQL's SQL dialect.
   • Benefit: Users familiar with PostgreSQL SQL can leverage their existing knowledge to write streaming queries in RisingWave. While RisingWave introduces streaming-specific constructs (like CREATE SOURCE, CREATE SINK, windowing functions for streaming), the core query syntax (SELECT, WHERE, GROUP BY, JOINs, etc.) feels very familiar.

3. Data Type Support:

   • RisingWave supports a wide range of data types that are equivalent or very similar to those found in PostgreSQL (e.g., INTEGER, VARCHAR, TIMESTAMP, DECIMAL, BOOLEAN, JSONB, arrays).
   • Benefit: Simplifies data integration and reduces the need for complex type casting when moving data between PostgreSQL (or PostgreSQL-compatible systems) and RisingWave, or when defining schemas for data from PostgreSQL sources.

4. System Catalog (Partial Resemblance):

   • While not identical, RisingWave provides system catalog tables and views (e.g., in rw_catalog) that are conceptually similar to PostgreSQL's pg_catalog. This allows users and tools to introspect system metadata.
   • Benefit: Tools that rely on querying system catalogs for schema information or object discovery can often be adapted to work with RisingWave.

Why is PostgreSQL Compatibility Important for RisingWave?

• Reduced Learning Curve: Users don't need to learn an entirely new query language or a new set of tools to start using RisingWave for stream processing.
• Ecosystem Leverage: Enables immediate integration with a rich ecosystem of client libraries, drivers, ORMs, BI tools, and administrative tools.
• Simplified Migration/Integration: Makes it easier to build streaming applications that complement existing PostgreSQL databases or to migrate certain analytical workloads from PostgreSQL to a streaming context.
• Developer Productivity: Developers can use familiar tools and SQL patterns, speeding up the development of real-time data applications.
• Community & Knowledge Base: Users can draw upon the vast amount of existing PostgreSQL knowledge and community resources for SQL-related queries and best practices.

Limitations and Differences

While RisingWave strives for high compatibility, it's important to note:

• Streaming Focus: RisingWave is a stream processing system, not a general-purpose OLTP relational database like PostgreSQL. Its primary goal is to process and analyze data in motion.
• Unsupported PostgreSQL Features: Not all PostgreSQL features are supported, especially those specific to OLTP workloads, complex transactional models beyond what's needed for streaming consistency, or certain advanced SQL functions not relevant to streaming. For example, stored procedures in the traditional PostgreSQL sense are not a primary feature.
• Streaming-Specific SQL Extensions: RisingWave extends SQL with constructs necessary for stream processing (e.g., defining sources with connector properties, time window functions, materialized view semantics for continuous updates).
• DDL/DML Differences: While querying (SELECT) is highly compatible, Data Definition Language (DDL) and Data Manipulation Language (DML) for managing tables and views in RisingWave are adapted for its streaming nature (e.g., CREATE MATERIALIZED VIEW defines a continuously updated view, not a one-time snapshot). Direct UPDATE or DELETE on base tables or sources is typically not how one interacts with streaming data; changes flow from sources.

Despite these differences, the high degree of PostgreSQL compatibility is a significant advantage of RisingWave, making it more accessible and easier to integrate into existing data stacks.

Related Glossary Terms

• Streaming SQL
• Source
• Sink
• Materialized View
• Connector