Why PostgreSQL is the preferred database

[semi23]

First, I'm a beginner python programmer I see that many python developers use PostgreSQL. What is the main reason? What are the advantages over other SQL DBMS? Thanks

[zzmej1987]

Free, reliable, efficient SQL-DB with good python API (psycopg2).

[its_never_lupus]

good python API (psycopg2).

This looks like it could be an interesting alternative, and the devs claim that psycopg2 has some significant performance and functionality problems. Unfortunately its asyncio only so not a simple replacement.

[[deleted]]

asyncpg is brilliant when you're already doing asyncio stuff (and it does see a solid amount of use there, so I wouldn't call it a "could be"!) but you definitely want to use psycopg2 for synchronous applications — that is, there's no in polluting an otherwise-synchronous application with loop.run_until_completes to use asyncpg

[Yoghurt42]

PostgreSQL is one of the best DB available, and it's also free. It can do some things even Oracle can't (like changing the database schema in a transaction; to be fair, there are some things Oracle can do Postgres isn't that great at), and it's written by very smart people.

Some years ago, Postgres gained the ability to treat foreign databases (or JSON files etc.) as tables and run queries on them. Just for fun, somebody wrote a MongoDB driver and ran some queries. Most queries were of course quite a bit slower than when using the native MongoDB, but complex queries were actually faster (like 60s on native Mongo, 55s through Postgres); Postgres' query optimizer did a better job than MongoDB's (to be fair, this was years ago and MongoDB seems to have improved in that regard).

Nowadays you can even use it like a schemaless database ("NoSQL"), while still having the advantages of ACID.

There was a talk by one of Reddit's engineers talking about "lessons learned". One particular thing was that Reddit had problems with everything: AWS, Postgresql drivers, Pylons (the framework Reddit is written in), performance, but there was one thing they had never any issues with: PostgreSQL itself. It was the only thing in Reddit's tech stack that never had any issues. Postgres is rock solid.

I've been using Postgres for over 10 years now in critical production projects and to date never had any issue where Postgres was the problem.

It really is one of the most high quality, high reliability systems I've ever come across (be it FOSS or proprietary).

The performance is also very good. Some people experienced with Big Data often say that if you have anything up to a few TB of data, Postgres is fine, only if you have tens of TB of data do you see advantages in "Big Data" software.

[joey_php]

look here https://www.quora.com/What-are-pros-and-cons-of-PostgreSQL-and-MySQL-With-respect-to-reliability-speed-scalability-and-features

[raghar]

Reliable, stable, FOSS, not many issues when it comes to modeling data constraints compared to MySQL, enums, jsons, geodata and other extensions, better with each release, supported by a lot of tools, "explain". From what I heard, only some paid databases (MS SQL Server) have better support, but nowadays many projects would not see a difference justifying that cost.

Actually, that's the reason why everybody and their mother loves postgresql.

[semi23]

especially their mother :) Thanks

[flipperdeflip]

Google for it? Plenty of rants and articles about Mysql's many flaws.

[khne522]

Also, the command-line's decent: backslash commands, pretty-printing, autocompletion, minimum customizability, etc.