Compatibility with PostgreSQL

Tengri is an analytical DBMS with a Postgres-like dialect of SQL. Tengri is based on the protocols and grammar of PostgreSQL, but uses its own implementation. For BI and ETL tools, Tengri looks as if it were PostgreSQL.

Key functionality of Tengri as an analytical DBMS includes:

  • Basic syntax elements of SQL dialect PostgreSQL:
    SELECT, INSERT, UPDATE, DELETE, JOIN, WITH, UNION

  • Window functions

  • Working with JSON via SQL

  • Principles ACID

  • Transactions

  • Transactional data integrity

  • Data isolation at Repeatable read level

SQL and client protocol

The SQL dialect and client protocol Tengri inherits a subset of the dialect and client protocol PostgreSQL, augmenting them with new constructs. This makes it possible to use with Tengri the client libraries from PostgreSQL and familiar approaches to writing SQL for those who have worked with Postgres before.

Due to this we provide such popular clients as DBeaver and Superset, sqlalchemy and psycopg libraries, connection to Tengri from Excel and so on. For client applications, Tengri looks like Postgres. However, we do not test all Postgres clients for compatibility with Tengri. And we do not aim to fully cover all the features of PostgreSQL. Below is a table with the main supported and unsupported components.

Support Do not support

client protocol

  • simple query

  • extended query (parse, bind, execute)

  • pipelined execution

connection settings

A minimum unchanging set of settings is sent to the client that is sufficient for most clients to function properly:

  • client_encoding=UTF8.

  • server_encoding=UTF8

  • DateStyle=ISO

  • TimeZone=UTC

  • transaction_isolation

  • search_path

All settings received from the client when the connection is opened are ignored

sql dialect

  • ANSI SQL 99 (excluding TEMP TABLES, USER TYPES, RECURSIVE CTE)

  • Window functions and operations with JSON

  • TRIGGERS

  • INDEXES

  • PLSQL

  • RETURNING

  • FOR UPDATE

  • COPY

  • CURSOR

schema tables

System tables are implemented in terms of schema names, tables, types and attributes and part of additional fields:

  • pg_class

  • pg_namespace

  • pg_attribute

  • pg_type

This is sufficient for most clients.

The other pg_catalog tables are locked and always return an empty result

Supported applications

This level of compatibility is sufficient to run supported client applications (DBeaver, Superset) or a solution you are developing based on libpq, psqlodbc or pgJDBC libraries. However, this may not be enough to run the application you need, which uses these libraries but makes queries with unsupported language constructs or to unsupported system tables.

For special client applications that are tied to Postgres specifics (DDL, indexes, etc.), we write our own implementation specific to Tengri. For example, we have a plugin for the popular dbt data conversion tool.

Support Comment

psql

Native for Postgres

libpq

DBeaver

Native for Postgres

JDBC

Superset

Native for Postgres

sqlalchemy + psycopg

Power Query (Excel)

Native for Postgres

odbc

dbt

Tengri plugin

Own implementation

Transactions

In Tengri all queries are executed in autocommit mode regardless of connection and session settings. Tengri guarantees transactional data integrity and atomicity of DML operations. The transaction isolation modes of PostgreSQL in autocommit mode degenerate into one and cannot be changed by SQL.

For cases where autocommit mode is not sufficient and multi-step transactions are needed, they are implemented by the standard PostgreSQL-syntax BEGIN/START TRANSATION, COMMIT and ROLLBACK, supplementing the standard Iceberg mechanics with end-to-end exclusive locking of affected objects. (Currently in testing phase.)

Compatibility testing

Testing is vital to the development of a system as complex as a DBMS. We place great emphasis on thorough and regular compatibility testing Tengri:

  • We maintain a set of unit tests and run them on every change. They cover separately the language constructs, queries from the publicly available test cases TPC-H and TPC-DS and sets of actual queries for supported client applications, which we update regularly.

  • We use SQLsmith to generate random queries. This allows us to ensure that the optimiser is correct and compatible with PostgreSQL in terms of SELECT queries.

  • We maintain a set of tests with the data to make sure that the query results are correct in general.