While PostgreSQL has been one of the most frequently deployed relational database management systems, over the past few years it has seen a sharp rise in popularity with developers.
In the Stack Overflow annual Developer Survey of nearly 90,000 developers, PostgreSQL is the second most used database technology.
There are many factors that contribute to PostgreSQL’s popularity, starting with its highly active open source community that, unlike a company-led open source DBMS like MongoDB or MySQL, is not controlled by any single sponsor or company. And, because PostgreSQL is so ubiquitous, there is reduced risk if there is a need to move the database from on premises to cloud or cloud to on premises, or even from one cloud platform to another.
The feature set of PostgreSQL is continually being extended, with the community testing each new feature thoroughly before integration (for the highest levels of reliability). Also, Postgres is lightweight (the source code is less than 20 MB), easy to install, and easy to move around in comparison with bulky legacy RDBMs. It is also highly flexible, supporting relational and non-relational models, and can be deployed across a wide range of infrastructures from bare metal to VMs, containers, and DBaaS.
Now let’s get into the six things that developers should know about Postgres.
1. No Need to Learn a New IDE (Integrated Development Environment)
With support for more than 80 IDEs, chances are the framework you work with today is already integrated with PostgreSQL. There are 18 freeware and 63 commercially available IDEs that support PostgreSQL. This is in stark contrast with a proprietary DBMS, where options are limited to their development and administrative tooling.
The Community Guide to PostgreSQL GUI Tools lists IDEs that support PostgreSQL, which include pgAdmin, a GUI maintained by the PostgreSQL community for developing and administering PostgreSQL databases on Linux, Unix, Mac OS X, and Windows. Another is DBeaver, a multi-platform database tool that supports most popular SQL and NoSQL databases, including Postgres. (Its Community Edition is free, while its Enterprise Edition has a license fee.)
2. Simple Local Setup and Configuration
Typically, developers can handle setup and configuration of Postgres by themselves with DBAs only needing to get involved with larger-scale production usage.
One of the original design principles of PostgreSQL was that it would interact seamlessly and predictably with operating systems and storage frameworks. In addition, PostgreSQL integrates easily with standard operating system backup and monitoring tooling. For these reasons, Postgres is a great enabler for developers across the entire application lifecycle — from prototyping to production.
Another option if you would like to altogether avoid having to set up and configure a local environment for your PostgreSQL deployment, is the Database as a Service (DBaaS) model. All of the major cloud providers offer PostgreSQL, such as Amazon RDS for PostgreSQL, Azure Database for PostgreSQL, and (Google) Cloud SQL for PostgreSQL. There are also third-party vendors that offer fully managed PostgreSQL cloud services, including our own EDB Postgres Cloud Database Service on the AWS cloud.
3. Flexible Multimodel Architecture
PostgreSQL is a pioneer of the object-relational DBMS (ORDBMS) and supports object-oriented programming. Today, all the leading ORM tools support PostgreSQL.
The multimodel ORDBMS approach is important for today’s advanced apps that typically have to query and manipulate both structured and semi-structured data, which can require implementing both SQL and NoSQL databases in a single application. PostgreSQL, for example, has built-in support for storing JSON objects in a compact JSON binary format.
4. Agile and DevOps Friendly
Perhaps one of the biggest attractions of PostgreSQL for developers is that it fits well into continuous development, integration, and deployment processes and lends itself to DevOps and agile practices. Developers can ensure consistent infrastructure configurations across the entire application lifecycle by introducing infrastructure-as-code recipes using Chef, Ansible, Puppet, or any other infrastructure automation platform.
And, PostgreSQL also fully supports the use of containers and microservices that are at the heart of today’s modular, app architectures using Kubernetes. Plus, the fact that PostgreSQL is readily available as a cloud service aligns well with the cloud-based nature of DevOps methods.
Also, PostgreSQL’s multimodel architecture and its support for JSON and ORM mean that systems can be quickly prototyped without the need to fully design a detailed schema.
Finally, PostgreSQL is highly portable, making it extremely easy to replicate instances for development, testing, and staging purposes. For example, developers can run PostgreSQL in a container on their computer and easily make the transition to production in the cloud or on-premises. It is easy to move PostgreSQL databases around, run them locally or in the cloud, or run multiple versions that do not interfere with each other.
5. Cool Things You Can Do with PostgreSQL
Before getting to the really cool stuff, it should be noted that PostgreSQL is highly ANSI SQL compliant so developers who already have SQL skills will feel right at home in PostgreSQL.
The really cool stuff starts the PostgreSQL multimodel architecture (discussed previously) that provides built-in support for many non-relational NoSQL features but without the drawbacks of NoSQL, such as client-side data analysis, lack of a powerful query language and optimizer, and data retention becoming an admin responsibility. The full-text search feature is fast and optimized, with support for fuzzy matching, ranking, phrase search and multiple languages.
In addition, PostgreSQL is highly adaptable with numerous plugins – many of them open source — that extend Postgres capabilities for time-series data aggregation and graphs, as a transactional graph database and as a spatial database management system.
Also, PostgreSQL uses Foreign Data Wrappers to seamlessly read from and write to foreign data sources that include CouchDB, Informix, MongoDB, MySQL, Neo4j, Oracle, and Redis. With Foreign Data Wrappers it is possible to use a single PostgreSQL database to federate at scale a diverse range of data sources and formats as well as app requirements, as illustrated below.
6- Smooth Road to Production
PostgreSQL provides a smooth road to production working on all popular deployment platforms, from bare metal to VMs (on premises or IaaS), containers, and DBaaS. The right choice of platform will depend on many factors, including the app’s scalability and availability requirements, its architecture, and the extent to which the organization wants (or needs) to have direct control over infrastructure and orchestration.
PostgreSQL is the third-most-popular technology being run by enterprises on Docker containers, according to this survey. Containerized PostgreSQL behaves consistently throughout the application lifecycle (development, staging, production) and is well-suited to microservices-based app architectures. It also delivers high availability in failover situations and optimizes compute-storage costs through elastic on-demand scaling rather than upfront over-provisioning. We’re seeing this deployment model used in combination with Kubernetes gaining traction for the reasons cited, as well as the flexibility to run and move containers on-premises and in the cloud.
Summary
It’s easy to see why PostgreSQL is still gaining popularity and why developers embrace PostgreSQL to play a central role in the enterprise data center. PostgreSQL’s inherent reliability, flexibility, and extensibility, together with the support of its active open source community, ensure that it will remain a leader that is kept up to date as DBMS methods and technology stacks evolve.
InApps Technology is a wholly owned subsidiary of Insight Partners, an investor in the following companies mentioned in this article: Docker.
MongoDB, Oracle and Redis Labs are sponsors of InApps Technology.
Feature image via Pixabay.
