Top 5 Reasons People Dismiss PostgreSQL 704
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."
Availability (Score:5, Insightful)
First post?
Other things... (Score:5, Insightful)
Re:Other things... (Score:5, Insightful)
If your application has an bug and inserts an invalid date, you don't want that error to cascade to another application (or another part in your application) and cause more errors down the line. By the time you detect the bug, it could be almost impossible to determine the source of the bug.
Putting consistency checking in application A doesn't prevent application B from inserting invalid data. And when application A reports an error (due to it's wonderful in-application consistency checking), now you don't know what caused the error. It's long past the time that you can get meaningful state information from application B, at most you have database auditing tools that tell you "application B did it", but that's more easily implemented in PostgreSQL as well (triggers).
And I'm not talking about super-advanced users only. I am talking about everyone who wants to catch the error early when they have the most possible information. Everyone who's just a programmer who wants to be able to trust that data from the database comes in a meaningful form. Everyone that just wants the database to do either what they expect, or throw an error.
Re:Other things... (Score:5, Funny)
That's why I use SQLite. Its lack of type checking is so profound, nobody has a problem grasping it!
Re:Other things... (Score:5, Funny)
You're just jealous that your year is shorter than mine. Don't hate on me because I refuse to recognize calendars that skip days. 31 days in every month. It's gotten me in a little trouble at work though...
Isn't this fixed in recent versions of MySQL anyways?
Re:Other things... (Score:5, Informative)
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?
Re:Other things... (Score:3, Informative)
Re:Other things... (Score:5, Insightful)
In particular this is likely to allow security problems in the application to cause malformed data to be entered into the database, thereby affecting other applications.
Re:Other things... (Score:5, Funny)
Re:Other things... (Score:5, Insightful)
MySQL has never cared about enforcing database integrity and are just starting to do so. The sad part is that the vast mayority of the people that use MySQL because it is the default database don't fully understand what data integrity or consistency is.
Cheers,
Adolfo
Re:Other things... (Score:3, Insightful)
Yes, the fact is that MySQL is trying to adopt a more PostgreSQL-like philosophy, using their market share and name recognition. In the meantime, the current users are in for a rough ride of backwards-incompatibility.
So, the question is, does MySQL get PostgreSQL's features and philosophy in place before PostgreSQL get's MySQL's market share in place?
Re:Other things... (Score:4, Informative)
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).
Re:Other things... (Score:4, Insightful)
When it should be done, it should be done in the outside.
When it must be done, it must be done in the inside.
Cheers,
Adolfo
Re:Other things... (Score:3, Informative)
Incorrect. time_t is always signed, and thus it can represent any time from 1901 to 2038 on 32bit systems, and two thousand times the age of the Universe on 64bits.
Re:Other things... (Score:3, Informative)
Unfortunately. that depends on where you look. Let me point you do the latest QDateTime [trolltech.com] class in qt. The relevant bits:
void setTime_t ( uint seconds )
uint toTime_t () const
Now this may be due to limitations on other platforms, but in any case you can't assume that everyone using "time_t", or that think they do, can handle signed integers.
Sometimes the tool IS the problem (Score:4, Insightful)
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
Re:Other things... (Score:4, Informative)
Re:Other things... (Score:5, Informative)
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.
Reason number 6 (Score:5, Insightful)
Re:Reason number 6 (Score:5, Informative)
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".
Re:Reason number 6 (Score:5, Interesting)
Of course since then I have discovered many more things to love about Postgresql, including triggers and stored procedures etc (To be fair, MySQL has some of these features now, but did not at that time)
Just to be clear my first Postgresql app handled ~5 million VoIP records per day on a single CPU, single disk desktop class machine and the only time I have EVER had Postgresql crash was due to a bad ram chip in server! Conversely, I can't count the number of time I and my customers have lost data with MySQL.
Re:Other things... (Score:4, Interesting)
Re:Other things... (Score:3, Interesting)
Exactly. I know PostgreSQL is a better database engine, but I know mysql, I can't be bothered to
Re:Other things... (Score:3, Informative)
What an attitude (Score:5, Insightful)
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.
Re:What an attitude (Score:3, Interesting)
> > 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
Re:Other things... (Score:5, Insightful)
Postgres -- getting closer to the kinds of features Oracle users want. Probably about at 8i level now. OTOH speed is nowhere near Oracle
MySQL -- Roughly the same speed as Oracle. Doesn't scale. Features ain't even close (though 5 is miles ahead of 3, another 10 years and...)
The problem for Oracle corporation is that 95% of Oracle users don't need the features of Oracle and thus MySQL is a good replacement for them.
Re:Other things... (Score:5, Insightful)
What if you have a table and you need a functional index on a user defined function? MySQL can't even do that, so it will be forced to scan the whole table. PostgreSQL makes it trivial.
PostgreSQL can also combine indexes into in-memory bitmaps before looking in the table. That means you don't have to make a multi-column index for every combination of attributes you select. This is done automatically by the planner.
When you actually make use of some of the "features that nobody needs" in PostgreSQL, you can see huge performance gains. I'm not sure how it stacks up against Oracle, because they don't let their users publish results.
But yeah, you're right. If you just port some MySQL code to PostgreSQL, the PostgreSQL code will likely not perform as well (unless there are joins where the planner can use some of the features like the bitmap index).
Re:Other things... (Score:3, Interesting)
You are going to laugh but since MySQL basically uses a 1 application model you just de-normalize and have the functional field inside the table itself.
As for benchmarks there are plenty of benchmarks of Oracle vs. MySQL (a tie to slightly in MySQL's favor on what MySQL does well using cheap hardware. Anything else Oracle
Re:Other things... (Score:5, Informative)
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.
Re:Availability (Score:5, Insightful)
(Only partially being sarcastic here. Access is fine for a lot of things. It's just the mentality that kills me. LAMP Developers are the true heir to the VB/Access Guy Mentality)
Comment removed (Score:5, Insightful)
Re:Availability (Score:3, Interesting)
The author, Jason Gilmore... (Score:5, Informative)
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 [blogs.com], it's quite snappy.
Re:The author, Jason Gilmore... (Score:4, Informative)
Now that Sun has 24x7 support for PostgreSQL, that issue has been soundly put to rest.
The name (Score:5, Interesting)
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.
Re:The name (Score:4, Insightful)
Re:The name (Score:5, Informative)
Re:The name (Score:5, Funny)
Re:The name (Score:5, Funny)
Hey dummy! (Score:5, Funny)
Re:The name (Score:3, Interesting)
Take for instance how much time and money goes into marketing brands (of anything) and how fiercly a brand name is guarded once its established.
The problem with PostgreSQL as a name is
Web developers... (Score:5, Interesting)
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.
Re:Web developers... (Score:5, Insightful)
I think what the grandparent meant is that a database can do more than handle SELECT, UPDATE, and INSERT queries, and that web apps that use DB backends contain a lot of code for functionality that could have been handled more efficiently and cleanly by the DB itself.
Ironically, I think your post kind of validates the grandparent, in that you seem to implicitly be thinking of SQL databases as little more than a better place to store data than a flat file.
Cheers.
Re:Web developers... (Score:5, Funny)
Re:Web developers... (Score:5, Interesting)
An example:
Newbie-admin asks "how do you make your syslog files go to a different machine?"
Perl-syndrome admin says "oh, that's easy - just write a quick perl script to tail the log files you want, then open a TCP connection to a perl script on the remote machine to write the data. I could write that in 15 minutes!"
Experienced-admin says "Why don't you just configure syslog to send the files to the remote machine? It takes all of 5 seconds."
perl-syndrome admin "TMTOWTDI!"
(and yes, this exchange *really* happened, but it's not the only one - I've seen lots of other examples of guys with perl-syndrome posting perl scripts that could be done much easier with things like sed and awk.)
Re:Web developers... (Score:5, Funny)
Jon^D: I had to cat 8-9 seperate quote files, compare each line in each of them to make sure there weren't any duplicates then sort
Jon^D: I wrote a nasty perl script to get it done
Jon^D: and it didn't work very well
skank: cat quote*.txt |sort |uniq
Re:Web developers... (Score:4, Informative)
Re:Web developers... (Score:4, Interesting)
Maybe I'm a hopeless desktop-fed n00b, but I've only used awk once, to take some experimental data that I'd previously entered into a file by hand, transform it a bit to provide input to gnuplot, then mangle it into a TeX table. It took a bit of time to learn, and you know, if I had to use it again I'd have to learn most of it over! I'm not entirely sure it was worth the time. Perhaps people like me should try to learn Sprog instead... or maybe just give in to the supposed "dark side" and enter the table into a spreadsheet and paste it into one of those hellish beasts they call "word processors"... NEVER!
Nobody's heard of it (Score:5, Interesting)
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
Re:Nobody's heard of it (Score:3, Funny)
Of course! PostgreSQL is so good that when people learn about it they switch to it. Hence anybody who knows about PostgreSQL uses it.
Ever since I discovered postgres, mysql has been relegated to...
Precisely!
Interesting article, but not the reasons I hear (Score:5, Interesting)
I don't hear those reasons when people dismiss PostgreSQL. The ones I hear are:
Re:Interesting article, but not the reasons I hear (Score:5, Funny)
Re:Interesting article, but not the reasons I hear (Score:3, Informative)
Use autovacuum. The current version has autovacuum built into the backend. How much easier can it get? And it shouldn't interfere to much with concurrent connections.
PostgreSQL is slow.
It gets this reputation if you run a MySQL app on top of PostgreSQL because MySQL does some types of things faster. However, I think you'll find in many cases the application will have superior performance to the MySQL version if you write PostgreSQL specific code in the application's data access layer.
Re:Autovacuum (Score:4, Interesting)
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.
Re:Interesting article, but not the reasons I hear (Score:3, Informative)
Not necessarily true. What about when joins are a factor? Or what about when you can use a PostgreSQL feature like a functional index (on a user-defined function) to speed up a query, and that's not even available in MySQL? Or what about when PostgreSQL has more index usage opportunities, like the ability to combine two indexes in an in-memory bitmap before scanning (that means you don't have to make multi-column indexes for ever
A few more reasons (Score:5, Insightful)
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:Number 1 (missing) reason (Score:5, Insightful)
Re:Number 1 (missing) reason (Score:3, Informative)
To a lesser extent, table inheritance, although I could probably work something out with triggers and a materialized view (but it would be a bigger pain) . Also triggers in MySQL must be done with normal SQL I thinks, while on PostgreSQL I can use a procedural language to make a more complex trigger (There's probably some workarounds for this too, but still... ) There used to be others, but I think they have closed the gap.
rep-lih-kay-shun (Score:4, Interesting)
#1 Reason (Score:5, Insightful)
Or this? (Score:4, Interesting)
Re:Or this? (Score:5, Insightful)
Benchmarks like that should be run with a couple of hundred active users all doing different things (mix of updates, insert, deletes, and selects) -- a real world use-case.
You will quickly find that scores change.
We had a MySQL versus PostgreSQL battle once. The MySQL people put together a benchmark showing MySQL was nearly 10 times faster. The benchmark was a single user going through the steps.
We then took the exact same thing and put Apache in front and benchmarked with 5 active users -- now they were about even. At 10 users PostgreSQL was about 5x faster. At 100 users MySQL was unable to finish the test. PostgreSQL was serving each of the 100 users at a rate comparable to how it dealt with 10 active users.
Benchmarks for benchmark sake is useless. Benchmarks that model your actual use case are quite valuable.
Re:Or this? (Score:3, Informative)
Re:Or this? (Score:3, Informative)
SQLLite is not going to handle a 1TB table very easily and I don't believe it supports most types of joins.
The benchmark linked to does not fit this.
Besides, 1 user on MySQL or SQLLite (each uses a single CPU only) it going to suck compared to a TeraData, Clustered Oracle or Clustered PostgreSQL (BizGres) database.
Statement still holds. Benchmark what you will actually be doing.
I know, I know! (Score:5, Funny)
It's because LAMP sounds so much cooler than LAPP!
Personal .02 (Score:4, Interesting)
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.
Fulltext Indexes (Score:5, Insightful)
Re:Fulltext Indexes (Score:4, Informative)
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.
BullShit (Score:5, Funny)
There are other reasons (Score:5, Informative)
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.
Re:There are other reasons (Score:3, Informative)
A huge omission (Score:4, Informative)
http://news.com.com/Sun+backs+open-source+databas
No newbie guides (Score:5, Interesting)
* 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..
No newbie guides (Score:4, Insightful)
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.
None of Those 5 Are My Reasons... (Score:3, Insightful)
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.
I avoid it.... (Score:5, Funny)
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.
Re:I avoid it.... (Score:4, Funny)
postgres in astronomy (Score:3, Interesting)
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 [sdss.org]. 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!?
Why it gets dismissed where I work. (Score:3, Interesting)
Re:Why it gets dismissed where I work. (Score:4, Informative)
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.
Re:Why it gets dismissed where I work. (Score:3, Informative)
Why I chose MySQL (Score:5, Insightful)
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.
How about... (Score:3, Insightful)
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.
Competence required (Score:4, Interesting)
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 [wikinews.org].
Top 5 reasons not to not look at Postgres (Score:3, Interesting)
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.
Avoidance through Ignorance (Score:4, Informative)
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?
Re:Avoidance through Ignorance (Score:4, Insightful)
"Most people avoid Linux because they are totally ignorant and are going with the popular flow no matter how ugly it is. They've jumped on the Windows bandwagon with no regard for what they are missing."
I think that you, as someone not proficient in Linux, should at least appreciate that some things are a pain in the ass to learn, even if there are considerable benefits. Don't be so quick to judge people who haven't adopted your favorite database package or OS or seafood dish.
Re:Crystal Reports (Score:3, Informative)
Re:Crystal Reports (Score:3, Informative)
No, they don't. It's owned by a French company called Business Objects [businessobjects.com]. Microsoft just licensed a stripped down version of CR for VB6.
Re:For me, one thing remains a [sad] fact (Score:3, Insightful)
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
Re:Why I'd rather not use PostgreSQL (Score:5, Informative)
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.
Re:Why I'd rather not use PostgreSQL (Score:3, Informative)
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
Re:Why I'd rather not use PostgreSQL (Score:3, Informative)
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 FU
Re:Why I'd rather not use PostgreSQL (Score:3, Informative)
For any high performance database task, you shouldn't expect 100% autotuning.
Re:Why I'd rather not use PostgreSQL (Score:3, Informative)
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
Re:Why I'd rather not use PostgreSQL (Score:4, Insightful)
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.Re:PostgreSQL is BSD licensed (Score:3, Insightful)
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
Re:One query - select count(*) from .... (Score:5, Informative)
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.
Re:postgresql bites because (Score:3, Informative)
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, he