Postgre SQL

Posted on June 20, 2011



PostgreSQL supports all of the features you would expect from an open source RDMS, including many that are found in commercial databases (Oracle, DB2, SQL Server) such as:

  • The basics: viewstriggersindexesforeign keys, ACIDity, transactionsquery optimization, comprehensive SQL support and data typesautovacuum (keeps your table statistics up to date).
  • The not so basics: Features that may not be seen on other DBMSes include reverse, partial and expression indexes, table partitioning, table inheritance, cursors, data domains, user-defined operators, arrays and regular expressions.
  • Procedural Languages: analogous to Oracle’s PL/SQL or SQL Server’s T/SQL, PostgreSQL supports internal procedural languages for when you need to get down and dirty with the data. Additionally, it supports a wide range of languages including Ruby.
  • Rules, which pretty much allow you to rewrite an incoming query. A typical use of Rules is to implement updatable views.
  • Multi-Version Concurrency Control: MVCC is how PostgreSQL (and other DBMSes) deal with concurrency and table locking. In practical terms, it allows for database reads while the data is being writen.
  • Write-Ahead-Log: the WAL is the mechanism by which PostgreSQL writes transactions to the log before they are written to the database. This increases reliability in the unlikely event of a database crash, as there will be a transaction log by which to rebuild the database’s state. PostgreSQL includes many configuration parameters to tweak the behavior of the WAL.
  • PostgreSQL scales up by efficiently using multi-core servers. It also sport an asynchronous processing API. Subselects are fast, you can refer to tmp tables more than once in a query and it can use more than one index per query, making it suitable for data warehousing applications as well.
  • tsearch2, which is PostgreSQL’s highly efficient full text search component. If you are committed to PostgreSQL, this is a very high performant search engine for PostgreSQL (as an alternative to solr orsphinx, for instance), with the added benefit that you’re not running a separate daemon or search service.
  • PostGIS for geospacial objects.
  • There are many replication options, although non of them are built into the core. This will change very soon, though.
  • You can tweak its brains out: open up postgresql.conf, and you’ll find many configuration options that can be tweaked to your application load and server capabilities. If you’re like me, this is lots of fun. I will say, however, that it will take time to understand many of the options and how they affect each other.
  • Excellent documentation, which not only goes through the basics of setting up and using PostgreSQL, but really gets into the details of the inner workings of the system. This is an invaluable resource, not only for day-to-day development but also for tweaking the database’s configuration files.
  • Much more in the contrib packages.

postgresql wiki




MYSQL Vs Postgresql

Posted in: Database