{{announcement.body}}
{{announcement.title}}

Testing PostgreSQL Compatibility in Yugabyte DB 2.0

DZone 's Guide to

Testing PostgreSQL Compatibility in Yugabyte DB 2.0

Let's test PostgreSQL compatibility in Yugabyte DB 2.0.

· Database Zone ·
Free Resource

Testing PostgreSQL compatibility in Yugabyte DB

Testing PostgreSQL compatibility in Yugabyte DB

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
  • Triggers
  • 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
  • Sequences
  • 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!

SQL Fundamentals

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);

and

SELECT product_id, supplier_id, product_name, 
       SUM(units_in_stock)
FROM products
GROUP BY product_id, CUBE(product_id, supplier_id);

JOINs

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
  • Array
  • JSON
  • Monetary
  • Binary
  • Enumerated
  • User-Defined types like CREATE TYPE and CREATE DOMAIN
  • Special types (macaddr, lseg, point, etc.)

Stored Procedures

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

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.

Further Reading

Which Is the Best PostgreSQL GUI? 2019 Comparison

Spring Boot and PostgreSQL

Topics:
postgresql ,sql (structured query language) ,cloud native ,database

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}