YDB compatibility with PostgreSQL
Warning
At the moment, YDB's compatibility with PostgreSQL is under development, so not all PostgreSQL constructs and functions are supported yet. PostgreSQL compatibility is available for testing in the form of a Docker container, which can be deployed by following these instructions.
PostgreSQL compatibility is a mechanism for executing SQL queries in the PostgreSQL dialect on YDB infrastructure using the PostgreSQL wire protocol. This feature allows the use of familiar PostgreSQL tools such as psql and drivers (e.g., pq for Golang and psycopg2 for Python). Developers can write queries using the PostgreSQL syntax while benefiting from YDB's advantages such as horizontal scalability and fault tolerance.
YDB's compatibility with PostgreSQL simplifies the migration of applications that were previously operating within the PostgreSQL ecosystem. This feature allows for a smoother transition of database-driven applications to YDB. At present, a limited set of PostgreSQL 14 instructions and functions are supported. PostgreSQL compatibility enables switching from PostgreSQL to YDB without modifying the project code (provided that the SQL constructs used in the project are supported by YDB), by merely changing the connection parameters.
The operation of PostgreSQL compatibility can be described as follows:
- The application sends queries to YDB, where they are processed by a component known as pgwire. Pgwire implements the wire protocol of PostgreSQL and forwards commands to the query processor.
- The query processor translates the PostgreSQL queries into YQL AST.
- After the queries are processed, the results are compiled and sent back to the application that issued the query via the PostgreSQL wire protocol. During query processing, it can be parallelized and executed on any number of YDB nodes.
The functionality of PostgreSQL compatibility can be graphically represented as follows:
