Migrate & Modernize

Database migration to PostgreSQL: an architect’s perspective

Posted by

onixadmin

Marcel Samuel, Principal Cloud Data Architect at Onix, shares his perspective on database migration to PostgreSQL and the key features and benefits of this technology.

As a lead architect in the data analytics domain, I’ve had the opportunity to work on multiple database migration projects, as well as data warehousing and consulting. My biggest challenge was dealing with massive volumes of data – ranging from “smaller” 5-minute snapshots to working on “annual” data analytics.

Starting from PostgreSQL 7, I moved towards Netezza – and then back to PostgreSQL (primarily due to the lack of user documentation). This was before the advent of technologies like YouTube and Stack Overflow. Initially, I found that Netezza – and AWS RedShift – essentially forked from PostgreSQL 7.

Read below on my insights into why I kept returning to PostgreSQL.

PostgreSQL is the future of databases

From my perspective, PostgreSQL isn’t another database system, but a robust database platform that supports over 30 commercial databases. I regard PostgreSQL as the future of databases for the following reasons.

Extensions
With its host of extensions, PostgreSQL users can easily add capabilities, depending on their business needs, including:
- PostGIS extension is used to handle spatial data in the SQL domain.
- hstore extension acts as a key-value pair for storage within PostgreSQL database.
- pg_prometheus extension integrates the Prometheus monitoring data storage with PostgreSQL.
- pg_partman extension simplifies the management of partitioned database tables.
- pgRouting extension adds geospatial routing capabilities to PostgreSQL.
- pg_stat_statements extension tracks the statistics on executing SQL statements.
- TimescaleDB extension is used to transform PostgreSQL into a time-series database.
- Citus extension enables the horizontal scaling of PostgreSQL for performing real-time analytics.
- PL/pgSQL extension enables advanced database logic to pass through a procedural language.
- uuid-ossp extension generates unique identifiers to identify every row.
Compatibility with SQL syntax
Most database professionals are aware of the challenge of incompatible SQL when migrating databases from Oracle to PostgreSQL. With the Oracle-PostgreSQL extension (orafce), they can ensure PostgreSQL’s compatibility with Oracle as a solution to this issue.
This extension enables a seamless transition without rewriting any code or recording any drop in database performance. This, in turn, saves organizations both time and resources. Here are some Oracle compatibility functions in PostgreSQL (available with the orafce extension):
- add_months: Adds the specified number of months to the date.
  Example: SELECT add_months(‘2005-05-31’, );
  Output: 2005-06-30
- last_day: Returns the last day of the month for a given date.
  Example: SELECT last_day(‘2005-05-24’);
  Output: 2005-05-31
- next_day: Finds the next weekday after a given date.
  Example: SELECT next_day(‘2005-05-24’, ‘monday’);
  Output: 2005-05-30
- dbtimezone: Returns the current time zone of the database.
  Example: SELECT dbtimezone();
- months_between: Calculates the number of months between two dates.
  Example: SELECT months_between(‘2005-05-31’, ‘2004-04-30’);
  Output: 13
- round (date): Rounds a date to the specified format.
  Example: SELECT round(current_date, ‘MM’);
- trunc (date): Truncates a date to the specified format.
  Example: SELECT trunc(current_date, ‘YEAR’);
- sysdate: Returns the current system date and time.
  Example: SELECT sysdate();
- instr: Returns the position of a substring within a string.
  Example: SELECT instr(‘PostgreSQL’, ‘SQL’);
  Output: 8
- substr: Extracts a substring from a string starting at a specified position.
  Example: SELECT substr(‘PostgreSQL’, 1, 5);
  Output: Postg

With these functions (and more!), organizations can ensure PostgreSQL-Oracle compatibility when performing database migration from Oracle.

In summary, with built-in extensions like orafce, PostgreSQL provides a comprehensive ecosystem and platform for effective data management. My verdict: PostgreSQL is my backbone for database migration and management.

Learn more about Onix’s data & analytics practice and how our experts are approaching projects with these powerful tools. Ready to get started? Contact us today!