Supports a large subset of all the things that make a modern RDBMS, including transactions, MultiversionConcurrencyControl
, procedural languages, and some freakish hack which is, for some reason, called "object-relational". The jury's still out on that one, as they are for most attempts by database vendors to create some weird dialect of the StructuredQueryLanguage
and stick the word "object" into it.
Users of MySql
tend to get in furious HolyWar
s. I think the proponents of MySql
are more practice- and sometimes hack-oriented, while the proponents of PostgreSql
are more principle- and sometimes ideology-oriented.
announced that they would make their own version of it and market it as the "Red Hat Database". They've shipped it for some time with RedHatLinux
Postgres originally didn't have a SQL front end, but had some weird proprietary thing instead. It has only been more recently (1995) that it got SQL. Also, it isn't fair perhaps the object relational bit is a freakish hack, although it's one I happen to like. But to make it sound like PostgreSql
did it in a "me too" sort of way is unfair since the original (pre SQL) postgres was where object relational was invented. The old IllustraDatabase
(which was then bought and used for parts for Informix) and CaIngres?
databases are both commercial forks of the old postgres project.
I don't know if I'm crazy enough to replace Oracle with it on any projects, but some days I'm tempted. :-) Since it grew up, a couple years ago, it's never failed me. -- MattBehrens
I've heard that PostgreSql supports TableInheritance. Has anybody found that this makes it more useful for storing objects in than other databases?
Table inheritance can be used to simplify some table structures. But it won't help with most of the problems of storing objects in relational databases. Potentially, it could even be worse, since you get two similar (but different) inheritance hierarchies to worry about. And you won't be able to migrate to other RDBMSes easily. -- AndersBengtsson
I think the only really good use for table inheritance is providing still one way to deal with (0-1):1 relations. The earlier solutions are cumbersome, separating the extra data into different tables will break queries every time you rearrange the tables and using probably-null columns for the extra data builds up clutter and sometimes breaks queries that don't explicitly list columns (but you shouldn't use those so it's not that important). -- PanuKalliokoski
Books available online:
This database seems so good that perhaps we should focus on its shortcomings. I mean seriously: why would you not use PostgreSql
? It won't be the ideal solution for every database need, so perhaps some folks who have gained a few scars could comment here.
- doesn't support prepared statements, nor precompiled stored procedure. That means it is very much unperformant in an environment where it has to do a lot of the same transactions.
- Also the client-server protocol is not on par with major commercial vendors
- It has very limited and awkward options of physical data layout
- Does not have the backup/recovery features of big databases (i.e. point in time backup, roll forward recovery, separate backups of different portions), backup is slow
Bottom line is that for high-end needs is not ready to replace the big 5 , but for small to medium businesses, POS, accounting, etc it has no match.
The client-server protocol was overhauled at some point in the 7.x series, enabling prepared statements and other goodies.
Version 8 was released 1/2005 with many fixes and improvements. In addition pgAdmin III is a very slick tool for administering the databases. Replication is being added on as an extra tool as well, I just hope it works better than SQL Server replication. What excites me the most is the GIS integration, of which I can only think of Oracle as having anything like it. It may not be an Oracle killer, but at leat until Yukon is shipped it is probably capable of replacing SQL Server (and more secure as well).
Since the above list was written, prepared statements and stored procedures have also been added; built-in support is provided for the latter to be written in plPGSQL, C, Python, Perl, and Tcl, and there are third-party modules for half a dozen other languages.
Regarding back up options, you can do online backups of different objects within a database, or different databases within a cluster, without taking the database offline. Also version 8.0 also added point-in-time recovery so it has that as well too. It's still new, so it might not be on-par with some of the commercial vendors, but its getting closer.
It is up to version 9. It is the default choice of database for CubicWeb