Why PostgreSQL

From OpenNMS
Jump to: navigation, search

Overview

We commonly get a question about why we use PostgreSQL or what the difficulty is to move to another database. While I do not know the original design reasons to use PostgreSQL, I do know the reasons we continue to use it, which are listed below. We are in the process of converting all the database access code to Hibernate, which will provide database independence, but there is still much work to be done on this effort. Please refer to the OpenNMS database schema for the layout.

Why?

  1. It's a mature, fast, enterprise-quality open-source database.
  2. It's what we've always used (i.e. inertia). There are a number of PostgreSQL-specific ties which make migration to another database a non-trivial process. Here is a (possibly incomplete) list of such ties:
    • "IPLIKE" comparison function: this is written in C and loaded in the database to provide LIKE-like functionality for IP addresses, e.g.: "192.168.*.*".
    • PL/PgSQL stored procedures for performing database-intensive operations involving availability calculations.
    • Installer database-related code is customized for PostgreSQL (although this is probably less of an issue now that we are using LiquiBase in 1.7+).
    • PostgreSQL-specific database triggers since 1.3. On a number of tables that have been around for awhile (ipInterface, snmpInterface, and ifServices), we have two different sets of primary keys: a legacy multi-column primary key (for example, nodeId and snmpIfIndex on the snmpInterface table) and a new, Hibernate-friendly single-column key (id on the snmpInterface table). We are converting the old code over to Hibernate, however it is a slow task, and the database triggers keep the pairs of primary keys in sync until we are done with migration and can eliminate the legacy multi-column primary keys.

PL/PgSQL stored procedure calls

  • src/web/src/org/opennms/web/category/CategoryModel.java
    • PreparedStatement stmt = conn.prepareStatement("select getManagePercentAvailNodeWindow(?, ?, ?) as avail");
  • src/web/src/org/opennms/web/category/CategoryModel.java
    • PreparedStatement stmt = conn.prepareStatement("select getManagePercentAvailIntfWindow(?, ?, ?, ?) as avail");
  • src/web/src/org/opennms/web/category/CategoryModel.java
    • PreparedStatement stmt = conn.prepareStatement("select getPercentAvailabilityInWindow(?, ?, ?, ?, ?) as avail from ifservices where ifservices.ipaddr = ipinterface.ipaddr and ifservices.nodeid = ipinterface.nodeid and ipinterface.ismanaged='M' and nodeid=? and ipaddr=? and serviceid=?");

Performance Enhancements

If you have the memory, making changes to the default postgresql.conf file can result in amazing performance improvements. See the Performance Tuning article for details.