PostgreSQL and DBA.nl

More and more companies are using the open-source PostgreSQL database. Despite the fact that PostgreSQL is not a commercial product (and therefore there are no official sales figures), this is apparent from trend analyzes by market analysts. We as DBA.nl also notice interest from our customers and now offer PostgreSQL services in addition to Oracle and SQL Server. We expect that more and more application suppliers will also supply their software for this platform.

For those unfamiliar with it: PostgreSQL is an open-source database system that rivals Oracle Database and Microsoft SQL Server in functionality and reliability. The usual features of a serious database product are all present. Some examples:

SQL:2011 compliant : The SQL standard is followed

Transactions are well protected: Multiversion Concurrency Control, support for “serializable” transactions. The method is similar to Oracle and to newer versions of SQL Server with “row versioning” enabled.

Integration : Foreign Data Wrappers for querying external sources, interfaces for many programming languages.

Scalable : unlimited database size, unlimited rows per table, 32TB maximum size per table (if supported by the filesystem).

Expandable : The functionality of the database can be further expanded by means of extensions. For example, there are extensions for Full-Text search, Geographical data and performance management.

High availability : (synchronous) replication to one or more “standby” databases. Integrable with cluster software for automated High Availability solutions.

Backup and recovery : support for (incremental) on-line backups, data imports/exports, log archiving, Point-in-Time recovery, database cloning.

Security : PostgreSQL implements a flexible framework where database access can be monitored through various authentication mechanisms such as Kerberos, Active Directory, Radius and PAM. The data itself can be protected down to column level via roles. SELinux security labels can also be used on Linux.

Data consistency protection : Write Ahead Logging for (crash) recovery, data checksums.

Programmable PL /pgSQL procedural language in the database. (similar to Oracle’s PL/SQL and T-SQL from Microsoft

Integration : Foreign Data Wrappers for querying external sources, interfaces for many programming languages.

Scalable : unlimited database size, unlimited rows per table, 32TB maximum size per table (if supported by the filesystem).

Expandable : The functionality of the database can be further expanded by means of extensions. For example, there are extensions for Full-Text search, Geographical data and performance management.

A piece of history

PostgreSQL’s roots lie in the academic world of Berkeley University in California. The database was subsequently developed further outside this university as an open-source project with an important focus on reliability, functionality and the highest possible quality of the source code. The focus on software quality and reliability led to the impression that user-friendliness and database speed lagged behind some other databases, but the latter has certainly not been the case for 10 years. A lot of attention has been paid to optimization and in practice a properly configured PostgreSQL database is an excellent performing system that is easy to maintain. It should be noted, however, that the “tooling” around monitoring and management of PostgreSQL is not yet at the level that Oracle and Microsoft offer; this may make management a bit more “labour-intensive” or at least require some extra attention and configuration.

PostgreSQL has been developed as an open-source project since 1996 and is now ready for the 10th major release (the current version is 9.6 from September 2016). The development model is similar to that of the well-known Linux kernel; a large group of developers are working on the software, and a steering group is responsible for reviewing and selecting the code that will eventually be included in a database release. Historically, PostgreSQL was developed for Unix systems and now Linux is the most common platform on which PostgreSQL is developed, tested and used. Linux is therefore in principle the recommended platform for production use. But also for Microsoft Windows, every PostgreSQL release is released simultaneously with that for Linux and with the same functionality. Even if you are a “Windows-only shop”, there is no reason to avoid PostgreSQL.

Support

When using the free downloadable releases there are no costs for licenses or support. You have the freedom to do what you want with it, on as many systems as you want, for as long as you want. In that case, however, in case of problems, you will have to fall back on support from the “developer community” (read: submit bug reports if necessary, follow mailing lists and ask questions on stackoverflow and similar sites). This does not require a support contract with PosgtgreSQL developers, but possibly with a party that will sort out problems for you and coordinate a solution if necessary. The PostgreSQL project has a published release support policy and will actively fix any software bugs in a release for 5 years. For example, currently release 9.2 from 2012 is supported until September 2017. It’s comforting to know that there’s no strict need to upgrade to a new version every year if you don’t want to; applying (security) patches to “older” versions is possible for years.

As is common in the Linux world (Oracle, Red Hat, Canonical), commercial versions of PostgreSQL are also available. The heavyweight here is EnterpriseDB Corporation, which provides software support and also offers management tools. A commercially supported version of the database offers more certainty to customers who would like to know where they stand and wish to make agreements about this. As is common in the Linux world, employees of companies like EnterpriseDB are also involved as programmers in the open-source project so that the open-source releases take advantage of any problems that are solved by “commercialists”. The fixes are therefore not reserved for commercial products (optional add-on features for the database can be).

What does DBA.nl offer?

For a database specialist like DBA.nl, PostgreSQL is a natural match. Our many years of experience in database management can be applied well to PostgreSQL, in which many concepts are familiar territory.

As with Oracle and SQL Server, we can assist you with installation, configuration or questions with daily use. In addition, it is possible to have periodic maintenance and health checks carried out by us and we are happy to discuss any resulting points for improvement with you.

If you have any questions or if you are interested in our service for PostgreSQL, please feel free to contact us.