YSQL vs PostgreSQL

PostgreSQL features in YSQL.

The YSQ API of YugabyteDB reuses a fork of the query layer of PostgreSQL as its starting point, and runs on top of YugabyteDB’s distributed storage layer called DocDB. This architectural decision means that YSQL supports most PostgreSQL features (data types, queries, expressions, operators and functions, stored procedures, triggers, extensions, etc).

Tip

A large portion of the documentation and examples written for PostgreSQL would work against YugabyteDB.

Why is this true? The reuse of the upper half of PostgreSQL means that applications in effect interact with the PostgreSQL query layer. Thus, not only are most of the features supported, they also working exactly the same way as they would in PostgreSQL.

The figure below diagrammatically shows how the query layer of PostgreSQL is reused - specifically components that receive the query (postman), the query parser / rewriter / analyzer, as well as planning and executing the query. Some of these components have been modified to work efficiently as a distributed SQL database.

Reusing the PostgreSQL query layer in YSQL

PostgreSQL features in YSQL

This section walks through some of the features in YSQL. If you have worked with PostgreSQL, you should find most of these features and how they function very familiar.

Feature in YSQL	Description of feature
Basics	SQL shell with `ysqlsh`, users, databases, tables and schemas
Data types	String / numeric / temporal types, `SERIAL` pseudo type, `ENUM`, arrays, and composite types

The following topics are covered in separate sections:

What's extra in YSQL?

Since YugabyteDB is a distributed SQL database, there are some features that make more sense for YSQL and hence are not present in PostgreSQL. These are outlined below.

Feature in YSQL	Description of feature
Data distribution with `HASH`	YSQL supports a `HASH` sort order in addition to `ASC` and `DESC` for indexes
`TABLESPACES` for geographic placement	`TABLESPACES` can be used to pin data in tables and table partitions to different geographic locations.
`TABLEGROUPS` for colocating tables	`TABLEGROUPS` enables colocating multiple smaller tables into one tablet for better performance.