Unique PostgreSQL-compatible Distributed SQL database
Franck Pachot
Posted on September 2, 2024
While many other distributed SQL databases claim PostgreSQL compatibility by using the same wire protocol and similar syntax, YugabyteDB stands out by actually using the same code and libraries as PostgreSQL.
To ensure consistent behavior, as in PostgreSQL, it's necessary to use PostgreSQL rather than re-implement its features from scratch. For example, different programming languages may have subtle variations in interpreting complex evaluations, like regular expressions, and differences in handling arithmetic operations on data types. While none of these behaviors are inherently right or wrong, if your application is designed for PostgreSQL, you'd prefer to have the same behavior on a PostgreSQL-compatible database to avoid wrong results. The most reliable way to achieve this is by using the same code.
Here is a simple example. PostgreSQL response to select 42 * (1/2) may be surprising, but it is zero because 1/2 is converted to an int:
postgres=> select 42 * (1/2);
?column?
----------
0
(1 row)
YugabyteDB returns the same result because it has the same C code. Many other databases return 21 as a float. A PostgreSQL-compatible database must return the same as PostgreSQL, or the application developed, tested, and operating for years on PostgreSQL may corrupt data in corner cases.
In terms of features, just implementing basic functionality and listing them as 'supported' is not enough. Applications built on PostgreSQL use all features, including their various options. YugabyteDB is based on PostgreSQL 11, with some merges from higher versions. PostgreSQL 15 features will soon be available, and the future versions will include newer PostgreSQL evolutions. The upgrade of YugabyteDB is rolling, with zero downtime, and this will apply to the versions that raise the PostgreSQL compatibility. The features do not only include SQL commands but also the PostgreSQL catalog views because many tools, such as ORMs, query them.
YugabyteDB uses the PostgreSQL catalog. Like with PostgreSQL, you can see the queries on catalog that are run by psql when running it with -E or \set ECHO_HIDDEN on:
yugabyte=# \set ECHO_HIDDEN on
yugabyte=# \d demo
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(demo)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, c.relispartition, '', c.reltablespace, CASE WH
EN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '17299';
**************************
...
Other Distributed SQL are not PostgreSQL-compatible
CockroachDB, which is wire-compatible with PostgreSQL, accepts PostgreSQL clients like psql but doesn't show the same behavior.
Queries may return different results than PostgreSQL:
cockroachdb=> select version();
version
------------------------------------------------------------------------------------------------------------------------
CockroachDB CCL v24.1.2-2-g5a68aaa15d7 (x86_64-pc-linux-gnu, built 2024/07/15 13:56:27, go1.22.3 X:nocoverageredesign)
(1 row)
cockroachdb=> select 42 * (1/2);
?column?
-------------------------
21.00000000000000000000
(1 row)
Running PgBench server-side data generation fails:
cockroachdb=> \! PGOPTIONS='-c client_min_messages=error' pgbench -iIdtpfG
dropping old tables...
creating tables...
creating primary keys...
creating foreign keys...
generating data (server-side)...
pgbench: error: query failed: ERROR: unsupported binary operator: <decimal> + <int> (returning <int>)
pgbench: detail: Query was: insert into pgbench_tellers(tid,bid,tbalance) select tid, (tid - 1) / 10 + 1, 0 from generate_series(1, 10) as tid
cockroachdb=>
Running a simple \d command in psql fails:
psql (16.2, server 13.0.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off)
You are now connected to database "cockroachdb" as user "admin".
cockroachdb=> select version();
version
------------------------------------------------------------------------------------------------------------------------
CockroachDB CCL v24.1.2-2-g5a68aaa15d7 (x86_64-pc-linux-gnu, built 2024/07/15 13:56:27, go1.22.3 X:nocoverageredesign)
(1 row)
cockroachdb=> \d pgbench_accounts
ERROR: at or near ".": syntax error
DETAIL: source SQL:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(pgbench_accounts)$' COLLATE pg_catalog.default
Running PostgreSQL regular expressions fails:
cockroachdb=> select version();
version
------------------------------------------------------------------------------------------------------------------------
CockroachDB CCL v24.1.2-2-g5a68aaa15d7 (x86_64-pc-linux-gnu, built 2024/07/15 13:56:27, go1.22.3 X:nocoverageredesign)
(1 row)
cockroachdb=> select 'P@ssw0rd123' ~ '^(?=.*\d)(?=.*[A-Za-z]).{8,}$';
ERROR: invalid regular expression: error parsing regexp: invalid or unsupported Perl syntax: `(?=`
YugabyteDB is the only Distributed SQL database that uses the PostgreSQL code to provide the same behavior as PostgreSQL.
yugabyte=# select 42 * (1/2);
?column?
----------
0
(1 row)
yugabyte=# \! PGOPTIONS='-c client_min_messages=error' pgbench -iIdtpfG
dropping old tables...
creating tables...
creating primary keys...
creating foreign keys...
generating data (server-side)...
done in 6.93 s (drop tables 0.68 s, create tables 1.07 s, primary keys 1.53 s, foreign keys 1.02 s, server-side generate 2.63 s).
yugabyte=# \d pgbench_accounts
Table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, lsm (aid HASH)
Foreign-key constraints:
"pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
Referenced by:
TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
yugabyte=# select 'P@ssw0rd123' ~ '^(?=.*\d)(?=.*[A-Za-z]).{8,}$';
?column?
----------
t
(1 row)
PostgreSQL compatibility | YugabyteDB Docs
Summary of YugabyteDB's PostgreSQL compatibility
docs.yugabyte.com
Posted on September 2, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
November 25, 2024
