YSQL vs PostgreSQL

YugabyteDB's YSQL API 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 architecture allows YSQL to support most PostgreSQL features such as data types, queries, expressions, operators and functions, stored procedures, triggers, extensions, and so on, all of which are expected to work identically on both database systems.

The following diagram demonstrates how YugabyteDB reuses the PostgreSQL query layer of, specifically its components that receive the query (postman), the query parser, rewriter, analyzer, as well as components responsible for planning and executing the query. Some of these components have been modified to perform efficiently in a distributed SQL database.

PostgreSQL Features in YSQL

The following table lists the most important YSQL features which you would find familiar if you have worked with PostgreSQL.

YSQL Feature	Description
Basics	SQL shell with ysqlsh, users, databases, tables and schemas
Data Types	String, numeric, temporal types, SERIAL pseudo type, ENUM, arrays, composite types
DDL Statements	Data definition language
Document Data Types	JSON and JSONB
Data Manipulation	INSERT, UPDATE, DELETE, INSERT ... ON CONFLICT, and RETURNING clauses
Queries and Joins	Queries, joins, FROM, GROUP BY, HAVING clauses, common table expressions, recursive queries
Transactions	Transactions and distributed transactions in YugabyteDB
Expressions and Operators	Basic operators and boolean, numeric, date expressions
Cursors	Declaration of cursors in YSQL
Stored Procedures	Support for stored procedures
Triggers	Triggers (on data modification) and event triggers (on schema changes)
Table Partitions	List, range, and hash partitioning of tables
Tablespaces	Definition of tablespaces in YSQL
Views	Views and updatable views
Extensions	Pre-bundled extensions for YugabyteDB

See also:

• Distributed transactions

What's Extra in YSQL?

Since YugabyteDB is a distributed SQL database, YSQL has a number of features that are not present in PostgreSQL, as summarized in the following table.

YSQL Feature	Description
Data distribution with HASH	Enables the use of HASH sort order, in addition to ASC and DESC for indexes
TABLESPACES for geographic placement	Enables pinning of data in tables and table partitions to different geographic locations
TABLEGROUPS for colocating tables	Enables colocation of multiple smaller tables into one tablet for better performance