Hubstaff has helped me find talented content writers that I can work with on my own terms. As I need more remote freelancers it will be the first place I look.
When you’re building a small database, you might opt for something like MySQL. But when you need serious power for an enterprise-class database, you need something bigger. You need PostgreSQL.
If you’re familiar with SQL, you know that it’s all about getting information out of a database. PostreSQL, also known simply as Postgres, is the same, but it’s made with very large databases in mind. And some of the world’s most popular big databases are built with this language; both IMDb and Etsy use it for database administration and management. A number of US government agencies have built their sites on it, too.
Postgres is an open-source language, which means you can download and run it for free—something to keep in mind if you’re already spending a lot of money on building your website. It’s also very flexible, as it supports many extensions and commands in multiple popular programming languages.
It also includes a number of features that make it more powerful than traditional relational databases, allowing developers to create a wider range of functionality. PostgreSQL.org puts it succinctly, "[Postgres] offers the flexibility to specify what you store, how you store it, and the ability to define new ways to search through it."
While most websites will function perfectly well with a standard relational databases, people looking for more power and the ability to work with massive amounts of data will find that PostgreSQL will meet their needs.
One of the big advantages of PostgreSQL over some other relational database languages is that it’s fully SQL-compliant. MySQL, for example, isn’t completely SQL-compliant. The advantage of maintaining compliance is that databases constructed with Postgres will have an easier time integrating with other SQL-compliant databases.
Having won the Linux Journal’s Best Database award five times, Postgres comes with strong commendations.
It’s worth noting that Postgres isn’t necessarily the right choice for any database project. It does give you some extremely useful database administration and management options, but that power comes with a cost. There are significantly fewer people who are skilled in PostgreSQL than in more popular database languages like MySQL. And because the language is more complex, you might find that hiring someone to build a database in this language costs more. It might also be more difficult to find hosting services that support the language.
That being said, if you need a big database that packs a lot of power, you can’t go wrong with Postgres.
As you’ve probably gathered from the above description, the PostgreSQL vs. MySQL question largely comes down to the requirements of your project. If you need a lot of customization when it comes to data types, queries, and the other low-level attributes of your database, Postgres will give you more options. That’s just how it’s built.
MySQL doesn’t offer that level of customization, but in its place it offers some notable benefits; it’s easier to set up and maintain, there are more developers and more hosts that support it, and it’s better for databases that primarily run high volumes of read actions.
The fact that MySQL isn’t fully SQL-compliant may push you towards Postgres if you plan on integrating your database with other SQL-compliant databases. If you’re not planning on those integrations, though, MySQL will work just fine. And it’s certainly no slouch—many of the world’s largest companies use MySQL to power their databases.
MySQL has many interfaces to choose from, as well, which can be nice for developers. There are even a number of graphical user interfaces (GUIs) that can be used, enabling non-developers to easily act as a MySQL DBA.
If you’re not sure which of the two you should use, there’s a good chance MySQL will be a good fit. Of course, consulting a developer or DBA who’s familiar with both is always a good idea.
The default interface for working with Postgres is psql, an interactive terminal that’s included with PostgreSQL installation. It doesn’t provide a whole lot of features, but if you’re familiar with other terminal applications, it can serve as a good way to get to know the system.
There are many other interfaces that you can use—and many of them provide more robust functionality. Database browsers, query tools, interface builders, and tools with other views and functions can make it much easier to work with Postgres. If you want to get to really understand how the language works, though, psql is a great place to start.
Once you’ve installed PostgreSQL and fired up psql, you’re going to want to run a few commands. Here are a few of the basics:
Here are a few options that might be useful, too: