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

Last week I did a post on the many reasons to use Postgres. My goal with the post was two fold:

• Call out some of the historical arguments against it that don’t hold any more
• Highlight some of the awesome but more unique features that are less commonly found in databases.

Once I published the post it was clear and was immediately pointed out in the comments and on hacker news that I missed quite a few features that I’d mostly come to take for granted. Perhaps this is due to so much awesomeness existing within Postgres. A large thanks to everyone for calling these out. To help consolidate many of these, here’s a second list of the many reasons to use Postgres:

Create Index Concurrently

On most traditional databases when you create an index it holds a lock on the table while it creates the index. This means that the table is more or less useless during that time. When you’re starting out this isn’t a problem, but as your data grows and you then add indexes later to improve performance it could mean downtime just to add an index. Not surprisingly Postgres has a great means of adding an index without holding that lock. Simply doing CREATE INDEX CONCURRENTLY instead of CREATE INDEX will create your index without holding the lock.

Of course with many features there are caveats, in the case of creating your index concurrently it does take somewhere on the order of 2-3 times longer, and cannot be done within a transaction

Transactional DDL

If you’ve ever run a migration had something break mid-way, either due to a constraint or some other means you understand what pain can come of quickly untangling such. Typically migrations on a schema are intended to be run holistically and if they fail you want to fully rollback. Some other databases such as Oracle in recent versions and SQL server do support, this. And of course Postgres supports wrapping your DDL inside a transaction. This means if an error does occur you can simply rollback and have the previous DDL statements rolled back with it, leaving your schema migrations as safe as your data, and your application in a consistent state.

Foreign Data Wrappers

I talked before about other languages within your database such as Ruby or Python, but what if you wanted to talk to other databases from your database. Postgres’s Foreign Data Wrapper allows you to fully wrap external data systems and join on them in a similar fashion to as if they existed locally within the database. Here’s a sampling of just a few of the foreign data wrappers that exist:

• Oracle
• MySQL
• Redis
• Twitter

In fact you can even use Multicorn to allow you to write other foreign data wrappers in Python. An example of how this can be done, in this case with Database.com/Force.com can be found here

First a huge thanks to all organizers of conferences, but especially for those that organize not-for-profit conferences. I do understand its a great amount of work, and in nearly all cases have greatly appreciated the experience made available by the work they put into it.

As for some guidance. I’ve been on nearly all sides of the conference with the exception of organizing, so again organizers please don’t take offense to the feedback.

First on timeline

Please publish this early on your site and on lanyrd

• Deadline for CFP
• Deadline for Call for Sponsorship
• Publish speaker list
• Early bird registration ends
• Regular registration ends

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.

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.

Update the talk for this is now viewable on YouTube here

When I first came across Django I was an immediate fan. It featured:

• Good documentation
• Steady but stable progress
• Community around apps which encouraged DRY

I’ve been a user off and on depending on my needs for nearly four years since discovering it, and throughout that time all of the above have remained true. However, as I’ve worked on and encountered more complex applications there’s one thing that has time and again broke down for me, which is the Django apps model. It hasn’t broken down due to Django only though, I’ve seen it break down in Ruby (Rails), Java, .Net, take you’re pick of language or framework.

The breakdown of this model is due to several things:

Heroku’s latest Cedar stack supports running anything. Heroku’s officially supported languages actually have their buildpacks public via Heroku’s github, you can view several of them at:

• Python Build Pack
• Ruby Build Pack
• Scala Build Pack

There have even been some created as fun weekend hacks such as the NES Rom Buildpack.

I’ve recently been amazed at the number of companies that are still using a VPN or other means to manage their apps/network. Not just large enterprisey companies, but small agile startups. I fully understand that it works, but 95% of these places are also using another key tool for access inside their company… Google Apps. I fully expect companies to use google apps, its more of the former that surprises me most. For a long time OpenID wasn’t at a usable point, even today it still isn’t without its faults. However, it does make for a much cleaner workflow once in place than having your users login to something with they’re used to using elsewhere.

In our personal lives we use email as our keys to the kingdom. In fact I now almost refuse to sign up for any service that doesn’t let me use oauth, so why should a work place be much different. So I inquired with a few companies to see if they were fine with securing things like documentation or wiki’s being google auth, they indeed were. Yet they still seem to have users keep one more username and password for their VPN to be able to login to access internal docs/tools.

I alluded in earlier posts of How Heroku Works that we have talented engineers. In fact I would venture to say that there is not a weak link when it comes to our engineers at Heroku. Ensuring we have talented engineers makes it easier for us to find other talented engineers and maintains a level of quality in our product. This means we must be very careful about not diluting our pool of engineering talent, which is where our hiring process becomes especially key. By the time we hire a new employee, we know without a doubt they’re a fit within our organization.

Our goal in hiring is seldom to fill a role, but more commonly to find more talented people share our goal (changing the world for developers).

In my earlier post on Teams and Tools at Heroku, I mentioned how we value engineers’ time; their work has enabled us to build a great platform. As a result of what we’ve built, we’ve had great growth both of our platform and of our teams internally. With that growth inevitably comes different distractions on engineers’ time. Despite how a manager may plan things, engineering work needs long periods of uninterrupted time. To ensure that no matter what, an engineer has plenty of opportunity to do the work he or she was hired to do, Heroku has Maker’s Day.

So we walked through setting up your virtualenv and installing some packages. The basic workflow for installing packages will work 95% of the time, however part of the time you will need a little more. Below are several cases that may require extra effort. Its likely worthwhile to skim these and only reference them when needed as they likely wont be part of your everyday workflow (with the exception of using mirrors).

Most packages you install should be on pip and an actual released version of a package. There are times however when you may need to test out a package that is still being worked on. For these cases there’s what developers commonly do and what you should do.

Heroku is a largely agile company, we work in primarily small teams that talk via api and data contracts. Its also a company comprised primarily of engineers, even product managers often write code. Heroku as a platform drives many of the features not from top down, but from bottom up based on engineers desires or skunkworks projects. There’s many valuable insights into how Heroku runs efficiently for engineering.

I’ll be diving into many various practices that enable Heroku to put quality engineering above all else, but first let me highlight the team structure and tools that enable this.