Distributed SQL features
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.
Tip
A large portion of the documentation and examples written for PostgreSQL would work against YSQL.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 |
| Savepoints | Savepoints in YSQL |
| Extensions | Pre-bundled extensions for YugabyteDB |
| Indexes | Indexes to enhance database performance |
| GIN indexes | Generalized inverted indexes in YSQL |
| Constraints | Define constraints in YSQL |
| Collations | Collations in YSQL |
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 |