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)."
I've heard this is the best SQL server around. Is it really better than MySQL?
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
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.
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
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
Postgres has had stored procedures for a while, look up CREATE FUNCTION. But adding better support for result sets does make them quite a bit more useful, now if only there was a decent JDBC driver that implemented result sets more completely.
-Sokie
------
Where are the slash-groupies? I distinctly remember being promised slash-groupies!
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.
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.
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.
Unfotrunatly there is no all-in-one rapid developemnt and flat-file database rolled into one, like Access, in the Unix world. But don't let that stop you from using Access though - it's a great tool for rapid development.
I use it all the time - you rapidly develope the small database, and when it outgrown the Access flat-file
Once that is donem and your database is really popular - migrate the front end again to Delphi to Delphi/Kylix and you'll be able to support Linux/FreeBSD and Windows desktops. People can VNC into a FreeBSD server that shares the Kylix app over VNC for other systems - Solaris, Mac, Psion.
Cool stuff.
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
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
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
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
In recent PostgreSQL, this date addition is simple:
Number of days since I was born:
joel@joel=# select current_date - '1972-10-12';
?column?
----------
11007
(1 row)
Number of days between Christmas 2002 and Valentine's Day:
joel@joel=# select cast ('2002-12-25' as date) - '2002-02-14';
?column?
----------
314
(1 row)
[we need to cast at least one as a date explicitly, otherwise PG assumes there are both strings data.]
Nothing complicated here, kids, move on.
BTW: for those using now() in PostgreSQL, moving to CURRENT_DATE (or CURRENT_TIMESTAMP to get the time and date) is recommended -- it's less quirky in procedures, and is ANSI standard syntax.
Tablespaces. Mostly for performance, I think - we just keep all the indexes in a different tablespace on a different array for less disk seeking.
Planned for 7.4 IIRC.
Good Win32 support.
Planned for 7.4. Seems code is already available, it's just being cleaned up prior toward merge.
yes. it's been great since 6.3.