PostgreSQL is a RDBMS that many claim is better than MySQL. This is basically the only Open Source competion MySQL has. There are other Open RDBMS but they are not in the league of MySQL and PostgreSQL.
PostgreSQL is a open source object-relational database server(database management system). It supports many advanced features that are not in MySQL.
Features of PostgreSQL
- This feature allow blocks of code to be executed by the server. Functions in PostgreSQL can be written many languages like PL/pgSQL, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/Tcl and more.
- An index is storing some columns of a table separately to allows quick access to the table. This makes the index smaller than the original table (due to having fewer columns), and it is optimized for quick searching. Most RDBMS supports this.
- A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit access to data. In addition to calling functions written in the native PL/PgSQL, triggers can also invoke functions written in other languages like PL/Perl.
- PostgreSQL uses Multi-Version Concurrency Control (MVCC), which gives an user a copy of the database, allowing changes to be made by one user without being visible to the other users until the change is committed. This also lets you 'roll back' to an older version of the database if necessary. This is similar to the Version control systems like CVS or Subversion.
- This feature lets tables inherit the properties of a parent table. Data is shared between parent and child tables. For example, adding a column in the parent will cause that column to appear in the child table also.
- Referential Integrity
- PostgreSQL allows column constraints, foreign key constraints, and row checks.
- Other features
- And many more features like...
- Full, inner, left and right joins
- Encrypted connections via SSL
- Point-in-time recovery
Another open source RDBMS is SQLite. This provides no serious competion for MySQL or PostgreSQL as it tends to a different need. It is a a small C library that implements a self-contained, embeddable, zero-configuration, single file SQL database engine. More about SQLite in the next post.