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
"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
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
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.
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
No, they don't. It's owned by a French company called Business Objects. Microsoft just licensed a stripped down version of CR for VB6.
The bitter lessons of a veteran coder: http://bitterprogrammer.blogspot.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.
#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
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.
...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.
If MS Access is good enough, PostgreSQL would have been massive overkill.
:o)
Good for you for getting that right. A lot of skilled devs won't use anything but the tools they already know, even if there is an astonishingly simpler tool that will get the job done in 10% of the time.
Personally, I think of everything as Java + PostgreSQL, so I've still got a lot of room to grow
Regards,
Ross
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.
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
Absolute bullshit. I've used PostgreSQL myself in a mission-critical production app for the past 3 years. (I've since left the company, but the app is still in use.) I have been consistently impressed by the quality and performance. There was a strong push to use mysql when the project started since the company already had in-house knowledge of it. Performance was one of the concernes. So I ran the benchmarks. Read performance of simple selects was inconclusive: mysql won some, and postgresql won some. However, postgresql consistently won write tests and scaled better as I added more client threads.
Nested parentheses in SQL can cause an engine crash. " like ... (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.
Bullshit again. Never ever seen that or even heard about it. Again, at my last job postgresql was part of a mission-critical application, and I've used it for a couple of projects before that too. The *only* time I've seen postgresql go down was when we ran out of disk space. And even then, it was not a crash but a clean shutdown. Give me a specific example of a query that you say caused postgresql to crash. Otherwise I'll assume you are yet another troll.
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.
Bullshit 3x. The app I was talking about was used for tracking work in a 3d production pipeline with a staff of ~300 artists. There was *a lot* of writes. (every checkin, every render, etc.) By the end of the project the database grew to over 10G. And postgresql didn't even blink.
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.
And your point is? All it requires is a single entry in crontab. And you can still run transactions while it's vacuuming. Really, what is your problem with it? It is no different than running a cronjob to do a backup, or a similar maintenance. And since 8.0 and up, postgresql does autovacuum, so you don't even have to worry about that.
So, in short, from my experience PostgreSQL Just Works (TM). And unlike oracle it doesn't cost an arm and a leg, and doesn't require an army of DBAs and sysadmins to maintain it.
___
If you think big enough, you'll never have to do it.
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
I was a big fan... until I needed to use PostgreSQL 7 for a real (commercially available) product.
... (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.
PostgreSQL 7 is ancient. There have already been multiple releases in the 8 series. I never worked significantly with 7, so I can't comment on it much, but with 8.x I haven't had the problems you've talked about.
Nested parentheses in SQL can cause an engine crash. " like
I've never seen 8.x crash, even when I've thrown gigantic, deeply nested queries at it. The optimizer definitely needs to be tweaked though. I've noticed that it tends to favor sequential scans over index scans too often. This is fixable by weighting factors in the config file. I will say that it does take some experimenting to get the configuration tuned properly, which is probably the biggest weakness in the 8.x series.
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.
Those are issues you'll only have if you refuse to run vacuum at reasonable intervals. PostgreSQL has some support for clustering tables. It's only a one time thing that doesn't get maintained during writes, but, periodically running it will solve your fragmentation issues (which wouldn't exist if you just ran vacuum).
The 8.x series also includes support for autovacuum, which should eliminate the issue completely.
It all depends on the situation, but PostgreSQL gives you a lot of options. Pretty much every database needs to have cleaner processes to clean free space.
With PostgreSQL, you can do it manually, or use autovacuum. You can also set the vacuum_cost_delay to change how much it interferes with concurrent access. Whatever works best.
Social scientists are inspired by theories; scientists are humbled by facts.
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
1. If by "mess" you mean other transactions will have to wait until VACUUM FULL is done, then yes. If you mean anything else, then no.
2. re: "vacuum buffer": you just pulled that out of your ass. The *only* thing that VACUUM FULL does and plain VACUUM does not, is physically move the data within the data files to truncate their size. It useful only if you've done a lot of deletes and want to free up that disk space (RTFA).
"vacuum buffer" is the amount of RAM vacuum will use while running. You can adjust it by editing postgresql.org. The more memory you give it, the faster it will run. That's all. It is not something you can overflow.
___
If you think big enough, you'll never have to do it.
Clearly if you think 300 graphic artists generate any serious write load on a db, your idea of system load is vastly different than that of the parent. Also he stated that vaccum sucks because of the risk of data loss, not the inconvience of having to run it at all.
I believe you're referring to "max_fsm_pages". That can, and should, be adjusted. If you leave that parameter at a reasonable level, and vacuum at reasonable intervals, it shouldn't be a problem.
For any high performance database task, you shouldn't expect 100% autotuning.
Social scientists are inspired by theories; scientists are humbled by facts.
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.
That's an utterly stupid argument, especially if you think it's a reason to contribute to mysql instead.
There isn't any way for someone to "take control" of a BSD-licensed project. Sure, someone could use a BSD project as the base for a proprietary project, but that isn't going to discourage anybody else from working on the open original. We have in fact watched several proprietary "improvements" of postgres quietly tank over the years.
On the other hand, MySQL AB own mysql lock stock and barrel, and only release GPL versions because they choose to. They could announce tomorrow that all their future versions will be high-price closed-source shrink-wrap, and no one could say boo to them about it. The difference from the postgres situation is that MySQL AB could take with them the vast majority of the existing development expertise for the code base. Postgres will continue as an open-source project no matter what any one company thinks about it --- you cannot say the same about mysql.
Bullshit again. Never ever seen that or even heard about it. Again, at my last job postgresql was part of a mission-critical application, and I've used it for a couple of projects before that too.
Guys, guys, it may be that certain coding styles trigger issues that other coding styles don't. I've had that happen to me with other products. Syntax and coding techniques that I preferred just happened to bother that particular product while it worked fine for other styles. No product will please everybody.
Table-ized A.I.
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.
Crystal Reports was designed and developed by some of the most sadistic and shit headed sons of bitches ever. Any developer with any experience using Crystal Reports despises it, loathes it, has fantasies of strangling the ignorant shit headded fucktards that created it. If you change the data source for a report, view, stored proc, etc., even though the added column for example is never used in the report, it will break the report because the dipshits save the definition in the report itself. Verify the database and "Fix Up The Report"? or get cryptic errors. Saves the connection information too. Can't switch between SQL Server authentication and Windows Authentication because the ignorant bastards hard code the connection information in the report at design time. FUCK Crystal Reports. I would rather hand code PostScript to output reports before I would ever use this stinking pile of bloody stool ever again.
No sooner do I get over one, then you put a better one right next to me. Bastards.
Have you ever used it? Ever? The following (edited) transcript is from an old 7.4 installation.
foo=# create table JoelPtIsAnIdiot ( idiot varchar );
foo=# \dt
public | joelptisanidiot | table | foo
foo=# create table "JoelPtIsAnIdiot" ( idiot varchar );
foo=# \dt
public | JoelPtIsAnIdiot | table | foo
public | joelptisanidiot | table | foo
Lessons: PostgreSQL (or rather psql I am sure) defaults to wrapping to lower case. It preserves case with quotes. And its namespace is case sensitive, hence the two tables existing simultaneously. No hideous ALL_UPPER_CASE identifiers, and no terrifying hoops unless one fears quotes.
Larry
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: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"
Here is a little background on why vacuuming is used:
When a user starts a transaction to a database the database will show data that was valid as of the start of the transaction. So if I issue a select that takes some time to return results and another user updates rows that I would be selecting after I issue my select, I will only see data that was valid at the time of my select rather than the newly updated data.
When that other user updated a row I was selecting from, a new row is inserted (or written to a previously deleted row). Any new transactions that select this row will get the new row rather than the old one my long-running transaction is still seeing. Once my transaction is complete, then the row with the old data isn't needed any more because newer data is in another row. This is called a non-overwriting storage manager.
What vacuum does is look for these unused rows. It goes through the tables in the background and sees if any transactions are using that data. If no transactions are using the row it marks them as free for the storage manager to write to. Future inserts can use that row to store data rather than adding to the end of the file. Vacuum doesn't move any data in this way. It's just marking rows that can be overwritten with new data. It's completely unobtrusive and a normal part of keeping the database running.
Some databases take a different approach. For example, Oracle uses an overwriting storage manager. When you update a row the data in the row is physically overwritten. To handle transactions, Oracle keeps what it calls a REDO log. The REDO log is like a journal in a journaled filesystem. It keeps track of all changes to to the data in the database. Any transactions that were open before the update to a DB row will notice that the data was updated and will then look at the REDO log to see what the correct data should be for their transaction.
I have heard that implementing an overwriting storage manager like Oracle has is very complicated, much more complicated than a non-overwriting storage manager like Postgres uses. I'm not a DB programmer so I don't know if that is true. I also heard a while back that the Postgres developers were investigating overwriting storage manager algorithms but I don't know what came of that.
Now, back to vacuum. In Postgres there is "vacuum full" which will move data around. It is used to compact the datafile to remove the space that's marked as unused and shrink your datafile size. You should rarely, if ever, have to use this as your unused rows will be used by new inserts. At least "vacuum full" is easier than the Oracle equivilent which is:
Again, "vacuum full" is rarely, if ever, needed. The Oracle equivilent would be more rare to need due to how Oracle's storage manager works.Slashdot: Failed Car Analogies. Amateur Lawyering. Anecdote Battles.
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.)
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
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.
Fact: Out of the box (source install) it's slow. You need to configure/tune it to get performance.
Fact: Good performance tuning info is hard to come by and the tuning takes some time
Fact: Once you tune it right, it's blinding fast
Newer seen it happen in 1.5 years produktion use.
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.
The optimizer has had a major overhaul recently. I'd think you like the results.
Vacuum is a total non-issue. Nothing to see, move along.
I've personally put together a pg server that holds a 160 gig base that is used as backend for customer self-service in a Telco. Every 15 minutes it get a load of updates from the company main (oracle) db so there is no shortage of writes.
I'm currently (after the weekends upgrade party) on pg 8.1.3 and performance is blinding. Admin'ing that box is sooo boring it just chuncks away. Zero issues.
TCAP-Abort
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.
I converted my Delphi app from firebird to postgresql because of scalability problems. Serious scalability problems. Firebird is good, but not nearly as grate as PostgreSQL (as a database engine). It simply cannot work with tables holding a few million records (the server crawls). An almost direct conversion to PostgreSQL gave the app the speed it needed (and afterlife to the server).
\m/
Comment removed based on user account deletion
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.
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?
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.
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)
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.
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...
... that's not a master in my book (it misses any slave). Also MySQL's replication being still statement based, some of the glorious new enterprise features like stored procedures and triggers simply screw up your full featured replication.
So MySQL replication does support a master to multiple slave setup where you can failover to one slave and the new master inherits all other slaves without the requirement of resynchronization. And when you later repair the original master, you can fail-back without significant downtime, right?
I might have not looked at it for a long time, but last time I looked it only allowed to promote a slave to a single standalone database
I do admit, the Slony-I replication system has a lot of shortcomings, most of which are due to the original design goal of "being able to install on an existing, old Postgres version and use it to upgrade to newer ones". But that mostly affected the implementation, not the initial design of features.
Jan
It takes a real man to ride a scooter
Perhaps you are thinking of the ANALYZE command (which can be done as part of VACUUM), which updates the optimizer's statistics for a table?
dtach - A tiny program that emulates the detach feat
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