Matt Hilbert

30 March 2023

3 Comments

What is PostgreSQL, and why do businesses need to know more about it?

For the last five years and more, there’s been an elephant in the room when it comes to the way businesses and organizations collect, manage, store and analyze data: PostgreSQL. An open source relational database that offers the kind of enterprise advantages that have already attracted businesses like Netflix, Instagram and Spotify according to the tech stack intelligence platform, StackShare. But what do they know that you don’t, what kind of advantages are they gaining, and why should you care about what database your business is using? Here’s your quick guide to everything PostgreSQL.

PostgreSQL, by the way, is pronounced ‘Post-gres-Q-L’, or it’s shortened to ‘Post-gres’.

Businesses now use more than one type of database

First up, business and organizations everywhere now use more than one type of database because the volume, nature and location of data has changed. Alongside the huge increase in structured data like relational and transactional data in SQL databases, there has been a meteoric rise in unstructured and semi-structured data, or big data.

As a consequence, the days of monolithic database estates which are SQL Server or Oracle only are over. Now, databases are chosen on a use-case approach, based on the advantages they offer for any given scenario. A good indicator of this is the variety of databases in use, as shown in the 2022 Stack Overflow Developer Survey:

Here, the usual suspects like SQL Server and Oracle now sit amongst open source databases like PostgreSQL and MySQL, and NoSQL databases like MongoDB and Redis. Importantly, the question about the databases in use was multiple choice and the responses add up to 273%, highlighting that developers are using more than one database. They’re moving between them, working on one database for one project and moving to another for the next.

A good explanation for that can be seen when comparing the features of two databases in common use, like SQL Server and PostgreSQL:

What’s notable here isn’t the intricacies of the features themselves, but the way that not every database can satisfy every requirement. There is always a trade-off. In this instance, SQL Server helps with database tuning and optimization out of the box, for example, while PostgreSQL features support for geographic objects and advanced data types such as JSON.

The deciding factor for many, however, will be the commercial license required with SQL Server. The freedom to explore different innovation paths and opportunities that comes with PostgreSQL’s permissive, open source license often prompts the decision to explore it further.

Hence the growth in cross-database estates, and developers now becoming accustomed to adapting the way they work to the demands of the project they’re working on.

Open source has become open for business

In conjunction with the rise in businesses and organizations using more than one database is the rise in the enterprise use of open source software like PostgreSQL. Once seen as a poor relation to established proprietary software from major vendors, open source is now seen as a credible and viable alternative. That’s reflected in the stand-out findings from Red Hat’s 2022 State of Enterprise Open Source Report. The survey of nearly 1,300 enterprise IT leaders worldwide found that:

• 89% see enterprise open source software as more secure or as secure as proprietary software
• 77% have a more positive perception of enterprise open source software than they did a year ago
• 79% think enterprise open source software provides flexibility to customize solutions to meet their business needs

That includes PostgreSQL which has actually been around for a long, long time. It goes back to a 1985 research project addressing the problems with contemporary databases at the University of California, Berkeley, through to when it was first formally released with an open-source, permissive license in 1997.

Since then, it’s grown to become trusted by businesses as a powerful, open source relational database that provides a robust and highly scalable platform for managing large volumes of structured and semi-structured data. For enterprises, it offers five major advantages:

The advantages of PostgreSQL for enterprises

• Customization: While it’s free to use, the bigger advantage of PostgreSQL is its license which is simple and very permissive even when compared to other open source software. Organizations can do whatever they want to it, as long as they let people know they started with PostgreSQL. As a result, major players like AWS, Azure and Google have stepped in and spurred innovation, contributing back and also offering PostgreSQL-compatible services which further boost its appeal.
• Extensibility: Alongside the freedom to customize, PostgreSQL allows for custom data types, operators, and functions, providing the flexibility to extend the database to meet specific needs. It can also be extended with a range of existing add-ons and plugins to analyze, for example, time-series data or geographic and spatial data.
• Scalability: PostgreSQL is highly scalable in terms of the huge quantity of data it can manage and the number of concurrent users it can accommodate, making it suitable for applications that need to grow over time, or accommodate spikes in usage.
• Robustness and reliability: PostgreSQL is known for its stability and low rate of data corruption, and has a track record of being reliable in high-concurrency, high-write, and high-read environments, making it suitable for large-scale applications that require high availability and uptime.
• Community support: PostgreSQL has a large, active and global community of developers and users who contribute to its development and provide support to others. This also provides access to a wealth of instantly available resources, including documentation, tutorials and forums for help and advice.

Developers are willing – and waiting – to work with PostgreSQL

While businesses have only recently come around to the advantages of open source software, developers have been advocates for a long time, as highlighted in the Stack Overflow Developer Survey referenced earlier. Over the last five years, when asked to name their ‘most loved database’, an average of 80,000 respondents each year and consistently chose Redis and PostgreSQL, both of which are open source, with PostgreSQL finally reaching the #1 spot in 2022:

There’s good reason for the popularity of both. Redis is an in-memory data store used for real-time analytics and enables developers to write what is normally complex code in a simpler way. PostgreSQL offers developers advantages they can use in a wider context beyond edge-cases like real-time analytics, thanks to five big attractions:

The attractions of PostgreSQL for developers

• Multiple language support: PostgreSQL offers support for multiple languages including Python, Java, Perl, C, C++, PHP and JavaScript, making it ideal for development teams that require a high degree of flexibility.
• Extensive feature set: PostgreSQL has an extensive set of features, including support for advanced SQL queries, full-text search, JSON and XML data types. These enable developers to build complex and sophisticated applications that require advanced data management capabilities.
• Support for advanced data types: In addition to the standard data types, PostgreSQL supports advanced types like JSON, the hstore key-value store, and arrays, making it easier to model complex data structures and store semi-structured data.
• Cross-platform compatibility: PostgreSQL runs on various operating systems, including Linux, Windows and macOS, giving developers the flexibility to choose the platform that best suits their needs.
• Conformance to SQL standards: PostgreSQL’s adherence to SQL standards can make it easier for developers to work with and transition between different database systems, providing more flexibility in their technology choices.

PostgreSQL is now in use in many sectors

With its rich feature set and extensibility across both structured and semi-structured data, PostgreSQL has been introduced across various sectors to address specific needs and challenges which cannot be addressed by other relational databases.

In Financial Services, the ability of PostgreSQL to manage high-volume financial data and analytics is well-suited for handling transactions consistently and reliably. Its high performance, scalability and support for advanced data types in particular make it useful for modelling complex financial data structures.

In Healthcare, the robust security features of PostgreSQL, access controls and authentication, along with ACID compliance and support for complex data types have made it a popular choice. It helps to ensure sensitive patient data is protected in line with HIPAA, while also enabling diverse and complex healthcare data like genomic data to be stored and processed.

In Media and entertainment, PostgreSQL is used to manage structured data as well as the unstructured data found in audio and video files, metadata, emails and social media posts. Its ability to scale horizontally with extensions such as Citus alongside its support for full-text search and advanced indexing also helps when supporting millions of concurrent users.

In Geospatial and mapping, PostgreSQL offers native support for spatial data types and functions through PostGIS, an open source extension which follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC). This popular extension provides advanced spatial capabilities, including support for geographic objects, spatial indexing, and a comprehensive library of functions for querying and manipulating spatial data.

That’s not the end of the story. If you’d like to discover more, watch the first of the PostgreSQL 101 webinar series, hosted by Redgate PostgreSQL Advocate, Ryan Booz. You can view Why PostgreSQL in 2023? on demand and free right now.