Postgre Sql
Postgre SqlSupports 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 and PostgreSql tend to get in furious HolyWars. 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.
Recently, RedHatTheCompany 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.
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.
November 2010 It is up to version 9. It is the default choice of database for CubicWeb.
July 2014 9.4 is about to come out and I really think the question is worth revisiting... why would you *not* use Postgres at this point? Other than a few relatively niche features (like the option for clustered indices) and extremely high-end RAC situations, there's not a lot left it can't do.
When you install MySQL, it just works. You don't instantly get into an argument with it over user permissions. The M in LAMP stands for MySQL because it's so easy to install-and-forget. And my LAMP project has recently upgraded to PostgreSql, even before we started using its GIS extensions.
["When you install MySQL, it just works." Except for CHECK constraints, of course. :-/]
{Perhaps they meant it's fscked up consistently?}
Funnily enough, last time I installed MySQL I did get into an argument with it over permissions: I wanted to have more control over them than it could give me.
[Indeed. Pretty much every time I use MySQL -- which, thankfully, occurs far less frequently than it once did due to increasing recognition of superior alternatives like PostgreSQL -- I get into an argument with it over permissions and have to issue some dire swarm of GRANTs before it will cooperate.]
{If enough people have the same opinion, then consider forking MySql into SaneSql or the like with better default settings. If it's mostly just configuration settings that are the problem, then it shouldn't be a big effort, relative to coding new features, overhauling the index engines, etc.}
[It's not just configuration settings; it's how the MySQL security model works.]
[As for a fork... Like MariaDb, you mean? Fixing the permission irritations would result in a mostly-MySQL that is incompatible with MySQL, and fixing the other limitations (like the CHECK constraints that parse but don't work) would possibly result in further incompatibilities. I see no reason to fork MySQL (or MariaDb) when there are already superior alternatives like PostgreSQL and FireBird.]
It was MySQL users complaining about configuring PostgreSQL. Wouldn't a better suggestion for consideration therefore be forking PostgreSQL to MyPostgres or the like with better default settings?
[That doesn't need a fork, just an I'm-used-to-MySQL-so-give-me-what-I'm-used-to post-installation script to set permissions to what MySQL users expect.]
Sometimes working with two devils you know is more productive than working with one devil you don't know.
[And sometimes it isn't.]
The devil of that decision is in the details :-)
EditText of this page
(last edited July 31, 2014)
or FindPage with title or text search