Last but not least, we need to decide whether to rewrite or reuse the PostgreSQL query layer.
Our preliminary decision:
The scalability of the YugaByte database query layer is considered during design. By rewriting the API server in C++, two APIs (YCQL and YEDIS) have been built in this query layer framework, and it seems easier and more natural to override PostgreSQL API first.
Our final decision:
We walked along this road for about five months before we realized that it was not an ideal path. Compared with PostgreSQL, other APIs are much simpler compared with PostgreSQL. We then re-complete the work, return to the drawing board and re-start using the PostgreSQL query layer code. Although it was painful at first, it was a better strategy to look back.
This approach also has its own challenges. Our plan is to first move the PostgreSQL system table to DocDB (YugaByte DB storage tier), initially supporting some data types and some simple queries, and adding more data types and query support over time.
Unfortunately, the plan has not been completely resolved. To implement seemingly simple end user commands from psql, you actually need to support a large number of SQL functions. For example, \d is used to list all table commands and perform the following query internally:
SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner"FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind IN ('r','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid)ORDER BY 1,2;
To meet the preceding query requirements, the following functions are supported:
WHERE clauses with support for operators such as IN, not equals, regex matches, etc.
CASE clause
Joins, specifically a LEFT JOIN
ORDER BY clause
Builtins, such as pg_table_is_visible()
Obviously, this represents a variety of SQL functions, so we must make all these functions available before creating a single user table! We publish the distributed PostgreSQL - query layer on the Google Spanner architecture, highlighting the detailed working methods of the query layer.
Even for expert users, there is a choice between the many databases available on the market, which seems unstoppable at first. This is because selecting a database for a given type of application depends on the trade-offs that these databases make in their architecture. YugaByte DB combines a set of very useful architectural decisions in a novel way, creating a unique open source distributed SQL database.
