Almost every IT system uses data. To be conveniently accessed from an application, it is stored in data repositories. The most popular types are file systems and databases. The type of repository is chosen at an early stage of system creation and strongly depends on the planned functionality of the system. For several decades, the most important were relational databases. The selection of the repository was practically reduced to the choice of a manufacturer of a particular relational database engine. The beginning of the 21st century brought growing popularity of the No-SQL approach – databases in which the data structure and consistency requirements have been loosened to increase efficiency and accessibility.
Although the choice has been complicated, one element remains unchanged. The type of database determines the operation of the entire IT system; both in functional aspects (i.e.data integrity, data access method) and performance (scalability, response speed, etc).
Why the PostgreSQL database?
When the PostgreSQL was chosen as the main base for the platform, XTRF presented arguments that were similar to those that would have been used today.
Immunity to over-deployment
Over-deployment is what some proprietary database vendors regard as their #1 licence compliance problem. With PostgreSQL, no-one can sue you for breaking licensing agreements, as there is no associated licensing cost for the software.
Better support than proprietary vendors
In addition to strong support offerings, PostgreSQL has a vibrant community of professionals and enthusiasts that your staff can draw upon and contribute to.
Significant savings on staffing costs
PostgreSQL software has been designed and created to have much lower maintenance and tuning requirements than the leading proprietary databases, yet still retains all of the features, stability, and performance.
Legendary reliability and stability
Unlike many proprietary databases, it is extremely common for companies to report that PostgreSQL has never, ever crashed for them in several years of high activity operation. Not even once. It just works. – with hundreds XTRF installations on PostgreSQL, we are witnesses to this stability.
PostgreSQL is available for almost every brand of Unix (34 platforms with the latest stable release), and Windows operating system.
Designed for high volume environments
PostgreSQL uses a multiple row data storage strategy called MVCC to make the platform extremely responsive in high volume environments. The leading proprietary database vendor uses this technology as well for the same reasons.
GUI database design and administration tools
There are many high-quality GUI Tools available for PostgreSQL from both open source developers and commercial providers. A list is available on our wiki as a community guide to PostgreSQL GUI Tools.
There are plenty of purely technical features that make PostgreSQL the first choice for XTRF, the most important include:
- Postgresql has functional indexes, partial indexes and json-content indexes for fast data retrieval independent of data size and growth
- Postgresql has first-class json support including modifications and querying, easing code modernisation with some NoSQL techniques
- Postgresql has a plethora of extensions like out-of-table auditing, trigrams and overloadable operators for specific use-cases
- Postgresql security subsystem is highly configurable and allows for restricted access to selected tables
- Postgresql has advanced stored procedure language – PL/PGSQL which allows some complex operations to be performed directly on the SQL server, closer to the actual data and therefore much faster.
Multiple DBs support
The first versions of the XTRF system were compatible with two types of databases: PostgreSQL and the HypersonicDB database. However, maintaining this compliance required duplication of work in the following areas:
- migration scripts for data when changing versions of the XTRF system,
- functional tests,
- performance tests,
- knowledge of the database from the tuning of performance parameters,
- maintaining knowledge in the helpdesk as well as installation and maintenance documentation for various databases (installation, configuration, backup policies),
- including in the support report information on the type of database used.
We quickly noticed that maintaining support for two databases is very expensive and does not benefit customers. Also, these databases differed in functionality – one type of operation could be easily performed in one database, and another in the other – as a result, the system was designed to use only the functionality common to both databases.
The above arguments meant that we based our further development on a single database – we chose PostgreSQL and we have been faithful to it for many years. In retrospect, with more than 100 installations scattered around the world and our Postgres clusters we maintain for our SaaS customers, we have to admit that it was a good choice. We are pleased that it is consistent with current global preferences in the field of relational database engines, i.e. stated at StackOverflow insights: Developer Survey Results 2018