What is PostgreSQL?
Learn more about the database loved by developers.
The database that powers Heroku Postgres
PostgreSQL is an open source, object relational database used by millions of developers and companies worldwide. According to Stack Overflow, it’s the second most popular database among developers.
Developers and companies love PostgreSQL because it scales, it’s easy to query, and it works with many different types of data. At Heroku, we’ve developed deep PostgreSQL expertise. We launched the world’s first database as a service using PostgreSQL back in 2011, and today we manage millions of instances. Our own platform has relied on PostgreSQL since 2007, meaning that when you use Heroku Postgres, you’re taking advantage of everything that we’ve learned about developing with, operating, and tuning PostgreSQL.
What is PostgreSQL? Here are the fundamentals
Relational, JSON, GIS, and more
Like Microsoft SQL Server or MySQL, PostgreSQL is a relational database. That makes it ideal in cases when data needs to be available in different contexts while staying consistent. PostgreSQL goes a step further than most relational databases by natively supporting JSON, offering object inheritance features, and working with a rich extensions ecosystem that makes geographic and other data types available.
Works with almost everything
Thanks to SQL, you can query PostgreSQL data in almost any way you like. Most importantly, SQL means that PostgreSQL works with common tools, frameworks, and infrastructure, including Ruby on Rails, Laravel, Tableau, and more.
Backed by a global community
PostgreSQL is developed by a global community under an open source license and used by developers worldwide. That means that someone else has almost certainly asked, and answered, any questions you might have. Finding learning resources and help with specific questions is just a search away.
PostgreSQL Basics: Key Terms
Query
A request for data or information from a database. In PostgreSQL, you use SQL queries to retrieve specific information, update records, or perform calculations on your data. Queries are the primary way you interact with your database to get meaningful results.
Table
The fundamental structure for storing data in PostgreSQL. Tables organize information into rows (individual records) and columns (specific attributes), similar to a spreadsheet. All your application’s persistent data is stored in one or more tables.
Data Types
The categories that define what kind of information can be stored in a column. PostgreSQL supports an extensive range of data types including numbers, text, dates, boolean values, JSON, arrays, and geometric data, giving you flexibility in how you structure your information.
Index
A database feature that improves the speed of data retrieval operations. Similar to a book’s index, database indexes help PostgreSQL find specific data quickly without scanning every row in a table, dramatically improving performance for large datasets.
Schema
A container for organizing database objects like tables, views, and functions into logical groups. Schemas help you manage complex databases by separating objects into namespaces, improving organization and security in multi-user environments.
ACID Compliance
A set of properties (Atomicity, Consistency, Isolation, Durability) that guarantee database transactions are processed reliably. PostgreSQL’s strong ACID compliance ensures your data remains accurate and consistent even during system failures or concurrent access.
Why use PostgreSQL
With so many data storage options, why should you use PostgreSQL? For over 20 years, the mission of the PostgreSQL community has been to provide an enterprise-class database that’s available to everyone. The result is a supremely queryable data store that prioritizes data integrity, stability, and performance.
Round the clock stability
A data store must earn its place at the heart of your infrastructure. PostgreSQL is built to serve and ingest data around the clock. Upgrades and migrations complete swiftly, while everyday uptime is guaranteed through automatic failover.
SQL compliance
Migrating from other SQL databases is straightforward, thanks to PostgreSQL’s high compliance with SQL standards. It meets 150 out of the SQL:2003’s 164 requirements, with no other database able to meet the full standard.
NoSQL-style data types and query
Working with non-relational data doesn’t mean you have to compromise. PostgreSQL handles JSON, key-value, column store, geographic, and other non-relational data types both natively and through extensions.
Extensions ecosystem
The worldwide PostgreSQL developer community provides a vibrant ecosystem of plugins that extend PostgreSQL’s functionality. Extensions offer connections to other database systems, add data types, support novel index types, and more.
Open source
As open source software, there are no unexpected bills or licensing audits with PostgreSQL. And if there is functionality that you’d like to see, you can join the development community to help see it come to life.
When to use PostgreSQL
PostgreSQL brings so much to the table, you might wonder if you should ever use anything else. There are particular situations, though, where PostgreSQL’s powerful query, operational speed, and stability make it ideally suited.
Operational database
PostgreSQL is unbeatable as the reliable data workhorse at the heart of your software’s infrastructure. It is responsive, scalable, and stable. In addition, creating dev, staging, and backup instances is effortless.
Data warehouse
Its popularity means that PostgreSQL is a standard target for most ETL systems, and a great store for data warehousing. Its performance, query power, and scalability make it ideal for terabytes of data.
Reporting database
The query flexibility of PostgreSQL makes it easier to dive deep into your data. Features, such as Foreign Data Wrappers, also let you query multiple PostgreSQL instances in a single query, bringing data together from across your organization.
Application database
PostgreSQL serves as an ideal foundation for modern web, mobile, and AI applications of all sizes. Its combination of reliability, feature richness, and performance makes it suitable for everything from small startups to enterprise applications handling millions of users.
AI database
PostgreSQL excels as a vector database via the pgvector extension, making it ideal for AI and machine learning applications. You can store, index, and query high-dimensional vectors for semantic search, recommendation systems, and similarity matching.
NoSQL database
PostgreSQL offers the flexibility of NoSQL databases while maintaining the reliability of a relational system. With native JSON/JSONB support, you can store and query unstructured data. The JSONB data type provides efficient storage, indexing, and querying of JSON documents, allowing for schema-less data models.
PostgreSQL vs. Alternative Databases
PostgreSQL vs. MySQL
PostgreSQL and MySQL are both open-source relational databases. However, Postgres offers superior standards compliance, a wider range of advanced data types, and better concurrency handling. Heroku Postgres provides automated backups, follower databases for read scaling, and specialized tools like Dataclips that aren’t available with MySQL add-ons.
PostgreSQL vs. Oracle
PostgreSQL delivers enterprise-grade features without Oracle’s hefty licensing costs. While Oracle may edge out in performance for certain specialized workloads, PostgreSQL offers comparable reliability, excellent extensibility, and a more modern development approach. Many organizations successfully migrate from Oracle to PostgreSQL with minimal adjustments.
PostgreSQL vs. MongoDB
PostgreSQL bridges relational and document database worlds with its JSONB support, offering MongoDB-like flexibility while maintaining ACID compliance and SQL querying. MongoDB excels at pure document-oriented use cases with simpler scaling, while PostgreSQL provides better data integrity, more powerful querying, and the ability to combine structured and unstructured data.
Heroku Postgres — a managed PostgreSQL database as a service
Backed by a team of PostgreSQL experts, Heroku Postgres takes care of everything you need to run PostgreSQL. Get up and running in seconds, then grow with ease.
Tuned, patched, updated
With Heroku Postgres, every database under our care is performance tuned by our expert operations team. You can rest assured that security patches and version updates are handled on your behalf, leaving you to focus on building your product.
Standards compliance
All Heroku Postgres databases comply with industry standards. Where you need stricter compliance, Heroku Shield delivers PostgreSQL with PCI and HIPAA compliance.
Unique ways to use your data
Only Heroku gives you Heroku Connect, which syncs data between a Heroku Postgres database and a Salesforce org. Dataclips let you share the results of Heroku Postgres queries as web pages or JSON objects.
See Heroku Postgres in action
PostgreSQL FAQ
Is PostgreSQL a relational database?
Yes, PostgreSQL is a relational database management system (RDBMS). It organizes data into tables with rows and columns and supports relationships between these tables through foreign keys. PostgreSQL goes beyond traditional relational databases by also offering object-oriented features, making it technically an “object-relational database system.”
Is PostgreSQL open source?
Yes, PostgreSQL is completely open source. It’s released under the PostgreSQL License, which is a liberal open source license similar to the MIT license. You can use, modify, and distribute PostgreSQL for any purpose, including commercial applications, without paying licensing fees.
How to pronounce PostgreSQL?
PostgreSQL is pronounced “post-gress-queue-el” or simply “post-gress.” The name comes from its origins as the successor to the Ingres database (hence “Post-Ingres”), combined with SQL to indicate its query language. Many users and developers simply refer to it as “Postgres” in everyday conversation.
What is SQL?
SQL (Structured Query Language) is the standardized programming language used to communicate with relational databases like PostgreSQL. It allows you to create, read, update, and delete data (CRUD operations) using commands like SELECT, INSERT, UPDATE, and DELETE. SQL also enables you to define database structures, set permissions, and perform complex data manipulations. PostgreSQL implements most of the SQL standard while adding its own extensions and enhancements.
Get started with Heroku Postgres
Options for an image and text are offered here, keep what you need and remove what you don’t.
Signup Now View Heroku Postgres Plans