What is PostgreSQL?
PostgreSQL is an open-source relational database. Learn the facts about what PostgreSQL is, PostgreSQL data types, and more.
A proven and powerful open-source database system
What exactly is PostgreSQL database? Relational databases like PostgreSQL exist to help users organize data and understand the relationships between that data. PostgreSQL is an open-source relational database that’s supported by 30 years of development, making it one of the most established relational databases available. PostgreSQL owes its popularity with developers and administrators to its noteworthy flexibility and integrity. For instance, PostgreSQL supports both relational and non-relational querying, and its open-source nature means that a dedicated community of more than 600 contributors constantly improves the database system.
Who uses PostgreSQL?
Across a diverse range of fields, such as financial services, manufacturing, retail, and logistics, PostgreSQL is a crucial database solution that helps developers maintain the integrity of their data, more easily manage workloads of all sizes, and scale as needed.
PostgreSQL serves as the main database. In addition to boasting a strong reputation, PostgreSQL offers substantial benefits for geospatial-enabled apps and apps that combine time series, JavaScript Object Notation Binding (JSONB), and relational data. And administrators have recognized the high level of reliability of PostgreSQL in safeguarding data. Overall, PostgreSQL is gaining in popularity because of its ever-expanding feature set and avid community of contributors.
Why use PostgreSQL?
Because PostgreSQL is robust, secure, extensible—and because it has a rich ecosystem of available tools—developers use PostgreSQL for a variety of use cases. The software is designed to be compatible with all major operating systems, including Linux, Windows, and Macintosh, and it supports text, images, sounds, and video, making it a popular database for people and companies with diverse needs. PostgreSQL is widely regarded as developers’ favorite database technology behind only MySQL.
However, there are many reasons why developers may choose PostgreSQL over MySQL as a database solution, including:
Access to powerful features
PostgreSQL comes with a wealth of options for users. For example, you can choose from features like point-in-time recovery, write-ahead logging, granular access controls, tablespaces, nested transactions, online backups, and multiversion concurrency control.
Reliability and compliance
Decades of development have helped make PostgreSQL extremely fault tolerant. It’s compliant with atomicity, consistency, isolation, and durability (ACID) properties for database transactions. Additionally, PostgreSQL supports multiple languages across triggers, foreign key attributes, joins, and stored procedures. PostgreSQL allows for the most common data types, including SQL 2008, and it supports Unicode, international character sets, and multibyte character encodings.
PostgreSQL is an open-source license
Because PostgreSQL is accessible as an open-source license, your users are empowered to be more flexible and innovative than with a commercial database system. With no licensing costs, your users are free to explore endless development possibilities and even modify or implement the source code however they choose.
Scalability of PostgreSQL
Remarkable scalability is a hallmark of PostgreSQL. The software can effortlessly manage vast amounts of data. The scalability of PostgreSQL applies not only to the amount of data it can manage but also to the number of concurrent users it can manage.
A variety of index types and full-text search
PostgreSQL offers your users a range of indexing techniques, including B+ tree index, Generalized Inverted Index, and Generalized Search Tree, in addition to full-text searching for string searches and strings of vector operations.
Flexibility
PostgreSQL is compatible with an array of the foremost programming languages and protocols, including C, C++, Go, Perl, Python, Java, .Net, Ruby, ODBC, and Tcl. That means your users can work in the language they understand best without risking system conflicts.
A rich support ecosystem
The open-source nature of PostgreSQL gives users the support of a dedicated community of contributors who are constantly refining the system, making it more secure and relevant. Your users can access the collective knowledge of this community to report bugs, crowdsource opinions on development projects, and answer their questions. The open-source nature of PostgreSQL provides users with many ways to get support, whether they’re working in frameworks for Ruby on Rails, Tableau, or Datadog. Not only can your developers access the community’s collective knowledge and crowdsource help, they can also connect with PostgreSQL experts and services to resolve issues or determine next steps.
JSON
Because PostgreSQL supports both relational and non-relational querying, users can access JSON data using SQL and JSON path expressions.
Extensibility
PostgreSQL goes beyond merely storing data—the software gives your users the power to define functional languages and data types, including custom types or user-defined types. Plus, your company’s developers can use a variety of available extensions and add-ins to customize their PostgreSQL experiences, including PostGIS, Citus, pg_cron, HyperLogLog, and t-digest. PostgreSQL achieves this degree of extensibility because, unlike most relational database management systems, it stores more information in its catalogs than traditional tables and columns. Your users have the power to modify tables, and in the process, extend PostgreSQL.
PostgreSQL data types explained
Your PostgreSQL users have a generous number of native data types at their fingertips, including JSONB and PostGIS, and they can also add new PostgreSQL data types easily. Each data type is designed to support a unique purpose, such as full-text search and date and time data. To create a table, your users first select a particular data type for each column. The purpose of these columns is to signify the type of data that belongs in a table field. The following are some of the most prevalent PostgreSQL data types:
Boolean
The Boolean data type is designed to express two-state values such as true/false, on/off, yes/no, and null values. You would commonly use this data type to evaluate conditional statements. Control flow can be contingent on the outcome of either true or false, like when using the PostgreSQL CASE expression, with various actions resulting from the evaluation.
Character
This data type consists of strings of characters such as letters or numbers, and it’s used to store text values. Character data types and string types can occur as either a fixed length, known as char, or variable lengths, known as varchar and long varchar. The length that your user selects affects the validation of the input.
Dates and times
The date and time data type is used to signify dates, times, and temporal intervals. The PostgreSQL time stamp data type is precise down to the microsecond and offers your users the choice to store time and date data with or without time zone information attached.
Numeric
Numeric data types come in two forms: exact and approximate. Numeric data types that are exact contain integer data types and decimal data types. Approximate data types, on the other hand, contain floating-point data types—for instance, 2-, 4-, and 8-byte integers, 4- and 8-byte floating-point numbers, and selectable-precision decimals.
Explore more Azure database services
Azure SQL Database
Build apps that scale with the pace of your business with managed and intelligent SQL in the cloud.
Azure Database for PostgreSQL
Focus on application innovation instead of database management with a fully managed, intelligent, and scalable PostgreSQL.
Azure Database for MariaDB
Develop applications with Azure Database for MariaDB using the open-source tools and platform of your choice.
Azure Cache for Redis
Lightning-fast and fully managed in-memory data store.
Azure SQL
See all Azure SQL database services for app migration, modernization, and development.
Azure Database for MySQL
Innovate faster with a fully managed MySQL database.
Pay as you go
Get started with pay-as-you-go pricing. There's no upfront commitment—cancel anytime.