Explain PostgreSQL Like I'm Five

What differentiates Postgres from other SQL DBs?
Can you include simple examples?

Comments

[Dian Fay]

Out of the most popular relational databases Postgres is the only independent open-source project. MySQL is owned by Oracle, and SQL Server is proprietary; MariaDB is still a little new on the scene. This makes it something of an underdog and enterprise adoption has been smaller-scale and slower; if an approved technology list has three relational database management systems (RDBMS) on it, Postgres won't be one of them (yet!).

Postgres is as good as any of its peers and imo better than most at the business of being an RDBMS. For purposes of trying to settle on one without an established preference, you'd want to know about their level of adherence to the SQL standard and the various non-standard features each provides.

To give an example where adherence to the standard lapses, take SQL Server's implementation of TOP (as in SELECT TOP 1 thing FROM table). The ANSI SQL standard does not recognize this syntax, accepting only SELECT thing FROM table LIMIT 1 instead. Or there's MySQL's limitations on cast types. I don't know exactly where Postgres falls relative to the others on conformance but the docs identify two unimplemented areas of the standard, and my impression is that it's among the more thorough implementations.

Postgres has a lot of extensions to the standard and/or going above and beyond its competitors. Some of them are immensely powerful, like the foreign data wrapper system which allows you to query anything you can think of as if it were a table by implementing an API. You can even use Python with the multicorn project. Others are extremely convenient, like real UUID types (implemented by most) or array fields (also found in Oracle). Some let you manage complexity better by doing bizarre things to your data model, like table inheritance (a table capitals inherits the fields of a table cities and can be inserted into independently; if you query cities, you also see rows in capitals). Its JSON support is also very good, although this is an area that's been seeing a lot of activity across the board lately.

[rhymes]

I feel like, at least for MySQL, the JSON support appeared after PostgreSQL implemented it :-)

Another couple of things PostgreSQL does really well: utf-8 (don't know why but for a while the default utf-8 implementation in MySQL was limited to three bytes) and managing and querying geographical data.

[Dian Fay]

Yeah, I've been looking into MySQL's JSON stuff in the past couple weeks and it definitely seems to be playing catch-up. Postgres hasn't solved the problem space entirely, but MySQL has a smaller function suite, no binary JSON, no direct indexing, and no lateral join. You can't even get a real boolean out of a JSON document in-query because MySQL doesn't have a boolean type and can't cast to tinyint.

[JeffD]

I worked with Postgres for PostGis extension: it add spacial object to make map like OpenStreetMap - A great and well documented project.
(This kind of extensions may exists in other SQL-DB)

[Salim MAHBOUBI]

Thanks a lot! Very Informative!

[Kasey Speakman]

Postgres is probably my favorite SQL database. It is quite versatile and solid, and I wish I had tried it much sooner. As it was, my first SQL database was MySQL. I used it for years. Then once I started diving into relational features I started to really dislike MySQL. I stopped using it a while back and it has improved since then, so I cannot really offer a comparison with Postgres at this time.

One other database I am quite familiar with is MSSQL. Here are my top comparison points between the two.

Things MSSQL does better

Admin tooling

SQL Server Management Studio is free now and is one of the best pieces of software to manage a SQL database I have ever used. In addition to solid data/schema manipulation tools, it has neat things like intellisense, showing graphical query plans, break-pointable query debugger, visualization tools (i.e. generate an ERD).

Declaring variables in SQL

In MSSQL you can declare variables at the top of queries. like this:

DECLARE @SomeVar INT = 42;
SELECT ... WHERE SomeField = @SomeVar;

This seems like a simple thing, but it drastically improves the test/debug workflow for queries. Parameterized queries are best practice to avoid SQL injection attacks anyway, so the queries I execute from code always have @-variables in them. Then it becomes really easy to test a query by copying it verbatim into a query window, and just adding DECLARE statements at the top to run it.

In Postgres tho, I have to manually replace all the variables with actual values to test the query. This is more error prone, and may require modifying the query to make it work. Example: having to use ANY for parameterized queries versus using IN when testing it. Or various casting issues (you can't use cast operator :: with parameters). Which means you may not be testing exactly the query you are really running.

Name casing

An minor irritation of mine with Postgres is that it basically ignores your chosen casing for identifiers. It lower cases everything. So CREATE TABLE StopPing creates a table called stopping when you later look for it in the schema. (You can still query for StopPing and it will match the table.) If you want to preserve cases, then you have to put quotes around the name. However, then you must forever refer to that table or field with quotes in every statement. Which is very annoying.

MSSQL does a better job here. It preserves the casing you chose for display purposes. (Any table or field listing shows the casing you used to create it.) However queries against those things are still case-insensitive. So I can use camel or Pascal case for my identifiers and match them up with the same kind of casing I use in code. Whereas Postgres seems to encourage you to use snake casing in the database which is at odds with the casing I use for code objects.

Things Postgres does better

Full text search fields

MSSQL has full text search, but it is a separate service. And it has some operational requirements (care and feeding of service, rebuilding indexes, restarting after crash, etc).

Whereas in Postgres, you can just add a full text search column to a table. You do have to take care and update the column whenever you change data that you want to search on. Then when you do a full text search, you are comparing your search to the full text column. To me this workflow makes a lot of sense versus having a separate service with ops dependencies. This ends up a great value proposition: a semi-google-like search which is very easy to setup. It is probably the most-missed feature when I work with our MSSQL-based system.

JSON support

Recent versions of MSSQL have limited json support. JSON data is stored in normal text columns, and there are 5 functions you can use to query/modify data as json. Postgres has both a text and a binary json format (jsonb) which should be a bit less expensive to query/index/store than the text format. There are also a ton of great functions for working with json data in Postgres. Like removing keys with null values.

I have seen several articles condemning using Postgres as a "document database". However, we definitely store some things as json documents because that makes them much easier to work with versus relational tables. But so far we have not indexed on json content, just the document ID. (Even though Postgres does support indexing on json contents). If we need to index on content, we will generate a separate relational table for that. But it is great that we have the choice of both in one database.

Postgres also has hstore for key-value storage.

I am sure there are a lot of other trade-offs people could point out, but those are my top ones.

[Salim MAHBOUBI]

Thank you !!!

[Guido DiPilla]

AS a noob I was drawn to this article for what its title claimed it would discuss - "Explain PostgreSQL Like I'm Five". Although good points were brought up in the comments as well, it did not live up to its billing... Click bait??? Almost...

[Dian Fay]

I don't take "like I'm five" literally most of the time, and especially not when the question addresses a specific implementation like this one. If it were "explain relational databases like I'm five", then it might be time to break out the story about Princess Architecta and her marvelous castle analytics. But Salim already has sufficient knowledge of what a relational database is and does to want to hear reasons he should choose Postgres over other offerings. A purely reductive high-level view wouldn't be helpful.

[Ian Kirker]

"Explain It Like I'm Maybe 18 or 19, I've Heard of Things But Have Very Little Experience"

Not very catchy. :/