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."
...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
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
The inventors of Ingres left the company formed around it when it was bought by Computer Associates and started developing the successor to Ingres, hence: Postgres. Make sense?
I wish I had a kryptonite cross, because then you could keep Dracula and Superman away.
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.
Postgres can create indexes on functions. So if you need case-insensitive queries, you can create an index like the following on your table:
CREATE INDEX my_index ON my_table(LOWER(column_name));
Then you can use something like the following query:
SELECT * from my_table WHERE LOWER(column_name) = LOWER('Search String');
This gives you case-insensitive searching with no performance penalty. A little more setup involved, but the same functionality as the other DBMS's you mention.
Isn't this fixed in recent versions of MySQL anyways?
In the last thing I installed which is whatever came from the FreeBSD ports collection, it was still a problem. 4.1 I believe.
As MySQL gets a lot of flack for this poor design philosophy*, they have come up with "strict mode" as a solution. That's a configuration option that is more strict about reporting errors. It's a step in the right direction, but that makes it wildly imcompatible with many MySQL applications.
So it's "fixed" in the sense that you can optionally break backwards compatibility to fix it. I haven't heard many reports about people actually trying to use strict mode.
* MySQL has backtracked on design philosophy before. Remember back when an ACID transaction was evil and slow and MySQL would never implement transactions or triggers or anything else?
Social scientists are inspired by theories; scientists are humbled by facts.
Strict type checking and triggers are both in MySQL 5, which has been out for a while now. You need to update your complaints.
I "inherited" a badly designed MySQL database that a couple of developers were shoveling data into with their nifty little apps. Empty dates were sometimes NULL and sometimes 0000-00-00 and sometimes something else.
I "inherited" a badly written C++ project where an integer was to be displayed as ASCII text, and the data structure was a character field, and not an integer. Oh, sometimes the number was stored as hex or base 10. Now, the cute part was that this excellent programmer converted the datatype to and from base 10 and hex, and his exhaustive testing apparently never went past 10.
Now, this code was copied and pasted about 3 or 4 times for different modules like admin or customer or something like that. And each one was subtly different.
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.
Now, MySQL is not a very logical or robust DB at all times, but it is documented, and any competent programmer could have gotten around the 0000-00-00, NULL, and "something else" things.
I checked some of MySQL's date functions, and one of them does this:
mysql> SELECT CURTIME();
-> '23:50:26'
mysql> SELECT CURTIME() + 0;
-> 235026
That is weird. The curtime value in numeric context is only good for comparison to another valid curtime() whatever, but it can't be added or subtracted as an integer. Yes, MySQL _should_ make date fields something generic xor NULL, or a valid date, not Feb 31, 2000, but its something that needs to be done at the programming level. 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).
MySQL's arguments, like it's features, always seem to be mutually exclusive.
MySQL's benefits:
* More Applications
* Has strict mode if you need it
* Easier to use by default
* Speed
* ACID
The problems are:
* Strict mode is not enabled by default
* If strict mode is easier because of all the benefits of data consistency, then MySQL is not easy by default
* PostgreSQL has more applications than does "MySQL Strict Mode".
* ACID is only on the InnoDB table type, and the highest basic data access speed is on the MyISAM table type
The list goes on. You can argue any one point and say it works just fine, and that you don't care about the other ones.
But PostgreSQL is a whole solution. When they say you have ACID compliance, it doesn't matter what tables you operate on. When they have a reasonably consistent SQL dialect, it can't be configured to be drastically different on a whim, making half the applications (or more) not even work. In short, the features in PostgreSQL don't have long lists of dislaimers and incompatibilities with other features. They just work as advertised.
Social scientists are inspired by theories; scientists are humbled by facts.
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.
Either you want transactional safety or you don't. If you don't use MySQL with MyISAM tables and have fast count(*), if you do use InnoDB (or better use PostgreSQL), but then there's no single count(*) that can be stored with the table since every transaction may see a different count(*).
The cool think about MySQL is that you do have the option (with Table-Engines). The cool thing about PostgreSQL are its advanced featured... One example: Hands up, who here knows what a partial index is?
In PostgreSQL you can setup indexes that only cover a part of the table (for example if you have an active flag on a table and most queries are on active entries, you can have a partial index only on rows that have active=true, and this can speed up things *significantly*); alas most folks even have not even heard about partial indexes, and that is why they do not appreciate PostgreSQL.
This is just one example.
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"
Just one teensy little problem with "strict mode:" any client can turn it off! Somebody is unclear on the concept of Codd's 12th rule.
What part of "A well regulated militia" do you not understand?
My reply was apt. The parent said that there was little reason to switch, I gave some reasons. I stated a fact (that MySQL thinks Feb 31st is a date), which is not bashing. It looks like bashing because it makes MySQL look bad.
I don't have a problem with other databases. The only database that, to me, stands out as particularly bad is MySQL. That's because their marketing deceives many people.
Someone may see:
1. "MySQL supports strict SQL compliance mode"
2. "MySQL is easy to use from the default install"
3. "MySQL is screaming fast"
4. "MySQL has transactions"
5. "MySQL has more applications written for it than PostgreSQL"
But...
#1 conflicts with #2 and #5 because strict is off by default, and there are fewer "MySQL Strict Mode" apps than PostgreSQL apps.
#2 conflicts with #4 because the default install and CREATE TABLE create MyISAM tables which do not support transactions.
#3 conflicts with #4 because the "screaming fast" reputation is from MyISAM tables. InnoDB is somewhat similar in performance to PostgreSQL.
Everything about MySQL pigeonholes you into a subset of the features that MySQL AB advertises, and then makes it as difficult as possible to roam between those features. In MySQL, if you need to change the type of a table, for instance if you want transactions, you can't do that without disrupting running applications. You need to pause and resume all the applications accessing that table.
Compare to PostgreSQL, if you need to, for example, change the disk that a table is stored on (of course PostgreSQL doesn't have different table types), you can make all the necessary DDL modifications in a transaction-safe way, and the application will never know the difference.
I know MySQL has uses. I use it (sparingly). Slashdot uses it, for good reasons I'm sure. But "has it's uses" does not mean "immune from criticism".
Social scientists are inspired by theories; scientists are humbled by facts.
But of course anyone who is is willing to tolerate MyISAM tables (which mysql's full text indexes still seem to require) already has pretty low standards for their data. (I don't mean "low standards" in a derogatory way.)
As a programmer of a search engine built on postgresql's tsearch2 I can confirm that it is a pain in the ass in a few ways. Installation isn't one of them. And in fact setup, while certainly more involved than MySQL, isn't very difficult either... if you can be satisfied with the defaults (just run the bundled sql setup script).
The main problem with the defaults for me is the crude "snowball stemmer" (does MySQL have any stemming support? I did a quick google search but couldn't seem to find any info... though i note some plugins that seem to offer it). Once you get tsearch2's dictionary based stemmer working, things become much more wonderful very quickly.
On the other hand for anyone who cares about full text search tsearch2, in it's complex design, offers a vast amount of configurability and power. It's indexes are pretty fast. There are multiple ranking algorithms, a "headline" function for pulling highlighted extracts from found text, (limited) field weighting, replaceable tokenizer, multiple languages and encodings, and on and on.
Unfortunately I don't think it comes close to something like lucene. But that's another kettle of fish.
Regarding MySQL's full-text search I came across this nice quote (from Kate of Wikipedia):
Of course the parent poster says up front that they have "pretty simple requirements" so all of my comments above probably make no impression at all. But anyhow, just something to think about.
There are plenty on benchmarks of MySQL vs. Postgres. The Postgres people seem to dispute these but they show MySQL killing Postgres.
Every single MySQL/PostgreSQL benchmark I've seen has either been absurdly trivial or benchmarked with MyISAM which is an apples to gorillas comparison. And they're all old benchmarks as well. Unlike MySQL's apparent philosophy of "1) Do the minimum to be able to claim we have feature XYZ 2) Make it fast 3) Maybe think about making sure it works sanely", the PostgreSQL community's top-most concern is data quality; performance is always seconday to that. Because of that there had been a lot of room for improvement through the 7.3/7.4 timeframe.
That's no longer the case. People commonly see 30%+ speed improvements from 7.4 to 8.0, and 2x that from 7.4 to 8.1. And more improvements are on the way. 8.2 is looking to have on-disk sorts that are 4x faster than today, as an example.
"Forever broken COUNT(). Although MIN & MAX were fixed in the latest release, COUNT() is still broken and there is no fixing in sight. Yes, I beieve 10 seconds execution time for count() on a table with just a few million records qualifies it as a broken feature."
You know that count() does full table scans in MySQL too when you use InnoDB? And for MyISAM the result is simply wrong while updating.
"No memory management. For example, here is 1GB database on a dedicated host with 2GB of RAM. PG should suck the while DB into RAM and run selects from there, right? Wrong. PG is extremely frugal about memory management. It caches the last few results, but otherwise goes to disk for data even if there is anough RAM to cache the whole DB. The PG developers keep saying that it's the job for the OS. Now, which OS should we use then? FreeBSD, Linux, Windows? Which one?"
That's right. Postgres requires that you specify how much memory it should use. As for every complex program, it requires some skill.