What is PostgreSQL?
In the world of databases, PostgreSQL (pronounced Post-gress-cue-el) is the most advanced and extensible of the major RDBMSs. What separates PostgreSQL from alternatives like MySQL and SQLite is its support for concepts from object-oriented programming. This makes it well suited to handling some of the most complex and high-volume data operations in the world, which is why it’s been trusted by Facebook, Skype, Apple, Instagram, Yahoo!, as well as a number of financial institutions, telecom services, and government agencies, including the US State Department.
If you need a database installation capable of supporting multi-petabyte databases and processing billions of events per day, PostgreSQL may be just the ticket.
The main thing to know about PostgreSQL is that it’s an object-relational database. As with a vanilla relational database, an object-relational database stores data in tables that can be queried using a language like SQL. The difference is that it also introduces some concepts from object-oriented programming that you don’t find in other relational databases, including user-defined objects and table inheritance. Other features include:
- PostgreSQL supports truly massive databases. In fact, there’s no maximum database size in PostgreSQL, and individual tables can be up to 32 terabytes a piece with upwards of 250 columns. Even individual fields can contain up to a gigabyte of data.
- It’s also ACID Compliant by default. Other RDBMSs can be made ACID compliant with certain extensions (MySQL, for instance), but PostgreSQL is designed for it out-of-the-box.
- It has a high level of programming language support on the server side. Out of the box it supports Python, Perl, and Tcl, and can be extended to work with C, C++, R, Ruby, Java, Javascript, .NET, and more.
When should you consider switching to PostgreSQL? Before we get to that, you might ask yourself three questions to help figure out if it’s right for you:
- Is your data complex? (Ie, do you have exotic data types like JSON objects, geometric objects, or IPv4/6 addresses? Do you need support for data structures composed of other objects?)
- Do you have a lot of it? (Ie, do you need to process millions or even billions of events per day or do you need to store petabytes worth of data?)
- Do you care a lot about data integrity and compliance? (Ie, do you need out-of-the-box ACID compliance and support for additional constraints, like primary and foreign keys, unique constraints, not-null constraints, etc.?)
If you answered yes to these questions, then PostgreSQL could be worth talking to a PostgreSQL expert.
PostgreSQL vs. MySQL
Choosing a database management system comes down to deciding between a few key tradeoffs: Do you want a database that has more features but might require more work on your end, or do you want something that’s easy to set up and use but which might place some constraints on what kind of data you can store and what you can do with it. In the world of SQL databases there are a few standouts, and today we’re going to look at two of them: PostgreSQL and MySQL.
There’s a lot of overlap between MySQL and PostgreSQL, but developers on these database platforms may have divergent skill sets. MySQL developers are more likely to have experience building databases to support web apps, multimedia sites, and e-commerce platforms. If part of your product is your data–if you’re building a recommendation engine, for example, or need to perform complex analytics–then you’re probably looking for someone with PostgreSQL experience.
MySQL overview
What makes MySQL the go-to option for so many people? In short, it’s kind of the Goldilocks option among SQL platforms: While not as feature-rich or extensible as PostgreSQL, it has just the right balance of features and capabilities that it’s just right for a wide variety of applications.
Some of MySQL’s key features:
- It’s widely used and easy to set up.
- It’s reasonably flexible and highly scalable.
- It’s extremely fast, especially for highly concurrent, read-only operations. This makes it a very good tool to work with many BI applications.
- It’s platform independent and cloud-ready. No matter how your operation works you’ll be able to get MySQL up and running on it.
- It’s partially SQL compliant.
- The InnoDB engine adds ACID compliance and multiversion concurrency control.
- It may be easier to find support for MySQL since there are so many developers who have experience using it.
MySQL, Oracle, and MariaDB
Oracle acquired MySQL in 2010 and while it remains the most popular open-source database platform, there have been concerns among some developers about MySQL’s ongoing support and status as an open-source project moving forward. After the Oracle acquisition, a number of the original MySQL developers created a new fork of MySQL called MariaDB.
Their close kinship means MariaDB has many of the same advantages as MySQL, and it maintains a high level of drop-in compatibility, meaning MySQL developers interested in switching over may do so without losing much (or any) functionality.
PostgreSQL overview
If MySQL is a highly fuel-efficient mid-market sedan, PostgreSQL is sort of like a diesel-powered off-road jeep. It has more capabilities and support for extreme use cases, but it’s probably not necessary if you’re going to the grocery.
Here are some of PostgreSQL’s key features:
- It’s object-relational. Unlike pure RDBMSs, PostgreSQL borrows some concepts from object-oriented programming, including user-defined objects and table inheritance.
- It’s optimized for complex queries. PostgreSQL excels when performing complex read-write operations with data that needs to be validated, but it can lag somewhat when it comes to read-only operations.
- It’s mostly SQL compliant. Out of 179 core features of the SQL standard, it meets 160 of them, plus a number of optional features.
- It supports exotic and custom data types. PostgreSQL provides native support for a huge number of data types by default, including JSON, XML, and H-Store, making it one of the few relational database with wide support for NoSQL features.
- It’s built for truly massive databases. In fact, there’s no maximum database size in PostgreSQL, and individual tables can be up to 32 terabytes a piece with upwards of 250 columns. Even individual fields can contain up to a gigabyte of data.
- It uses multiversion concurrency control. MVCC allows multiple readers and writers to work on the database at the same time, rather than enforcing a read-write lock policy.
- It’s transactional and ACID compliant. Among database platforms, PostgreSQL is known for protecting data integrity at the transaction level. This makes it less vulnerable to data corruption than some other platforms. It’s supported by an active dev community that regularly adds new features. That said, it is less popular than MySQL, which can make finding a managed PostgreSQL instance more of a challenge.