Should one use Postgre?

Out of curiosity and also as a way to better understand where MySQL fits in the database space, I have a 3 basic questions:

1. What are you using Postgre for? (Web serving DB? Backend database for a custom application? other?).

2. What do you like most about Postgre ? (speed? price? open source?)

3. What do you think are the reasons not to use Postgre?

Thanks,
Nick

1. I use PostgreSQL for websites mainly since that is what I do all my work in. These sites are both intranet and internet and include a few e-commerce sites. It can be used for anything that requires a db though.

2. What I like best about PostgreSQL is how it scales to meet high demands, has a great windows based admin tool (pgAdmin II) and is a very robust database that I can trust with my data. It also is quite reasonable speed wise.

3. If you are in a windows environment, PostgreSQL can only compile using Cygwin which can be very messy. MySQL has good windows support. But otherwise there is no reason not to use PostgreSQL.

When i started using it a couple of years ago it was the only Opensource DB that had proper data integrity and foreign keys. It was the only real relational database. I did use MySQL for a while but its lacking of those features is what caused me to ditch it. It does have those now, but the speed of the database drops to below the speed of postgres when they are used. Also it still uses table locking which is a shocking way of designing a DB. Ever since I have used it PostgreSQL has had its row locking in place. It seemed the perfect solution and it still is.

Hopefully in 7.4 they will release a Win32 binary of it. From my last discussions with a couple of the developers it looks very likely.

Andreas,

Thanks. Quite interesting.

So, when you say that you could potentially use Postgre SQL for any database purpose, why do you think some many people still spend up to $60K per CPU license for Oracle? On the other end of the spectrum, why did you discard the options of using a commercial database product such as MS SQL or Sybase, and went to Postgre instead? Was that only cost?

for really high end database apps you may have a reason for going with Oracle. Oracle can be used for extreame high end cases where nothing else will work. It scales the best out of all the databases.

as for MS SQL or Sybase, they were not even considered. Deep down these 2 programs are very much the same. The cost of MS SQL is very high and there is no community support for it like there is for PostgreSQL. Also it is from Microsoft, who are renouned for their security holes. The recent SQL Slapper worm that infected heaps of MS SQL servers is a definate sign as to why i wouldnt even consider it.

I see... it is surprising though, that you mention there is no commnunity support for MS SQL... I believe Microsoft has forums like this one, no? I am just wondering if you need to choose an open source product in order to get community support (implying that the DB vendor can't provide that forum to its customers), or if an inexpensive commercial product could have those advantages too.

Nick

There is some community support for the commercial solutions but it is nowhere near the level that you find for opensource. Since it is opensource people get more involved in the software and are able to have so much more input into the product. The whole opensource community is quite unique and i dont think a commercial vendor will ever be able to achieve that sense of community.

>> Ever since I have used it PostgreSQL has had its row locking in place.

PostgreSQL does not use row level locking. It has a feature better than row level locking called MVCC (Multi-version Concurrency Control). MVCC allows concurrents writes to occur in separate transactions. There's a sophisticated way to resolve conflicts. See:

URL

Another reason you don't see a lot of community support for commercial products is because a lot of what you pay for is support. When an open source user has problems, s/he must either read the manuals or ask somebody. If you pay for something, you can [usually] ask them (at least for a short period of time).

A bit late to the discussion but:

1)I use it for both personal websites and it is the main DB used at work for web based related stuff. I'm pretty comfortable with it, but haven't gotten really into the guts of it (pl/pgsql, advanced triggers, domains, etc.), but am using the features that it has over mysql (unions, subqueries, foreign keys)

2)Besides the price, stability and feature set, the community seems to be very strong and helpful. The pg mailing lists are very helpful with experts and the pg developers alike responding quickly and friendly. I have very rarely seen ego and elitism rear its head within any of the mailing list discussions, which isn't always the case especially when dealing with a large group of bright people.

3)The only reasons I wouldn't use postgres would be:
-If I had a website on a 3rd party host and didn't want to pay more for the hosts that support postgres
-If a contracting job stipulated I use another DB
-I wanted to use some 3rd party software that used another DB and it would be either too hard (or I am being too lazy) to port the backend to postgres

In regards to people using the high priced db systems, I'd say in many cases that's more of a comfort level for the non-technical people that are the ones making decisions than an actual difference in quality of the product or technical support. In my experience large, rich companies usually don't act in the quick manner you think you deserve when paying the big bucks if your company isn't large and rich as well.

-b

All of the above. So far, I have been very happy with PostgreSQL. I still use MySQL for certain smaller jobs, especially when they need to be portable to other hosting providers, but for any serious applications, I use PostgreSQL. By the way, a.koepke mentions that MySQL now has relational features, but they are still very minimal compared to a real RDBMS. Referential integrity is only the beginning of what a relational DBMS should do. Views, constraints, triggers, stored procedures, schema, domains, custom datatypes, etc... are all advantages of PostgreSQL and Oracle which you won't find in MySQL.

I use it for all of these reasons, but I think you left out one important aspect: functionality. PostgreSQL has more data management and constraint functionality than just about any DBMS besides Oracle. Also, it is very stable. I have never yet had a crash, even when I threw ridiculously complex queries just to test it out.


It's still not quite as mature as Oracle, DB2, etc... in the area of replication, failover, etc... Also Oracle's procedural language and features are more powerful than PostgreSQL's (at least, according to one Oracle/PostgreSQL admin I know)

I use postgres for many projects. I choose it because of its extensive functionality set. For example, I use subqueries a lot, which makes my page code a lot simpler than it used to be.

I would use Oracle for certain large, very high reliability applications. It has the best feature set and it has the best support for redundant synchronized database servers. The price is a large barrier unless you're a large company.

I recommend MySQL a lot, also. It doesn't have all the features of postgres, but it has excellent documentation and is a bit easier for simple applications.

MS SQL isn't as fast or reliable as MySQL and it's more expensive and more vulnerable to bugs and security issues.