PostgreSQL is an open source object-relational database system with a strong reputation for reliability,data integrity,and correctness since it implements the SQL standard very well.
It runs on major operating systems,including Linux, UNIX(AIX, BSD, HP-UX, SGI IRIX, Mac OSX, Solaris, Tru64),and windows.
My personal preference for PostgreSQL to MySQL are:
- It implements the SQL standard very well.
- It includes support for “advanced” SQL stuff like window functions or common table expressions.
- It is very innovative in terms of how plpgSQL interacts with SQL.
- It supports lots of advanced data types, such as(multi-dimensional) arrays, user-defined types.
- It supports all sorts of performance optimisation that you’re used to from Oracle, SQL Server.
- Postgres is “better SQL”–more standardsy, with better query planning so you can follow the relational model more closely if it suit you.
The drawbacks to PostgresSQL today are:
- Its behind MySQL in terms of popularity.
- Since its not as popular as MySQL its a bit harder to get community support.
- Replication is not as well implemented as in MySQL.
Both databases(PostgreSQL and MYSQL):
- Have very good tooling (MYSQL Workbench vs. PostgresSQL pgAdmin111).
- Have companies behind them if you need professional support(Oracle and Percona vs. EnterpriseDB).
- Are proven and mature in production.
- Are used by major players(Google Cloud SQL, Pinterest, Facebook, Twitter: MYSQL/Amazon Redshift, Instagram:PostgresSQL).
Few Lesser known facts about PostgreSQL Features:
- PostgreSQL has multi-value fields(arrays,nested tables) which can reduce the need for joins and dramatically increase the performance of storing and retrieving “multi-dimensional” data structures.postgresql.org
- PostgreSQL has a very rich data type system and allows user-defined data tpes and composite data types.www.postgresql.org/docs/9.2/static/rowtypes.html
- PostgreSQL also has class based inheritance between tables so that for example “customer” and “suppliers” tables could both inherit common properties such as triggers,columns etc. from an “organizations” table. A select on customers would return customers,obviously,and select on organizations would return customers and suppliers referrencepostgresql.org
Posted by Rufus Nganga