PostgreSQL

Capability

Currently, PostgreSQL is the most feature complete open-source relational database mana­ge­ment system:

• Fully ACID compliant.

• Adheres to the ANSI-SQL 92 and ANSI-SQL 99 standards (probably the most compatible of all existing SQL im­ple­men­ta­tions).

• Supports MVCC with complete transaction isolation.

• Allows for unli­mi­ted database size and unli­mi­ted number of records in a table.

• Fully supports foreign keys, joins, views, triggers and stored procedures in several pro­gramm­ing languages.

• Supports subqueries, even within FROM statements.

• Indexing algorithms include B-trees, R-trees, hash functions and GIST.

• Supports point-in-time recovery, schemes (tablespaces), asyn­c­hron­ous replication, nested transactions and archi­ving with no down­time (hot back­ups).

• Supports access to the system catalogue through infor­ma­tion schema.

Stability

PostgreSQL is known for exceptional reliability, resulting from high-quality code originally written for UNIX systems. The server has been actively used in production environments for over 20 years, with many databases serving thousands of users and terabytes of data.

KoMnA has been using PostgreSQL since 2004 to host data for a great majority of our products and solutions. We've never encountered a bug of any significance (or even a docu­men­ta­tion error) and we've never ex­pe­ri­enced any service failures or data loss.

PostgreSQL is well-designed and provides high availability, including hot back­ups (i.e. making back­ups without stopping the server) and upgrades with only a few seconds of service interruption.

Overall, server administration is relatively simple and can be fully automated using shell scripts. Since GUI tools are not a requi­re­ment, emergency server mana­ge­ment can be easily handled with a mobile phone or through slow com­mu­ni­ca­tion lines.

Extensibility

PostgreSQL supports stored procedures (i.e. database functions) written in several pro­gramm­ing languages , including C, C++, Java, Perl, PL/pgSQL, Python, Ruby, Tcl, and more. These can be used to further extend database functionality.

KoMnA mainly uses the Python and PL/pgSQL languages, the latter being very similar to Oracle's PL/SQL. We also developed several low-level C functions to help with, among others:

• User authentication, activities tracking, and extensive logging.

• Com­mu­ni­ca­tion with external systems.

• Text sorting and filtering for a custom language (e.g. Slovenian), which is several times faster than the standard Unicode support.

Scalability

PostgreSQL is an open source project and its code is available under a very liberal BSD type licence. This allows us to use the server in our own products or deploy any number of databases for any number of users with no licensing costs or restrictions.

The license also gives us access to the source code, which helps with software de­ve­lop­ment and is crucial for its long-term maintenance. The result is a modern, secure, and always up-to-date database without scalability or over-deployment concerns, saving time and money for us as well as our clients.

Connectivity

Connectivity is one of the most significant advantages of PostgreSQL over commercial databases. There are hundreds of projects that developed a wide variety of tools to work with the database, including libraries that can be used to create clients in different pro­gramm­ing environments such as Ada, Basic, C, C++, C#, Delphi, Java, JDBC, Lisp, .NET, ODBC, PHP, Pascal, Perl, Python, Ruby, Scheme, Qt and others.

This makes PostgreSQL databases accessible from everywhere, regard­less of the computer platforms used. Data exchange between infor­ma­tion systems is thus greatly simplified, and so are future upgrades and im­prove­ments, either by KoMnA or other vendors.