What is PostgreSQL?
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 organise 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.
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.
An open-source licence
Because PostgreSQL is accessible as an open-source licence, 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.
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, Generalised Inverted Index, and Generalised Search Tree, in addition to full-text searching for string searches and strings of vector operations.
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.
Because PostgreSQL supports both relational and non-relational querying, users can access JSON data using SQL and JSON path expressions.
PostgreSQL goes beyond merely storing data—the software gives your users the power to define functional languages and data types, including customised types or user-defined types. Plus, your company’s developers can use a variety of available extensions and add-ins to customise 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 catalogues 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 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:
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.
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 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
Build apps that scale with the pace of your business with managed and intelligent SQL in the cloud.
Build or modernise scalable, high-performance apps with fully managed NoSQL database service.
Innovate faster with a fully managed MySQL database.
Develop applications with Azure Database for MariaDB using the open-source tools and platform of your choice.
Lightning-fast and fully managed in-memory data store.
Industry-leading performance and manageability for your cloud database.
See all Azure SQL database services for app migration, modernisation, and development.
Fully managed, intelligent, and flexible cloud database services.
Ease your database migration to Azure.
Explore Microsoft Azure Database for PostgreSQL
Building scalable applications with Azure Database for PostgreSQL will help your business get the most out of your database. Learn how to quickly, easily, and confidently scale your workload with Azure Database for PostgreSQL.Learn more