PostgreSQL - The database that fits all
April 27, 2020
PostgreSQL - The database that fits all
Score 10 out of 10
Overall Satisfaction with PostgreSQL
PostgreSQL is used in several Departmental Information Systems across the Bank, from open source systems which operates with the DBMS to in-house developed systems like the one we use for Operational and Technology Risk Assessment, and Asset Management.
- It is an excellent DBMS, which is scalable, performs well, allows replication, supports ACID and a big subset of the SQL standard (in several cases, it is a superset).
- Is much better at data types than the other DBMSs, with a more rich semantics, with geo-spatial types, complex numbers, etc.
- It supports several methods of indexation, including B-trees, Genetic Algorithms based indexes, and GIN indexes that accelerates full-text searches.
- Its flexibility to select from a variety of procedural languages to make stored procedures is astonishing.
- The performance of PostgreSQL has been enhanced through the years, but always is better to have as much performance as we can.
- The replication services could be done directly within the database, and more easily.
- The Object Orientation of the Database could be extended, and albeit it manages inheritance of tables, and accepts XML and JSON as primary types, it would be wonderful if one could attach methods more easily to tables (to make them more like classes), and instances (rows for example).
- Being open source free software, the ROI couldn't be better.
- There are a lot of young developers who know the technology and are interested in advancing their careers with the platforms and technologies in which they are invested. It is more easy to contract developers for the platform, then the projects are more quickly finished, then the ROI is made in shorter time.
- There is a lot of prejudice with PostgreSQL and open source in general, because, they say, there is no company behind it, but thank God, their minds are changing. A lot of companies and consultants will technically support your project and your infrastructure.
SQL Server is an excellent product from Microsoft, it is a derivative from Sybase which originally developed the SQL Server form Unix and Linux, and Microsoft purchased it to migrate the DBMS to Windows Server. But the cycle comes full circle, and now Microsoft recommends its use in Linux. The problem with SQL Server is its pricing scheme. Before 2012 it was sold by CPU, but after that, it was priced with licenses for 2 cores each, and its cost is in around USD 4K for the standard version and USD 15K for the Enterprise version. A sound recommendation for Microsoft, go back to your previous licensing scheme if you do not want your user base eroded by open source products like MariaDB or PostgreSQL.
SQL Server has better performance per core than PostgreSQL, when all other variables are equal (sometimes). But if you use all the available resources of your server for PostgreSQL and limit the cores you use for SQL Server (i.e.: to 1/4 of the cores), PostgreSQL runs circles around SQL Server.
In comparison to MariaDB, PostgreSQL adheres better to the standards and has a richer set of primary data types, among other advantages.
MariaDB is a derivative of MySQL, which gives you more freedom (given its license) that could give you Oracle, the actual owner and maintainer of MySQL.
There are several companies that you can contract for technical support, like EnterpriseDB or Percona, both first level in expertise and commitment to the software.
But we do not have contracts with them, we have done all the way from googling to forums, and never have a problem that we cannot resolve or pass around. And for dozens of projects and more than 15 years now.
Do you think PostgreSQL delivers good value for the price?
Are you happy with PostgreSQL's feature set?
Did PostgreSQL live up to sales and marketing promises?
Did implementation of PostgreSQL go as expected?
Would you buy PostgreSQL again?
PostgreSQL is open source, and then, there are several companies that could give you technical support, like EnterpriseDB or Percona.
PostgreSQL is easy to install and configure and is multi-platform, you could use Windows, Mac, Linux, FreeBSD, and you could use ALL the hardware you have purchased for your project, without sinking hundreds of thousand of dollars in proprietary software, to use only a small part of your server for the database.
Let's see a concrete case: You have a project to make an information system which uses an underlying database. Then you purchase 3 servers, which cost USD 50K for the Production server, USD 25K for the Contingency Server and USD 10K for the Development Server.
If you use a proprietary database, we know what companies, they price their software licenses per every 2 cores if we use the servers physically or every 2 vcpus (threads) if we use virtual machines, and the cost range is between USD 15K and 50K for each 2 cores license. If my production server has a configuration of 24 SSDs of 3,84 TB, 2 TB of ECC RAM, and 2 CPUs AMD EPYC 7702, with 64 cores each (128 cores and 256 vcpus or threads per server), the cost of the proprietary database could be more than USD one million, and if we use a subscription mechanism to get the updates, we could be talking of another million each 3 to 5 years. That is ten times the cost of the hardware. And you can TOUCH the hardware.
Is PostgreSQL slower than the proprietary databases that we talk about? Yes; but for what margin? In in-house tests, the performance of PostgreSQL was from 50% to 75% typically, with several scenarios where PostgreSQL where speedier. But we could use ALL the cores for the DBMS, not just a few.
For us PostgreSQL is the best database in existence. Period. If we use proprietary databases yet it's only for legacy information systems, for contractual restrictions or because our providers haven't seen the light (yet).