Slashdot Mirror
PostgreSQL 7.4 Released
Christopher Kings-Lynne writes "PostgreSQL 7.4 has just been released. The list of new features is impressive and includes greatly improved OLAP performance among many other speed improvements."
← Back to Stories (view on slashdot.org)
This might also be a good time for the developers working on phpPgAdmin to create a new version that rivals phpMyAdmin in functionality.
The guitars sound good, now give me about 10db more on the cow bell.
For me to migrate my company's systems from MySQL to Postgres. Shame that my nice O'Reilly book won't cover the new features ... but I probably am not so advanced as to need them!
Now how does it compile and run on FreeBSD / x86-64?
I use PostgreSQL extensively, and I have had a hard time convincing my-mySQL (I'm so clever) exclusive friends to give it a try.
One thing that should be noted is that the JDBC drivers (http://jdbc.postgresql.org) are now among the best I've used. For those developing Java apps, the choice is now even more clear.
....still no native replication. MySQL has this one single advantage over Postgres.
Oh, raw disk use would be nice too.
Martin Brooks / Slayer99 #linux / UIN 2178117
IN/NOT IN subqueries are now much more efficient.
Queries using the explicit JOIN syntax are now better optimized.
New multikey hash join capability.
Cursors conform more closely to the SQL standard.
Sounds like they pushed closer to the SQL standards, good job guys.
Does anyone here know more about this "New client-to-server protocol" they speak of?
TruePunk | Games
I run postgres on my own database servers (when I'm not making movies, that is). Now, there's a distributed database project associated with Postgres, trying to add replication into the databases' bag of tricks.
Lotus Notes implements e-mail and lots of other things on top of a database engine that performs replication. So, could Postgres be used to develop a Lotus Notes type application with replicated databased for e-mail, calendars, team rooms, etc?
This is America, damnit. Speak Spanish!
So I finally get off my lazy ass, bring out my old P166-MMX, download postgres 7.3.4, compile it (on a 166!), install it, issue my first select statement, check slashdot for a break and find this!
Grrr, maybe it's time to buy a faster DB server.
"To save the planet, I had to go to the worst spot on Earth, and that was Philadelphia." -- Sun Ra
Not to ignite the flames at all (actually, I would prefer a straight answer)...
We have one client who uses postgres, the rest are all mysql-based... Is there an unbiased (as far as can be) comparison ?
Simon
Physicists get Hadrons!
Access denied
Welcome to phpMyAdmin 2.5.5-dev
phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server.
Error
MySQL said:
#2002 - Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)[Documentation]
It appears they are on top of things.
because no one can effectively pronounce it.
Is it pronounced "Post Grays"?
"Post Grez?"
"Post Gress?"
"Post Gray?"
"Post Gruh?"
I've also heard people refer to it as "progress"...
Seriously, though, as an IT manager, you can't 'sell' the concept of using an OS RDBMS that you can't even pronounce. People use MySQL because its pronounceable. My Sequel. Simple. Yellow. Different.
Those guys over @ Postgreays need to figure out a better friggin name for their RDBMS if they expect it to be used in the enterprise.
Ok, this is nice - One thing that's always put me off postgres is the need for vacuuming. It seems to me it's a labour-intensive task that the computer ought to do on its own, without my help :-)
Simon.
Physicists get Hadrons!
I'm sure I'm gonna get modded down for this, but does anyone know when is there gonna be a version that can run in windows natively (without using Cywin)?
I ask because we are FORCED to use Windows boxes at work, and they gave all of the developers 2. We can't reformat and put linux on (or do a dual-boot) because they check to make sure everything is status-quo. And right now the atmosphere around here is not the greatest, so I'd rather not risk anything with the PHB's by trying to trick them.
I usually have my 2nd machine as a server running mySQL as a testbed for my database apps. I'd LOVE to switch to Postgresql, but I'm limited as to what I can do.
Any idea when a Windows native version will be available?
Does anyone know the timeframe for a native win32 postgres server? And I don't mean cygwin postgres. I mean really on win32.
Can anyone comment on PostgreSQL OLAP? Does anyone use it for day-to-day business operations? How well does it integrate with mainstream OPAL analysis and repoting applications? Hyperion? Congnos? Business Objects?
"God fights on the side with the best artillery." - Napoleon, Marshal of France - speaking truth to power
Because they care about your data, among other things! You could have the fastest database server in the world, but if you find your data is corrupt, or truncated without warning, it doesn't do you much good.
Here is huge list of MySQL Gotcha's that absolutely floored me when I first read it. In my opinion, a "gotcha" in regards to a database is a "Bad Thing(tm)"
MySQL Gotchas"
Open Source Time and Attendance, Job Costing a
Sorry, not in the dictionary.
I've heard of the glories of postgre...now I may actually dump mysql and use it! Looks like it'll be easier to configure, and in my book that wins.
Damon,
http://actionPlant.com
PgAdmin 3 is also ready and in now multiplatform.
you've got to be kidding. I guess if the couple seconds it takes to set up a cron job to run the vacuum is labor intensive. I didn't think so.
It's hard to believe that's how Micronians are made. Why don't we see it right now by having you both kiss one another?
Full text searching also got another overhaul- I plan on messing around with it when I get some free time. They've included a .sql file you can just import into an existing DB.
The real power here is that the index is quick to update, and as a result, can be done in real-time via triggers and stored procedures- neither of which you can do with MySQL :-) The new release is also even more SQL compliant- something else MySQL can't claim. PostgreSQL is both SQL92+98 compliant if I recall.
It can't be said enough- PostgreSQL is now MUCH faster...and due to features like stored procedures, triggers, and some of the best locking available combined with some of the best transaction support, it's actually far faster at many of the same tasks if you take advantage of these greater abilities.
Even back as early as '99, PostgreSQL absolutely mopped the floor with MySQL when as little as 10% inserts or updates were thrown into a select test. Why? Piss-poor locking and zero transaction support. The stuff you have to do in the application layer to make up for proper(or ANY) transaction support will make most benchmarks completely pointless.
MySQL always has, and always will be, a DB best suited for blogs and 2-guys-in-a-garage; it's slapped together, has a low featureset, and is not standard-compliant. PostgreSQL is not an enterprise fish(replication still needs work if I understand it correctly)- Oracle, DB2 etc have that market pretty well covered- but it's great for everyone else who isn't, say, a multibillion $ company...if those people just bothered to have an open mind instead of pointing their fingers at benchmarks showing MySQL running out of an in-ram-only table can select 50,000 rows faster than PostgreSQL can, and whining about how they need to make a cron job to vacuum/vacuum analyze tables at an appropriate time(with autovacuum, also in this release, there goes that excuse!)
Please help metamoderate.
Ellison purchased his version of SQL off of IBM in the late 1970's or early 1980's for about $150K. Oracle has no ties to PostgreSQL.
For some applications with a chance of growth I've had two issues with Postgresql. One is that despite the fact that they have talked about being an "enterprise level" database for ages, we found that in any kind of swift moving transaction enviroment we had to VACUUM pretty regularly. How they expected folks to leave pgsql running over extended periods of time (months -> years) is beyond me. Looks like they may have solved it. It will be interesting to see if the systems can take a pounding and stay up 24/7 for a while without slowing to a crawl.
The other issue has been replication. With mysql this has saved our bacon more then once. Nead to do intensive analysis on live data and don't want to disturb active system? Set up a nice slave and query away.
Want basic fault tolerance? Set up a slave, you have a live mirror of the data.
Have lots of queries coming in (load balance the reads at least).
PostgreSQL now has some type of replication available from PostgreSQL Inc, but it looked to me like somewhat of a hodge podge of perl, triggers and who knows what else.
I think I'll try it out, and if I can get the same replication speed as I do with a mysql array I'd switch over, but first glance it didn't look like I would. Anyone compared the replication performance yet (and ease of setup, I was very impressed with mysql in this regard).
pretty informative . i will check those facts.
I work with both of them, so I can compare both. Personally, I see many cases, especially when the data model is complicated enough, when PostgreSQL is faster than MySQL. But for that I am spending some extra efforts, because many OSS projects are ported to work with one DBMS, not with both.
I love PostgreSQL and it's functionality but unfortunately there are still many developers of other open source projects who heard about MySQL and did not do any research for existing alternatives and thus made his project based on MySQL.
And, again unfortunately, while PostgreSQL is very close to SQL standard, but MySQL is not that close, so you cannot just substitute the database library - you have to re-write (and thus re-test) all SQL code of the project. So, that's why I still have to use MySQL.
With all my respect to great technical quality of PostgreSQL software, I think PostgreSQL team doesn't do a great job to make PostgreSQL being popular. The athmosphere in PostgreSQL community reminds me the one of BSD (read: very unfriendly).
Less is more !
Don't panic, but you seem to be shoved up the ass of an overweight geek.
We'll stay with you on the line until the spelunker and protologist arrive to extricate you from your precarious position.
New autovacuum tool
The new autovacuum tool in "contrib/autovacuum" monitors the database statistics tables for "INSERT"/"UPDATE"/"DELETE" activity and automatically vacuums tables when needed.
If you think PostgresSQL is now faster than MySQL, fine, post a real benchmark, with both databases being properly tuned.
But MySQL has had transactions and row-level locking for quite some time now, so the fact that you claim that it doesn't indicates that you don't know what you are talking about.
Futhermore, why must you bring up your dislike for MySQL in a message about Postgres' great new features? If Postgres really is improving, great! Talk about that! Why must you at the same time talk about how sucky MySQL is? (Especially since what you say about MySQL is clearly untrue).
I'd love to have a native Windows version, also.
Why not just get a copy of VMWare and run PostgreSQL under that?
Hmm. re-reading my post, I can see why you replied like you did, so let me spell out the case I have:
There's a DB with (currently) about 30 million rows in it, it's the audit trail for a clients adserver. Queries are run on the db by their clients to figure out their usage, click-throughs etc. over periods. The ad-server gets anything from 0 to ~20 inserts per second across some 200 sites, and depending on the client policy, is cleared daily,weekly,monthly or yearly of that clients data.
Now, my understanding of the vacuum command was that it effectively took the DB offline (not good with the hit-rate I have), and my understanding of 'auto-vacuum' was that it would negate that effective downtime. It appears that that is not the case.
MySQL copes with this quite happily. I was simply investigating whether postgres would cope equally happily... more strings to the bow etc...
Simon
Physicists get Hadrons!
Or maybe you could have paid attention yesterday when you downloaded it. It was out all day yesterday. Quit your whining!!
This is just ignorance ... "Sequel" was an IBM product that SQL replaced. (In the early 1960's AFAICR.)
Sent from my ASR33 using ASCII
If I install a Fleshlight in my Roomba, does that make me a turtle-fucker?
I think the word you're looking for is "Democrat".
So we should rename PostgreSQL to Prequel?
Boo!
2 years and no mod points. Join reddit. Because openness is good.
and besides, development in Oracle is so slow, I can still use my Oracle 5 for DOS manuals while coding Oracle 9 for Linux!
Feature-wise, I agree PostgreSQL is way more loaded. And full, native transaction support prob. makes it more reliable too. So use it if you are building a banking system (or anything that deals heavily with money, where reliablity is paramount). But Mysql is designed for speed and average use on websites, where performance is important. So stop saying Mysql sucks because it doesn't have feature X or feature Y.
For losing data and poor performance with updates and inserts, Innodb gives you transaction and row level locking. These problems apply only to the default MyISAM table type. I use MyISAM for heavy SELECT tables, and Innodb for more updated and inserted tables, and in ones I don't want to lose data in.
VIVA1023.com | Political Fashion.
We have one client who uses Linux, the rest are all Windows-based... Is there an unbiased (as far as can be) comparison ?
Now, see your problem?
Neither PosgreSQL or mySQL are full, complete, and utterly perfect implemtations of a database. Neither is Oracle, BTW.
mySQL got a HUGE push some time ago. Back then, mySQL couldn't be beat for handling read-only (Actually, highly read-almost exclusively always). mySQL was a champ when you had a web site, mostly static catalog of products (for example), and had really limited demand for SQL (Like one query that read 'select * from catalog;')
That basis of comparison is no longer true.
So, at the time, hords of little corporate minions lined up and specified mySQL. Not a bad bet at the time, but open mindedness only seems to happen once in computer circles. Day 1 you have a need, day 2 you actually research available solutions, and day 3 you declare a "winner" and it is forevermore cast in stone as the "one true solution". The fact masses of people tend to go thorugh the same process at basically the same time doesn't help. Thus the broad noise that mySQL is "the Answer(tm)".
Anyway, postreSQL has always sought to compete in the full function space. Oracle was/is a much better "comparison" to postgreSQL than mySQL.
Now, both mySQL and postreSQL have improved over time - greatly. postgreSQL seems to be focused on getting things "correct", while mySQL doesn't seem so concerned. Bascially postgreSQL will not provide a feature, while mySQL will hacking it together in some bizzare way (re: early "transaction" handling). mySQL has quite a few anti-social behaviors. Over time, their refinement of those various behaviors drive certain development costs and create some degree of lock-in dependency (a continuing basis for self-justification).
Bottom line, if you invested in learning and implementing mySQL, and it is still working for you, then there is absolurely no need to be concerned with postgreSQL yet.
If you are in the database selection mode, you should surely look towards postgreSQL and try to de-hype yourself from any pro-mySQL bias. Hype has inertia and much of the pro-mySQL hype is based on old comparisons and narrow needs. Yes, evaluate both, but don't assume mySQL or postgreSQL is "better" based on what you hear.
isn't maintaining standards throughout development a good thing? people get upset if they upgrade and things magicall Don't Work anymore.
My other sig is an import.
I have never had the pleasure of playing with PostgreSQL - does it allow you to partition your database (multiple buckets) so that a flat search on all fields returns in a reasonable period of time (a la Oracle 9i Rac)? Along those lines does it allow raw mode access, as well as file access?
I would be interested in getting my hands dirty if it would be worth my while.
Lodragan Draoidh
The more you explain it, the more I don't understand it. - Mark Twain
MaxDB is a sort of merger between sapdb and mysql. As of today, MaxDB includes features such as:
* Views * Server-side cursors * Stored procedures and triggers * Automatic failover (to a standby server) * Scheduling and automatic messaging on alerts * Snapshots * Archive tables * Synonyms
And these are features in addition to mysql's feature set!
Check it out guys! http://www.mysql.com/products/maxdb/index.html
But don't be in too much of a rush to upgrade, mysql interoperability is slated for Q1 of 2004.
Enjoy!
And bill at a much higher rate because you still have to manually do (EXTENTS) everything that most other RDBMS providers handle automatically.
Saying Android is a family of phones is akin to saying Linux is a family of PCs.
Elephants aren't sexy.
These types of posts are lame.
Who the hell cares if you compiled this on some archaic beast and now have to recompile? Here, I'll give you $10, go buy a machine that's 5x faster.
Same with all those "Damn, I just downloaded the ISO yesterday and now there's a new version... teeheeee" get a life. No one cares about you and your 9600 dialup connection you're saturating using archaic means of installing an OS. (read: iso based installs)
You've heard of jokes right?
Alex
Just configure your defaults or use the defaults.
:)
Oracle allows you to specify extents because matching your data from block size, extent size and buffer sizes all the way up allows you to tune for your data, your throughput, your filesystem io and your information's typical size.
That is a feature my friend, not a nuesance
Off-Topic but still felt compelled to answer...
Some of the "gotchas" mentioned in your link actually make sense:
Which is to say that a string column that accepts NULL can have (wait for it...) null string ""... where's the gotcha in that?
The thing about timestap columns makes perfect sense... the insert into select thing (where you should just define temp tables...
Also please remember that MySQL tries to be a fast and reliable db server and i guess some of those gotchas are in fact side-effects of that
I wish I could filter out the annoying Pickens articles...
What? You didn't know MySQL ran via cygwin?
- C:\mysql\bin\cygwinb19.dll
MySQL just has a better installation process is all.BTW, I run cygwin on my WinXPPro laptop (the only way I'll get UNIX-like OS features on my Dell Inspiron 5150 sadly) along with KDE3. Yep and uh-huh.
-- @rjamestaylor on Ello
I suspect you've got the name wrong.
The language/system called S or Splus is commonly used for such things by financial companies. There's a free gnu version called R with an enormous library of prediction software:
http://www.r-project.org/
Don't expect to predict the market with it though. Incidentally, there's an interesting book called The Predictors about a group of physicists including Stephan Wolfram who were funded by a major Wall Street firm to write market prediction software (as opposed to the more common option pricing and risk avoidance packages that assume market efficiency)
Sure the more features you have the more complicated something is. But these do not account for the added complexity on a Windows installation or even a linux installation. I also find running the thing to be harder to run and manage even under linux.
I didn't see any source code there.
All I got was a core dump.
the preceding comment is my own and in no way reflects the opinion of the Joint Chiefs of Staff
IMHO, I find MySQL to me more along the lines of a db like Foxpro, DB2 or something...great for smaller, desktop or dedicated webserver needs...and just fine for that.
But, if coming from an Oracle background, I find that the built-in transaction processing...procedural language, sequences, triggers, etc....is closer to what you are used to. I'd feel pretty comfortable using PostgreSQL in a critical environment. And I really don't consider ANY data I have to keep....to not be critical.
My $0.02...
Light travels faster than sound. This is why some people appear bright until you hear them speak.........
Comment removed based on user account deletion
First of all, I hope someone knowledgable chimes in with some information about K. In the mean time, this is what I understand of it.
It is a language used in Kdb. A super fast database. The database (as well as, I believe, the language) is a array based. In other words, while most DBs access records on a row by row basis (if you want two column, the other three columns in a five column table will come along). It also doesn't do any transactions so it is not advisable to be used as a server with multiple clients (again, my understanding of it). It also tries to keep everything in memory.
On top of those, it has native support for time series. While most DBs deal with 'sets' of data points (and do unions, intersections, etc.), Kdb is aware that the data is a time series and records in the db as such. You can do queries such as 'return an element which comes after another element which is 5% smaller than the element before it).
Pretty cool, but need someone to explain it. (I've actually tried convincing postgresql people to implement it but they are a bit conservative).
I use PostgreSQL extensively, and I have had a hard time convincing my-mySQL (I'm so clever) exclusive friends to give it a try.
Maybe, just maybe that's because your-SQL friend is having absolutely no problems with hiSQL and is simply too busy going forward with his life instead of bitching about what tool he should use on the problem.
Never forget that "quality" is defined by the user, not the manufacturer.
echo '[q]sa[ln0=aln80~Psnlbx]16isb572CCB9AE9DB03273snlbxq' |dc
Who do you know that actually provides a Relational Database Management System?
There are lots of SQL DBMS's out there but SQL isn't relational.
Null values anyone?
Actually thats not true anymore. Lots of automated stuff in 8i (which has been around for a long time) and even more in 9i. Presumably even more in 10g but 9i takes care of all the major stuff.
My blog: http://jkratz.dyndns.org/~jason/blog/
I'm thinking I should give it a while. I hope it's better than MySQL, because, in my opinion, MySQL is overrated.
I've recently started a project using MySQL because we want to get away from the ridiculously overpriced licensing that MS makes you get. I'm not trying to be a troll or anything, but I'm trying to give a realistic viewpoint by someone who's used to MS crap, but really wants to switch over to open source.
I love Open Source and I definintely try to keep an open mind about it (hey, whatever gets the job done at are more efficient and cost-effective manner is good stuff) and am by no means a Windows/Linux zealot.
Take a look at MySQL's current state. Way behind on the times... for example: it *still* doesn't have stored procedures. Do you realize how annoying it is hardcoding SQL statements? There's complete lack of subquerying, which really makes it a pain to do certain calculations often requiring additional queries, which is extremely inefficient (although I do understand that it's currently in alpha).
Things like this.. make me think twice. If these useful features haven't even been implemented yet, then how can I (someone who's used to using MS crap) trust it?
I'm gonna give PostgreSQL a try and hopefull it has more functionality (and stability) than MySQL!
We have secretly replaced these Slashdot mods' sense of humor with a rusty nail. Let's see if they notice!!
...and you're advertising hostip.info in your .sig. The paranoiac in me questions your motives. Any self-respecting web advertiser wants geo-targetted banners...
http://www.kx.com/
A Simple search of google of "kdb database" gives this. They in their website referance the "S" languange as well as other information. Giving creedance to the other poster's information on S not K as the programing language.
http://osb.sra.co.jp/PowerGres/introduction-en.php
Damn! All these years, I've thought jokes were supposed to be humorous.
Well, thanks (sortof) for supporting my contention that "K is NOT S" (though now I'm curious what 'S' is, wish that guy had supplied a link), if you had clicked the link in my initial post, it would've taken you...well...here, smart guy!
But, since I am at work and don't have time to read the entire site before I get home I'm hoping some nice poster (or even a nasty one! I'm not being picky) will chime in with some useful info on whether 'K' is
Quod scripsi, scripsi.
I have to agree I have used the following databases on enterprise applications mySQL,MS SQL, Oracle, DB2, and Postgre.
I have to say that I strongly Prefer Postgre over mySQL but I can work with both. On windows environment however you will see me using MS SQL 2000(with ADO.Net the applications are incredibly fast much faster then Oracle using a JDBC driver). I personally Hate Oracle I think its a waste of money and a Overpriced piece of bloated crap. Much like most people think about Windows.
MySQL is a great product it just does not give me much insentive to use the product other then the price. Ease of use is just not there. Postgre is much easier for me to use. I can accomplish my goals with either database but I usually have a very limited time constraint to design an east to maintane database.
I recommend to Anyone I know if they are building on a Linux Platform Postgre and Windows Platform I recomend MS SQL 2000.
"Structured English QUEry Language"
p =r eferences&dl=GUIDE&dl=ACM
http://portal.acm.org/citation.cfm?id=811515&jm
which is much harder to use than SQL.
Now you know.
Maybe try a CDROM based Linux version?
...fail to work with WebSphere CMP :(
I understand your comment, but to me it's both a feature and a nuisance. Oracle, out of the box, seems to require an in depth knowledge that you shouldn't need just to get a small database rolling. In the past, they weren't concerned with small DB users, but that's a market they've decided they wanted to break into in the last couple of years.
While MS SQL Server isn't the best option for VLDB's (although it's getting there), nor is Sybase anything, it (as well as DB2) doesn't just automate the table EXTENTS for you, but manages cleanup automatically. Oracle's filesystem, IMHO, was a mistake on their part. When you remove data from tables, the extents are still there. The other players in workstation/enterprise RDBMS's handle that for you automatically, and while Oracle allows for extents to be defaulted, there is no way to clean up the mess left behind a DELETE query.
Oracle's standard practice for cleaning that up is to remove all of the data, wipe the DB, rebuild it, and copy the data back in. That's just preposterous if you ask me. And regarding defaulted EXTENTS, we had to default but the initial table size and extents to the same number (which was around 1MB because of the initial table size) to ensure not having to manually clean up the DB from time to time. So our 2GB database takes 7GB of space on the HD. Not a big deal, but it's still silly that we have to do that.
Saying Android is a family of phones is akin to saying Linux is a family of PCs.
I stand corrected, K is not S or R. I just downloaded the demo version from here:
http://www.kx.com/download/download.htm
Strangely, the whole install comes in at only 308k. Suffice it to say that I can't imagine there being much in the way of statistical models in there.
Kdb's niche is in handling huge amounts of time series data, such as every tick of every stock on an exchange. A regular db isn't designed for such frequent small updates, and also can't optimise tasks like correlation. What you really want to do to compute a correlation is step over two arrays in parallel. With a normal database, you'd have to fetch large chunks of data from each table and then running your calculation in main memory.
Here's a good overview of array databases generally, as well as Splus and Kdb in particular:
http://www.cs.nyu.edu/shasha/papers/jagtalk.html
As far as I can tell, the thing K gets used for most is technical analysis, the strategy Malkiel criticizes but that remains popular. If you want to find all the cases where stock broke through its three-month high on a Friday after 4:00, Kdb is the way to go. Unfortunately, when I looked a few years ago, there didn't seem to be any way for a private individual to get historical intraday data in any volume except to log it yourself from a subscription quote service.
Here's the Splus homepage, btw.
Actually, this is in 8i, and admittedly, I have no experience with 9i or 10g (we'll never need the 10g power here, at least not in my careerspan). I posted a longer commentary here regarding the manual processing we have to do, and Oracle's recommended practice.
Saying Android is a family of phones is akin to saying Linux is a family of PCs.
PHP geeks, don't forget to check out plphp. You can create functions/triggers with php for PostgreSQL.
Robby Russell
PLANET ARGON
Robby on Rails
...everywhere I look, there are guides and how-to's on MySQL and php. I'd very much like to learn and use modPerl and Postgres, but I can't find books published in the past year (or two), I can't find how-to's, there isn't much documentation (other than the sites themselves), and the general lack of info is really holding me back.
...postgres may have better SQL compatibility, but MySQL is being used in mission critical applications, and with the buzz, and the development behind it, and the enterprise support, postges users shouldn't hold their noses so high in the air when it comes to looking at MySQL. I see linux overtaking windows. This is because it has the developer momentum behind it. If you see this also, why don't you also see that MySQL has the momentum behind it as well?
I fear I'm going to have to hold my nose and start learning MySQL (and possibly php), but I'd rather not.
Why isn't/where is documentation/how-to's/guides on building databases and/or web sites using modPerl and Postgres?
I have the Postgres book, and all the manuals from the Postgres site. Where can I/Can I get more info?
Does MySQL have all the buzz because it's pushed by a for profit company? Is postgres similar to Debian and that's why it doesn't have the buzz that MySQL does? Mickey what's his face is always in the news, MySQL is always in the news, they are continually announcing partnerships with enterprise developers...
If Postgres doesn't make an effort to market itself better, at some point in the future, MySQL will surpass Postgres in every category simply because it has the momentum behind it. Just like the windows/linux battle.
MySQL has documentation, guides, how-to's, and whatever else coming out the ears. If postgres wants to become more popular, its going to have to address this.
Maybe postgres and its advocates want to keep newbies out?
Still no "WITH RECURSIVE" construct or even Oracle's version, "CONNECT BY, to allow for the quick selection from tables that represent graph-like or tree-like data.
File under 'M' for 'Manic ranting'
I happen to think that Postgres IS the technically better DB, but accessability is just as important [installers, admin tools, etc] to its preception and adoption. MySQL grows because it's "good enough", simple, and free...Postgres is just "better" & free.
Is this some kind of a sick joke?
Disclaimer: I use Postgresql and the JDBC driver everyday at work and at home. And I've actually read the source code. And I actually like it nevertheless.
The JDBC drivers are clearly not the main focus of the PostgreSQL community. Here are a few nasties:
-- Very very bad documentation. There is no way to known which features are implemented and which ones aren't.
-- On the same line, the driver claims to be JDBC3 compliant, which is simply not true.
-- When you post a request on the JDBC list, there is always some guy with a 3-month old patch that could solve your problems. It seems that very little development is actually happening.
-- The driver is a pain to compile: You must compile Postgresql at the same time, otherwise it won't work.
-- There is some kind of support for Postgresql distinctive features, but you must read (and fix) the code to make them work.
-- SLOW! DOG SLOW! For example, there is no support for prepared statements (it's my understanding that Postgresql does not support those anyway)
-- Some smaller weirdness, but well I guess that every package as those.
Otherwise, I've never have an actual reliability problem with the driver. Once you have all the workarounds, it simply works.
"Well go fix it then!". First, someone is already working on that, I guess he could use a little exposure (http://sourceforge.net/projects/jxdbcon/).
Second, for my pet project (WKB4J , Boost your Java GIS now!), I just retrieve the raw results using a binary cursor and my queries are about 15 times faster. I agree that this is a particular case.
David
Nobox: Only simple products.
DB2 in the same league as Foxpro, you have to be joking, right???? DB2 is the largest deployed enterprise database in the world. It's bigger than Oracle in all but a few markets (like SAP/Peoplesoft and a few other ERMS's). It's also scalable and reliable beyond anything else bar Terredata's data warehousing stuff. Gartner has shown DB2 growing since late 2000, mostly at the expense of Oracle, people are more and more finding that good old DB2 is just as capable at a fraction of the price. I don't mean to sound like a DB zelot, but lumping DB2 with Foxpro is just too much for me to handle =)
There are 4 boxes to use in the defense of liberty: soap, ballot, jury, ammo. Use in that order. Starting now.
I'd love to use postgres but tell me, how can you run a database for any serious application without point in time recovery, which postgres does not support. How do people out there deal with this shortfall?
I think he meant dBASE2, the long forgotten ancestor of Foxpro. In fact he probably meant db3, which was a mysql-like database-like PC application with built-in programming language and reasonably fast access using flat files and indexes.
Otherwise, I agree with your post. I work in an Oracle shop, and though it's a great product to work with--gobs of support software and 3rd party support--it's pricing is ridiculous. And DB2 seems to be getting more interesting with cross-platform support and that huge IBM support behemoth behind it.
Everything I've ever learned the hard way was based on a statistically invalid sample.
Yes, if you run simple queries in a single user scenario you do get better performance with mysql than with pg. With more complex queries and more users however, the simplistic query optimizer and concurrency manager that mysql has makes it perform worse than pg.
The Raven
FYI, Postgresql does support prepared queries.
--- It is not the things we do which we regret the most, but the things which we don't do.
I believe calling it "YourSQL" will make more developers aware of it...
We are in the midst of moving our databases away from Oracle. There were three contenders: MySQL, Postgres, and Matisse (OODBMS).
Speedwise, PosgreSQL trails the pack by a fair bit. Sometimes it would be comparible to Oracle, and other times it wouldn't be without a fair bit of tuning. Outer-joins, for example; the optimizer can't seem to make heads or tails of it.
I spent two years lurking on the Postgres lists, and when doing performance testing, was asking for help tuning queries and the database in general; this isn't a statement made based on, "I tried it once, and it didn't work."
The guys on the list (especially Tom Lane) were very helpful and polite, but I just couldn't get reasonable performance out of the database without doing some serious SQL-rewriting (our CTO thinks that relational databases require too much tweaking already; putting optimizer hints into the queries is just too much).
Overall, the database is great - great feature set, great developers, and a good support community, but the optimizer is not efficient enough (search for the word optimizer in the PostgreSQL lists, and you'll find hundreds of posts where the optimizer is doing a sequential scan and ignoring indexes when it should be using those indexes).
MySQL (4.0.16, using InnoDB tables) has foreign keys, transactions, etc. I haven't been able to crash it yet (I miswrote a query on purpose, and let it run over 2 days at 99% CPU, and the machine stayed up, and is still up a week later).
MySQL doesn't have triggers or stored procs, but as a DBA and senior developer, I can honestly say that's a good thing.
- if you modify a table that a trigger or stored proc uses, chances are the trigger and stored procedure are invalidated quietly behind the scenese - the database doesn't tell you until you call the stored procedure or execute a statement that causes the trigger to be executed.
- debugging a stored procedure or trigger is not easy.
- people tend to forget about triggers and stored procedures; they're hidden logic that can cause no end of problems.
- triggers and stored procedures are (in most cases) database-dependant; they are a huge hinderance when moving to another database. We have 12,000 lines of Oracle stored procedures. I dislike them.
- the database is for data storage. It's not for application develoment. Keep the business logic in the application, and the data-storage logic in the database. Oracle is trying to sell their RDMS as a development tool to justify the price. Don't believe the hype.
PostgreSQL is trying to position themselves as an Oracle replacement, and thus have a similar feature set. PostgreSQL is also very good at very large databases (probably even more so than MySQL, at least until InnoDB gets multiple tablespaces in the next release).
Databases with simple queries where results are not needed instantly would do well with PostgreSQL.
post-gray squirrel vs. my squeal
Yeah, a bunch of legacy slop-code dumped by a company that didn't know what do with it. So the MySQL boys will be "incorporating" SAP DB functionality into MySQL? That sounds like a multi-year rewrite, campers. Don't hold your breath waiting for a migration kit or interoperability.
They could have saved themselves the time and expense and gone and bought a database concepts book for $50 instead.
Can you easily return result sets from stored procedures with this release?
Also, anyone know if the python stored procedures are more native or still just external programs?
Re-read the post you're replying to.
What it actually does is change the table definition
You're not winning any points here.
Should I have used PostgreSQL?
I have set up a box at work running Redhat 9, Apache, MySQL and PHP. MySQL has proven to work excellently, particularily with Dreamweaver. My problem is that soon in the future, I am going to need transactions that will need triggers and proper database integrity.
The only reason I choose MySQL was this. phpMyAdmin has proven to be the best web-based tool I have ever used for any Unix database system.
I started using PostgreSQL, really I did, but I kept running into problems of things just not working. (I even tried pgMyAdmin, but it just wasn't the same).
It came down to MySQL was more popular and I found it easier to use. I am very busy and don't have a lot of time to spend training myself on the database side of things, but I'd be willing to give PostgreSQL a second chance if I knew it would have some must-have features.
SO... is MySQL good enough for databases in the 500MB and 1000 transactions/day range? Are there any must-have features for PostgreSQL that I should change my mind?
I'm SURE there are other people with this same question... anyone have experience with both want to comment?
Didn't a Japanese hardware company fund Ellison early on?
Might not always be the fastest...but I can't imagine developing a database application - and putting all the join code in the app - that could instead be a subselect, inline view, etc.
I'm not a huge fan of triggers - but often find circumstances in which their careful application has saved a huge number of hours.
Stored procedures are another example of a technology that when used wisely can result in a huge improvements to flexibility and adaptability. My favorite use of them is when on a fast-moving project - I can have the developers create an object model and almost immediately start developing to it. My first task is to knock out a set of stubbed stored procedure (or sometimes views) that map to that object model. Often within a week they've got a fully working application (with a fake backend) to continue testing and tweaking. Then I can take my time mapping the object model / stored procedures to a relational model - perhaps using some advanced features that would have been time-consuming to build and test. The net result is drastic reduction in development time, complete dba-control over all queries, and the ability to change the model occasionally (as performance or whatever demands) without any impact to the developers.
The concern that stored procedures aren't portable isn't completely valid either. Stored procs shoudn't be complex - simple ones can be easily ported between most databases.
Views, unions, and subselects are other sql capabilities that the mysql-ab team has frequently referred to as being only useful to 1% of the applications out there. And unlike the triggers and stored procs described above - I'd consider these core database capabilities.
You have that many email addresses?
WOW!
The man who trades freedom for security does not deserve nor will he ever receive either. - Benjamin Franklin
I use explicit cursor fetch statements myself, but how do you use binary cursors? When I tried that (from Java, with PostgreSQL 7.3 and the standard 7.3 driver in Debain), I got errors -- stream errors, if I remember correctly.
Anyone who needs to use COUNT() is completely out of luck. Can't use PgSQL. This problem has not been fixed in the latest release:
Meaning horrendously slow for large tables. "Replication", "Enterprise features" when basic stuff like this is broken. This is a shame
Is there any open source/free OLAP software out there that I can use with Postgres or MySQL? I'm a student and can't afford to shell out big bucks for proprietary OLAP.
See also A Brief History of PostgreSQL
Programming can be fun again. Film at 11.
I'm not sure if I want to use PostgreSQL. I'm afraid SCOracle will start claiming that it infringes on their copyrights.
What's wrong to use PostgreSQL on Windows? I know few projects (including web-sites in production) successfully using PostgreSQL (under Cygwin) on Windows accessible through ODBC and JDBC. The have made performance evaluation tests (comparing PostgreSQL/Cygwin vs MS SQL 2000) before finalizing their decision and haven't find any significant performance advantage of MS SQL.
Less is more !
One good reason you might want the database to have some say in the caching is to prevent cache pollution.
Oracle, for example, implements a split cache with hot and cold halves. Small reads get added to the head cache, while major reads (such as full table / index scans over a certain threshold) are added half way down the LRU stack in the "cold" half.
The idea here is that full scan type activity is less likely to provide cache hits on future queries. Also- full scan blocks come at a much lower cost per block than single, random access blocks, and therefore represent a lower value / storage ratio.
This is the kind of guess that's difficult for an operating system to make.
Neither PosgreSQL or mySQL are full, complete, and utterly perfect implemtations of a database. Neither is Oracle, BTW.
I completely agree!
Back then, mySQL couldn't be beat for handling read-only
Actually, at the time, most real benchmarks proved that to be false. MySQL and several others performed some benchmarks, using the current version of MySQL and outdated versions of PostgreSQL. Futhermore, most tests were a single query against a database. The result, MySQL was wicked fast. This was done because most web guys didn't know anything about DB's and so they did what they thought was a good test. They tested what they knew. The tested a single query with a single user and it was, indeed fast. In fact, faster than PostgreSQL. The problem was, few people tested concurrent access (typical of web sites). Those that did found that the speed advantage of MySQL very quickly disappeared and PostgreSQL shinned wonderfully. To date, MySQL's performance myths still persist. The simple fact is, MySQL has never been a performing beast, as most people think. Simple fact is, even all those years ago, PostgreSQL almost always out performs and out scales MySQL at all but the most basic of SQL tasks. And so, it became lore that MySQL is wicked fast and that PostgreSQL is wicked slow, but feature rich.
Since those days, PostgreSQL has gotten much, much faster and now scales much, much better. MySQL, I believe has worked some on it's scalability, however, the last thing I saw, it's still far behind PostgreSQL for all but the most basic of SQL tasks.
That basis of comparison is no longer true.
The generally available comparisions were never true. Generally speaking, everything that MySQL ever put into print on benchmarks were lies or willful efforts to mislead and misrepresent the truth. Simple fact is, MySQL did everything they could to prevent being compared toe to toe with a modern PostgreSQL. It doesn't take a rocket scientist to figure out why that might be.
Day 1 you have a need, day 2 you actually research available solutions, and day 3 you declare a "winner" and it is forevermore cast in stone as the "one true solution". The fact masses of people tend to go thorugh the same process at basically the same time doesn't help. Thus the broad noise that mySQL is "the Answer(tm)".
Couldn't agree more. Worse, more often then not, the people picking the RDMBS solution are completely unqualified to make such a solution. Then, the legacy "winner" finds its self as the solution over and over again.
Anyway, postreSQL has always sought to compete in the full function space. Oracle was/is a much better "comparison" to postgreSQL than mySQL.
Absolutely correct!
Bottom line, if you invested in learning and implementing mySQL, and it is still working for you, then there is absolurely no need to be concerned with postgreSQL yet.
I'm not sure I buy 100% into that statement. Just the same, I think it rings true enough. I generally tell people, if you wouldn't use an Access DB for your solution, then you shouldn't use MySQL. The funny thing is, Access actually has a richer set of features than even MySQL does. I think the Access comparision comment still holds true today.
Yes, evaluate both, but don't assume mySQL or postgreSQL is "better" based on what you hear.
I strongly believe in "best tool for the job". As such, it is true that PostgreSQL may not be the best tool. Even the PostgreSQL developers will tell you that. Just the same, I often find that MySQL is rarely the best tool for the job. That's not to say that it's not a good SQL introduction and *maybe* as a learning tool, it might have value. The maybe is there because if you use it as a learning tool, it's going to teach you a lot of bad habits.
Yeah...I think Oracle IS starting to price themselves out of a lot of markets. I'd love to learn some DB2....but, how? Where?
Light travels faster than sound. This is why some people appear bright until you hear them speak.........
> Anyone who needs to use COUNT() is completely out
of luck. Can't use PgSQL. This problem has not been
> fixed in the latest release:
why would you be doing count(*) on *huge* tables without a where clause anyway? I mean, sure - I run queries like that all the time - in adhocs, where a scan of 100+ million rows might take 10-30 seconds.
But I can't think of the last time I've built an application and needed to do count(*):
- without a where clause
- on a huge table
- and needed fast response time.
Also note that most databases don't seem to store the min/max values for non-index columns to speed up this situation. Informix does, IIRC, but I don't think that Oracle does.
So yeah, it would be nice if this was improved, but really - I use this functionality all the time, and am completely unaffected by this issue.
Well, your best bet is to read the source code of my project, it's in one of the classes. Otherwise, they must be used in a transaction and I think that Postgresql supports only one binary cursor at the same time.
David
Nobox: Only simple products.
select column from table order by column asc (desc) limit 1;
"I love my job, but I hate talking to people like you" (Freddie Mercury)
is indicative of the problem with Postgres. I'd say that if you were asking questions about MySQL, you'd have a ton of answers and links. This is the same problem newbies experience on linux distribution mailing lists looking for help. Linux is great. Linux is ready for the desktop. Linux is ready for mainstream. Just don't ask any questions without becoming a developer first.
If you had mentioned that you were an Access user, they probably would have hacked slashdot to get your ip address, and come looking for you.
I never have mod points when I need 'em. If parent doesn't deserve
+1 informative, I don't know what does.
*sigh* back to work...
you mean the database calls aren't abstracted in either piece of software?? i would think you could just switch a flag to change databases, if you coded it right. i've coded a db abstraction object in php before, but wonder if the SQL is similiar enough to completely facilitate this. easiest to tell which database is fastest if you can change them on the fly ;)
Might not need the grid stuff in 10g but they always add stuff that is handy for lots of other things.
That being said I read your other posting. I know (and was aggravated by in the past) the goofy space management issues with Oracle but if you delete records that space becomes available for more data. Sure it's not reclaimed by the OS but it's not like you delete data and can never use that space again.
My blog: http://jkratz.dyndns.org/~jason/blog/
That's true, and we did find a workaround. The workaround was to set up initial tablespace at 1MB and EXTENTS at the same size. Fragmentation never occurs now. We take up extra drive space, but space is cheap (same arguement folks make about taking too many clocks in code.. Moore's law will fix that for you, yada yada).
I don't want to sound like an M$ zealot, but SQL Server handles all of this seamlessly. I have a very hard time imagining why folks use Oracle over SQL Server, other than the ability to run it on UNIX boxes.
Saying Android is a family of phones is akin to saying Linux is a family of PCs.
Because Oracle offers a huge featureset above and beyond what SQL Server offers and runs very well on NT ;) It also costs a hell of a lot more (well...can cost more as you can get lower spec versions of the main db product). Really....with locally-managed tablespaces (available for most tablespace types in 8i R3 (8.1.7) and all AFAIK in 9i) you don't have to worry about this stuff anymore.
My blog: http://jkratz.dyndns.org/~jason/blog/
8i R3 (8.1.7)
Maybe that's the problem.. I think our DB's are on 8.0, though I have to admit since I don't have DBA privelages, I haven't paid that much attention to which release.
I wonder, though, about this comment...
Oracle offers a huge featureset above and beyond what SQL Server offers
Got any examples? I haven't seen anything that I can't do with SQL Server other than the nice exception handling in PL/SQL (although being able to do a SELECT TOP 1 query like t-SQL would be nice).
Saying Android is a family of phones is akin to saying Linux is a family of PCs.
Look at the 9iR2 docs on otn.oracle.com (dunno if this requires registration but its free). Look at the new features of 10g database.
Oracle has made huge changes since 8.0x. I started being a DBA on release 8.0.5 but that was over 4 years ago. Its almost a completely different database these days and to be honest, especially on 9iR2 which is what we're using now, I don't have to do much.
My blog: http://jkratz.dyndns.org/~jason/blog/