Oracle vs PostgreSQL: First Glance – Testing YugabyteDB’s Compatibility
Roland Takacs wrote an interesting blog post titled Oracle vs PostgreSQL: First Glance earlier this month. The genesis for his blog post was that he was in the middle of migrating his current Oracle tech stack to Python, parquet files, and PostgreSQL. As such, Roland thought it might be a good exercise to document the various Oracle features he was accustomed to and figure out what the equivalent functionality was in PostgreSQL. In his post, Roland walked us through 15 different Oracle features and their PostgreSQL equivalents.
Seeing that YugabyteDB is a PostgreSQL-compatible, distributed SQL database (in fact it reuses PostgreSQL’s native query layer), I thought it would be an interesting exercise to verify which of the “Oracle functional equivalents” in PostgreSQL that Roland highlighted would also work in YugabyteDB. YugabyteDB supports more SQL features than any other distributed SQL database, including CockroachDB. In this post I’ll walk you through each of the features from Roland’s post so we can see how YugabyteDB stacks up on the “PostgreSQL compatibility” vector.
Oracle ships with a table called
DUAL which can be thought of as a dummy table with a single record that can be used for selecting when you’re not actually interested in the data, but instead want the results of some system function in a
SELECT statement. For example:
SELECT 1*3 FROM dual;
DUAL table doesn’t exist in PostgreSQL, but you can achieve similar results by excluding the
YugabyteDB doesn’t have a
DUAL table either, but supports the same functionality as PostgreSQL.
'Concat with ' || NULL
To achieve the same Oracle functionality in PostgreSQL, we’ll need to use the
SELECT concat('Concat with ', NULL);
YugabyteDB can perform string concatenation just like PostgreSQL.
SELECT concat('Concat with ', NULL);
ROWNUM and ROWID
ROWNUM is a pseudocolumn which indicates the row number in a result set retrieved by a SQL query. Likewise,
ROWID is a pseudocolumn that returns the address of the row.
SELECT rowid, rownum, country FROM country WHERE rownum <= 5;
In PostgreSQL, you can execute something like this:
SELECT ctid AS rowid, row_number() OVER(ORDER BY country) AS rn, country FROM country LIMIT 5;
In YugabyteDB (as in PostgreSQL), the
ROW_NUMBER() function is a window function that assigns a sequential integer to each row in a result set. We can perform a similar query on the
us_states table in the Northwind sample database and get the same results.
SELECT row_number() OVER(ORDER BY state_name) AS rn, state_name FROM us_states LIMIT 5;
YugabyteDB currently doesn’t expose a PostgreSQL system column like
ctid, so for now, Oracle’s
ROWID functionality is not supported.
IF EXISTS for DDL Operations
IF EXISTS statement in Oracle you can check whether or not a database object exists before running a DDL operation on it. In PostgreSQL this can be achieved with statements like the ones shown below:
CREATE TABLE IF NOT EXISTS table_name (...); DROP TABLE IF EXISTS table_name; ALTER TABLE IF EXISTS table_name RENAME TO new_name; ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;
Let’s put these
IF EXISTS PostgreSQL statements into practice against YugabyteDB.
CREATE TABLE IF NOT EXISTS account( user_id serial PRIMARY KEY, username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (355) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP );
Table creation succeeds.
ALTER TABLE account DROP COLUMN IF EXISTS email;
The email column in the
account table is successfully dropped.
ALTER TABLE IF EXISTS account RENAME TO customers;
accounts table is successfully renamed to
DROP TABLE IF EXISTS customers;
The table is successfully dropped.
Outer Join Using (+)
SELECT * FROM countries c, locations l WHERE c.country_id = l.country_id (+);
In PostgreSQL you can’t use (+) inside a query to force an outer join. As Roland points out, instead you’ll need to do something like this:
SELECT * FROM countries c LEFT OUTER JOIN locations l ON c.country_id = l.country_id;
We can perform a similar outer join against the Northwind sample database. Doing an outer join will get us all customers and orders for customers that have placed orders.
SELECT * FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;
We can also add a
IS NULL to the query to only return those customers who have not placed orders.
SELECT * FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.customer_id IS NULL;
This query returns the two customers who have not placed orders.
UPDATE Using Another Table
In Oracle you can use a subquery to update values in a table depending on values in another table.
UPDATE countries SET country_name = upper(country_name) WHERE country_id IN (SELECT country_id FROM locations);
In PostgreSQL you can achieve similar results with the following statement:
UPDATE city SET city = upper(city) FROM country WHERE city.country_id = country.country_id;
YugabyteDB currently does not support a FROM clause in an UPDATE. You can track the resolution of this issue on GitHub, which is slated to be resolved in the upcoming 2.2 release.
DELETE Using Another Table
Similar to the previous example, sometimes you want to
DELETE values that match another table.
DELETE FROM locations WHERE country_id in (SELECT country_id FROM countries);
DELETE FROM city USING country WHERE city.country_id = country.country_id;
YugabyteDB currently does not support a
USING clause in a
DELETE. You can track the resolution of this issue on GitHub, which is slated to be resolved in the upcoming 2.2 release.
In Oracle you can use a
MERGE statement to insert or update data depending on its existence. Put another way, if the data exists, the columns are updated, if not, a new record is inserted.
MERGE INTO customers a USING suppliers b ON (a.name = b.name) WHEN MATCHED THEN UPDATE SET a.email = b.email WHEN NOT MATCHED THEN INSERT (name, email) VALUES (b.name, b.email);
INSERT INTO customers (name, email) VALUES ('Somebody', '[email protected]') ON CONFLICT (name) DO UPDATE SET email = customers.email;
Out of the box, YugabyteDB supports all the upsert functionality you’ll find in PostgreSQL.
Below is an example of “on conflict do nothing”. In this case, we don’t need to specify the conflict target.
INSERT INTO sample(id, c1, c2) VALUES (3, 'horse' , 'pigeon'), (4, 'cow' , 'robin') ON CONFLICT DO NOTHING;
In this next example we have a real “upsert”. In this case, we need to specify the conflict target. Notice the use of the EXCLUDED keyword to specify the conflicting rows in the to-be-upserted relation.
INSERT INTO sample(id, c1, c2) VALUES (3, 'horse' , 'pigeon'), (5, 'tiger' , 'starling') ON CONFLICT (id) DO UPDATE SET (c1, c2) = (EXCLUDED.c1, EXCLUDED.c2);
We can also make the “update” happen only for a specified subset of the excluded rows. We illustrate this by attempting to insert two conflicting rows (with id = 4 and id = 5) and one non-conflicting row (with id = 6). And we specify that the existing row with c1 = ‘tiger’ should not be updated with “WHERE sample.c1 <> ‘tiger'”.
INSERT INTO sample(id, c1, c2) VALUES (4, 'deer' , 'vulture'), (5, 'lion' , 'hawk'), (6, 'cheetah' , 'finch') ON CONFLICT (id) DO UPDATE SET (c1, c2) = (EXCLUDED.c1, EXCLUDED.c2) WHERE sample.c1 <> 'tiger';
You can check out more examples of upserts in the YugabyteDB documentation.
In PostgreSQL, tables can inherit the data and structure from an existing table. Oracle table inheritance doesn’t exist in this form.
INHERITS command is currently not supported in YugabyteDB, but you can track the progress of the issue on GitHub.
In Oracle you can create a table without additional objects like indexes or constraints. And depending on your
SELECT condition, your table may or may not populate with data.
CREATE TABLE country_2 AS SELECT * FROM country;
To create the table without data, you can execute:
CREATE TABLE country_2 AS SELECT * FROM country WHERE 1=0;
To copy both table structure and data:
CREATE TABLE new_table AS TABLE existing_table;
The PostgreSQL example above works in YugabyteDB as expected, for example:
CREATE TABLE employees2 AS TABLE employees; SELECT * FROM employees2 limit 5;
To copy only the table structure you can execute the following:
CREATE TABLE new_table AS TABLE existing_table WITH NO DATA;
This works in YugabyteDB as expected, for example:
CREATE TABLE employees3 AS TABLE employees WITH NO DATA; SELECT * FROM employees3 limit 5;
As Roland points out, unlike Oracle,
TRUNCATE operations are transaction-safe in PostgreSQL. This means that if you place a
TRUNCATE within the transaction statements such as a
ROLLBACK, the truncation operation will be rolled back safely.
TRUNCATE is not transaction-safe in YugabyteDB. However, the feature is actively being worked on and can be tracked on GitHub here.
CONNECT BY-START WITH-PRIOR clauses from Oracle don’t exist in PostgreSQL. At least not by default. Roland recommends installing the
tablefunc extension to get similar capabilities. He also points out that in PostgreSQL you can use recursive Common Table Expressions (CTE) to achieve the same result.
In YugabyteDB you can install the
tablefunc extension by issuing:
CREATE EXTENSION tablefunc;
YugabyteDB also supports CTEs (documentation is in progress), if you want to go that route.
Oracle and PostgreSQL allow for table partitioning in similar ways. Note that partitioned tables in these single-node databases enable a single table to be broken into multiple child tables so that these child tables can be stored on separate disks (tablespaces). Serving of the data however is still performed by a single node.
Following is an example of partition by list.
CREATE TABLE sales ( salesman_id INTEGER PRIMARY KEY, salesman_name VARCHAR2(30), sales_region VARCHAR2(30), sales_date DATE, sales_amount INTEGER ) PARTITION BY LIST (sales_region) ( PARTITION p_asia VALUES ('INDIA','CHINA'), PARTITION p_euro VALUES ('FRANCE','UK'), PARTITION p_america VALUES ('USA','CANADA'), PARTITION p_rest VALUES (DEFAULT) );
CREATE TABLE sales ( salesman_id INTEGER, salesman_name VARCHAR(30), sales_region VARCHAR(30), sales_date DATE, sales_amount INTEGER ) PARTITION BY LIST (sales_region); CREATE TABLE sales_p_asia PARTITION OF sales FOR VALUES IN ('INDIA','CHINA'); CREATE TABLE sales_p_euro PARTITION OF sales FOR VALUES IN ('FRANCE','UK'); CREATE TABLE sales_p_america PARTITION OF sales FOR VALUES IN ('USA','CANADA'); CREATE TABLE sales_p_rest PARTITION OF sales DEFAULT;
Because YugabyteDB is a distributed SQL system vs a monolithic one like Oracle and PostgreSQL, partitioning means distributing data across multiple nodes of a cluster and is for the most part handled automatically by the database to create optimum availability and performance characteristics for the data. At a high-level, YugabyteDB’s design in this respect is inspired by Google Spanner.
In YugabyteDB, a SQL table is decomposed into multiple sets of rows according to a specific sharding strategy. Each of these sets of rows is called a shard. These shards are distributed across multiple server nodes (containers, VMs, bare metal) in a shared-nothing architecture. This ensures that the shards do not get bottlenecked by the compute, storage, and networking resources available at a single node. High availability is achieved by replicating each shard across multiple nodes. However, the application interacts with a SQL table as one logical unit and remains agnostic to the physical placement of the shards. YugabyteDB supports both HASH and RANGE sharding (partitioning.) For more information, check out the documentation on the subject.
Note that the PostgreSQL feature of partitioning a single table’s data into multiple disks is also automatic in YugabyteDB since you can attach as many disks you need to YugabyteDB nodes and data will remain auto balanced across them. Therefore, YugabyteDB intends reuse and extend PostgreSQL’s table partitioning SQL syntax for row-level geo-partitioning which allows specific rows to be pinned to specific regions of a geo-distributed/multi-region cluster. The cluster can now serve transactional reads and writes for users local to a given region without incurring any cross-region latency. You can learn more about row-level geo-partitioning on GitHub and track the feature here.
In Oracle you can organize your code into blocks, procedures, or functions. For example:
DECLARE ... variables BEGIN ... code to be executed EXCEPTION ... exception handling END;
The functional equivalent in PostgreSQL would look like this:
DO $ DECLARE ... variables BEGIN ... code to be executed EXCEPTION .. exception handling END $;
YugabyteDB supports PostgreSQL procedures and functions out of the box and should as expected. 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; $;
As Roland points out, there is no functional equivalent in PostgreSQL for Oracle Packages. You can use naming conventions to try and organize your procedures into something that might appear like an Oracle package, but in reality, the functionality that an Oracle DBA would expect just won’t be there. YugabyteDB is no different than PostgreSQL in this respect.
As you can see from the examples, YugabyteDB is highly compatible with PostgreSQL and, in turn, Oracle. YugabyteDB already supports 10 of 15 features highlighted in Roland’s post with most of the missing features becoming available in the next release. So, if your organization is executing on a strategy to use less, not more Oracle in the future and are looking at PostgreSQL as a database technology to help you get there, you should be considering YugabyteDB. With YugabyteDB you get automatic geo-distribution, continuous availability, and open source licensing, all without sacrificing on SQL feature depth, performance, or ACID compliance.