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.
TipA large portion of the documentation and examples written for PostgreSQL would work against YSQL.
The following diagram shows how YugabyteDB reuses the PostgreSQL query layer, specifically the 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.
SQL Features in YSQL
The following table lists the most important YSQL features which you would find familiar if you have worked with PostgreSQL.
|Schemas and Tables||SQL shell with
|Data Types||String, numeric, temporal types,
|DDL Statements||Data definition language|
|Queries and Joins||Queries, joins,
|Expressions and Operators||Basic operators and boolean, numeric, date expressions|
|Stored Procedures||Support for stored procedures|
|Triggers||Triggers (on data modification) and event triggers (on schema changes)|
|Extensions||Support for PostgreSQL extensions|
Advanced features in YSQL
The following table lists the advanced features in YSQL.
|Cursors||Declaration of cursors in YSQL|
|Table Partitioning||List, range, and hash partitioning of tables|
|Views||Views and updatable views|
|Savepoints||Savepoints in YSQL|
|Collations||Collations in YSQL|
Going beyond SQL
Because YugabyteDB is a distributed SQL database, YSQL has a number of features that are not present in PostgreSQL, as summarized in the following table.
|Data distribution with
||Enables the use of
||Enables pinning of data in tables and table partitions to different geographic locations|
||Enables more read IOPS with low latencies in YugabyteDB clusters|
||Enables colocation of multiple smaller tables into one tablet for better performance|