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
Most complaints I hear about it have to do with that vacuuming thing and clustering issues. ...and speed of course.
Never used PostgreSQL though
*ducks*
READY.
PRINT ""+-0
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
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
Namely notifications. This allows me create a very "interactive" aplication.
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.
please excuse my apathy
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.
VACUUM is a pain.
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.
Social scientists are inspired by theories; scientists are humbled by facts.
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.
I'm seeing alot of people citing a reason of low postgres adoption is that 'its confusing to setup' and 'administer' (or it just feels weird) compared to MySQL.. I just have to ask.. what kind of crack are you guys smoking? To me, PostgreSQL administration has always seemed neatly packed into easy, concise and well documented config files like any other *nix service, with a couple of straightforward command line utilites. In contrast you have MySQL whos admistration functions are taken care of by a seemingly random chaotic array of oddball mysql commandline programs whos number is only dwarfed by the amount of built in functions in php. How anyone can think mysql adminstration is more sensical is beyond me. There at least it seems there is a rhyme and a reason behind postgresql administration functions as opposed to mysql where the developers thought it was a good idea to add a whole new command line utility for everything feature that could have been taken care of with a command line parameter. After that it comes down to SQL, which PostgreSQL is the obvious winner (at least it was as of 4 years ago.. its been about that long since ive done web development). A lot of people say mysql is "good enough".. mabye I'm behind on the times, but back then there was NO WAY i would have considered mysql for any project at all givin its limited SQL functionality. Your web apps ended up being twice as big because youd have to hardcode database logic into the program instead of letting SQL do the work for you. You could barely call MySQL a relational database.. it boggles my mind that people love it so much.
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.
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.
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.
Just create a functional index on lower(column) and search on lower(column). There are likely other solutions to this problem (such as ILIKE).
-- The world is watching America, and America is watching TV.
PostgreSQL will always be slower than a MySQL database that uses MyISAM tables.
:)
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 every combination of attributes you select, and it organizes the tuples to fetch into sequence for faster reads)? What about the ability to manage a large number of joins with the GEQO (genetic algorithm for determining the optimal join order)?
There are all kinds of opportunities for speeding up queries that don't involve sacrificing consistency.
Then again, MyISAM should not be used for anything more complex or important than storing cooking recipees.
What if you're a German and you store the family beer recipe in a MyISAM table? I'd start looking at a place to live in exile in case the power dies and you lose the recipe. You don't get any more mission-critical than that
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.
The older table type in MySQL, MyISAM, doesn't handle concurrency well. The InnoDB table type uses the same locking approach as PostgreSQL and Oracle, and should stand up to multiple users much better.
Datawarehouse systems often have users running complex query operations.
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.
Rod Taylor
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.
You _can_ do the same things from the psql prompt. The system executables just make life easier.
I can throw myself at the ground, and miss.
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
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?
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.)
Yes, UNIX timestamps do nothing before 1970, etc, etc
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.
The creatures outside looked from Alt-Right to Antifa; but already it was impossible to say which was which.
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.
To address some of your points (and ramble on slightly)...
MySQL's default configuration still (even in 5.0) creates MyISAM tables when no table type is specified and that of course doesn't support commit/rollback. This only applies to official binaries, of course. Various distros which package it may use a different default table type. As for the official ones, the InnoDB table type which transactions is available by default at least in the -max binaries, though I'm not sure on the other packages MySQL AB makes. Last I saw, the future roadmap indicates that they have transactional support for MyISAM on the TODO list so they might have commit/rollback on the default table type some day.
Subtransactions are an area where MySQL was ahead of PostgreSQL for around 18 months, first appearing in a stable release with 4.0.14 (2003-07-18) but not in PostgreSQL until 8.0.0 (2005-01-19). Replication is another area where MySQL has an arguable advantage since its solution is built-in as opposed to added on later.
Subqueries first appeared in production in MySQL with 4.1.7 (2004-10-23) and in PostgreSQL with 6.3 (1998-03-01). Advances to more fully support them came in PostgreSQL 7.0 (2000-05-08) and 7.1 (2001-04-03). Even comparing the current MySQL implementation to the PostgreSQL 7.1 level, MySQL has some notable issues with them. For example, subqueries are not currently supported in view definitions in MySQL (5.0, obviously, since previous versions didn't support views at all). In my own work with a 5.0 database, I've come across and reported some interesting bugs with subqueries, dealing with doubly nested subqueries and some combinations of joins/subqueries. Unfortunately, both bugs have been in the database for about 3 months without any progress towards a fix.
I agree with your final point; in my eyes, MySQL 5.0 is worlds ahead of any previous release. So I'm cautiously optimistic for the future but I prefer PostgreSQL for now and until I have a compelling reason not to.
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.
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
ncorrect. time_t is always signed
Unfortunately. that depends on where you look. Let me point you do the latest QDateTime 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.
Live today, because you never know what tomorrow brings
"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.
Have you looked at phpGroupware or it's prettier offshoot eGroupware? They come with one, maybe two forum modules. The one I used was Fudforum, bad name but it works. Works with either PostgreSQL or MySQL, at least on Debian you get the choice at install time.
Yours Sincerely, Michael.
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.
I shall inform you that PunBB does run on PostgreSQL if you wish it to.
"The way we can tell it's C# instead of Haskell is because it's nine lines instead of two." -- wadler
Sure it does:
SELECT * FROM sometable WHERE (some_column ILIKE 'SeaRcH STrinG');
You can also do regexp instead of simple LIKE matches.
- Raynet --> .
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