Skip to main content

Craig Kerstiens

Why Postgres

This post is a list of many of the reasons to use Postgres, much this content as well as how to use these features will later be curated within PostgresGuide.com. If you need to get started check out Postgres.app for Mac, or get a Cloud instance at Heroku Postgres for free

UPDATE: A part 2 has been posted on Why Use Postgres

Very often recently I find myself explaining why Postgres is so great. In an effort to save myself a bit of time in repeating this, I though it best to consolidate why Postgres is so great and dispel some of the historical arguments against it.

Replication

For some time the biggest argument for MySQL over Postgres was the lack of a good replication story for Postgres. With the release of 8.4 Postgres’s story around replication quickly became much better.

While replication is indeed very important, are users actually setting up replication each time with MySQL or is it to only have the option later?

Window functions

This is a feature those familiar with Oracle greatly missed in Postgres. In fact even SQL Server had some form of them, though it was with T-SQL, which adds a bit more complexity to the feature. This is a feature that once you have you can’t live without; the other options that existed before were slower and much more complicated. With the release of 8.4 window functions were added to bring Postgres on par with Oracle in this area. For more info on using them check the Postgres docs above or PostgresGuide.com.

Flexible Datatypes

Creating a custom column is simpler in Postgres than any other database I’ve used by far. Excluding custom datatypes, even Postgres’s out of the box datatypes make Postgres stand out far ahead of other databases. In particular the ability to create a column as an Array of some datatype. Want to store a game of tic-tac-toe in a database, or an array of 1 user’s phone numbers? It simply becomes a single column that can contain multiple phone numbers for a user.

Functions

Need to do some logic outside of standard SQL? Postgres likely has a function already available to do it for you. What about the time you wanted to take all rows returned by a query and combine them into a function? Give array_agg a look. Need to split a string and grab a part of it or some other string action, there’s a function for that.

Custom Languages

Want to use another language inside your database? Postgres probably supports it:

Extensions

Need to go beyond standard Postgres? There’s a good chance that someone else has, and that there’s already an extension for it. Extensions take Postgres further with things such as Geospatial support, JSON data types, Key Value Stores, and connecting to external data sources (Oracle, MySQL, Redis). I could easily have a full post on extensions available alone, fortunately someone else has already created an awesome one - PostgreSQL Most Useful Extensions.

NoSQL gives flexibility

I don’t want to get too NoSQL versus SQL debate…. no matter which side you fall on you can get both in Postgres. With hstore and PLV8 you’ll get the flexibility in your data that you would with Mongo along with all of the above features. Will Leinweber has a talk that he’s given at several conferences recently that highlights Schemaless SQL.

Custom Functions

Didn’t find the function you wanted in the above? Try creating it yourself:

    CREATE FUNCTION awesomeness(varchar) RETURNS boolean
	    AS 'CASE WHEN $1 == \'postgres\' THEN TRUE ELSE FALSE END;'
	    LANGUAGE SQL
	    IMMUTABLE
	    RETURNS NULL ON NULL INPUT;

Common Table Expressions

Often times when exploring data or creating a new view you’ll want to load data into a temporary table. When exploring data you only need this for a temporary time. Why actually go through the effort of putting it into a temporary table, especially if you only need it for a single query. Common Table Expressions let you accomplish just that.

Development Pace

For some period of time MySQL and Postgres were both moving at fast paces. In recent years though Postgres has rapidly picked up its pace of how much gets packed into a single release. Just take a look at the Major Releases.

Conclusion

Hopefully you’re convinced on why Postgres is a great tool. Next take a visit to PostgresGuide if you need some direction on where to start or how to use many of the above features.

If you’re looking for a deeper resource on Postgres I recommend the book The Art of PostgreSQL. It is by a personal friend that has aimed to create the definitive guide to Postgres, from a developer perspective. If you use code CRAIG15 you’ll receive 15% off as well.