Slashdot Mirror
Top 5 Reasons People Dismiss PostgreSQL
Jane Walker writes "In an effort to dispel some of the FUD surrounding this impressive product, this article puts forth several of the most commonplace reasons for a user to dismiss PostgreSQL." From the article: "While PostgreSQL's adoption rate continues to accelerate, some folks wonder why that rate isn't even steeper given its impressive array of features. One can speculate that many of the reasons for not considering its adoption tend to be based on either outdated or misinformed sources."
MySQL is pre-installed by most webhosts, and does the job for most tasks.
First post?
...coauthored an excellent book on PostgreSQL that was just published by Apress. The title makes it sound like it'd be a bit light, but it takes you all the way up to writing stored procedures, writing C programs that hit the database, using all the utilities, and so forth. I'm using PostgreSQL as a Jabber backend and the book has already proved useful.
Too bad they didn't talk about hitting PostgreSQL from Ruby... but since most folks are using ActiveRecord to do that, it's probably not a big deal. And if you use the Ruby/C client, it's quite snappy.
The Army reading list
Really? Has anyone actually gotten PostgreSQL to work with Crystal Reports? The article claims this, but I've run into all sorts of issues trying to get data from PostgreSQL into Crystal relating to types and stored procedures. Crystal Reports themselves won't tell me if they support PostgreSQL, and I've tried numerous times to call them on it.
Titus Barik
"Postgre" is three times as long as "My".
Then again, the P in LAMP has always been about the scripting language, not the database.
MySQL and PHP have been quite the dynamic duo of the internet.
That, and PostgreSQL took longer to have a native Lose32 port.
The fact that you can bring Python right into PostgreSQL for good stored procedure justice seems to go unnoticed.
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
Most complaints I hear about it have to do with that vacuuming thing and clustering issues. ...and speed of course.
Never used PostgreSQL though
*ducks*
READY.
PRINT ""+-0
Indeed. And once most people are familure with MySQL and the various tools and language support, there tends to be little reason to switch. PostgreSQL is a better database product, but many (all?) of the features that it's cheering section continue to tell us all about whenever the issue comes up, are simply not ones that the majority of MySQL users want or need. Maybe PostgreSQL fans should target Oracle usres.
"Who are in control, they are not in control of anything - they don't even control themselves!" - Glen Beck
I think first and foremost is that is web developers who don't understand SQL, and so go about happily re-inventing its functionality in their web apps.
99% of the problems that web developers face have already been solved for them, but they think that SQL is just a data dump, and thus see no reason to use Postgres, because they think that MySQL does everything they need. In reality, their apps would be faster to write and easier to maintain if they used SQL features.
It's kind of like perl-syndrome, but on a larger scale.
The biggest reason I've found personally why people don't use postgres is because they've never heard of it. Everybody and their dog has heard of mysql, but I've never found somebody who knows about postgres who isn't actually using it. mysql, for whatever reason, just has better marketing.
g -else". If I want a toy database, I'll use sqlite. If I want a real database, I'll use postgres. There really isn't much room in between the two.
Why that is I'm not entirely sure, since ever since I discovered postgres, mysql has been relegated to the role of "use-only-when-a-stupid-web-app-can't-use-anythin
Dlugar
Computer Go: Writing Software to Play the Ancient Game of Go
Perhaps I have to fill out a form on the website?
I don't hear those reasons when people dismiss PostgreSQL. The ones I hear are:
Their database (MySQL, Access, Oracle, whatever) is working for them good enough to justify switching. Me? I'm using PostgreSQL, and I won't switch, even though Oracle now has a free version. Too much work to fix something that's not broken. And while I might never be able to use MySQL in my main project because it lacks some features I really need, it's good enough for lots of people.
please excuse my apathy
None of those five had anything to do with why we can't use it. Postgres's replication options are niche afterthought hacks. This immediatly makes it an unacceptable choice for anyone who's reliability or performance needs exceed that of one server. Which is pretty much any system where the cost of downtime is non-trivial.
Reason #6: Mysql
For instance, database manipulation is done directly via the command line (yes, screw the shiny GUIs). You run a program that somehow sets the environment and retains the password somewhere establishing a session or something like thant, and then you use other programs that take SQL as parameters. Table creation from the command line is downright unnatural. In MySQL you have a contained enviroment, the MySQL client, and you have special (non-standard) SQL to deal with the database. It took me almost a week to get this concept difference and it was very frustrating.
Some concepts also do not translate easily for MySQL users. Postgres terminology for certain things is completely different (academic, some might say). There's a learning curve, for sure.
For people coming from Clipper, DBase and Access, MySQL "feels" more natural than Postgres. If they're given a choice, they'll stick with MySQL until someone comes up with a book like "Postgres for MySQL users". Until then, oh well.
"Better" is a pretty subjective term, clearly it wasnt "better" when it came to appealing to a lot of people like mysql was. I suspect that the reason mysql has caught on so well was because the learning curve was so low. Postgres always had more features that DBA's cant live without, subselects, triggers, stored procedures, etc. And yet mysql still caught on faster, so the better question here was really what does "better" mean.
Exactly how do you pronounce it anyway?
Waiting for ad.doubleclick.net...
Nowadays PostgreSQL is a full featured database, better and more functional than MySQL in some areas. However, they lost the race early on. They were slow to develop and the early versions didn't work that great.
I like to compare it to Unreal versus Quake. Unreal was delayed for years while they created this VM and scripting language and made all this object-oriented ivory tower crap. Meanwhile there where several Quake versions which if you look at the code are very hackish but worked very well and had awesome performance (JC is not an architect, he's a code monkey and a good one at that). The Unreal engine never really took off until just in the last few years (combination of the end of Quake and finally creating a fun slightly different game where the Unreal engine can do it's thing [UT2003 and up]). Lots of wasted money there with the Unreal engine, I wonder if they have even broke even from the money dumped into it. And the problem is, nowadays the engine looks very dated. All that work that took too long and now the technology has changed.
It has to do with mindshare and previous history. Way back in the day... 1997, postgres was difficult to setup for some people. It was not the default choice included in many setups at ISPs. If you wanted to have an interactive web application at an ISP, on a unix machine, the most common option was MySQL. (On a windows machine it would be an ODBC connection to an access database, or a MS SQL server) Once something has achieved a significant mindshare and some momentum it is difficult to overcome. (But not impossible, especially if you do a better job, just takes time)
Speed isn't everything but some of these are insane.
Why do people dismiss PostgreSQL
It's because LAMP sounds so much cooler than LAPP!
"Nine times out of ten, starting a fire is not the best way to solve the problem." - my wife
Crystal Reports can connect to an ODBC data source and psqlODBC is the official PostgreSQL ODBC Driver.
Yes but it doesn't suck as MUCH as it did!
I want to learn database programming for my java course and for personal growth. I wanted to learn postgresql as I was aware for years its a real database unlike mysql 3.x. But 5.x is about finished and most distro's come with 4.x which come with alot of features postgresql have.
But for me windows support and mysqladmin are easy for non mission critical stuff like my home pc. So I am starting with mysql and I will learn postgresql by this summer.
http://saveie6.com/
At OSCON, the Postgres people had postcards on their table of whatever their mascot is (I forget) roasting a dolphin on a spit over a fire.
Funny yes, but not something that inspires one to take them seriously.
Ironically, they have a better product on many levels, but that kind of zealotry just plain puts me off.
-- Trinity in high heels carrying a whip: The donimatrix - there is no spoonerism
I have pretty simple requirements for a database, I don't need triggers, stored procedures, or any of that stuff. What I need for web applications is a database that I can efficiently search, and that means fulltext indexes. Sure, there's plugins for Postgres that add fulltext indexes, but they require ungodly complex setup and tuning. With MySQL it's two keywords.
From what I understand "Post-gres-que-ell"
I was a big fan... until I needed to use PostgreSQL 7 for a real (commercially available) product. To call it slow would be an abomination of the word. Slow doesn't even begin to describe b-tree insert times. Yes, I tuned the engine and dropped indexes at the appropriate times. Yes, my data structures were relational. Yes, this contradicts some published benchmarks. My use is real world and in reality, PostgreSQL is slow... and a bit buggy.
... (SELECT A INNER JOIN B) INNER JOIN ..." But the crashing is tolerable. Hand-holding the query optimizer is not. Quite often, the optimizer gets the query plan wrong. Sending special commands to disable internal features is often the only resort.
Nested parentheses in SQL can cause an engine crash. " like
While it's true that PostgreSQL is more database than most corporate weenies need, it falls down in moderate write environments. It's best used for systems that write data very infrequently, otherwise it fragments quickly. The only solution to table and database fragmentation is dump & reload.
Vacuum is asinine. Any command that needs to be run periodically under threat of complete and total data corruption should not be. That's right. Only PostgreSQL makes you vacuum or else your transaction ids overflow. This is modern? I'm shocked.
PostgreSQL is to Oracle what Gimp is to Photoshop
#1 because we're lazy ass sys admins who learned mysql first and don't want to bother learning another software package that does more or less the same shit . sad . true .
Stupid as it sounds, I don't think most people can intuitively pronounce "PostgreSQL" (I know I can't). It's much easier to say "My SQL" and not risk sounding (or feeling) like a dunce.
Check with the marketing folks - this kind of thing is really important when it comes to general acceptance. When it rolls easily off of the the tongue, it's more likely to be discussed.
There's a Starman, waiting in the sky / He'd like to come and meet us, but he hasn't got the time.
PostgreSQL is not necessarily the easiest beast in the world to get going. A few years back, I converted a chat/gallery portal system Ethereal Realms (http://www.erealms.org/ from MySQL to PostgreSQL, since at the time it was felt that features like proper referential integrity and stored procedures would really pay off.
The code was shortened considerably, was more stable overall and the OpenBSD port compiles properly without threading issues. However, despite all of those advantages and the database server being on a bigger server with more memory performance suffered considerably.
Want a good starting place in settings? The default documentation does little if anything to really help you on the matter, its like trying to learn the English language solely through the use of a dictionary.
There are tutorials available, some out of date and while Usenets can certainly help, you'll get wildly varying answers because some of the configuration options are more black magic then science. Rather makes it hard to get started when you don't know exactly where to start or how increasing a value will really affect performance as a whole. You are expected to load test the database before implementation which is not always possible for small hobby sites, and it can test patience.
With MySQL you had a few configuration files designed for use in various environments and it would show you how certain settings had changed. This is not the case with PostgreSQL in fact 32 connections is the default which is painfully inadequate for most peoples needs when dealing with a site. I'd personally love to see an application that detected your memory and other settings and came out with sane settings, at least with such an option you'd have a place to start.
Queries were slow, but then that was supposed to be MySQL's strength. Solution? Run explain/analyze on everything and tweak the hell out of every single query being generated. If you don't necessarily understand how the query is analyzed and run by PostgreSQL then there must be something wrong with you!
Vacuum? That concept alone can throw people in for a loop, especially when designing a system which is meant to be run by people with no technical experience. So you have to code in a serious amount of intelligence into the application or rely on Auto-Vacuum (not available at the time) which can slow performance down even more.
Because of vacuum, I had to design a process for the site to lock out all users. This had never been required under MySQL and took a while for the users to get used to. In certain cases, if the lock-out failed, the vacuum would cause permanent locks in tables which would quickly pile up scripts on the webserver side leading to extreme high loads or just crashing the machine.
PostgreSQL has a LOT of features and a lot to offer in general. However, there is a major learning curve required to get it going right. I've had other sites implement the code and whenever they hit the version which required PostgreSQL most gave up on the idea of migrating or complained endlessly on how things seemed sluggish. That is NOT a major selling point when trying to get the unwashed masses to adopt your product.
If you're doing something on Solaris 10 that doesn't need you to pay out the ass for Oracle, you can get PostgreSQL supported by Sun.
e +PostgreSQL/2100-1014_3-5958850.html
http://news.com.com/Sun+backs+open-source+databas
500GB of disk, 5TB of transfer, $5.95/mo
My company has a semi-realtime application that needs to insert ~200-2000 rows/sec constantly. It isn't true realtime because the db can be shutoff, rebooted, etc and the inserts will queue waiting for the server to come back online.
Approx 1 year ago, we were doing some enhancements on the application and I tried replacing the mysql backend with postgresql. We needed plenty of expert help tweaking postgres to get it to a point where it could keep up with the inserts, however we could not run the vacuum job. There was no way to measure the exact performance difference between mysql and postgres, but I estimate mysql (innodb) to be able to handle 5x the load of postgres.
Why would I want to use a DB server that can only handle 20% the load of a competitor?
Maybe more people could use it for new development. But once you have invested in a backend database platform it is a pain in the a** to change it to a different provider, takes a lot of time, and in the end may provide no business value.
One thing I know that's missing from Postgres is the relative lack of advertisement or PR. I realize that it's an open source project, but it's something to consider. In my field (landscape ecology), ESRI gets most of everyones business, including spatial databases. It's unfortunate that so many people shell out a truckload of money for ArcSDE when they could be using PostGIS, a free extension of Postgres to allow for the storage, querying, and manipulation of spatial data. Plus, it easily imports the industry standard shapefile.
PostGIS is gaining momentum in my field, but it, along with Postgres, needs more advertisement. When I started learning Postgres, I was a little leery. I had thought it was going to be incredibly complex and arcane, but I was pleasantly surprised. PostgreSQL just needs to get the word out.
Per Square Mile, a blog about density
Too hard to pronounce.
fak3r.com
i've read that PostgreSQL has a version that runs on SCO.
1. Lack of administration tools
Having been forced to work with Oracle before they had a usable GUI (It can be argued they still don't) theen MySQL Server, I learned to appreciate a database GUI. I've grown to *HEART* mysqlcc, and more recently mysql-administrator, mysql-query-browser, AND phpMyAdmin. Wake me up when the same are available for PostgreSQL AND they are bundled with major distributions like the MySQL tools are. Oh, and they need to WORK, too.
2. Familiarity
When I switched BACK to Windows without having touched Linux for 5+ years, the apps we initially standardized on use MySQL as the back end, many of them exclusively so. MySQL seems to be more ubiquitous in the OSS world, despite its license being less-free than PostgreSQL
3. Time
Who has the time to investigate or extend PostgreSQL, and why bother when there is MySQL? I've read up a little on PostgreSQL and I like its feature set better than MySQL, but I'd have to spend time learning about administering, backing up, restoring, configuring, and tuning it properly. I've already put that time into MySQL and right now I need to learn the ins and outs of asterisk on top of my usual workload. MySQL is running just fine, why switch now? When we develop an app for distribution which would not meet MySQL's requirements (e.g, requiring us to GPL the product), THEN I will put time into learning PostgreSQL.
The Christian Right is Neither (Christian nor right). See: Matthew 23, Matthew 25, Ezekiel 16:48-50
When I started programming a website, I knew I needed a database. I also knew absolutely nothing about php, sql, or even what they stood for. I was using a Perl based hacked link farm that used a flat-text database storage. Someone then pointed me to a php link farm that used MySQL. The installation text that came with the app was so easy to follow for a newbie, I had the link farm up and running in no time. I went to Books-A-Million a few weeks later, and found many books on PHP, MySQL, php/mysql - and nothing on PostgreSQL. When I finally did read up on RDBMS, found out that PostgreSQL did some functionality that MySQL didn't (at the time); I already had most of my site designed in php/mysql. I looked more into sub-queries in PostgreSQL, but the community structure was so scattered and non-newbie friendly, I decided to stick it out with MySQL (and havn't regretted it once). So my reasons for preference have nothing to do with wanting a windows version, different language, or other such assumption. Instead, my reasons are:
* as everyone says, the name is catchy: MySQL
* when I first was introduced to it, and to this day, Michael 'Monty' Widenius takes a personal interest in his work, and is a real down to earth guy ( had the pleasure of emailing him once) [you can probably still see him posting on the mysql dev lists these days..though I havn't followed it in a couple of years]
* Extensive script language support for web development
* Books for newbs and professionals (many books)
* I like their website more..always have
My shallow reasons..
My Thoughts, Kyndig
The reasons listed in TFA are nowhere near why I don't use it (granted, I've only used databases as toys thusfar).
A few years ago, I decided to learn a DBMS and teach myself SQL. I tried Access because it's "user friendly." Call me crazy, but I felt it was anything but. So I tried Postgres because everyone spoke so highly of it (and I'm very comfy with the command line). I read a lot of documentation and did a lot of things that felt like "progress" before I gave up.
I picked up MySQL next. It had some quirks, sure, but it was maybe an hour before I was comfortable enough with the DBMS that it didn't stand between me and learning SQL.
I picked up Postgres again last year and got much further along with it. I actually made a database, and it had tables and everything. I gave up because everything just "felt" more complicated than in MySQL.
I really want to learn Postgres. I do. I'm convinced it's more powerful and flexible. I just don't have the time, patience, or need.
Both MySQL and Postgres have their quirks that make it so you can't just jump in and start playing with SQL, and that sets the bar higher than it needs to be. Sure, every product will have some such complexity, but the lower the bar, the wider the userbase.
"Avoid employing unlucky people - throw half of the pile of CVs in the bin without reading them." -- David Brent
They forgot reason #0 -- MySQL is "good enough". Most everyone who's done web development is used to MySQL, it works 99% of the time, why would they switch?
Recursive: Adj. See Recursive.
Well, in my case, I dismiss PostgreSQL because in fact remains true: -
It does not have a programmable front-end with which a novice like me can program business logic into! Don't mention the likes of Navicat, PhpMyAdmin and others for they do not cut it! And by the way I do not use MySQL either.
For sure Microsoft's Jet Engine with Access as the front end, has and continues to do wonders on this front. My recent project was to have the largest tables with no more than 8,700 records and to have no more than 11 tables in all. Putting business logic to the database in anything other than MS-Access was very very tough for me. In Access, it took me just three weeks.
Nine months on, the application is still running fine.
The Postgresql Community is superb. I've received a huge amount of help in #postgresql on irc.freenode.net . I cannot say enough about them
EnterpriseDB (based on Postgresql) has a nice new logo too, which hints at something.....
Coincidence? I think not!
May the best RDBMS win.
Funny. Being a ColdFusion developer (since 1996..), I'm mainly a windows person, with a bit of Mandriva XP - I run a Linux server at home with a few little things on it. Now, the psql application is one of top reasons why I like postgres, which I've been using with CFMX for the past couple of years.
For me, the ability to quickly document tables with the \d function is just awesome - extremely quick, no messing around. So I much prefer running a putty window with PSQL to MS' enterprise manager.
One "stumbling block" was that Postgresql required installation from source on Mandriva 2006 / Silver club licence, whereas mysql was prepackaged. I was supposed to be able to use some RPMs but it was a RPITA and I couldn't get it to work - just tons of conflicts.
It was sort of nice to do the from source installation though; it required a bit of research and will probably help those linux skills coming slowly along. It all worked well; not a single hitch with it either. Only thing is that Coldfusion people still need to use the latest 7.x JDBC libs - the newer ones won't work.
ISO certified == THX certified
...because I don't know how to pronounce it.
Is it "Post Grace"? "Post Grey"? "Poss Grey"? "Poss Gres"? "Progress"? "Platypus"? "Post Raisin Bran"?
Whatever it is, it sounds vaguely French, which is just suspect to begin with. And I'm not dredging up the whole Iraq/UN thing either, although if I have to invoke Freedom Fries to make a point, I've got the mayonnaise ready.
Give me a RDBMS that I can pronounce, and I'll use you in my software.
MySQL. Easy. "My SQL". Doesn't get much easier than that, plus it sounds sorta friendly.
MS SQL - same thing, slightly different spelling. Maybe not as friendly, but you can put it in a Powerpoint to your boss and not sound like an idiot.
Oracle. Now you're talking. Even has a bit of mistique to it, a bit of enigma.
DB2. Not as sexy, but still undeniably pronouceable.
Sybase. Sock it to me.
What PostgreSQL - however the hell you say that - really needs is a new name. Forget features, forget marketing, forget RDBMS death match performance comparisons. Nobody cares. MySQL lacked tons of features for years, and we all used it then and continue to use it now. Why? You can pronounce it. Simple.
FWIW, I have had very good experiences running postgres in astronomy applications, including for of order millions of galaxies with of order hundreds of attributes in the Sloan Digital Sky Survey. For scientific applications, open-source is a must, because (a) you have to be sure that the db is doing what you think it is, (b) you have to be able to rely on support or self-maintainance into the asymptotic future, and (c) (usually) you end up having to make small customizations.
Point (b) is especially big: in the Sloan Survey early days we had a proprietary database with our only copy of some of our data; when the company went belly-up (I think is was Objectivity), our data was effectively "encrypted" in whatever proprietary internal format was used by Objectivity and we had no way to reverse-engineer it, and no-one to call.
On point (c), try calling Oracle or Microsoft and asking for customizations that astronomers want. Evidently they don't consider us an important part of their market!?
David W. Hogg -- assoc prof, NYU Physics
Try this on a table with a couple of million rows
select count(*) from tablename
or
select count(fieldname) from tablename
This is incredibly slow as PostgreSQL scans the entire table! I know there are work arounds that will return approximate but this isn't good enough. I keep hearing how it isn't possible, that the table stats can't be updated etc... but other DB's handle this extremely fast.
I love PostgreSQL but I won't recommend it to Clients yet.
Many internet users are novices and do not understand how to utilize search engines.
Slower than MySQL ? Don't have any benchmarks, but I always hear this.
the reason I chose MySQL over PostreSQL is Windows support, there was no reason for me to downgrade to Linux just so I can use a database, but I see that's no longer the case, I think I'll give it a try eventually.
pronunciation
Although where I work we would like to use postgresql, we do not because it does not support case insensitive queries like mysql, sql server and sybase.
Now, about a year ago, I had a client that wanted a web site back-end written. Now, I wasn't sure what the future of that site was going to be, whether I was going to be involved, etc. I also knew that it would be probably be run on inexpensive shared hosting solutions.
Guess what I chose? MySQL and PHP. The reason was because those are always available. It gives my client the flexibility to move it to any hosting solution. PostgreSQL simply is not everywhere. In my case, I run my own servers and can afford to have to understand it. But my client needs a hosting solution that does all the work for him (including back-ups). There's something to be said for using "the standard".
And you know what? I originally chose PostgreSQL because it was ACID compliant, but I have to say that MySQL sucks a lot less than it used to. It defaults to tables that support commit/rollback. It supports sub-transactions (which PostgreSQL v7 doesn't support, not sure about 8). It (FINALLY) supports sub-selects. If you're still turning up your nose at MySQL, it really isn't as bad as it used to be.
Sometimes it's best to just let stupid people be stupid.
I looked at autovacuum, but we don't use it. We have customers with quite busy databases (we're talking several hundred queries per second, 24/7, with probably 5% of those being INSERTs or UPDATEs), and a VACUUM at the wrong time can cause problems. We prefer to time out VACUUMS for when the DB is relatively quiet.
My top demotivator for the change is the inherent weird feel of using PostgreSQL. Call me flamebait, but the problem is that it is just not MySQL.
... for others it probably isn't enough better to make the effort worthwhile.
My top demotivator for the change is the inherent weird feel of using Linux. Call me flamebait, but the problem is that it is just not Windows.
No, the "problem" (if you can call it that) is people that are simply comfortable using what they've always been using. Making a switch to a new technology will require considerable effort and in the case of these two products a substantial learning curve. Is PostgreSQL sufficiently "better" than MySQL? For some, sure
The higher the technology, the sharper that two-edged sword.
Installation
Good installation documenation with Postgres is pretty sparse. It's not too complicated but it's not easy to find answers. This mainly includes how to properly setup pg_hba.conf which is vague at best on how to configure.
It might be better in newer installs but in RHEL3 I was just scraping along.
Application Support
As mentioned there are some great apps, but there are just are not many applications supporting Postgres, most web apps are LAMP (with M being very much in represntation). I think it would help Postgre if there is a comprehensive PHP-PGSQLPHP-MYSQL conversion equivelants document/tool to help developers either to transition or at the least open up the cross-platform support for multiple DB engines.
Documentation
Recently there have been a growing number of updated books on Postgres including those which work along with PHP, so that situation is improving, the books I had to work with were circa 2000 or earlier before schema support.
So, yes, I tried it, for a while, almost got there, but I just wasn't achieving as much progress as I had hoped. Maybe later I'll go back when conditions get better.
Keep up the good work, I'll be watching.
"Enjoy what you're doing! If it becomes drudgery, you're doing it wrong!" - Jim Butterfield
I'm seeing alot of people citing a reason of low postgres adoption is that 'its confusing to setup' and 'administer' (or it just feels weird) compared to MySQL.. I just have to ask.. what kind of crack are you guys smoking? To me, PostgreSQL administration has always seemed neatly packed into easy, concise and well documented config files like any other *nix service, with a couple of straightforward command line utilites. In contrast you have MySQL whos admistration functions are taken care of by a seemingly random chaotic array of oddball mysql commandline programs whos number is only dwarfed by the amount of built in functions in php. How anyone can think mysql adminstration is more sensical is beyond me. There at least it seems there is a rhyme and a reason behind postgresql administration functions as opposed to mysql where the developers thought it was a good idea to add a whole new command line utility for everything feature that could have been taken care of with a command line parameter. After that it comes down to SQL, which PostgreSQL is the obvious winner (at least it was as of 4 years ago.. its been about that long since ive done web development). A lot of people say mysql is "good enough".. mabye I'm behind on the times, but back then there was NO WAY i would have considered mysql for any project at all givin its limited SQL functionality. Your web apps ended up being twice as big because youd have to hardcode database logic into the program instead of letting SQL do the work for you. You could barely call MySQL a relational database.. it boggles my mind that people love it so much.
*shrug* not me. Find me a comparable product with table inheretance and I'll consider it. Until I see one, the other DB's are pretty much lacking.
All's true that is mistrusted
One "problem" with PostgreSQL is that it assumes actual competence on the part of the administrator. The default ./configure ; make ; make install is designed to create a system that will actually start up on as many platforms as possible. After that, it is up to the competent administrator to tune it for the specifics of the installation. Using the default PG install in a performance comparison demonstration shows nothing but the incompetence of reviewer.
Have a 2 CPU AMD64 box and 16 GB RAM and fast SCSI drives as your dedicated DB server? Fine, make your settings appropriate for that. Running on a shared P200 with 128M RAM and a slow IDE drive? Different tuning entirely. I have production systems at both ends of the scale.
I am extremely happy with PostgreSQL. It's robust as hell - I've had individual PG connections to the DB up for over a year. On rare occasions I've had a machine running PostgreSQL get unceremoniously killed but every single time when the machine has been restarted, PostgreSQL has started up without any problem. This is not always the case.
~~~~~~~
"You are not remembered for doing what is expected of you." - Atul Chitnis
What are you talking about??
Of course you can make case-insensitive queries in PostgreSQL:
SELECT foo, bar FROM table WHERE lower(fritz) = 'blat';
You can even make indexes on lower-cased versions of your column(s) (for example) if you want the query to use an index.
Most people that I've worked with don't know the importance or value of the database features that MySQL didn't include before version 5. Even with the arrival of MySQL 5 they insist on using MyISAM tables. Most people don't care about solid reliable data warehousing. They care about simple tables that will make their forums run. If they didn't use PostgreSQL before they will not use it now that MySQL has a competitive feature set.
;-)
If you want to make people switch to PostgreSQL explain to them that since it has a BSDish license, instead of a dual licensing scheme like MySQL, using it might be 500 bucks cheaper
Cheers,
Adolfo
Yeah, MySQL handles it really well... by giving you an approximation!
___
If you think big enough, you'll never have to do it.
Two of PostgreSQL's biggest problems: Very little documentation that mere mortals can read (if they can even find it), and a rude, elitist cheering squad. The product my be the greatest thing to hit Open Source since RMS, but most people who need a database (usually for web dev, but yes, often for "real" applications as well) will never find out about all of PostgreSQL's golden features.
"Who are in control, they are not in control of anything - they don't even control themselves!" - Glen Beck
I do not trust that the contributions I make by learning it and using it and answering other people's questions will be a wise investment in the future. It's quite possible that some random company will manage to market and promote their closed version of PostgreSQL to the point where it squeezes out the free as in speech versions, and then my investment will be effectively lost.
Need a Python, C++, Unix, Linux develop
The database has improved a lot since then, and I currently support two Postgres databases, one on Linux and one on IRIX, both running in mission-critical situations. What that means is that if either one fails, I get phone calls in the middle of the night with complaints. In over 6 years, I have not fielded one phone call attributable to Postgres itself.
None of the issues raised in the article was even remotely a factor in my choice of Postgres. A big (and ultimately deciding) factor in its favor is that it can be compiled and installed on a broad range of hardware and operating system versions. MySQL fared less well in this regard.
Why do people keep using C#, Java and C++ despite the fact that both Lisp and Smalltalk are simpler, more powerful and have better programming environments? People hate to change, so they stick with what they know. Also, more support might be another reason; MySQL is a lot more popular than PostgreSQL, it's easier to find a host that supports it, a buddy that knows it, etc.
Where are the analytic functions??
I know for our workplace one of the reasons we don't recommend PostgreSQL is because of all the FUD. It is sad because PostgreSQL is an amazing product.
Postgres did not have a native Win32 version.
You could use Cygwin, but that made it a pain to install, so no one bothered. Then they found MySQL. It 'just worked' and they started telling their friends. People aren't going to switch unless it's worth the pain.
Hint: For 99.99999% of people using a database, it will never be worth the pain.
Postgres has a better license than MySql. However, I didn't like it
for a few reasons:
* installer won't work on FAT disk volumes on Win32 (requires NTFS)
* SELECT performance much lower than MySql. Most websites use SELECT than any other command
* MySql has better documentation, is very easy to install and use.
I don't like MySql's license though...
"The best songs are always on the B side of the record"
Simplicity appeals to the masses,
Functionality appeals to those who need it.
I am all for using it, but will my employer want to use it? Oracle, Microsoft and DB2 are all free (with lots of crappy limitations) now so why bother using postgre unless you want to save money. Most companies seem more intent on staying in ruts and not changing anything. I worked for a non-profit, they were replacing the crystal reports server with custom web applications (charging others to use) and getting rid of the crystal entriprise server (and license). I had to get the darn thing working on a 9i database rather than the native 10g. I had to laugh when the DBA messed up and deleted 2 months worth or work with a few delete table .... the whole application is stored in the database weird shite.
Table names (and perhaps even column names) have to be in ALL CAPS. Sorry can't name a table PersonContact, you must use PERSON_CONTACT.
Guess it's just my aesthetic pedanticness but I do not like ALL_CAPS except in my C constants. It also makes any kind of automated conversion from another DB that much more annoying.
A bad design decision that's never really been fixed. I believe it is possible to get UpperLowerCase table names in Postgres, but you have to jump through so many error-prone hoops, it's not worth the trouble.
Reason number 6 is the damnned Postgres zealots that feel the need to bash everyone else's database rather than promote their own. I use MySQL and Postgres on a regular basis. I'm proficient in both. And to the dismay of Postgres users everywhere, there are times which *gasp* MySQL is better suited. "Oh, you are probably a lame programmer and use it for trivial web stuff". Not true! I look at a project and each databases strenths. It has nothing to do with the seriousness of an application. When I was writing VoIP billing software, we'd sometimes see 4-5 million CDR's (call detail records) in a single day. Our first iteration actually used Postgres and choked on that many records. We had to make some compromises with MySQL. We had an additional field for Unix epoch time because of MySQL's lacking (at the time) date and time math. There was a tradeoff. It was deemed that having billing invoices generate in 5 seconds (as opposed to 5 minutes) was more important than programmer time. Welcome to the real world. Another project I had was for writing worker punchcard system. Six months of records only topped out at 50,000 records and we decided Postgres' procedural languages would be a great help to us. Lose the zealots and attitude and maybe you'll have a greater user base.
If an officer ever threatens to taze you, say you have a pacemaker.
The top reason is the exact same reason *most* people use MS Windows instead of Linux. There is more 3rd-party software that runs on or even requires MySQL and that is more compelling than the fact that PostgreSQL is better in most other aspects.
Faster -------------------- More features
SQLite ----- MySQL ----- PostgreSQL
Need fastest speed? Use SQLite 3.3.4 or something similar
Need most RDBMS or ORDBMS features? Use PostgreSQL 8.1.3
Need compatibility with most packages? Use MySQL 4.x
insert into table set field=42;
ERROR: parser: parse error at or near "set" at character
When I have a table with a lot of columns, I don't feel like counting fields. This allows far too many bugs into queries (oops you got rid of a value on accident! oops you transposed two on accident...etc...). This problem is mostly taken care of by modern DB packages, but still...
(Disclaimer: I have but the smallest experience with Postgres.)
Did you ever notice that *nix doesn't even cover Linux?
I don't find support for PostgreSQL in all the other OSS products I use. Last time I checked, I couldn't integrate a PostgreSQL database with a semi-complex DB-backed mail system based on Postfix/Courier-IMAP/SpamAssassin/Amavis/Courier-M aildrop/etc. And it is odd to me that if PostgreSQL is becoming more popular in the OSS developer world that there are not more PostgreSQL integration options with these kinds of tools.
For me, that's always been the barrier, even though, having come from the Oracle world before I sat down with MySQL (and at the time thought it was a piece of $&#!@), PostgreSQL seemed technically superior. However, as I wait around for better OSS support for PostgreSQL, MySQL has made a lot of strides, especially with the InnoDB storage engine, in terms of more complex functionalities (transactions/nested queries/etc) and more enterprise-like reliability and features (clustering, replication, etc).... so, while MySQL may not be Oracle yet, I am finding fewer and fewer reasons why I will need to change, even if PostgreSQL finally becomes more widely integrated into other OSS apps.
(Maybe the subject should start with "Not enough")
But whatever - let 'em remain mystified and befuddled if it keeps the signal-to-noise at a convenient level.
"Our interests are to see if we can't scale it up to something more exciting," he said.
Vacuum kills performance. Some uses maybe OK with loosing 50% or more while VACUUM runs. In some uses it's unacceptable. In our case (a lot of inserts with majority of selects going for the newly inserted records) performance degrades within 6-8 hours after running VACUUM & friends. VACUUM takes ~20 minutes to complete which is completely unacceptable during the day and we can't delay it till night.
No, AUTOVACUUM is not an answer because it kicks in unexpectedly and makes random queries run unexpectedly slow at unexpected times. Usual VACUUM makes all queries run slow at predetermined time. Not a very appealing choice.
More reasons:The biggest reason I choose MySQL over anything else is speed...for the simple stuff...which is most web based apps...it's the fastest option...plus there's room to grow if you need to...
Ease of replication...MySQL is real easy to set up when it comes to replication, not only is it easy, but it's full featured...
Availability...it's available almost everywhere you go (most default installations of Linux and a good number of proprietary unicies...binary packages are available from MySQL for AIX, Solaris, MacOS, BSD, HP-UX, and Novell)...there are many software vendors that support MySQL as a backend database...not so many that support PostgreSQL...this is all handy when I'm trying to get a new application up and running...it's gotten so I don't even have to think about wetting up a new MySQL installation...
I would like to add that I run 2 PostgreSQL servers...but I have found it to be overly cumbersome for the majority of my applications.
Until then, I've got little reason to double up on SQL server software.
Most people avoid Postgres because they are totally ignorant and are going with the popular flow no matter how ugly it is. They've jumped on the MySQL bandwagon with no regard for what they are missing.
... I know one dumbass who spent $8000 for SQLServer based on a lie from the Microsoft salesman who told the dumbass that Postgres can not in any way handle Triggers! The fool couldn't be bothered to ask me or even to spend 2 minutes at postgres.org. Then there's other people who think your shop has to have the big name software or else you won't have any credibility with your clients. Hmmm ... have smart clients who get from you a cost effective and powerful product at a good price ... or have stupid clients who's money passes from you to the Database salesmen, leaving less for you. Which do you prefer?
... so here's my 2cents.
... it was a nightmare, no wonder people didnt use it.
... remember that you are the people.
Heck do you want ignorance?
I haven't seen much about the windows world in this thread
I am not proficient in Linux. It took me two weeks of spare time to get postgres with the PostGIS spatial engine up and running properly. The pathetic typos in the configuration scripts, the dumbass instructions that contradicted the contents of the files they described
Then, about 1.5 years ago, a Windows installer came out for Postgres and PostGIS and it all changed.
Literally 5 minutes later I was adding data to my spatial database and learning how to use the powerful spatial query functions.
Sure MySQL does have some brutally weak spatial abilities, but its a joke compared to what PostGIS can do.
Suddenly Windows users got to make the equivalent of an ArcSDE backend for their UMN MapServer websites, instead of spending $50,000 on ArcIMS with Oracle / ArcSDE. Heck I can build a few such sites for less than what the software costs to use the ESRI / Oracle crap.
The moral of my story and main reason for people avoiding the current, powerful, fast, spatially enabled postgresql is that people are stupid. Disagree?
George Bush + Linux = "I will not let information get in the way of the fight against Windows"
Yes it was a pain that Mandriva removed postgresql from the cd-sets, but the cure is fairly simple (at least as a silver member). As root: urpmi.addmedia "club.club_x86-32_2006" https://dl.mandriva.com/rpm/club/2006.0/i586/ with hdlist.cz urpmi postgresql-server Should do the trick.
You've obviously never had a close look at the seams.
http://outcampaign.org/
I have seen mysql pushed to its limits and it worked. Mysql is funny, we tweaked things a lot, found out it doesnt like to join more than 5 or so tables, beyond that it doesnt want to use indexes. This may have changed in recent versions.
BUT, when dealing with mysql, I cursed up a storm and became a violent man figuring out its little quirks. With postgres I'm very calm, the universe makes sense and I get things done without wanting to take an AK-47 and kill everything in sight.
One issue I have with postgres is its partitioning support. Yes that's great that they added it but what we need is a global index, THEN we're talking oracle-quality database. It's mostly there, but that added step would be absolutely huge.
I'm going to try slony soon so I hope to hell that scales.
2 years and no mod points. Join reddit. Because openness is good.
missing alternate indexes, no optimization run can bring down both DBs to the knees. After a lot of inserts, to bring the indexes up to date and have them actually usable you need to run vacuum for postgres. I haven't used mysql on such huge data, but from even simple 100.000 - 100.000 table joins I know that with some good alternate indexes you can speed up things enormously.
"Freiheit ist immer auch die Freiheit des Andersdenkenden" - Rosa Luxemburg, 1871 - 1919
One of the great features of Postresql is Multi-Value Concurrency Control (MVCC). In a nutshell, readers never block: "querying a database each transaction sees a snapshot of data (a database version) as it was some time ago."
If you have a single, long-running write transaction (e.g. a batch process), and many short-running read transactions (e.g. serving web requests), this works very well. When the batch process completes, readers "atomically" switch to the newly-committed version. This (drastically) simplifies the batch process, since you don't have to worry about readers blocking or seeing inconsistent state. (Things get more complex in the many-writers scenario, however.)
I don't think MySQL has this feature. (Please correct me if I am wrong.)
Last time I tried PostgreSQL on a "simple website", despite the claims it ran at half the speed of MySQL. Of course we all know MySQL is 'cheating', or that PostgreSQL might scale better, which might be interesting for 0,01% of the web sites out there. Also, only 0,01% do need the power of Oracle or PostgreSQL, unlike developers at Slashdot, who always will tell you to run a "real database" like Oracle, or PostgreSQL.
People should be more thoughtful when they name things.
How do you pronounce it?
Will people stare at you with raised eyebrows when you stutter forth an attempt to mention it?
"We should use Post...postgreesc... post-grayscu... Myesscueell"
Funny, when I saw Zawodny at OSCON, IIRC the gist was that Yahoo used MySQL where it needs scale and Oracle where it needs superior data integrity?
m l. html (interesting discssion of how Livejournal scales:
More:
http://jeremy.zawodny.com/blog/archives/000593.ht
http://jeremy.zawodny.com/blog/archives/cat_mysql
"LJ relies pretty heavily on caching nowadays. None of the stuff in MySQL was quite what they needed, so they built memcached. Used by LJ, Slashdot, Wikipedia, others use it now. Original version in Perl, now written in C. Lots of O(1) operations inside make it quite fast. The client can do multi-server parallel fetching (kick ass!). They run multiple instances on boxes with more than 4GB RAM. They have a 90-93% hit rate on the cache."
My point is that the tool is not always the problem. Now if C++'s integer arithmetic had an issue, that is another story, but the programmer simply was not good.
;-)
No, the tool IS part of the problem in such cases. If the programmer was not that good at C then C was the wrong tool and thus part of the problem. The programmer should've taken the time to study up on C, or picked a different tool. There are times when the tool is not appropriate for the job--you probably shouldn't use C if you need to do heavy text processing and need to get the job done fast (use Perl instead), or if you are less experienced and want a language that supports sound object-oriented programming maybe try Python, etc.
MySQL was not designed as a robust relational database, and its creators didn't seem to be intent on making it so, or else they'd have designed it differently. It was designed as a very quick and quite dirty SQL frontend/ISAM backend system to support small, informal databases (or so it seems): Basically, its heritage is to be like the old Ashton-Tate dBase but using SQL to query the tables. Since then it has lost that focus and now we have large websites storing millions of records in mySQL.
MySQL is a great tool if used as intended, however it definitely IS a problem if your accounting system uses it for example. People started doing crap like that and complained about mySQL's lack of features, thus we have things tacked on like innoDB tables and such to add this robustness.
PostgreSQL was not always as super-robust as it is now, and in its present form its source code is probably almost unrecognisable from 10 years ago, however its architecture was more sound and thought out from the start, as its heritage was as an academic project. Its challenge was not to add features as was the case with mySQL--PgSQL was designed for extensibility. PostgreSQL had to catch up in performance and stability, which it has done in spades.
Personally, I always use UNIX timestamps (seconds since 1970). They can be directly added, sorted, and converted into any timezone, and its very portable. But thats just me. (Yes, UNIX timestamps do nothing before 1970, etc, etc).
It seems somehow wrong that your business logic has to perform low-level validation of basic datatypes, and it is cumbersome and error-prone to deal with unrecognisable representations. Only the geekiest of geeks could tell me whether 1984293617 falls on a Thursday without runing it through some kind of conversion program (simple as that may be for a geek). What about people who point-and-click their way through some report designer--they're gonna have to deal with some giant integer in a column entitled "something-date". The other problem is that it is not very precise for some applications that need sub-second timestamp values.
Personally, I like PostgreSQL because it accepts ISO standard formats, you don't need to do anything to convert timezones--you simply specify the time zone when you insert or query and it issmart enough to figure it out when you query fordatain Eastern time zone and it was inserted in Pacific timezone. Furthermore, it knows Feb 30 isn't valid, and knows when leap years occur, and can format the date in many different ways with simple built-in functions, can be accurate to the millisecond and won't crash and burn in 2038.
FYI, I believe the "seconds since UNIX epoch" representation of date/time values is a SIGNED integer, so they are in fact good for earlier dates than 1970 (they are good to some time in late 1901 in fact). That is still a pretty limited range and why early systems didn't use that representation inmany cases (couldn't store birthdates for a lot of people who were still alive in the early 1970s becasue they were born before 1901). It is still a problem in some applications ad that is why 32-bit "UNIX-style" time is discouraged.
I think it's a shame that people resort to such kludges without adequately lookig for more appropriate alternatives...but that's just me
Oh-oh. We must be getting successful. People are bashing us on Slashdot now.
--Josh Berkus
PostgreSQL Project
For a better understanding of where PostgreSQL sits with respect to MySQL, it's worth reading the history of PostgreSQL on Wikipedia.
The short story is, it has deep roots in academia. It was Michael Stonebraker's experimental, "post-relational" database. It had "advanced" features, relative to its precursor INGRES, some of which still remain (e.g. extensible types). (Others, like built-in storage and querying of time-series data, do not.) After the academic project was abandoned, two of Stonebraker's grad students ripped out some of the more esoteric (and unstable) features and added a real SQL parser.
Anyway, I wasn't involved in any of the academic work. However, I was an early adopter in this transition period, circa 1995 (when it was called Postgres95). It was buggy, but it was very, very cool.
I think when MySQL came along, Postgres still had not fully shed it's "academic" pedigree, and still was complex, quirky, and buggy. MySQL was light-weight and simple, and "just worked."
I love PostgreSQL, use it daily, and have had no stability problems in the last five years. But, it was not quite the write DMBS and the right time.
I can't be bothered to learn something new when it seems everything supports MySQL.
I'm glad you don't work for me with that attitude. I'd rather work with someone who is interested in learning new things and will bring some creativity to the job. People of your mentality have to be careful they don't fall into the "false laziness" trap--using some tool or technique or techology because you are too lazy to learn something new, only to end up doing load of extra work to avoid the shortcomings of your inappropriate design choices. The result is scads of legacy code at higher layers of an application to handle things like datatype verification, basic referential integrity and so on.
All the various different executables to do different tasks rather then one shell like MySQL, a permission system which seemed from my limited usage more perverse then MySQL's
I've never found it to be a major struggle to use PostgreSQL, though being a more full-featured database it will naturally be a bit more complex to manage.
I'm puzzled about the "all the various executables" part too, since many of them were invocable from the psql shell anyways. Also, it sounds like you've not lookded at PostgreSQL for awhile because its permissions system has undergone a lot of work--certainly it can be complex but it is very flexible and powerful, and honestly it gets rid of most excuses you had to execute all your database operations under the database superuser (or some other single user account) in your backend code.
I have better things to do with my time, like write cool code that uses MySQL.
You might want to examine how you used your time...if you had spent a few hours or a couple days learning something new for a change (like PostgreSQL) then it might've saved weeks or hours of frustration trying to use mySQL for too-complex tasks.
MySQL might have grownup a lot in recent years, but at its heart it was meant for much more modest tasks, like storing guestbook entries, record collections, as a temporary datastore/embedded database, high-performance querying of relatively static ad/or non-critical data and so on.
Last time I used PostgresSQL, after installation and loading the table with a a few dozen million records, a select count(*) from tablename took eons. Then, another one immediately afterwards took the same eons. I wouldn't call that impressive when 1) the simple number of records in a table isn't optimized into a O(0) response and 2) it isn't even cashed after doing the work once. Unless you dig really deep into how to optimize and what to avoid in Postgres it is, in comparison to MySQL *extremely slow* in some functions. So if you just want to create an application that does a lot of querying but does not need transactions etc. MySQL will make your application much more responive in many cases.
http://www.sun.com/software/solaris/postgres.jsp
I'm surprised nobody mentioned Firebird. I started using it since 1.0 and I cannot complain. And yes, this is reason not to use PostgreSQL. Friend of mine has been now forced to use postgres, complaining every day (he is ending up dropping whole database schema nearly each day because of altering some view/SP and postgres not controlling dependencies, about syntax and usability where firebird use for select into var suspend etc. etc.). Most importantly, Firebird have a decent learning curve. Syntax is very comfortable for users, kinterbasdb is functioning nicely (even PHP has now documented connection set)... Functions in postgres are acting like functions (suprise!), which is what I do not want, I want SP using from my apps. When functions, then UDF. No, really. Any reason to switch to postgres from firebird?
Not really a reason to avoid PostgreSQL, but its default non-ANSI SQL way of quoting characters in string constants (using also backslashes) is for me a major annoyance when developing cross-RDBMS applications (yes, I already know of PQescapeString()).
Fortunately, PostgreSQL developers recognized this is more of an annoyance than a feature, and are going to change the default behaviour in PostgreSQL 8.2 (while preserving the old behaviour using a new syntax).
...for me is MySQL.
I'm not a database-y developer really, so I don't have a grasp of the technical abilities of each database. I just go by how easy it is to get third party stuff to install and run on my webhost, and so far PostgreSQL is pretty crappy on that score, through no real fault of its own.
Pretty much every database-enabled open source CMS or blogging app I've tried to use wants MySQL. I assume from these that it's really difficult to write cross-database-platform code, because hardly any of them will work with PostgreSQL. And some of the ones that claim to, don't (yes I'm looking at you Drupal with your lying version requirements!). Even the plugins for things which do end up working (I'm using Movabletype with its standalone db) depend on MySQL rather than using whatever you've set up with the host application.
If I had more spare time, and the inclination to learn more PHP and web development and database stuff, I'd probably have a look at helping out the projects I've tried and failed to use. But I don't.
Game dev and music blog
I would love to switch to Postgres. The main reasons I don't are:
:(
a) I have third-party applications that only run on MySQL. So I'd have to run two different database engines. Bah.
b) Even though I've used an abstraction layer from the get go, I found out that subtle differences still would cause me a lot of effort to move my own applications from MySQL to Postgres. And right now, it simply isn't worth the effort.
Assorted stuff I do sometimes: Lemuria.org
Why MySQL beats PostgreSQL:
- Windows install (5 minutes and you are up and running. You can argue that Real Developers doesn't use Windows, but on the other hand - Real Developers value their time and tend to choose what allows to get the task done in shortest and easiest timeframe while fulfilling client's needs. MySQL tends to be the safe bet here, especially in small web and workflow applications).
- Simple GUI tools (again, I don't want to start writing SQL, if all I want is my table, in a minute and holding some test data).
- Examples for most languages, development enviroments and in books presume you use MySQL. Again, no need to figure what is the difference between navigating result set by PK or cursor, in example.
During learning/developement nobody gives a damn about database settings, it's functionality details or scalability. 99% of the developement is projects that doesn't need (or goes without, since developer[s] retains such functionality in app layer) triggers, stored procedures or 'correct' type checking. Therefore we just pick what get's us started NOW, instead of 'after you'll reconfigure that, this, execute such query, update users table manually or wait for a few minutes for everything to load up).
Reason #6: Postgresql was made by database gods and I am not worthy.
Yes. Postgresql is perfect and unique free database that operates perfectly on Windows (or if your a Fanboi that likes suffering: Linux) and never ever will have any fucking problems. If it has problems, then YOUR the problem. Fuck you. Your not worthy. Use Firebird or that shit toy laughably called My'SQL'. HA!
The only thing better then Postgresql is Oracle. That is because Oracle was made by Jesus. But you probably don't need it because it's very high end, and obviously if your too pathetic to use postgresql then by fuck all means don't sully Oracle with your filth.
Postgresql is also probably better then even MS SQL, but I am not sure about that. Screw Microsoft. Unless it's Windows. Then you should use Windows if it's better then Linux (and it usually is because it's easier and hardware install is better and the directories make sense and it's easier to install software and it's stable now and if you get a virus or adware your a moron and it's not microsofts fault it's because Linux isn't popular's fault and your fault and it makes perfect sense). Best tool for the job, of course. I am not a fanboy, you are the fanboy.
Your not worthy. Your not worthy.
My take is that "mySQL" is a marketing wonks dream name, "postgreSQL" says difficult and geeky. PostgreSQL is also a grown up database and has a different target audience to mySQL aiming itself at the Oracle and DB2 market. mySQL is aiming at a different market. I examined the strengths and weaknesses of mySQL and PosgtreSQL when deciding which OS database to use for my business and chose PostgreSQL because of its better support for transactional processing and ACID. My current applications built on the rock of postgreSQL include a 250GB datawarehouse modelling the UK electricity market which is used by major players in that market. It has never gone wrong, performs with impressive speed and has never written a record incorrectly or returned an incorrect row. Without postgreSQL I wouldn't be in business. It is the best OS database out there and competes with many of the paid for databases very well.
Comment removed based on user account deletion
Comment removed based on user account deletion
Like Germany has ~10 million microbreweries? (and if you did, it's not like nobody knows your families recipe. And if they don't, then there is no problem coming up with a similar as 'the family recipe'.
Which one does not belong in the following list:
Warsteiner, Bittburger, Heineken.
This space is intentionally staring blankly at you
DISLAIMER: I have never used a database in my life, shut up already.
There are many reasons not to use postgres 7.
Want to add a not null column with a default value? Thats 3 statements. Plus one to update the existing rows to the default value.
Want to rename a column? Create the new column. Copy the data over. Copy any contraints. Update any forgein key contrainsts. Drop the old column. That's right, postgres 7 does not do RENAME on columns!
Here's one that will catch you out :
SELECT a.*, b.* from a;
The default behaviour for postgres 7 is to join a and b automatically, giving you a potentially huge result set instead of warning that b does not have a from clause. Yes, you can turn this off, but having it as default behaviour? Insanity. Fine if your statements are always 100% correct, but if there's a novice developer on your team who misses things like this, expect trouble instead of a helpful error.
I could go on. Glad to see that version 8 is a big improvement.
Is the name, i think! MySQL sounds cool while Postgre sounds like some kind of desease!
Postgres uses the BSD license rather than the GPL. I expect that causes a lot of Linux users in particular to avoid it, unfortunately.
Right now I am root on one of the biggest private GForge implementations in the world, and we use PostgreSQL on the back end. I've been thoroughly impressed with it. There were some complaints when I came on board about PostgreSQL falling over but when I looked into it, to my astonishment, there were a number of problems with the system configuration that caused PostgreSQL to run out of available connections. It had never been tuned from the default settings! So no problems were seen until we were past 17,000 developers. Honestly, I still haven't tuned it. Haven't needed to. It's very fast with the default configuration and once I got the OS and the hardware tuned that it was sitting on, it ran faster than ever.
I've managed PostgreSQL at a few shops and usually about the time someone is ready to rip it out and replace it with Oracle, it's because the DBA is a paper tiger who was only trained and certified in Oracle and doesn't know how to tune PostgreSQL, and won't take the time to learn.
Just FYI.
Sticking feathers up your butt does not make you a chicken - Tyler Durden
Last I checked, Postgres required a nightly VACUUM operation, otherwise over time the entire db would grind to a crawl. And forget about getting anything done while the db is being VACUUMed, which usually takes quite a while.
This is, to put it lightly, quite a deal breaker. I hope this is no longer the case now, is it?
with mysql, when you upgrade between major releases, you just compile and install it and keep on truckin'
with postgresql you have to run pg_dumpall, and then restore it after upgrading to the next major version. which is extremely gay, and half the time it doesn't work, or doesn't work according to the postgresql docs, or sometimes loads the tables but not the users or vice versa. thats stupid and is what keeps me from using postgresql.
Does the name Pavlov ring a bell?
I love postgres, but the oledb driver is pretty buggy.
Now, I am NOT a DBA, and I'm generally a newbie when it comes to databases. I've been trying to use PostgreSQL for a small learning project. PostgreSQL itself is fine (then again, for our very limited needs pretty much anything would be), but pgAdmin III has been driving me nuts... the two worst things that have happened (repeatedly) was pgAdmin crashing when trying to view data in some tables, and pgAdmin hanging indefinitely when trying to drop some tables (and every time after when trying to interact with those tables - until I deleted the entire database and started again). Yes, I don't really know what I'm doing, and these were most likely caused by user errors, but program crashes and freezes are NOT a good sign, no matter what.
I guess I'll give Navicat a try, it might be better.
ClutterMe.com - easiest site creation on the Net. Just click and type.
Does an e-commerce app count as mission critical? In some parts I have to use InnoDB tables because of the transactions.
Also, please explain how PostgreSQL has the advantage over mySQL for "mission critical" apps. What's wrong with MySQL?
(Mods: Please DONT mod this insightful, i'm asking a question and I want it to be answered)
Databases, like filesystems, are inherently undesirable. Why should programmers have to write programs that explicitly restore their execution state after it has been annihilated by a crash? This can and should be transparent.
You're right. I confused UNION with INTERSECT.
USE HOT GRITS WITH STATUE OF NATALIE PORTMAN (NAKED AND PETRIFIED)
Done properly, no need for encryption nor even TCP. The logs go out via serial cable to a system which is off the network and not connected to any other machine, save that one which it is receiving log data from (via serial cable). That's about as secure as you can get...
There's a billion little content management web application thingies that use PHP/MySQL. I wish it was Python/MySQL. With PHP you end up using Smarty to stay sane, so you end up with tagless scripts anyway.
The reality of IT is the fact that most people use the software they need to get the job done. I happen to use both MySQL and Oracle for my uses and my job but PostgreSQL is pretty nifty for personal projects. Oracle is a large animal and cost more than a Maybach for 1 CPU of support (ok, probably not but you get the idea). This same arguement can be applied to, 5 reason you should not be using Linux, or 5 reasons you should not be using OpenOffice, etc.
And not sure what if anything this has to do with Postgres. Thanks for stopping by.
really. how do you tell your PHB "we need to use this thing you've never heard of and will probably never know how to pronounce when you tell our prospects that we're using it"
instead, you shrug and say "tell them we're using 'MY ESS QUE ELL' not 'my sequel'."
- Entertaining Bits from the Ancient Kernel Tree
because I can't pronounce its name!
I have great faith in fools; My friends call it self-confidence. Edgar Allan Poe 1809-1845
From time to time they change the structure of the database. This is toxic waste. If you are not aware of it and upgrade your system and postgre is updated, you will have to move the data to a machine that can still understand the old database, dump it and migrate it back in. This has kicked my ass more than once. It seems like it is always at the most inopportune time as well. Then there is the vacuum nonsense to deal with.
With Mysql, I have never had this problem. The database just works regardless of an upgrade to the way they do things. It takes care of it. I don't recall one time where I had to dump and migrate my data, except from one machine to another. Usually I just move the files, even from platform to platform and they are still fine. In fact I have had mysql applications running for years without any intervention. It just works. I can't say that about Postgres, as well as Oracle.
...which is why Oracle has released Oracle XE. It is not open source, but it does compete on price (free).
It's quite fantastic.
I look at it this way...
... I would rather something "simple" like Mysql, but then there is that "easy" argument again. :-D
Remember Beta and VHS? I think there is a similarity here.
Beta - was better and more capable, just not well marketed.
VHS - not as good but much better marketed and easy to obtain.
Since this country proves out that TTM is FAR more important then quality this happens time and time again.
I use and will continue to use PostgreSQL. It is superior to MySQL in feature set and reliability. We use it for all our servers and applications. (Browser-based CRM, Predicitive Dialers, ERP solutions, etc.)
Granted I disagree with the articles #1 comment because we shoe-horned Postgresql on a Windows 2000 AS back in 2004 with version 7.2 because we had a client that wanted a windows based server... BUT, it wasn't easy and for that reason I will acquiesce to the articles point.
In any event I am pleased that Postgresql is continuing to push some of the bigger DB companies to change their tactics but I will not go to Oracle anytime soon. Nor will I go to MySQL. Just because it is "easy" is NOT a reason for me... You can do EVERYTHING with Postgresql that you can with MySQL and much more. Just because it is "easier to use" means nothing, other than laziness prevails.
The only thing I will say against Postgresql that is a "pro" for Mysql... I get annoyed typing P-o-s-t-g-r-e-s-q-l
l8r
Here are the possibilities:
Now we have to ask, "who benefits by complaining about people posting links with associates ID's?" The obvious answer would be employees and/or stockholders of Amazon. Now we have potential bias.
In fact, I'd argue Slashcode should parse Amazon URL's and add associate ID's for Slashdot if none already exist. That could potentially be a better revenue base than subscriptions.
It's not like Amazon is going to lower its pricing if everybody on Slashdot refrains from the practice, so consider an associates link a sign of an intelligent poster.
My God, it's Full of Source!
OUTSIDE_IP=$(dig +short my.ip @outsideip.net)
Good reason not to use what is plainly an excellent product? Of course not. But for Christ's sake, people, learn to make better names. "MySQL"? Great name (though I look forward to the days when the "my-" and "i-" prefixes are gone), good enough product, big win in popularity.
Shop as usual. And avoid panic buying.
I run MySQL because I have to; programs that users demand run only with it.
MySQL regularly hangs. Now, there's definitely a kernel bug there, because killing the mysql process generally fails, even with -9, and the machine can't always reboot cleanly once mysql is hung. But I don't think the kernel is the sole malefactor; no other program I run, multithreaded or otherwise, does this.
I don't take MySQL seriously. Every shred of documentation I've seen for it covering users and passwords tells you to enter the password on the command line. If a server's documentation specifically instructs you to do something suicidally stupid, that's a sign to me that they are fundamentally not getting it.
Does it have an impressive checklist of features? You betcha. Very impressive. It's got lots of features, all perfectly checkboxed, just like MS Office. What it doesn't show any signs of is the kind of serious engineering design I'd want to see.
So that's the thing; even if you hate mysql, you still have to use it to get a lot of programs to run, because they have dependencies on particular quirks or extensions. So unless you want to spend a few days revising something, you're stuck.
That said, I think I might have lost less time by simply reimplementing stuff rather than putting up with this crap.
My blog: http://www.seebs.net/log/ --- My iPhone/iPad app: http://www.seebs.net/seebsfrac/
Performance
Generally even with tweaks to the original configuration, PostgreSQL has slower low end performance. This in part to the way PostgreSQL handles incoming connections; PostgreSQL forks on every incoming connection whose backend setup is a bit slow. However by coding things as stored procedures, one might experience some improved performance. Compared to MySQL, MySQL is very fast on both simple and complex SELECTs. MySQL handles connections very fast thus making it a better candidate for multiple client applications that connect/disconnect all the time.
Text searching
In most applications, text searching is a need more than just a want. Out of the box both PostgreSQL and MySQL ain't the top performers. (This is why people opt for commercial options). PostgreSQL text searching is enabled by an external tool (such as tsearch) not bundled by default with the DBMS engine. On the other hand, according to a manual release for MySQL 3.23 and later, full-text search using MATCH clauses and WITH QUERY EXPANSION are supported. Those who know the their database theory well will tell you that using pluggable extensions to the DBMS is just a bad idea when it comes to performance.
Feature Set
PostgreSQL wins hands down when it comes to features. This goes back to the philosophy behind the creation of PostgreSQL; Stack up features. Oh the philosophy on MySQL is ramp up the speed.
Performance in a Distributed Environment
MySQL and PostgreSQL have support for replication. However MySQL replication has been thoroughly tested, thanks to the large user community that MySQL enjoys also. Unlike inbuilt replication methodologies employed in MySQL, according to the PostgreSQL 8.0.7 Documentation release, PostgreSQL replication solutions are developed externally for example Slony-I which boasts popular slave/master replication. Besides Slony-I there are other commercial and non-commercial replication solutions for PostgreSQL.
These are some of the core reasons why people choose one DBMS over the other. I don't know about you. I recently read an article that suggested that people who are jumping on to the Postgres bandwagon are just doing so in the hopes of using some of its features down the line, but the truth is you can do just as well with MySQL. Hey innoDB will be gone in a few years... Yeah Oracle sucks but you can now get Oracle Express.
-Dickens
Sticking feathers up your butt does not make you a chicken - Tyler Durden
Uh, may I point out a database more nefarious than MySQL?
MS Access.
I'm sure there are worse databases out there, but I've not come across any that are remotely as popular as MS Access with the clueless.
EnterpriseDB is a variant of PostgreSQL that is "compatible with most Oracle applications".
How does it compare with Oracle? Is it really a clone in most features?
--
Before, Saddam got Iraq oil profits & paid part to kill Iraqis. Now a few Americans share Iraq oil profits, & U.S. citizens pay to kill Iraqis. Improvement?
There need not be a binary distinction between "winners" and "losers".
PostgreSQL, MySQL, SQLite and others are just alternative choices, and they compete for different database niches. If I'm a project manager ledaing five developers who are supposed to build a standard Web app, I would be using MySQL since all five are likely to know how to use it already. If my job is to build a spatial store for a research project, my choice may be PostgreSQL (because of PostGIS). If I need to manage 70k of personal addresses, SQLite's simple API, in-process ability and small memory footprint may make it the component of choice.
What I don't like about /. is that too many people here are religious fundamentalists rather
that rational, professional, informed decision-makers.
I urge you to find out your requirements and match them against available products, pick what suits you, and if the cross-product is empty, report to your manager that you need resources to extend the nearest match with additionally needed capability or even build your own from scratch.
That's like you build everything else, so that's how you should build software, too.
Also, is it legal to charge for EnterpriseDB in the way they do? They've made the price list a graphic so it cannot be easily quoted.
Note that they are restricting free use to 1 CPU, 4 GB of data, and 1 GB of memory. I believe there is a free version of Oracle that is similarly restricted.
Most users who are unfamiliar with open source projects tend to think DB administrators manage them entirely through a series of cryptic shell commands. quoth the article.
This is funny. I was talking with a DBA who works for a client the other day. The person who'd been acting as DBA had, through casual but relentless frobbing about, screwed up their systems royally. They replaced that guy with this guy, a professional DBA. I'd just gone through their system and sent them a very long and complex script to fix most of their problems.
One thing we agreed -- neither of us would ever modify anything significant in a production system using the point and click part parts of a GUI. We always figure out what we want to do, put it in a script, simulate the effect of running the script on the production system to the best of our ability, then after we're satisfied if we use the GUI it's simply to load the script and run it.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
The only thing that nags me about Postgres is the stupid dump+restore upgrading cycle...
With MySQL all I have to do is service stop, rpm -Uvh, service start, and it all works.
With Postgres I have to disable all access to ensure what I dump is the latest data, then do a dump, then service stop, then rm -rf the data dir, then rpm -Uvh, then service start, then restore, then enable access again.
Even so, I use Postgres. Just wish it was easier to upgrade.
the funny thing is, PostgreSQL's triggers can be row level triggers or statement level triggers, while MS-SQL's triggers can only be statement level triggers, so in that way PostgreSQL is better!
"22 astronauts were born in Ohio. What is it about your state that makes people want to flee the Earth?" Stephen Colbert
much easier to say than pohst-es-que-el
"22 astronauts were born in Ohio. What is it about your state that makes people want to flee the Earth?" Stephen Colbert
I think he was referring to the spit-roasting part.
Naw, a real company would never do that.
As Slashdot says, "laugh, it's funny".
My God, it's Full of Source!
OUTSIDE_IP=$(dig +short my.ip @outsideip.net)
> Reason number 6 is the damnned Postgres zealots that feel the need to bash everyone else's
> database rather than promote their own.
Hmmm, I don't see that.
I've extensively used db2, oracle, sybase, informix, sql server and used mysql and postgresql as well. Now, I've got opinions about all:
- oracle: too expensive, crazy ceo, dirty business practices, but sometimes slick
- db2: fringe tools are clunky, but very fast & reliable
- sybase: evil company
- informix: best database ever developed
- sql server: too gui-driven, too windows-dependent
- postgresql: in position to take "best database" away from informix - eventually
- mysql: tricky licensing, company told people that transactions were *not* important, data integrity problems, least portable sql
And I get the same feeling from most other people. In other words, I don't see much in the way of pro-postgresql zealotry. But I do see experienced users of *every* other database who are extremely uncomfortable with the short-cuts taken by MySQL AB with their product.
These people have legitimate criticisms of MySQL. And while MySQL is definitely getting better (well except for the whole oracle monster owning their most critical components), it still has a long way to go. And just like Oracle & Sybase, MySQL AB is a company who's proven that they aren't very trustworthy (see their comments on transactions). So, until they've got a solid track-record with a highly ansi-compliant product few experienced data heads are going to trust them much.
But that has nothing to do with Postgresql.
wouldn't you rather have a Lapp dance?
now N mediocre programmers have to get it right as opposed to the 5-20 expert programmers working on *SQL. Bad tradeoff.
(Let's see, "debacle", OK, uh, um, "monacle"? (No, that's "monocle".) "Cackle"? No. "Tackle"? No. "Oracle"? Yes!
The word pronounced "sequel" is spelled/spelt "SEQUEL", and is an actual (SQL database) product.
"SQL" is pronounced "Ess Queue El".
(Similarly, "URL" is pronouonced "Ewe Argh El", not "Earl"; "GUI" is pronounced "Gee You Eye", not "Gooey"; "UUID" is pronounced "Ewe You Aye Dee", not "Ee-eew Ihd"; and "OK" is pronounced "Oh Kay", not "Oh-Killy Dough-Killy".)
Those who sacrifice security to condemn liberty deserve to repeat history or something. - Benjamin Santayana
On most OSes, shared memory is a precious resource and requires either static, compile-time tunables, or run-time tunables that are difficult to juggle. Shared memory is hard to port between OSes because nobody supports it quite the same way.
r esources.html
http://www.postgresql.org/docs/7.4/static/kernel-
So now we have to recompile our kernels just to support Postgres? That is not cool, and means remote configuration of server machines to support Postgres is *very* dangerous.
Additionally, when Postgres starts eating up all my system's shared memory, the other, play-nice facilities that don't consume masses of the stuff start choking because it's difficult to tell Postgres to throttle itself. So now I have to tell *both* the system *and* Postgres to match their limits. Postgres expects me not only to reconfigure my kernel, but to tell it what I've done.
After all this, I find out that Postgres, like every other database engine out there, has a bunch of quirks in its command-line, the same way that MySQL and every other DB engine out there does.
I'm sorry, but it's just not practical to support Postgres without extensive tuning and configuration, that I just can't afford to do on my remote machines. Therefore, MySQL or the newly-freed commercial engines are my only options.
Otherwise I expect the DB to throw an error.
John McAfee 'It was like that time I hired that Bangkok prostitute; to do my taxes, while I fucked my accountant'
Yes, date/time handling that actually works and records all the necessary information in a sensible format is the reason I use PostgreSQL.
GCHQ Quantum Insert installed. If only our tongues were made of glass, how much more careful we would be when we speak
If I remember correctly, the syntax to show what tables are in a PostgreSQL db is rather, well, unintuitive. In MySQL, it is just "show tables".
Also, the first time I used PostgreSQL I had a hard time getting any query to work, until I realised you can't use double quotes, only single.
I use it because it's incredibly well supported. When I first started using it (WAY back in the day) I found something that didn't work quite right, (actually it did, I just didn't know right from wrong at the time,) posted a message to the mailing lists, and had workaround within a few hours.
6 -03/msg00126.php
Here's a post to the advocacy page from someone in wisconson working as a contractor. His praise mirrors mine, and that of hundreds of other users.
http://archives.postgresql.org/pgsql-advocacy/200
--- It is not the things we do which we regret the most, but the things which we don't do.
...nobody knows how to pronounce it! Post-gress-kill? Post-gress-cue-ell? Post-gree-ess-cue-ell? Post-gray-sequel? It's an unfathomable mystery!
"Destroy science and religion. Science would re-emerge exactly the same; but not religion." - Penn Jillette, paraphrased
This is modded as funny but it's really true. Marketing, including the product's name, can give one product the edge over a competing product. A name change and some sort of marketing campaign would make a world of difference in peoples' perception of PostgreSQL. I'm a programmer but jeez I could have come up with a name that's catchier. How about FreeQL or something?
So I don't know anything, lets start with that. I'd like to know how people manage their postgresql installations. I mean, I use SQL Server 2000. When I want a table I go in, type in a few things, check some boxes, make some relationships, cascade if I want, rearrange the orders of columns (a nice feature if you work with RAILS) and then drag and drop that into my Visual Studio designer to get some typed datasets that automatically have indexes and constraints and can be used from code as if I was working against the database itself. What's there in the postgresql world that'll let me do that? I know with MySQL I can do most of that (except for rearrange the order of columns which I haven't figured out - not that I spent time trying to do it).
Thanks for the help
Note: this post is not necessarily directed at parent.
;-)
:)
Paraphrasing an Apple-related sig as seen on Slashdot: "It's not PostgreSQL I hate, it's the PostgreSQL users".
First off, any product that turns it's users into the fanboy crowd as seen on Slashdot (or in fact, anywhere) anytime Mysql is mentioned must be dangerous in some way. Secondly, any product that needs that kind of cheering and propaganda must have something to hide. Thirdly, I do not want to identify with that kind of rabid fanboyism, no matter if it is well-founded or not.
If you guys would just try to tone it down a notch and wipe the foam from your lips, maybe we would be willing to listen. Attacking the competition - especially in the words and tone the PG crowd usually use - is bad marketing and it is only counter-productive.
Especially when the said Mysql user can't identify themselves with your list of flaws. Oh, I'm sure the list is valid - but I've never been bitten by any of the issues. Maybe because I program in a certain way, maybe because of the libs I use or maybe because they are mostly theoretical flaws - I don't know, but listen now, because I'm gonna repeat myself: I have never been bitten by any of the Mysql issues you PG people like to bring up.
It's very good to see that the Linux crowd mostly have learnt this - I'm a happy Linux-only user and I feel proud of that, especially as the blatant Windows bashers are so few and mostly kids anyways. Even though I feel those have much more of a case, personally.
Focus on the good stuff, promote the strengths, outdo instead of attack the leader, behave civilized. In short, grow up. And I might be happy to apply for a membership one day.
Spine World
That is not true at all. Betamax lost because it was too expensive, made even worse by the shorter amount that could be recorded on one tape.
Not only is Postgres the better product, it is also free, so price is not an issue. In fact, I am really puzzled by the Slashdot mob being such admirerers of MySQL as Postgres is actually more free than MySQL is.
In the beer sense of "free" there isn't a single situation where you would ever have to pay "Postgres Inc" to do anything you like with it and you get a few free trivial extras thrown in that you'd have to pay a 3rd party for in the MySQL world. Like, ehrm, online backups. (who would want those anyway?)
And in the speech sense of the word, you can use the code in any damn way you please without having to use the same license; heck, you could turn it into your own commercial product and sell it for thousands if you want to. You can't get any more freedom than that!
My vote is with Excel! Do I win a prize?
When i have a programming project, i want my contractors to get the job done. They have specifications and functional requirements and they can be as creative with that as they want to be.
[...]
IT professionals are no longer the new surgeons. They're plumbers.
Wow. Perhaps you are personally a fine person, but I bet as a boss people think you are a bit of a jerk.
In my experience the result of this sort of work environment is often mediocre and sometimes disastrous, particularly in a "waterfall" project management environment. My comments don't relate excluseively to programmers, they include the people who write the specs and functional requirements--ESPECIALLY the latter, because poor design and planning can doom a project before one line of code is written. And as for programmers, I do not want them to be confrontational, but I fully expect them to be creative and make suggestions to improve upon a specification.
Although a person can get lost in "creative" pursuits and mired in details that do not contribute to end goals, the opposite can happen too. Engineers and developers can pigeon-hole themselves into doing things a certain way, using certain tools. Sometimes you cannot avoid it because you are working with an existing system, but if you are developing from the ground up you should ALWAYS use some creativity.
I expect professionalism, and something that will stand the test of time. And i pay them accordingly.
Professionalism goes both ways you know. If you expect professionalism from your developers, then you should respect them as professionals, not deride them with opinions that they are just "plumbers" and "aren't paid for creativity". Just as is the case with open source coding, "many eyes make bugs shallow" in a specification, and when a programmer asks why it is the way it is and "wouldn't it work better this way" it can be very beneficial.
But on "small", I think you are unfair.
I concede that MySQL is very good at querying massively large tables of information, and as you point out its weakness is in referential integrity and data validation. Also, MySQL is not good with databases that are very dynamic--it is best that data be static (inserted once then selected ad-infinitum with few UPDATEs or DELETEs). Very excellent for most data logging or discussion forum websites, with few tables and simple relationships or none at all.
So to clarify, "small" refers to schema size and complexity, NOT the quantity of data within that schema.
Even assuming the right indexes, my experience has been that people:
1. inadvertently create Cartesian products, don't know why they're getting 10 copies of each row, and slap distinct on it. The database server now does 10 times as much work because of the product, PLUS it needs to DISTINCT them.
2. Grouping by a dozen columns because they don't understand how "group by" works, or how to perform grouping in a subselect and join it into the main result set.
3. Grouping by incorrect column lists and using "having" to narrow down the resultset, making the DB do much more work than it would with an appropriate "where" and "group by" clause.
It seems to me that most "performance" problems are due to programmer ignorance, rather than actual database slowness.
Most won't complain about performance on simple selects, deletes, or updates, because there's no joining or grouping going on. Improperly joining or grouping will cause performance hits straight-away, because a simple error, masked by 'distinct' or a convoluted 'group by', will cause the database to do several orders of magnitude more work.
Of course, the ignorant programmer then proceeds to blame the database.
I propose this: A "Warning mode" or "Development Mode" for all databases that will detect an unconstrained Cartesian Product in conjunction with a "distinct" directive. This warning should direct the hapless programmer to a web site explaining how to fix the problem appropriately.
It should also detect and warn on inordinate numbers of columns in a "group by" clause (say, more than 4 or 5), and point to a site containing information on grouping in subselects (derived tables in the FROM clause) and joining the results back into the main query as a table. I'm sure PG understands derived tables -- I don't know about MySQL, though.
Of course, this is a self-fulfilling complaint, because bad programmers writing bad SQL will *cause* the database to grind to a halt for other concurrent, correctly written queries as well.
use postgres, we can even get a post about it going without about half the posts being about how some other rdbms implements date data types.
They need Rodney Dangerfield to be the spokesperson, load up the bus with boothbabes and beer and head out to do a tour of LAN parties - then we'll get things going on.
Compiere uses Oracle. Does it work with EnterpriseDB?
I think that the real reason is much simpler. Most people don't want to waste the resources to run two or more database servers. Since the majority of applications seem to use MySQL, most people will follow the path of least resistance and just use that for their DB server unless they just must have features Postgresql offers.
Personally, I prefer Postgresql but run MySQL for that exact reason... some apps I use require MySQL and I can't waste the ram/cpu to run a second server. I also love SQLite. What I wish is that both projects would add a simple compatibility layer. Let MySQL apps run transparently linked to Postgresql or SQLite and I would happily remove MySQL.
Anyone that lauds date handling in PostgreSQL should take a closer look at the wire protocol. Here's loadCalendar() in the JDBC driver:
j dbc/org/postgresql/jdbc2/TimestampUtils.java?rev=1 .18;cvsroot=pgjdbc
http://gborg.postgresql.org/cgi-bin/cvsweb.cgi/pg
HORRID. Aside from being complicated and verbose, it changes format pretty dramatically depending, presumably, on how the server was built and what OS it runs on. Some years ago I found myself patching the damn thing because somehow the standard RPM on RedHat6.2 used yet another slightly different format.
32-bit UNIX-style seconds-since-1970-GMT might be inadequate, but a 64-bit milliseconds-since-epoch is just fine and much, much more reliable. And if you really want to be guaranteed infinite future-proofing, send the number as a string.
I moved to Firebird for a while, but now I use MySQL predominantly for one reason only - integrated, robust, proven database replication. When and if PGSQL catches up, I'll consider migrating back.
Jeff Schnitzer
PostgreSQL is a fantastic free database. I've been using it for 5 years solid. I've run it on Linux and FreeBSD and it never once crashed. Plently of other people who have used this database will tell you the same thing. Sun even decided to include Postgres standard in it's Solaris 10 product. So whats the problem? The problem for PostgrSQL is that fact that it's counterpart, MySQL is mentioned in countless books, magazines and articles, surpasing PostgreSQL by a mile.
Why is this? Perhaps it's because most people who work with databases these days do so within a web server type environment. Most books and tutorials on web development seem to feature MySQL exclusively and since lots of aspiring web developers read these books, it's the database they've come to know by default. There's even the L.A.M.P acronym. Although I personally prefer, F.A.P.P (FreeBSD, Apache, PostgreSQL, PHP). But that doesn't sound so good.
FreeBSD suffers from the same problem. When people think of Open Source operating systems, they naturally think of Linux, not BSD. BSD has been around a long time and is extremely stable. Some even go so far to say that FreeBSD is more stable than Linux. So why is Linux a household name? Perhaps, it's the story of the underdog from Finland competing with the all mighty evil wizard from Redmond that makes it so a compelling? Who knows.
...the first thing I did when I saw this topic's heading was scroll away. Now I'm posting without reading any of it just to complete the job...
Sorry sir, but I think you should try it again. When 7.0.0 came out, it was a super huge step forward from 6, and made it way more usable, but it was still not perfect. I use here 7.4 on several production enviroments, and one of it runs since more than 3 years with only one postgres hickup, which I could fix very fast - was some db transactino corruption, which was fixable very easy.
I have also a lot writes to the db for logging, updates, etc data, and I run vacumm every night and I enver had any issue with it.
I have not yet tried any 8 version in production, because I am not able to update those production boxes easily. But 7.4 is very solid and I have no issues at all.
"Freiheit ist immer auch die Freiheit des Andersdenkenden" - Rosa Luxemburg, 1871 - 1919
Well, technically any language that doesn need a CR and/or LF to denote a new instruction/command you can write any program in 4 lines...
- Sig
Administration and development: There are numerous impressive efforts going on in this area, and three products are particularly promising. pgAdmin III has a particularly long development history and is capable of handling practically any task ranging from simple table creation to managing replication across multiple servers. Navicat PostgreSQL offers features similar to pgAdmin III and is packaged in a very well-designed interface.
;-)
Yes, that's correct. And pgAdmin III continuously crashes whenever one tries to edit data in the tables while Navicat crashed already in the first three minutes of use. I.e. just after the setup's been finished and I tried to connect. Fully reproducible all the time on both... luckily psql console still works
Now, mod me down freely. My karma can't get any worse...
Vaccuum.
+++OK ATH