PostgreSQL benefits and challenges: A snapshot

The database market continues to be splintered among open source and commercial products, with multiple options in each camp. PostgreSQL, a three-decades-old, community-supported open source project, continues to gain in popularity and is used in production by large enterprises in many parts of the world.

Yandex, for example, stores petabytes of data in PostgreSQL, which enables the company’s email service to handle more than 150 million emails a day. GitLab, which has been using Postgres for years, maintains a large cluster that handles 181,000 transactions per second. IKEA, which moved to PostgreSQL to reduce its total cost of ownership (TCO), has a number of databases running multiple terabytes of data.

[ Also on InfoWorld: The year of PostgreSQL is every year ]

Finally, InCountry, a startup helping international digital businesses comply with local laws in more than a hundredcountries, uses Postgres to power its data-residency-as-a-service global distributed database. It’s an advanced and complicated solution requiring a mature and reliable database management system.

To understand if PostgreSQL is the right database for your projects, it is essential to understand how it fits into the database landscape, as well as its specific benefits and challenges.

The benefits of PostgreSQL

A number of characteristics and features of Postgres make it appropriate for a very wide range of applications:

• Code quality – Every line of code going into PostgreSQL is reviewed by multiple experts, and the entire development process is community-driven, so bug reporting, fixes, and verification are accomplished very quickly.
• Extensibility – PostgreSQL is an extremely flexible solution with extensions covering the needs of nearly every potential use case. Even if you need something very specific, such as support for certain data types or extended logging functionality, you can write your own extension or have a PostgreSQL vendor do it for you.
• SQL and NoSQL – PostgreSQL can be used as both a traditional SQL relational database management system for rows of transactional or statistical data, and as a NoSQL solution for storing JSON documents. This flexibility can reduce costs and increase your security posture. Using just one database management system means you don’t need to hire or contract with the expertise required to set up, manage, secure, and update multiple database solutions.
• Spatial data – Businesses often rely on Postgres for applications that use spatial data because Postgres has some excellent capabilities for handling such data. For example, Postgres has specific data types for geometrical objects, and PostGIS enables easy and fast spatial database creation. This has made Postgres especially popular with transportation and logistics companies.
• Data availability and resiliency – For mission-critical production environments, such as for government agencies, financial services firms, and healthcare companies, privately supported versions of PostgreSQL provide additional high-availability, resiliency, and security features. 

PostgreSQL also has a few significant advantages over other open source databases:

• Not controlled by a single vendor – Unlike some other open source databases, Postgres is not a single-vendor solution. The progress of a single-vendor solution is ultimately 100% dependent on the decisions of the vendor, not the user community. It’s the same for bug fixes. Only the vendor decides what is going to be fixed in the next release. While the user community can put pressure on the vendor – and smart vendors listen to their user communities – there is always a strong possibility that the needs of some users will be ignored.
• No vendor lock-in – If an open source DBMS is privately owned, the vendor will typically try to lock in customers by selling a family of complementary products, such as cloud infrastructure that comes with database as a service (DBaaS). While these products may be desirable for getting the most out of the database, they also make it harder for the customer to switch to other solutions.
• Popularity – As noted above, PostgreSQL is a very popular solution. This is important because companies that standardize on an open source database with a relatively small customer base will find limited options when it comes to getting third-party installation, troubleshooting, or consulting services. It will also be harder to find people to hire with the appropriate level of expertise. And whether you’re looking to hire or contract for a service, the relatively rare expertise will likely come at a higher price – if it exists at all.

The challenges of PostgreSQL

While all these benefits of Postgres are compelling, there are important challenges to be aware of when adopting this database. Postgres challenges include:

Cost – There is a common misperception that, as an open source solution, PostgreSQL is 100% free. However, while downloading, installing, and using the solution are free, there are almost always costs related to migration, support, and maintenance – and these costs should not be underestimated. So it may be important to work with a PostgreSQL consultant early on to determine what these costs may be.

Time – Ensuring a seamless PostgreSQL development or migration project can take longer than anticipated. Unexpected issues often require additional research that can delay progress. So it is important to build time for on-the-fly research into the project schedule from the beginning.

Not controlled by a single vendor – While this is listed as a benefit above, it also has a potential downside. Users moving to PostgreSQL will need to choose among multiple vendors and solutions that perform the same function. For example, Postgres has five backup solutions from different companies. This means you must take the time to understand the benefits and challenges of each one, so you can choose the best option for your use case. Again, for companies just getting started with Postgres, it may be helpful to seek the advice of a consultant.

PostgreSQL on-premises vs. in the cloud – Working with PostgreSQL in the cloud is not the same as on-premises. Many Postgres extensions are unavailable in the cloud, and vendors often lock some default settings, which limits Postgres configurations and capabilities. The cost for cloud resource consumption can also be quite high. In fact, many companies have had to move from Amazon RDS back to on-premises Postgres to gain control over expenses and ensure scalability.

Deployment size – While switching to Postgres will likely be easy for small businesses with small databases, midsize companies and large enterprises will quickly face migration challenges that will require working with a support vendor or migration service.

In-house expertise – Maintaining any database management system requires expertise, and PostgreSQL is no exception. Companies must compare the costs associated with hiring the required expertise versus having an existing team learn Postgres from the ground up and then rely on a third-party vendor or consultant to supplement the in-house team. If you’re hiring the expertise, do this early enough in the process to ensure the new team can fully understand the business need and the business logic of the application before beginning development or a migration. Next, design a step-by-step migration plan that anticipates possible challenges.

[ Also on InfoWorld: The shifting market for PostgreSQL ]

Even if you hire an in-house Postgres team, you should still consider working with a PostgreSQL consultant with years of expertise to review the development or migration plan and even test multiple vendor solutions to ensure the right fit. This will likely save significant time, effort, and money down the road.

PostgreSQL is a popular and well-supported database that continues to improve in ways that meet the needs of even the most complex, mission-critical use cases – which explains its growing adoption rates and why more and more enterprises are choosing it as their primary database management system. If projects are approached properly and supported by the right level of expertise, companies can ensure a successful deployment that will meet their evolving needs for years to come.

—

Ivan Panchenko is the co-founder and deputy CEO of Postgres Professional, a company focused on making PostgreSQL enterprise-ready. A PostgreSQL enthusiast and well-known member of the PostgreSQL community, Panchenko works closely with enterprise customers and helps define product strategy. Panchenko is a professional astrophysicist and a software developer who started developing PostgreSQL-based applications in 1998.

—

New Tech Forum provides a venue to explore and discuss emerging enterprise technology in unprecedented depth and breadth. The selection is subjective, based on our pick of the technologies we believe to be important and of greatest interest to InfoWorld readers. InfoWorld does not accept marketing collateral for publication and reserves the right to edit all contributed content. Send all inquiries to newtechforum@infoworld.com.