
YSQL vs PostgreSQL
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.
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. |