Slashdot Mirror
PostgreSQL 7.3 Released
rtaylor writes "Nearly a year's worth of work is out. The new tricks include schema support, prepared queries, dependency tracking, improved privileges, table (record) based functions, improved internationalization support, and a whole slew of other new features, fixes, and performance improvements. Release Email - Download Here - Mirror FTP sites (at bottom)."
PostgreSQL is a great database. I always run it as a daemon on my iBook since the Smallttalk development environment that I run needs a relational database for source code control.
-Mark
Did they do anything to improve/add replication support? That seems to be the only real thing that was holding it back from replacing Oracle, as far as I can tell. I know several projects for such a thing were in the works, but they appeared to be very beta.
Software piracy is victimless theft.
Replication out of the box?
For "simple" stuff, MySQL is (supposedly) quicker than PostgreSQL but you might want to check the following link for just why MySQL sucks for non-toy apps
WOOHOO!
DROP COLUMN [column] FROM TABLE [table];
This up-until-now lacking feature has been the bane of my existence. I HATE cruft being left lying around.
(btw, I don't know if that is the correct syntax, just a guess)
My
Limekiller
OK so you have found software that addresses your problems and that accounts for the conditions that are vital for about 0.0001% of people who are looking for a database. For everyone else who is looking for a database solution PostGres, MySQL etc might just be worth a look in.
It's an Oracle killer based on the fact that 80% of Oracle installations out there are overkill.
Seriously, look at the amount of Oracle installations out there. Now how many of them _need_ any of those features? Is it worth the extra cost of Oracle? More than likely it's all some marketing crap that eveyrone is led to believe than "only Oracle can do this".
My PHB is like this. He insists we use Oracle because we need an "industrial strength database", for a db with 40,000 records. Argh! Oracle is freaking expensive, we got Larry Ellison crusing around in some damn yacht race on our bill. In our case, Postgre would be an Oracle-killer, it's just getting people past that fact that Oracle is unnecessary for alot of applications.
PostgreSQL now supports the ALTER TABLE
HURRAY! this has been my biggest annoyance with postgresql since I've started using it. there are workarounds for older versions, but they become arduous when you have a lot of existing data.
this is a *very welcomed* implementation.
There aint no pancake so thin it doesn't have two sides.
You are linking to something that counts security advisories for Linux and compares them with security advisories for other OS'es. You seem to have misunderstood that security advisories for a certain OS are something entirely different from security advisories for a RDBMS. The 2 are completely unrelated.
p orted-platforms.html
In fact, if you want to you can run PostgreSQL on Windows if receiving less security advisories is more important than the time-to-fix. Or if you feel really paranoid, just run PostgreSQL on OpenBSD. That gives you an excellent safety record, with only 1 remotely exploitable security problem in nearly 6 year.
For a complete list of supported platforms see http://developer.postgresql.org/docs/postgres/sup
see subject
___
If you think big enough, you'll never have to do it.
Dancing Girls
The PostgreSQL now includes a number of beautiful dancing girls
I can't tell you how long I've been waiting for this feature! Now I can get rid of Oracle for GOOD!!
Kudos to the PostgreSQL team!
That article is 2 years old. MySQL has advanced greatly since. The author of that paper seems to suggest that the only reason to not use MySQL is for its lack of transactions. Well boys it is in and works great.
Do you want to live in a world where things like the GUI, 3D graphics, wordprocessing, webserving, and other commercial products were never developed?
With the exception of wordprocessing all of the innovations you cite were developed by academic R&D teams, NOT by commercial software vendors.
This one exception, wordprocessing was NOT an innovation arising from the computer revolution - it's roots go back to IBM Selectric Magcard typewriters.
All the commercial vendors have done is copy, copy and copy.
Citations:
GUI - Stanford Research Institute Augmentation Center
Wordprocessing - IBM Typewriter Division
3D graphics - Evans and Sutherland, UofUtah and Harvard
Webserving - T. Berners Lee, CERN
The fact is that if the closed source behemouths were to dry up and blow away, it would probably enhance innovation by reducing the barriers to entry in the marketplace imposed by the likes of Microsoft.
After all, what VC is going to fund a new wordprocessor these days? VC's know all to well what Microsoft would do to any new market entrant that starts to gain traction. They have the horrible example of Netscape to look back on.
Prevent email address forgery. Publish SPF records for y
That article's very much out of date, as is stated in an addendum at the top and as is made clear in the later posts in the discussion of the piece.
...
I'm the OpenACS project manager so want to make sure that people understand that the piece (and much of the commentary) was accurate when written, but that it was written many moons ago. However, nowadays MySQL has the InnoBase backend which provides full transaction support, and has seen other major improvements.
Our project only supports Oracle and PostgreSQL, and I still feel MySQL is lacking in many areas, but it has improved greatly in the last couple of years.
So has PostgreSQL, of course! We love it
Of course, he uses it on a Thinkpad, but you have to spend money to make money.
You are not alone. This is not normal. None of this is normal.
so it simply makes sure it IS death and it stays that way..
Nobody expects the spanish inquisition!
Whoa, buzzword attack!
/me gets tinfoil hat
Well one of the most important subjects on slashdot is the broader acceptance of open source software. Things have been going pretty fast lately; we have a state-of-the-art office-suite, database-server, browser, movieplayer etc blabla you name it. Many of those things weren't ready for prime time until very recently. Now they're getting there _fast_ so it is to be expected that some of the (now much more frequent) major milestones in the history of broad acceptance of open source get posted on slashdot. Though I agree with you that not every Linux kernel release should be on Slashdot. But this release of Postgresql has some VERY important features which will make it much more interesting to use as a replacement for Oracle or Sybase.
0x or or snor perron?!
Enterprise noun def: an organization so large that the people who buy systems don't know how they work and so have to hire someone who does.
PostgreSQL can handle the 50 million rows provided the data structures are well-designed, and according to their press release they can handle the replication (it's always dynamic). The query rate of 2000/sec is more a question of the server hardware, server configuration, and network infrastructure than the database software per se. I don't know about "complex 3D boolean queries" but I know for a fact that PortgreSQL can process big, ugly, inefficient queries pretty well.
What makes M$SQLServer and Oracle more "suitable" for enterprises is their add-on tools (both have loads, though of varying quality) and the fact they maintain service centers for when the DBA throws up his hands and gives up. From my perspective though, the reduced licensing costs (even for large installations, we're talking about hundreds of $'s times thousands of users!) could pay for a lot of up-front hardware and ongoing code review. If I were starting a largish e-commerce project today, I'd start on Open Source just because or the reduced up front cost. You don't become among the richest people in the world by giving anyone a good deal.
Everything I've ever learned the hard way was based on a statistically invalid sample.
Well, if you insist... :-)
It's better for certain things (most things, actually). PostgreSQL is a bit more feature-complete as a SQL database than MySQL is. MySQL is improving, certainly: it now has transactions and such. But PostgreSQL has quite a bit more: triggers, rules, stored procedures, and views, for instance.
In terms of speed, MySQL is faster for certain specific operations but that speed comes at the price of database integrity: the lack of rules and triggers means that it is not possible for the database to enforce consistency between tables. One must thus trust applications to do the right thing, which is generally not wise.
It's like the difference between an OS with memory protection and one without. The one without may be faster for certain things, since the OS doesn't have to worry about messing with page tables and dealing with page faults of various kinds, but the price is that you now have to trust the applications running under the OS to do the right thing and not touch memory that doesn't belong to them.
As I said, MySQL is faster for certain things. But PostgreSQL is reportedly better at handling lots of concurrent transactions than MySQL. It's not clear, then, that MySQL is much better than PostgreSQL, if at all, under high load situations. And if it isn't, then there's really little reason to go with it over PostgreSQL.
Finally, even if MySQL is faster, it's not likely to be so much faster that it is the difference between success and failure. And I can tell you this: experience shows that the initial requirements of a project are often vastly different, and usually much less demanding, than the final requirements for the same project. So it makes more sense to go with the most capable database backend you can lay your hands on, as long as it remains within your budget (your real budget: remember that you're likely to spend a lot more money than you expected, if only because the requirements will change over time). That means going with PostgreSQL over MySQL, if given the choice. You have to make the decision early because changing your database engine mid-project is extremely difficult, especially if your code was written to work around the limitations of the database engine, as it almost certainly will if you're using MySQL.
These days I don't think the question should be whether you should go with PostgreSQL instead of MySQL. It should be whether you should go with MySQL instead of PostgreSQL. PostgreSQL should be the default choice these days, because it is so much more capable at the same price.
Use 'slashdot stuff' in the subject line in any email you send me if you want to get past the spam filter.
If mysql is 'borking' at anything over 8000 rows, I'd take a real hard look at my design if I were you. I did a conceptual demo for a client once, where we sucked up a raw data file from their mainframe - 65000 rows, five tables, and the largest table had about 18 columns. The import took all of 45 seconds, and there was absolutely no performance problems of any kind. This was being assessed to determine how much faster this would have been compared to a RAD-based solution they were currently using. But 65,000 rows is small potatoes.
Thats exactly what it means..
SELECT * FROM function()...
Rod Taylor
Move on. There's nothing to see here.
No, it hasn't. A summary of the list of missing features:
Of course, they give crap rationalizations for each, and/or that "it's planned for [distant version of MySQL]". Of these features, only the last might be considered trivial, and even that is quite a pain if you're trying to write some portable SQL.
The others, particularly the lack of triggers and foreign keys, make this a data integrity nightmare for anything nontrivial.
Sure, sure, "but you can do it all in code": typical response. You know, that was their response to lack of transactions, too. "Too slow", "you don't need those". Right. You could just write a whole database in your code, too. The point of using a RDBMS (and, lacking relations of any sort, makes MySQL just a DBMS) is reliability so you don't have to constantly worry about these things.
PostgreSQL has all of the above features, and quite a few more. It's an OORDBMS. (Yes, this is very cool, and lets you do some very nifty things.) It's got better-than-row-level-locking (MVCC; MySQL does table locking only.) And all the other things people have mentioned here.
MySQL is a toy database, and should be treated as such. Not just for transactions: for all the things that make a robust RDBMS.
Don't think of it as a flame---it's more like an argument that does 3d6 fire damage
Maybe it was a troll, but here's the proper information:
- Transactions are available to MySql.
MySQL 3.23 release has several major features not present in the 3.22 or 3.21 releases. These include: full-text search, replication between a master and many slaves and several new table handlers that support large files and transactions
- Is that information about foreign key constraints in the MySQL manual? .. Why, yes!
Are record locks really a non-existent issue? Maybe the MySql user manual can shed some light on that point: "Performing a read in share mode means that we read the latest available data, and set a shared mode lock on the rows we read."
- The stored procedures and triggers are not here yet. Thank goodness something in that old link doesn't need to be refuted!
So yes, you might want to check out that really old critique of MySql, but then again you might want to look at the MySql.com website if you want current information. Then you can compare the newest PostgreSQL to the newest MySQL.
Main feature I've been waiting for replication.
:)
As of a couple of months ago none of the replication options for postgres were any good. Most were unreliable, offered very small features or very hard to set up.
Some looked like they had promise, but were not there.
Please, please, please, add replication to the next release
I also wish performance for simple case dbs was faster. eg key value dbs compared to the performance of sleepy cats berkley db.
I'm sure there would be a *lot* of money to be had if someone were to make a good replication system. Possibly releasing it blender styles? Or offering to implement replication for businesses for a fee?
Perhaps one of the postgres groups could ask for donations from some of us users so some developers could work on it full time. I know I could easily convince my boss to cough up for it. Almost any business that relies on postgres could be convinced to chip in I think.
FWIW, I once fixed up a company hopelessly wedded
to Oracle by essentially replacing all their
reporting systems by just:
The end result ran under 10 minutes, while the old
crap took overnight.
My motto has always been, get the sh*t out of
the fabled "DB" as soon as you can. Then you
can use awk/python/perl whatever to actually
do something useful with it.
When I installed postgresql 7.2.x, unicode was supported automatically! I use it to store chinese and japanese text in a dictionary type lookup database.
The beauty was that the java code would automatically convert the original Big5/JIS/other encodings to UTF8 first, and then JDBC would store the unicode into postgresql.
I remember playing around with Postgres a while ago and looking at the supposed OO features, and quite frankly they didn't seem very impressive at all. Basicaly just 'inheritance' of table structures. I mean... Color me underwhelmed. I can't think of a single reason why you would need this.
It didn't have nearly the same kind of features of true OODBs, What exactly is the use for Postgres's OOness?
ReadThe ReflectionEngine, a cyberpunk style n
I agree with you -- it was one of the few attempts at humor here that actually came off.
Now, everyone joins the VP-MKTG's bandwagon and wants their new reports compiled and summarized that way. In MySQL, without support for views, every query ends up having to be constructed again, including the tortured logic involved in having no sub-selects or unions. With view support, all we need to do is toss that awful query into a view, and select out of the view. It's not gorgeous, but it works. And you can even hand off the view to the other developers, so they don't get stuck in the quicksand of recreating the logic from scratch.
And now, because PostgreSQL supports functions that return datasets, we can toss all that logic into a function, and call that instead.
So, in answer to the question of what can PostgreSQL do that MySQL can't do: unions, subselects, views, functions. All are time savers. Lacking them, we can devise work arounds, but having them is very, very nice.
Everything I've ever learned the hard way was based on a statistically invalid sample.
Well, I would definitely call the management of Slashdot 'idiotic'. And I certainly don't have much faith in them technically.
MySQL was used for the US census's website, not for their central purpose. I would be very suppressed if they were actually storing the real data in a MySQL database.
And again, MySQL was used for a small part of Yahoo (the finance stuff) not for storing their link catalog.
ReadThe ReflectionEngine, a cyberpunk style n
This is slightly off-topics but anyway...
There have been some references to msAccess here, what I like about Access is the ease I can build an ad hoc database application (but where the data could be reused easily should there be a later requirement).
While Postgres sounds great, I want to know if there are and tools that approach this ease of development, within a linux environment. Ability to choose the back-end database would be a huge plus - I'd certainly give Postgres a go.
RG
And just b/c MySQL might be the right tool for a given job doesn't mean that job isn't "REAL WORK". Kinda like the whole Linux/Windows debate...it's all about suiting the app/OS/whatever to the given task. And MySQL performs very well for tasks that have lightweight database needs.
We've been running Postresql in production for over 3 years, we have hudreds of thousands of rows for each customer that uses our product. Fast? Please, it screams. Our initial choice was Oracle, but we couldn't eat the cost, it would have cost about $12,000 just for one server -- nuts if you ask me.
We chose PG because it had 90% of what Oracle had and less the fat. Postgresql is far easier to get running and far better on the memory footprint, it runs in around 8mb's... big whoop. If you know anything about Oracle, its hardly good about memory, and one bitch of a product to get running (right).
Lastly, I'll say this about Postgres, its in our opinion, and this is from 20 years of experience with Oracle (I go back to the Oracle 6.24 days when I worked for Prime Computer and ported Oracle to the 50 Series machines for Prime), that Postgres is much more stable out of the box on Linux than Oracle could ever be, Oracle is as buggy as it gets, and don't let Larry fool you. Its got bugs.
As far as this dude throwing shit at open source and saying that the commercial counterpart is better? Possibly, on the desktop you might be on to something, but I firmly believe that you CAN'T beat PHP, Apache and Postgresql as an application platform for 99% of the Web world out there. The three products in question that I mentioned are far better products than ANYTHING on the commercial market right now... And yes, they are Open Source.
Yes its got a very good back up system.
pg_dump, which is by far the ONLY way i'd want to back up. Not only does it dump your data, but it dumps your tables, views, stored proc's, etc. And to re-recreate your database, you simply import the script...
Now if your talking replication? Thats a different story.
The first time I tried using JDBC was with postgres, It was pretty simple, but I ended up using Accesss (and eventualy MSSQL) for production stuff, simply out of lazyness. I do recall that JDBC seemed to have a lot more capabilites on postgres then on the Microsoft stuff. (like, you could call moveFirst(), moveLast(), etc on resultssets).
But its entirely possible I just don't know what I'm doing. In any event, it seemed to work fine.
ReadThe ReflectionEngine, a cyberpunk style n
Hmmm... I guess it is all a matter of perspective. For an RDBMS to handle 50,000 records, particularly on todays hardware, is actually quite trivial. I've regularly run into apps that size (web based and otherwise) using (shudder) MS Access 2000. While Access is a real dog, it can handle it and "never have problem one" size wise.
In my experience with database applications, anything under 100,000 records is considered a small database. I have to admit in the last year or two my firsthand experience with mySQL has been limited, but from what I see in the current feature set there are situations where PostgreSQL is the definite superior choice.
Sure, MySQL can handle a lot of records and is quite stable and lightweight, however the lack of such basic features as foreign key constraints and restrictions such as table-level-only locking limit its usefulness as an "Oracle killer".
Yes, MySQL can do SELECTs from million-plus record tables, do lots of inserts per second etc etc in all the benchmarks, but what happens when you have 100,000s to millions of records and dozens to hunderds (maybe thousands?) of concurrent users who all frequently write to and delete from the database in addition to doing SELECTs. mySQL falls apart because all the users have to wait in line to write to a given table because the whole damn thing gets locked out until the first user to open a write transaction hasn't finished yet.
A large database also adds challenges to maintaining referential integrity. If foreign keys were so expendable that they could be eliminated (functionality put in the application) to boost performance, why does such functionality remain in Oracle MSSQL and others? Maybe because if it is done in the database (and done right--solid and stable) you don't have to trust your data integrity to application code. You don't want to deal with orphaned records when you have 45 tables and 2.5 million records to manage, just because some flunky Java programmer didn't cover all the bases in a class buried somewhere in a big enterprise web app. Foreign keys allow for an extra layer of protection for important data--if a database constraint is violated by an application bug it'll sure get noticed faster.
These are but two examples where PostgreSQL has the edge (even if it can't top out some arcane benchmark). Given that, mySQL is still very useful--and it doesn't have to be limited to "toy apps". Slashdot can use mySQL quite well, thank you very much. It is certainly NOT a "toy app", and mySQL's performance can handle all the slashdotting thrown at it, as far as retrieving data goes. Keep in mind though, that Slashdot data is fairly static. Its database is read far more often than it is updated/inserted/deleted (even given how many comments and articles are posted and with moderation). The relationships between tables are simple enough that referential integrity is manageable through application code, and above all, the data is not THAT mission critical (if a comment or 12 disappear from Slashdot, the lights will stay on and planes will not fall out of the sky). Speed and compactness are primary for Slashdot, the rest is secondary, and mySQL can fit the bill. If I need something to handle payroll, or health records or whatever--I thing PostgreSQL has reached the point where I find it the more trustworthy of the two with data of that nature.
While PostgreSQL vs mySQL can ignite a virtual Jihad, it should come down to this--pick the right tool for the right job (not just features and performance, but also personal preferences--what tools are you most comfortable with, etc etc).
...mySQL had proper, full support for constraints Slashdot wouldn't post so damn many duplicate articles...
HAR HAR HAR...just jokin' around...pleeeeze don't kill me.....
What makes M$SQLServer and Oracle more "suitable" for enterprises is ...[snip] ... the fact they maintain service centers for when the DBA throws up his hands and gives up.
Redhat will quite happily sell you a version of Postgres that they then support. For a price, of course, but a shitload cheaper than either Oracle or DB2.
Dave
I write a blog now, you should be afraid.
I've used MySQL with PHP/Perl for a while on various projects, and always half considered Postgres, but since many web hosts don't offer it (why?) never really considered it seriously (don't wish to rewrite a lot of db code - and yes I do know about PEAR with PHP, but it's a performance hit).
So, does PG+PHP match the speed of My+PHP? Is it as thoroughly tested? As I'm moving away from using Perl, I'd be really interested in seeing some benchmarks with this new version of PG...
Code, Hardware, stuff like that.
Agree with your last statement.
I'll add that, for all open-source databases do a great job, there will always be a market for the 800 lb. gorillas like Oracle and DB2.
There are some high performance features of these proprietary applications that, I daresay, will never trickle into the Open Source world.
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
What they don't tell you is if you use innodb tables for transactions, you won't be able to back up your database without shutting mysql down unless you buy the innodb hot backup tool. So yeah, they have transactions it just doesn't work well with doing backups on your data.
Postgresql has mvcc (multiversion concurrency control) meaning that readers or writers don't block other readers or writers from accessing the same data.
If you are going to start talking about vapor features then what about postgresql's plans to support point in time recoveries (pitr), redo logs, savepoints, and full clustering with multiple masters.
Incidentally, the link you point to says that stored procedures are planned for version 5 of mysql. Mysql is currently on version 3 with development work being done on version 4. Version 5 is a long time off. Triggers are something that the mysql developers will consider, and are not guaranteed to be implemented.
Getting back to what is present in the software now, mysql doesn't support stored procedures or triggers. Postgresql meanwhile supports triggers, stored procedures (written in python, perl, or sql), and rules (which allow you to intercept and rewrite sql queries).
"When you sit with a nice girl for two hours, it seems like two minutes. When you sit on a hot stove for two minutes, it
Well Postgres always had a rule subsystem which allowed you to code custom recordsets. Unlike MS SQL stored procedures you could also allow writes!. In other words posrgres rules allow you to code table like structures with both read and write access.
I always thought this was the coolest thing ever in a database. Now postgres has given you another tool in functions which return recordsets.
War is necrophilia.
Well the fact of the matter is MySQL still has no stored procedures and no triggers. Two very important things IMHO.
Zoot!
Let's go down the list:
...and a year after the bug was fixed, do we see a benchmark update? Nope.
...whatever.)
What the MySQL developers conveniently fail to mention is that if you use transaction-aware table types, performance drops dramatically. Under load with multiple concurrent connections, PostgreSQL is pretty close to the speed of MySQL or faster by default and blows MySQL's doors off when MySQL is transaction safe.
Regarding foreign key constraints, see note regarding transactions. And if you are really concerned about fk speed, you don't have to use them in PostgreSQL either.
Record locks hunh? It may surprise you to know that as a user, you don't need to explicitly tell PostgreSQL to lock tables with your queries. Ever. This a relational design issue. This should be handled by your database architect when they layout the table structure, rules, foreign keys, views, and triggers. You do have someone that designs the table structure ahead of time right? Sure you do. But why don't you have to explicitly lock the tables is PostgreSQL?
Maybe it's because PostgreSQL is smart enough to know when you need them without your help.
Transactions? Aren't those only for banks and e-commerce? Nope. Let's say you want to update all the users in Slashdot to give all of those loyal geeks one extra karma point. So you select on all users, grab their current karma, add one, and update the record. This has two problems: concurrency and completion. What happens if the user is moderated up or down in between the moment that the record is selected and the moment it's updated? Looks like the user has accidentally been given either (a) an extra point or (b) had a point taken away. Also look at what happens if the database goes down while doing the work (someone kicked a cord), who got the extra point and who didn't? Darn. Wish I had transactions...
So you use the transaction-aware MySQL tables. Wow! Performance has sure dropped out and we have to think about implementation details like locks. I sure wish there was a way to avoid stupid programming mistakes like forgetting a lock. Well...you could use just about any other database out there (including PostgreSQL).
As for stored procedures and triggers, you need not talk about features that aren't here *yet*. Version 4 isn't out of beta yet and you're hanging on a possible v5 feature? While we're at it, let's talk about how multi-master replication will appear in PostgreSQL by then. And didn't you hear? Microsoft's IIS will have its security holes patched up in two years too. Vaporware is vaporware. Believe it when you can download it.
------
Now then, on to personal gripes about MySQL above and beyond the ones I have listed above.
Benchmarks: On MySQL's benchmark page comparing PostgreSQL, they complain that no utilities are available for benchmarking but their own. This is not strictly true. No benchmark can be made because the syntax to the different RDBMSs are so dissimilar that none can be made currently without a strong bias. Stored procedure support, for example, would definitely skew results away from MySQL. But that wouldn't be fair for a benchmarking tool since MySQL doesn't support stored procedures. The same is true of triggers, rules, views, and other such "unimportant" features.
Of course MySQL's benchmark shows MySQL in a good light. They use only the feature set of MySQL to perform the benchmark.
They also mention on the page that they've contacted the PostgreSQL developers for tuning information and methods of improving the benchmark tool. I cannot express loudly enough that THIS IS A LIE! The PostgreSQL mailing list has many instances of developers reporting that they (a) never heard about this "contact" until someone pointed out the MySQL page, and (b) they have been ignored when they've tried to submit tuning techniques and other optimizations. Sounds like some people don't want their benchmarks to give the "wrong" results. Heaven forbid!
As it stands now, the benchmark is a year and a half old. MySQL is on its 53rd patch revision and PostgreSQL is two minor version releases later since this benchmark was released. Weren't you saying something about posting stale information? They still have a page complaining about vacuum bugs and the desire for a newer version of PostgreSQL that fixes the problem.
Feature comparisons: another source of info, it talks about the query speed on mostly read only data. Did someone forget to mention that flat files are even faster for mostly read only data?
It states that since MySQL has more users, it must be better than PostgreSQL. Funny how that logic doesn't seem to work for Windows. They use the same logic with the number of books. It wouldn't surprise me if there were more books on DOS than Linux. Does that make DOS better? Does that say anything about the relative quality of those books? No.
MySQL supports more APIs and languages. This is correct unless you want to count stored procedure languages. Oh wait, MySQL doesn't support stored procedures. (Yet! They'll be there in two or three years or so.
It then touts MySQL's fine replication facilities. Hello people! How often has slashdot gone down due to database issues? Hardly a poster child for stability or reliability.
According to mysql.com, PostgreSQL doesn't have a unit test/regression test. It makes one wonder if they've even used PostgreSQL.
PostgreSQL is said to be deficient with ODBC. Too bad they couldn't provide any specifics.
I'll relax about the statement that MySQL had more functionality with ALTER TABLE. FYI for readers, that has just changed with PostgreSQL 7.3.
They are correct that PostgreSQL doesn't have MERGE. Instead, they use the SQL92 standard UNION. Does the same thing. And let's not forget about views. What was that!? MySQL has extensive use of non-standard syntax? Any queries you write in MySQL will only have a prayer of working on MySQL? Say it ain't so!
PostgreSQL has had full text search for a while as part of contrib.
I don't even want to start with "MySQL Server is coded from the start to be multi-threaded, while PostgreSQL uses processes." Aside from databases on Windows, this helps whom significantly? A clue folks: Apache HTTPd also uses processes extensively. Pure thread support was only really added for the sake of Windows. This is one of those times where stability and consistency are more important than raw speed. This is your data!
And on and on...
As a final note, I would like everyone to take a trip down memory lane with me and recall that the MySQL dev team didn't see a need for MySQL to have transactions or any other of those "fancy" things at all until a couple of years ago -- when everyone started to realize that MySQL wasn't really twice as fast as PostgreSQL even though MySQL was crippled feature-wise.
The MySQL has so much misleading information (apart from the items that are outright false) on the web site, I wouldn't recommend it to anyone who wants correct information, "current" or otherwise.
- I don't need to go outside, my CRT tan'll do me just fine.
MySQL supporters are like people who believe that the moon landings were a hoax. No matter what contrary information they're confronted with, they still consider MySQL to be the best. It's all just an anti-MySQL conspiracy apparently...
:(
Rational and complete arguments like yours be damned.
This is your data. If it's important enough to store, it's important enough to protect. ACID is not optional.
- I don't need to go outside, my CRT tan'll do me just fine.
The thing that makes Postgresql completely different from MySQL is that it is an *active* RDBMS. By active, I mean that you can set it up so if it gets certain kinds of data, it can operate on that data to create new records, delete records, update other tables etc.
Postgresql has the *intellegence* built in. You can write all sorts of georgous functions to do stuff, especially if, like us, your shop uses several languages... PHP, Perl, Java, Python, C++, etc. Why replicate your business logic everywhere?
Transaction support and file/record locking are the least of your problems. If you do serious database stuff, at some point, you are *going* to want VIEWS, TRIGGERS, RULES, and STORED PROCEDURES (functions). Having this functionality in the database engine, instead of in your code makes a heck of a lot of difference when the time comes to scale.
Coming from a MySQL backgroud in a multi-language shop, we clearly saw the limitations, and decided to switch the entire database platform over to Postgresql a year ago.
We haven't looked back since.
Newsfollow.com
Time to learn how to use cvsup...:) Hit google up for it.
BWP
Just today I found a need (not a chance to use, but a *need*) for a subquery. While contemplating copying and pasting (it's only like 30 rows) data between database tables, I happened to see this article.
How easy is it to switch over from MySQL to PostgreSQL? Is there a simple tool to convert between the two? (And as a sidenote... The machine I want to do this on is a third-hand computer, a 300 MHz, 128 MB RAM webserver... Am I going to notice a performance hit if I put PostgreSQL on it?)
________________________________________________
suwain_2
PostgreSQL is not OORDBMS, it ORDBMS - and that is a benefit of it. There are plenty of document on Internet explaining it from different angles.
Here is what make PostgreSQL ORDBMS different than other RDBMS.
The Third Manifesto us the best book covering the subject.
My own experience shows, that OOP paradigm should be neither ignored or overused. Languages, like Python, help you to use OOP only when you really need it. Similar way, DBMS, like PostgreSQL, helps you to use objects in your databases only when you really need them, and according with SQL'92 standard.
OORDBMS, offenly stays for ODBMS databases, with some SQL interface extensions. Primary such systems are designed for persistence of serialized objects. Therefore they inherit all problems related to ODBMS, and first of all - lack of theoretical support (OOP is based on heuristics), lack of ad-hoc queries, lack of reflection mechanisms, very tight-coupling, lack of on-the-fly db schema changes, lack of consistent replication, and so on, and so on. In few words, it's easy to use OORDBMS as ODBMS, but you cannot use OORDBMS as RDBMS (without additional SQL-compatible transaction manager) - it's ODBMS by it's nature.
In ORDBMS PostgreSQL relations are first class objects with all theoretical support of relational algebra, while inheritance and ADT are just addons. You can use ORDBMS as RDBMS (that's the way most of use use in real life), but you cannot use it as ODBMS (without additional OR-mapping manager) - it's RDBMS by its nature.
Less is more !
Non-native speakers and dyslexics aren't excluded from the responsibility of communicating clearly and effectively. It may be more difficult for them, and that fact should be respected. But the skill's overall importance is not dependent upon the writer.
:)
Mistakes happen, and flaming someone for making an honest mistake is inappropriate. Pointing out the mistake hurts no one. It presents the opportunity to avoid the mistake in the future and allow someone to become a more effective writer. If you choose to ignore the correction, that's your prerogative.
As a point of fact, bad spelling and grammar hurt non-native and dyslexic readers much more than native readers. Also, I find that non-native speakers tend to demonstrate much better skills in grammar than most Americans.
(Making sure I stay at least slightly on topic...)
MySQL doesn't care about spelling and grammar as much (No foreign keys, etc.). The lazy like that about it. PostgreSQL can be made a bit more anal retentive about your data.
At the end of the day, if the same person who wrote it into MySQL has to read it back, it works fine. PostgreSQL enforces the rules so that anyone -- not just the original author -- can read it back with perfect comprehension.
- I don't need to go outside, my CRT tan'll do me just fine.
(btw, I'm running 7.2.1-2. I think the previous versions don't do it by default when I tried..)
I would just imaging that the characters are sorted by their unicode sequences, just like ascii characters are sorted by their ascii sequence.
If you are refering to manually sorting the data for speed, my project was pretty simple, so I didn't and don't really know how.
What type of of application are you building that needs unicode?
Cheers
PiT rollbacks, multi-master and the like are coming down the 'chute too but are too much mucking around to be worth the bother just yet.
Got time? Spend some of it coding or testing
on the concurrancy issue. But once again, what if the update fails halfway through? How do you know which records have been updated and which still need updating?
Going to add and drop a temporary column?
With PostgreSQL (and any other ACID database), that same SQL you wrote is atomic. It either works completely or not at all. No special keywords. No extra steps. It just works.
When it's that easy in PostgreSQL, why would you use MySQL? Note that this is a write operation; Don't assume that MySQL is faster.
- I don't need to go outside, my CRT tan'll do me just fine.
Comment removed based on user account deletion
Too bad. When Internet burned tons of startup money, they hired lots of "so-called programmers" to do web-development stuff. No wonder that MySQL and PHP (and Linux!) was typically a choice. Who cares about transactions? Who cares about aspect separation? Just show the first home page to the boss!
The positive outcome: big bosses heard about Linux. Could Linux be where it is now without those so-called programmers? I doubt so. Professional Services from IBM and Microsoft would decide for you what technology to use after your boss has decided what partnership contract to sign.
But that wasn't the only way to "educate" big bosses about Linux: startup boom sparked Linux marketing boom creating OSDN, and others, including Slashdot. As a result, Linux is not self-selling itself: everyone loves Linux therefore Linux is protecting your investments. Crowd effect.
Could it be possible would Linux be really bad? No. Why it didn't happened to PostgreSQL? I think b/c PostgreSQL-based few companies didn't care about marketing. Or cared wrong. Or didn't have money to care. Compared to what? To Linux. Try to find some subject about Linux using google - besides mail-lists you've got many official documents, FAQs, HOWTOs, learning courses, support companies. Try to do it for PostgreSQL - mostly mail-lists and few official docs.
With improved better marketing PostgreSQL may become in one or two years as Linux today. Without good marketing only PostgreSQL developers, few enthusiasts and some Slashdot readers will know that not all open-source databases are so bad.
Less is more !
Me too.
I want replication too.
Thanks in advance.
-
ping -f 255.255.255.255 # if only
The OWNER option on CREATE TABLE, for example, will save a lot of fidgeting around. Some of the deeper changes show that they really do have a handle on their code. I expect them to surpass Oracle in every respect bar bloatedness and management-as-a-career by Christmas 2003. (-:
I do note a *lot* of `breakme' changes like integers no longer accepting an empty string as equivalent to zero. I guess this is where we find how portably we really wrote our code.
Got time? Spend some of it coding or testing
I am the project administrator for a CRM app on sourceforge. We prefer working with PostgreSQL and highly recommend it in our documentation. Of course Sourceforge only has MySQL for the demo so we have to support it too ;-)
IIRC, 7.3 has ALTER TBLE DROP COLUMN (one annoying thing missing when you are trying to initially design a table), and it compiles under Windows, so my two complaints are now gone.
LedgerSMB: Open source Accounting/ERP
If you compile the TCL support, you can also get an application (bundled with it) called pgaccess. This is sort of like an MSAccess clone written in TCL with PostgreSQL on the backend.
So yes, there are RAD tools similar to Access.
LedgerSMB: Open source Accounting/ERP
Practical PostgreSQL
PostgreSQL: Introduction and Concepts I won't really comment here, as I've never had any real problems dealing with dates. I can't offer you a simpler form for that calculation off hand, but I'm pretty sure one must exist.
Topher
Does it support the latest JDBC standard, and does it work fine under heavy load?
Prior to 7.3, I used to do most of my prototyping in MySQL. Then I would convert the database over, and test it, then I would dump, add triggers, etc. and restore.
;)
;)
There are two scripts that come with PostgreSQL to take a database dump from MySQL and turn it into something you can use with PostgreSQL. So the switch is painless.
3 cautions, though
1) PostgreSQL timestams are time-zone independent, and the database manager will correct for timezone if set. So if your timestamps are off by a certain factor, that is probably why.
2) Timestamp format is different, so you may have to rewrite any time-stamp parsers.
3) Limit clauses in MySQL are non-standard.
Coming from someone who supports both
LedgerSMB: Open source Accounting/ERP
No simple equivalent of SHOW COLUMNS. Consequently, it's hard to find any high level language API that allows you to read the structure of a database. Python... nope. Tcl... nope. Perl... nope. (Or at least not the last time I checked).
$dbh->func($table, 'table_attributes');
works fine for me in perl.
Proper keyboard support for the psql client is broken by default on many of the linux installs I work on (debian seems to be particularly bad). It's *very* frustrating trying when the arrow keys and tab completion doesn't work.
So file a bug report with your distribution. You can't blame poor packaging on the postgres people. I really haven't had these same problems, and I would suggest that the client checks if a library it needs is installed, and doesn't provide readline without it. Perhaps the package only suggests libreadline as a recommends rather than a requires and you haven't installed it? (I don't have any potato machines still running postgres to check against)
The documentation is tough reading. Very formal. Obviously done by comp-sci academics.
True, the documentation sucks. This is the main thing that annoys me.
No matter how hard I try, I cannot grok the date-time functions, which I find to be extremely cryptic.
Not to mention changing the functions without changing the documentation with 7.2.1, fuckers. This cost me many hours of debugging only to find that ('oh, and we removed a couple of bits of functionality without EVEN LOOKING AT THE HOWTO ON THE WEBSITE') it was a compatibility change.
Moving right along. I don't do particularly many calculations with dates inside postgres, um - I think they should really import a decent date handling library and dispose of those icky functions - I remember writing expressions at least 3 lines long to work out the time period of a month (1st through [28-31]st of a calendar month) at least 11 days previously. Messy.
Comment removed based on user account deletion
I agree that the documentation could be presented much better. Better searching, better user interface, better examples, etc...
The whole structure of your database (and many other details) are available inside the pg_* system tables. Once your learn how these work, you can just make queries against these, which can return any aspect of the table structure. (in a far more sophisticated way than MySQL, by the way)
Also, I have to comment on the date/time issue. I found out, through a very painful piece of experience (a project that was all about dates), that it is impossible to rely on MySQL to manage dates properly. Mainly this is because MySQL doesn't support proper constraints for date column types. Any year can have a 0 month, as well as a 1-12 month. Any month can have a 0 day, as well as 1-31 (notice, it doesn't deal with differences in the number of days in each month). The result, A year can actually have 13 months, and every month can have up to 32 days. This just begs for data corruption!!
PostgreSQL's approach to date manipulation is more complex. That's because it is CORRECT. Every aspect of date/time management is available, including the all-important INTERVAL datatype, which is a dream for serious time-related projects. (and it is probably a dream to imagine it will ever appear in MySQL's TODO list)
By the way, casting to INTERVAL is the correct way to deal with your "elapsed days" problem:
(now() - mydate)::interval
This is usually all you need when dealing with this, if you are using it for other date calculations. But, if you really want to get just the days as an integer, without the chance of seeing '1 year 2 months' in the output, then your above expression should be updated to:
int4(extract(epoch from (now()-mydate))/ 60/60/24)
This is still somewhat ugly, but here is the beauty of a true DBMS: just use it to create a function (stored procedure) that you can call anytime, which could look something like:
days_elapsed(now(),mydate);
Now, is everybody happy?
"Did you ever stop to think where you'd be when you finally kill your idols and have noone left to copy? "
Does this apply to Bill Gates and larry Ellison as well?
As far as strictly data engine features go, Oracle has:
materialized views (which can increase performance)
point-in-time recovery
data partitioning
more flexible backup and restore options
the ability to use Win NT passwords and security (I know, that's not a big issue for most)
PostgreSQL does have some support from third-party software. phpPgAdmin for example, is a great tool and gets better with each release. pgAccess is a tool that comes with PostgreSQL, but I haven't used it much in the last year or so, and I remember it feeling like it was not quite ready for prime time. But it's a Windoze front-end for the PostgreSQL server and that's a big deal! There are also a couple of books about it (with more on the way, I hope), one of which, Practical PostgreSQL, published by O'Reilly is very good and available online.
The biggest thing you get for all the money you spend on Oracle is a "known" product. There are hundreds of books on Oracle (many are awful--caveat emptor) as well as classes and trainers and consultants and DBA's everywhere in the world (repeat caution above).
I love PostgreSQL, enjoy working with it, and am delighted with the new functionality the developers chose to include in version 7.3 (almost like they read my mind). Face it, for most medium-sized projects, we create a connection to the database (I often use ODBC) and start firing off queries. It doesn't matter to our program which database is behind the connection. We want speed, efficiency, and safety for our data. Anything more is window dressing (or comfort for the suits). Long live PostgreSQL!
Oops, I'm going to get down-modded for editorializing... *sigh*
Everything I've ever learned the hard way was based on a statistically invalid sample.
I don't have this problem, but "insitu" data migration "fairies" can be very important.
;)
Example: DB takes 20 terabytes. 10 terabytes free.
Say it's time to upgrade to latest DB. Who's wants to tell the boss: "We need to buy 10 more terabytes which we won't use for months after that". Or "there'll be long downtime reloading 20TB from backup tapes".
Worse if the backup tapes don't store the DB in a version independent format! (which could be the case for ASAP disaster recovery - snapshot of everything). Backups still work, but upgrading could be a bit harder eh?
If you are going to start talking about vapor features then what about postgresql's plans to support point in time recoveries (pitr), redo logs, savepoints, and full clustering with multiple masters.
PITR, was planned for 7.3 but was delayed for 7.4 because of some other internal changes but they didn't wish to delay 7.3 for it. It's now scheduled for 7.4.
Clustering is such an overloaded word. Nonetheless, multi-master (IIRC) replication is already underworks.
Distributed quiries is something that comes up from time to time. Not sure what the actual work effort is, however, I do know the protocol is being significantly enhanced to allow for replication and distributed queries.
IF (big if) you can temporarily remove those FK stuff etc and your users can wait for the time it takes to reload the affected table, you could do this in Postgresql (and not Oracle).
;).
(pseudoSQL)
begin;
lock oldtable;
create newtable (withcolumns that you want);
select columnsyouwant from oldtable into newtable;
drop oldtable;
rename newtable;
build indexes etc;
commit;
If anything fails everything will rollback and things will be fine.
I did this on a live site and it went well - old columns, new columns, modified columns whatever. Didn't have to resort to backups
That's where Postgresql beats Oracle.
Apparently MS Access treats fieldname=NULL differently. Nonstandards compliant.
AFAIK the standard says to do NULL tests using IS NULL and IS NOT NULL. fieldname=NULL should always return null according to standards.
You have to turn on a setting in Postgresql to support MS Access stuff which use the nonstandard behaviour.
Probably many others. But don't let crap stop you from switching from crap to good stuff.
But MySQL isn't libre software on Windows is it? It wasn't the last time I checked.
So on Windows you have the choice of an installer (MySQL) or open source (PostgreSQL).
Did I miss something?
- I don't need to go outside, my CRT tan'll do me just fine.
Considered using FOP for your reporting? That way you get more layout control than with HTML, your final output is in PDF format, you can use XSLT to build the stylesheets to do the formatting (and just have your program output XML to the reporting layer)... generally cool stuff.
You are indeed correct. I mistyped. With updates like in my example, you don't have to explicitly lock a table. Strictly speaking, you don't have to in MySQL either; you just do it with some risk to your data.
In the next versions of PostgreSQL, support for nested transactions would solve those particular problems. But as that's a feature not yet available, I cede the point.
In your example, a lock is indeed the way to go.
- I don't need to go outside, my CRT tan'll do me just fine.
A more realistic test would have been 1000000 rows in 15-20 tables. That's more on the line of the kind of system I've seen. Maybe on the small side.
We tried to arrange for some time on a massive beowulf cluster equipped with a 3 TB RAID and several GB of memory installed so that we could test against 1000000000 records and 52.7 tables, but they were booked solid.
Needless to say, I can assure you that based on the client's needs, the test I outlined (as inconsequential as it may seem), was quite appropriate.
This talk of Access and Postgres reminds me...how do you get past the problem where you try to link Access to Postgres, and Access 2K complains something like "The size of a field was too long"? I gave up on the linking because of this.
It's not the ODBC driver, as Excel does a data import of the same view with no problem!