The Distributed SQL Blog

Thoughts on distributed databases, open source and cloud native

PostgreSQL Compatibility in YugabyteDB 2.0

VP Developer Relations

The team at Yugabyte and members of the community were excited to announce the general availability of Yugbyte 2.0 this week. One of the flagship features of the release was the production readiness of the PostgreSQL compatible, Yugabyte SQL API (YSQL). In other blogs we covered Jepsen testing results, new performance benchmarks and ecosystem integrations including the GraphQL projects Hasura and Prisma. In this post we’d like to take the opportunity to dive a little deeper into the PostgreSQL compatibility features to demonstrate what’s supported and what’s around the corner.

What’s the TL;DR?

In a nutshell, YugabyteDB 2.0 supports:

  • Distributed transactions
  • Serializable and snapshot isolation
  • All simple and complex data types
  • Foreign keys
  • Stored procedures
  • Triggers
  • Most built-in functions including window functions and various expressions
  • Simple and complex queries including JOINs, UPSERTs, etc
  • Native JSONB support
  • Auto-increment ids
  • Indexes and Views
  • Sequences
  • Authorization & access control
  • Plus various extensions, with more on the way!

Why does PostgreSQL compatibility matter with Distributed SQL?

Almost every large organization these days has (or is developing a strategy) on how to use less (not more) of monolithic SQL databases like Oracle and SQL Server for new applications. A lot of the motivation to look at distributed SQL alternatives revolves around cost, the migration of workloads to the cloud, the adoption of cloud-native technologies and a move away from proprietary licenses to 100% open source databases.

At Yugabyte we decided early on that instead of creating a brand new flavor of SQL, that we’d aim for PostgreSQL syntax and wire-protocol compatibility instead. Why? As firm believers in open source and open APIs, building a proprietary SQL API was not even an option for us. We selected PostgreSQL because we were truly impressed by the openness, maturity and feature completeness of the query layer. These attributes have created a highly passionate and thriving community of users. Industry analysts are also observing these same trends that have led to a big resurgence of PostgreSQL, especially over the last 5 years. Matt Asay’s post “Why Oracle’s missteps have led to PostgreSQL’s ‘moment’ in the database market” analyzes the trends in a fair amount of detail.

Ok, let’s dive into the compatibility details!

SQL Fundamentals

In this category we have 30+ different types of “basic” SQL queries which include both simple and complex SELECTs, GROUP BYs, CUBEs, ROLLUPs, UNIONs and more. Everything we’ve tested so far shows YugabyteDB supporting these types of commands without issue. This is unlike CockroachDB which offers limited PostgreSQL compatibility compared to YugabyteDB. For example you can issue queries that contain GROUPING SETS and CUBES in YugabyteDB, which you can’t in CockroachDB:

and

JOINs

In this category YugabyteDB supports all the various types of JOINs your use case might require, including:

  • INNER JOIN
  • OUTER JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • NATURAL JOIN

For example a SELECT with an INNER JOIN on three tables:

Table Management, Constraints and Datatypes

As you might expect, YugabyteDB supports the creating and deleting of tables, plus the altering of columns and sequences. Because YugabyteDB is a distributed SQL database, you can also safely assume that the specification of primary and foreign keys, check, unique and NOT NULL constraints all work out-of-the-box. For example:

One thing to note however, is that currently YugabyteDB only permits primary keys to be specified in the CREATE TABLE statement. This limitation will be removed when this GitHub issue is resolved. (Foreign keys however, can be added in a CREATE TABLE or ALTER TABLE.)

YugabyteDB supports all simple and advanced datatypes available in PostgreSQL including:

  • Boolean types
  • Character types
  • Numeric types
  • Temporal types (date, time, etc)
  • UUID types
  • Array
  • JSON
  • Monetary
  • Binary
  • Enumerated
  • User-Defined types like CREATE TYPE and CREATE DOMAIN
  • Special types (macaddr, lseg, point, etc)

By comparison, CockroachDB doesn’t support User-Defined Types or the DOMAIN type.

Stored Procedures

Stored procedures allow developers to “bundle up” operations that would otherwise take several queries and round trips into a single function. Stored procedures also help minimize duplicate code, as developers can reuse existing stored procedures to perform the same actions. What’s the difference between a FUNCTION and a PROCEDURE? A function cannot execute transactions. In other words, inside a function you cannot open a new transaction, even commit or rollback the current transaction. In PostgreSQL 11, stored procedures with transactional support were introduced to remedy this problem. Unlike CockroachDB, YugabyteDB supports the range of capabilities related to stored procedures including variables, constraints, loops, transactions and functions. For example:

Indexes, Views and Triggers

Indexes are an effective way to find specific rows much faster than it could do without indexes. YugabyteDB supports both basic and advanced capabilities in regards to indexes, including CREATE, DROP, multicolumn and something CockroachDB doesn’t support, partial indexes. For example:

Although a view doesn’t store any data (unlike a materialized view), they do provide yet another way to offer up data to a query. A view is based on one or more tables, which are known as the “base” tables. When a view is created, we are essentially creating a query, often complex, so that the data it comprises can be accessed more readily. For example:

A trigger is a function invoked automatically whenever an event like an INSERT, UPDATE, or DELETE happens. For example:

Note that currently CockroachDB does not support triggers according to this open GitHub issue.

Window Functions

Window functions include things like DENSE_RANK, LAST_VALUE, and NTILE. Window functions are similar to aggregate functions, like AVG( ), in that they operate on a set of rows. However, unlike an aggregate function it does not reduce the number of rows returned by the query. The term “Window” is used to describe the set of rows on which the function operates. In a nutshell, a window function returns values from the rows inside the specified window. For example:

Testing PostgreSQL Features with YugabyteDB

There are two easy ways to start exploring YSQL using readily available PostgreSQL tutorials.

  • PGExercises tutorial: 81 exercises including simple and complex queries, JOINs, subqueries, aggregations, working with timestamps, string operations and recursive queries.
  • PostgreSQL Tutorial: 20 in-depth guides with hundreds of exercises that get you familiar with basic and advanced features of PostgreSQL that are applicable to YugabyteDB.

What’s next

  • Compare YugabyteDB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
  • Get started with YugabyteDB on macOS, Linux, Docker, and Kubernetes.
  • Contact us to learn more about licensing, pricing or to schedule a technical overview.

Related Posts

VP Developer Relations