Testing PostgreSQL Compatibility in Yugabyte DB 2.0
Let's test PostgreSQL compatibility in Yugabyte DB 2.0.
Join the DZone community and get the full member experience.Join For Free
The team at Yugabyte and members of the community were excited to announce the general availability of Yugbyte 2.0 this week. One of the flagship features of this 100% open source release was the production readiness of the PostgreSQL compatible YugaByte SQL API (YSQL).
You might also like: The Northwind PostgreSQL Sample Database Running on a Distributed SQL Database
What’s the TL;DR?
In a nutshell, Yugabyte 2.0 supports:
- Distributed transactions
- Serializable and snapshot isolation
- All simple and complex data types
- Foreign keys
- Stored procedures
- Most built-in functions including window functions and various expressions
- All manner of simple and complex queries including JOINs, UPSERTs, etc
- Native JSONB support
- Auto-increment ids
- Indexes and Views
- Authorization & access control
- Plus various extensions, with more on the way!
Why Does PostgreSQL Compatibility Matter With Distributed SQL?
Almost every large organization these days has (or is developing a strategy) on how to use less (not more) of monolithic SQL databases like Oracle and SQL Server for new applications. A lot of the motivation to look at distributed SQL alternatives revolves around cost, the migration of workloads to the cloud, the adoption of cloud-native technologies and a move away from proprietary licenses to 100% open source databases.
At Yugabyte, we decided early on that instead of creating a brand new flavor of SQL, we’d aim for PostgreSQL syntax and wire-protocol compatibility instead. Why? As firm believers in open source and open APIs, building a proprietary SQL API was not even an option for us. We selected PostgreSQL because we were truly impressed by the openness, maturity, and feature completeness of the query layer. These attributes have created a highly passionate and thriving community of users.
Ok, let’s dive into the compatibility details!
In this category, we have 30+ different types of “basic” SQL queries that include both simple and complex SELECTs, GROUP BYs, CUBEs, ROLLUPs, UNIONs, and more. Everything we’ve tested so far shows Yugabyte DB supporting these types of commands without issue. For example, you can issue queries that contain GROUPING SETS and CUBES in Yugabyte DB:
SELECT contact_title, count(contact_title) AS number_of_people, city, country FROM suppliers GROUP BY GROUPING SETS (city, country, contact_title);
SELECT product_id, supplier_id, product_name, SUM(units_in_stock) FROM products GROUP BY product_id, CUBE(product_id, supplier_id);
In this category, Yugabyte supports all the various types of JOINs your use case might require, including:
- INNER JOIN
- OUTER JOIN
- FULL OUTER JOIN
- CROSS JOIN
- NATURAL JOIN
For example a SELECT with an INNER JOIN on three tables:
SELECT customers.company_name, employees.first_name, orders.order_id FROM orders INNER JOIN customers ON customers.customer_id = orders.customer_id INNER JOIN employees ON employees.employee_id = orders.employee_id;
Table Management, Constraints, and Datatypes
As you might expect, Yugabyte supports the creating and deleting of tables, plus the altering of columns and sequences. Because Yugabyte DB is a distributed SQL database, you can also safely assume that the specification of primary and foreign keys, check, unique and NOT NULL constraints all work out-of-the-box. For example:
CREATE TABLE customer_customer_demo ( customer_id bpchar NOT NULL, customer_type_id bpchar NOT NULL, PRIMARY KEY (customer_id, customer_type_id), FOREIGN KEY (customer_type_id) REFERENCES customer_demographics, FOREIGN KEY (customer_id) REFERENCES customers );
One thing to note, however, is that currently Yugabyte DB only permits primary keys to be specified in the CREATE TABLE statement. This limitation will be removed when this GitHub issue is resolved. (Foreign keys, however, can be added in a CREATE TABLE or ALTER TABLE.)
Yugabyte supports all simple and advanced datatypes available in PostgreSQL, including:
- Boolean types
- Character types
- Numeric types
- Temporal types (date, time, etc)
- UUID types
- User-Defined types like CREATE TYPE and CREATE DOMAIN
- Special types (macaddr, lseg, point, etc.)
Stored procedures allow developers to “bundle up” operations that would otherwise take several queries and round trips into a single function. Stored procedures also help minimize duplicate code, as developers can reuse existing stored procedures to perform the same actions. What’s the difference between a FUNCTION and a PROCEDURE?
A function cannot execute transactions. In other words, inside a function you cannot open a new transaction, even commit or rollback the current transaction. In PostgreSQL 11, stored procedures with transactional support were introduced to remedy this problem. Yugabyte DB supports the range of capabilities related to stored procedures including variables, constraints, loops, transactions, and functions. For example:
CREATE OR REPLACE PROCEDURE transfer(INT, INT, DEC) LANGUAGE plpgsql AS $$ BEGIN -- subtracting the amount from the sender's account UPDATE accounts SET balance = balance - $3 WHERE id = $1; -- adding the amount to the receiver's account UPDATE accounts SET balance = balance + $3 WHERE id = $2; COMMIT; END; $$;
Indexes, Views, and Triggers
Indexes are an effective way to find specific rows much faster than they could do without indexes. Yugabyte DB supports both basic and advanced capabilities in regards to indexes, including CREATE, DROP, multicolumn, and partial indexes. For example:
CREATE TABLE test(id int primary key, b int, c int, d bool); CREATE INDEX test_idx ON test(b,c) WHERE d = true; EXPLAIN SELECT * FROM test WHERE b = 0 AND d = true; Index Scan using test_idx on test (cost=0.00..5.04 rows=10 width=13) Index Cond: (b = 0) (2 rows)
Although a view doesn’t store any data (unlike a materialized view), they do provide yet another way to offer up data to a query. A view is based on one or more tables, which are known as the “base” tables. When a view is created, we are essentially creating a query, often complex, so that the data it comprises can be accessed more readily. For example:
CREATE VIEW customer_view AS SELECT cu.customer_id AS id, cu.fname || ' ' || cu.lname AS name, a.address, a.postal_code AS "zip code", a.phone, city.city, country.country, CASE WHEN cu.activebool THEN 'active' ELSE '' END AS notes, cu.store_id AS sid FROM customer cu INNER JOIN address a USING (address_id) INNER JOIN city USING (city_id) INNER JOIN country USING (country_id);
A trigger is a function invoked automatically whenever an event like an INSERT, UPDATE, or DELETE happens. For example:
CREATE TRIGGER PayRate_Change ON Employees AFTER UPDATE AS BEGIN INSERT INTO ChangeLog (EmpID, CHangedBy, DateChanged, OldRate, NewRate) SELECT i.empid, suser_sname(), getdate(), d.PsyRate, i.PsyRate FROM inserted i JOIN deleted d ON i.empid = d.empid AND d.PsyRate <> i.PsyRate; -- does not take `NULL`s into account END;
Window functions include things like DENSE_RANK, LAST_VALUE, and NTILE. Window functions are similar to aggregate functions, like AVG( ), in that they operate on a set of rows. However, unlike an aggregate function, it does not reduce the number of rows returned by the query. The term “Window” is used to describe the set of rows on which the function operates. In a nutshell, a window function returns values from the rows inside the specified window. For example:
SELECT product_name, group_name, price, LAST_VALUE (price) OVER ( PARTITION BY group_name ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS highest_price_per_group FROM products INNER JOIN product_groups USING (group_id);
Testing PostgreSQL Features Against Yugabyte DB
There are two easy ways to start exploring YSQL using readily available PostgreSQL tutorials.
- PGExercises Tutorial: 81 exercises including simple and complex queries, JOINs, subqueries, aggregations, working with timestamps, string operations and recursive queries.
- PostgreSQL Tutorial: 20 in-depth guides with hundreds of exercises that get you familiar with basic and advanced features of PostgreSQL that are applicable to Yugabyte DB.
Published at DZone with permission of Jimmy Guerrero. See the original article here.
Opinions expressed by DZone contributors are their own.