Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

PostgreSQL 8.4 Out

timothy posted more than 5 years ago | from the would-you-name-your-daughter-postgres? dept.

Databases 191

TheFuzzy writes "PostgreSQL version 8.4 is now out and available for download. The main cool features in this version are: recursive queries (for doing trees etc.), windowing functions (for doing reports) column-level permissions, parallel database restore, a beta in-place upgrade tool, and a host of administrative improvements. And, of course, better performance, mainly on reporting queries. Some of the over 200 new or enhanced features are listed here."

cancel ×

191 comments

Sorry! There are no comments related to the filter you selected.

sweet (-1, Offtopic)

loafula (1080631) | more than 5 years ago | (#28547187)

thats awesome. first?1??!?!?11

So why (2)

jlechem (613317) | more than 5 years ago | (#28547255)

Does anyone even use mySQL when they have features like this? The only issue I have ever had with this DB was when I was trying to connect a .net app to it and it took me a while to find a workaround.

Re:So why (1)

XanC (644172) | more than 5 years ago | (#28547289)

Not one of these features is remotely compelling enough to switch an existing, working app from MySQL.

Re:So why (5, Interesting)

ducomputergeek (595742) | more than 5 years ago | (#28547349)

No, but Oracle taking over MySQL and the community already showing signs of forking in 4 different directions might be a reason to seriously look at PostgreSQL.

Re:So why (1)

Foofoobar (318279) | more than 5 years ago | (#28547375)

Nope... I was planning on continue to stick with MySQL as MariaDB is going to bring much needed improvements to the project

Re:So why (1)

NormalVisual (565491) | more than 5 years ago | (#28548813)

that, and the fact that four years later from the original bug report, stored procedures still can't even find out what caused a thrown exception [mysql.com] , with no indication that they even give a damn about it. Working with stored procs under MySQL is a frigging joke.

Re:So why (1)

MBGMorden (803437) | more than 5 years ago | (#28549483)

I'll concede your point, but the original poster did state that his app was working fine as is with MySQL. If the app is simple enough you might simply not have to use stored procedures at all.

Truthfully though, I'm not sure what keeps drawing users back to MySQL as a default. As the OP stated, it's often not worth the effort to switch a working app to a different database (and I've still got one application hitting a MySQL database because it works fine - all it does it let the company secretaries enter in phone calls that their bosses receive in a help desk type fashion), but I can't fathom why anyone chooses it when starting out from scratch anymore. PostgreSQL works great, and is just as easy to use. MS SQL Server (which despite the MS haters really isn't that bad of a DBMS) is also available in the Express version which is also a very easy to use and robust system as well. I'm sure Oracle is a great choice too but I simply haven't had any experience with it so far (it's not free and my employer strongly prefers MS SQL Server for everything so I simply haven't had a chance to play with it).

To me MySQL is simply a legacy system. I'll keep it for as long as I need, but anything new will use something different.

So what? (0)

Anonymous Coward | more than 5 years ago | (#28547403)

Not one of these features is remotely compelling enough to switch an existing, working app from MySQL.

So what? There's *lots* of places where software X is much better than software Y but the cost of migration is higher than the cost of doing nothing.

Re:So why (1)

Estanislao Martínez (203477) | more than 5 years ago | (#28549955)

Not one of these features is remotely compelling enough to switch an existing, working app from MySQL.

Sure, let's grant that, for the sake of argument (though I'd say that most of the time it is true). Now, why do people keep developing new applications to use MySQL?

I can never get a good answer to this question. The answers I tend to get are of the form "because they don't want to spend the time to learn a new RDBMS." These people tend to spend a lot of time cleaning up after MySQL's flaws on applications that they inherited, too.

Re:So why (1)

Morgon (27979) | more than 5 years ago | (#28550395)

I don't know, I think windowing functions are pretty significant,
Sure, it depends on whether you have an app that benefits from them, but assuming you do, it's pretty significant. Doing sub-sorts on large datasets is a killer without functions like these.

Re:So why (1)

Mad Merlin (837387) | more than 5 years ago | (#28547303)

Does it have built in replication yet?

Re:So why (5, Informative)

Anonymous Coward | more than 5 years ago | (#28547435)

No, the replication and hot standby patches were not considered of high enough quality to be a part of 8.4. They will be a high priority during development of 8.5.

PostgreSQL would never, ever ship something which has a WTF-list as long as MySQL's replication does: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html [mysql.com]

Re:So why (4, Interesting)

Phroggy (441) | more than 5 years ago | (#28547495)

Does anyone even use mySQL when they have features like this? The only issue I have ever had with this DB was when I was trying to connect a .net app to it and it took me a while to find a workaround.

Because I don't need features like this, and don't know how to use them. At least, as far as I know, I don't need features like this. Since I don't know how to use them, it doesn't really matter, does it?

MySQL is what I've been running for several years; I'm familiar with the software. I use DBD::mysql in my Perl scripts; I'm sure most things would work fine if I simply switched to DBD::Pg, but would any queries need to be changed? I have no idea. Of course I'd need to migrate my data from MySQL to PostgreSQL; I'm not even sure of the "correct" way to migrate data from one installation of MySQL to another (copying the data files and then fixing whatever's broken usually works well enough). Of course, I'm running a few PHP-based webapps that currently use MySQL; I don't know if it's possible to get them to work with PostgreSQL or not (switching database engines in PHP isn't as simple as it is in Perl).

I could take the time to do the research and find answers to these questions. Or I could keep using what I know works just fine. Maybe someday I'll have some compelling need to try PostgreSQL and see if switching is practical. Today is not that day.

Does this answer your question?

Re:So why (-1, Troll)

Anonymous Coward | more than 5 years ago | (#28547773)

Your answer could have been shorter: "I'm a luser who doesn't need a real RDBMS."

Re:So why (1)

zehaeva (1136559) | more than 5 years ago | (#28548419)

PHP supports PostgreSQL its as simple as doing a global find-replace of mysql_ with pg_ there done. Okay a bit too simple but the mysql functions and the pg functions have the same names other than the prefixes and they behave in the same exact manner. You may need to change a little bit of SQL.

Once you get to start to use the more advanced features of postgresql you'll look at mysql more as a toy rather than a fully featured RDBMS.

It's rather like when you first started playing with mysql and went "Wow, this is so much better than Excel!"

~Z

Re:So why (0)

Anonymous Coward | more than 5 years ago | (#28549315)

Looking at pg_query_params() and never worry about sql-injection again might also be a good idea.

Re:So why (1)

zehaeva (1136559) | more than 5 years ago | (#28550081)

I would have hoped that he would be using parameterized queries to start with.

Re:So why (1)

jalefkowit (101585) | more than 5 years ago | (#28549607)

PHP supports PostgreSQL its as simple as doing a global find-replace of mysql_ with pg_ there done. Okay a bit too simple but the mysql functions and the pg functions have the same names other than the prefixes and they behave in the same exact manner.

... or you could just use PDO [php.net] , the way God intended.

Re:So why (0)

Anonymous Coward | more than 5 years ago | (#28550199)

was not aware that God endorsed programming objects! kidding aside I'm kind of suck in php4 land. ignoring that there is something about using OOP for what boils down to a run time script that i find odd. does data encapsulation and all the other oop goodies really help you? i find it to be a bit overkill for most websites. also given how loose php is with data typing and other features it really seems .. inconsistent? i don't know, i just never felt a real good warm and fuzzy by using oop in php. maybe if it were stricter with its constructs or maybe if i used it for a more robust application than "grab data from db push to webpage" i'd like it more. sorry for the wall of text.

Re:So why (-1, Flamebait)

Anonymous Coward | more than 5 years ago | (#28548497)

Haha, yeah, you definitely answered his question. MySQL is suitable for cute wannabe "developers" who knows enough of a script language or two to be able to set up connections and send queries.

Re:So why (1)

h4rm0ny (722443) | more than 5 years ago | (#28549575)


You have given what is, I believe, the most common reason for not switching: MySQL works well enough for what you need so it doesn't make sense to expend additional time and effort (you probably don't have much of the former) to learn something that you will only use for the same purposes. It makes sense.

But in a friendly response, I'll just mention that once you are familiar with PostgreSQL, you will be aware of what more you can do with it and that may lead to the second part of your logic (you will only do the same things with it) to become false. Because you'll find opportunities to do things in different ways that you may prefer.

Also, you can become one of us super-annoying people on Slashdot who keep talking about why PostgreSQL is better than MySQL. ;) Really, you don't need to learn PostgreSQL if MySQL is meeting your needs. But you might find it fun, more useful than you might have thought, and you wont magically forget your MySQL knowledge so it's not as if you're compelled to seek out only jobs and projects that use PostgreSQL.

Just some general comments - not a dismissal of what you've said.

Regards,
H.

*sigh* (2)

Estanislao Martínez (203477) | more than 5 years ago | (#28549701)

You have given what is, I believe, the most common reason for not switching: MySQL works well enough for what you need so it doesn't make sense to expend additional time and effort (you probably don't have much of the former) to learn something that you will only use for the same purposes. It makes sense.

There are two versions of this argument:

  1. The version that applies to a system that already exists, and has been developed with MySQL.
  2. The version that applies to a system that has yet to be built.

I can buy the argument in case (1), but not in case (2). In particular, when people say that MySQL works "well enough" for what they need, I simply do not believe them. They are simply not counting the amount of time they've wasted on data integrity issues over the years, because they just don't know better that with a superior RDBMS, those problems could be solved from day one.

Re:So why (0)

Anonymous Coward | more than 5 years ago | (#28547577)

I'm currently stuck on mysql because Postgre has no support for connections over windows ipc pipes.

Re:So why (1)

temojen (678985) | more than 5 years ago | (#28547617)

Because most common web apps are only built on mySQL, because most ISPs only have mySQL, because most common web apps are only built on mySQL...

Re:So why (1)

Dan Ost (415913) | more than 5 years ago | (#28548827)

Fortunately, it's getting much easier to find a hosting provider that offers PostgreSQL. Of course, if you just rent a bare machine and install your own image, you've always been able to install PostgreSQL.

Re:So why (0)

Anonymous Coward | more than 5 years ago | (#28547663)

I tried to use it for the project I'm currently working on, but I could not find an ultra-cheap provider (>=$5/month) that had it as an option. If anyone knows of a decent cheap host that offers PostgreSQL I would gladly switch; I signed up on a monthly plan with the hopes of being able to switch. (Although now that I've re-written everything, it's not as imperative.)

Re:So why (1)

AKAImBatman (238306) | more than 5 years ago | (#28550445)

LunarPages [lunarpages.com] is around that range and they provide both PostgreSQL and MySQL as options.

Re:So why (-1, Troll)

ByOhTek (1181381) | more than 5 years ago | (#28547831)

RPF: Rabit Postgres Fanbois.

Honestly, the more rabid and on-the-offense a community gets, the more a am suspicious of the product.

Re:So why (0, Redundant)

ByOhTek (1181381) | more than 5 years ago | (#28547877)

Rabit -> rabid...

Damn typo daemon.

Either that or the postgress fanbois really are getting all the chicks and I am on the wrong bandwagon.

Re:So why (1)

grcumb (781340) | more than 5 years ago | (#28549687)

Either that or the postgress fanbois really are getting all the chicks and I am on the wrong bandwagon.

I'd reply to this, but my Postgres DBA is here, working on my equipment. Before she got her PhD, she was an Olympic gymnast. She can suck the chrome off a trailer hitch and gives a whole new meaning to the phrase 'hot swap'. Now, if you'll excuse me, I have some... uh, maintenance to perform.

Re:So why (4, Insightful)

Just Some Guy (3352) | more than 5 years ago | (#28548353)

Honestly, the more rabid and on-the-offense a community gets, the more a am suspicious of the product.

First, the only on-the-offense part was a list of new high-end features. Second, it's pretty easy to become rabid when you try to tell people about your quad-turbo Ferrari dumptruck that does 0-60 in 4 seconds fully loaded and pulls 1.5g on the skidpad while getting 137 miles per gallon, but keep getting shouted down by Kia Fanbois who make fun of every feature your truck has that they don't - until they get a half-assed version of it and then act smug like they invented it.

PostgreSQL fans have nothing on rabid MySQL fans, I promise you.

Re:So why (0, Troll)

Foofoobar (318279) | more than 5 years ago | (#28549501)

wow. That didn't sound rabidly defensive at all. Not in the least.

Re:So why (1)

h4rm0ny (722443) | more than 5 years ago | (#28550067)


Yeah, but kind of funny though. ;)

Re:So why (3, Insightful)

digitalunity (19107) | more than 5 years ago | (#28550135)

It's pretty much spot on though. When a new feature comes out for MySQL, the fanatics love to shove it in your face, even if the real RDBMS have had it for years.

PostgreSQL does everything mySQL does, but better. I would have thought great unicode support alone would be enough to sway the mySQL believers but I was wrong. Now I don't even debate the issue unless I'm sure I am even talking to a reasonable human. The whole mySQL vs. PostgreSQL debate usually descends into the mud quickly with neither side listening to the other.

I'll stick with PostgreSQL, thank you very much.

Re:So why (1)

HeronBlademaster (1079477) | more than 5 years ago | (#28550683)

My former employer uses PostgreSQL because it's about a bazillion times better at handling large (read: several hundred GB) tables... and even then we made pg run out of internal row OIDs and we had to tinker with pg's innards.

Yay for large data sets!

That said, I use MySQL for small projects because I can't be bothered to set up Postgres on my server.

ask... (1)

SiggyRadiation (628651) | more than 5 years ago | (#28548311)

...slashdot!

Re:So why (0, Troll)

CarpetShark (865376) | more than 5 years ago | (#28548647)

In theory, I too was much in favor of PostgreSQL over MySQL, for many years, until VERY recently, when I actually got around to using it for serious projects. Then I discovered that pgadmin on windows can't even backup a PostgreSQL server unless the versions match EXACTLY (minors as well as majors) across builds and platforms. That's pretty insane. I'm now worried about what other seemingly obvious issues might not have been addressed in pg.

Re:So why (1)

0racle (667029) | more than 5 years ago | (#28548769)

You stopped using a DB system because a app related to but not developed by PostgreSQL didn't do something you wanted it to? I stopped using Oracle because I didn't like the colour scheme in Toads tools. No seriously, I say that with no hint of sarcasm.

For a serious project, I would expect someone to use scripts anyway.

Re:So why (1)

mvdwege (243851) | more than 5 years ago | (#28548799)

Eh? Why would that be a problem? Just use the supplied backup & restore tools (pg_dump and pg_restore), they work just fine. If you really can't back up a database without a pointy-clicky GUI, you have no business being a DBA.

Mart

Re:So why (1)

CarpetShark (865376) | more than 5 years ago | (#28549303)

pgadmin is the official frontend to pg_dump. Since you've no clue what YOU'RE talking about, I hope you're not a DBA. I'm not, by the way.

Re:So why (1)

Dan Ost (415913) | more than 5 years ago | (#28548885)

I believe this is by design.

Dumping it from one database and loading it into the other will always work, and, I believe, is the recommended procedure.

Re:So why (1)

h4rm0ny (722443) | more than 5 years ago | (#28550159)


I was about to predict some abuse coming your way, but I see it's already appeared. : /

If PostgreSQL is not for you, no problem, but seriously, just using pg_dump would be the proper way to do this and it seems a shame to change to a different database if that's the sole issue. </friendlycomment>

Re:So why (3, Interesting)

rasherbuyer (225625) | more than 5 years ago | (#28549825)

The only reason there are comparisons between MySQL and PostgreSQL is because they are both Open Source. Otherwise there is no comparison.

PostgreSQL is a fully featured, enterprise ready, RDBMS and stands for comparison with Oracle and DB2 and to a lesser extent Sybase/SQL Server. MySQL is not even in the same league as any of the previously mentioned. However, web developers seem to like it...

I'll show my colours and say that I've been working with Oracle for over 10 years, and I love it, it gets better all the time. I can't comment on DB2 as I haven't used it for about 10 years and then only briefly.

I've just finished managing a six month data migration project from SQL Server to Oracle. Oracle is head and shoulders above SQL Server in the query stakes and stomps all over it with it's procedural language (PL/SQL vs TSQL). PostgreSQL is much more Oracle-like than anything else, pl/pgsq is even comparable with PL/SQL although not as feature rich.

I recon the Postgres guys are at least at a par with 8i comparing against Oracle, which is pretty damned impressive.

Re:So why (1)

digitalunity (19107) | more than 5 years ago | (#28550449)

Maybe you can relay back to Oracle please that the eBusiness java interface is god awful slow.

Thanks. You might have more sway than I do.

And more... (4, Informative)

jadavis (473492) | more than 5 years ago | (#28547277)

Upgrade in place is done via pg_migrator [pgfoundry.org] .

VACUUM now makes use of a "visibility map", which means that it doesn't need to process old data each time VACUUM is run. If you run VACUUM on a large table, and then immediately run it again, the second run will be instant.

The recursive queries are the SQL standard common table expressions, that is, WITH and WITH RECURSIVE.

The window functions is a great addition, but with PostgreSQL it's even better because you can define your own custom window functions with CREATE FUNCTION.

There are also a huge number of little improvements, like "auto explain" which is a module that can automatically log the "EXPLAIN ANALYZE" output when a query takes a long time. This is a great convenience for DBAs, because you don't have to look at long-running queries in the log and attempt to EXPLAIN ANALYZE them manually.

Re:And more... (1)

tcopeland (32225) | more than 5 years ago | (#28547437)

> Upgrade in place is done via pg_migrator

Outstanding. That was kind of painful when I upgraded RubyForge to PostgreSQL 8.3 [blogs.com] ; looking forward to a much smaller downtime window for the upgrade to 8.4.

Too bad replication didn't make it in there... maybe in 8.5.

Re:And more... (0)

Anonymous Coward | more than 5 years ago | (#28549005)

Apparently replication is the priority for v8.5 development (so says another poster).

Thanks for all the good work. (1)

Futurepower(R) (558542) | more than 5 years ago | (#28547507)

Very impressive. Thanks PosgreSQL developers.

Re:And more... (1)

afidel (530433) | more than 5 years ago | (#28548041)

Auto explain sounds like a killer feature, I wish Oracle had that (auto trace is NOT the same).

PostgresSQL (-1, Flamebait)

Anonymous Coward | more than 5 years ago | (#28547285)

about as useful as a poopy flavored lollipop.

why do we need ANOTHER open source database that isn't one billionth as good as MySQL?

Re:PostgresSQL (-1, Troll)

Informative (1347701) | more than 5 years ago | (#28547379)

Boob

cross database joins?? (1, Interesting)

Foofoobar (318279) | more than 5 years ago | (#28547311)

Can I do a cross database join yet? To date this has still been a feature that has yet to be implemented that I can do in just about every other RDBMS.

Re:cross database joins?? (1)

Just Some Guy (3352) | more than 5 years ago | (#28547369)

You have an interesting idea of "database". Why not use schemas instead?

Re:cross database joins?? (1)

MagicMerlin (576324) | more than 5 years ago | (#28547473)

That's not quite fair. mysql doesn't really have them either. mysql cross database joins are simply working around there lack of support for schemas. Schemas are better in virtually every way...

if you need x database joins in postgres, you can always use dblink of course.

Re:cross database joins?? (1)

Simon (S2) (600188) | more than 5 years ago | (#28547505)

Can I do a cross database join yet?

No. They where not implemented in this version, and the workaround is still the one using contrib/dblink, which allows cross-database queries using function calls.
Still, even if I sound like a fanboy, this is a very minor annoyance: PG is the best OSS, Free, Gratis, RDBMS available.

Re:cross database joins?? (5, Informative)

Anonymous Coward | more than 5 years ago | (#28547623)

If you're coming from MySQL: What MySQL calls "databases" are called "schemas" in PostgreSQL. MySQL has no equivalent of PostgreSQL's "database".

There is also db-link and dbi-link.

Parent is correct (4, Informative)

Just Some Guy (3352) | more than 5 years ago | (#28548215)

I don't know why that got modded troll because it's essentially true. A PostgreSQL database is subdivided into schemas, each schema being much like an entire MySQL database. If you have a database named "mycompany" with schemas "financial" and "hr", you could have something like

select
employee.name,
budgetitems.lineitemamount
from
hr.employee,
financial.budgetitems
where
employee.paylevelid = budgetitems.lineitemid

You can have multiple schemas with the same table names and disambiguate them by referring to schema.table instead of just table. In other words, they're like MySQL databases, and some mod owes the parent an apology.

Re:Parent is correct (1)

javilon (99157) | more than 5 years ago | (#28548569)

Thats perfectly fine and well, but can you put two different schemas in two different boxes? no, you canÂt.

Re:Parent is correct (1)

Just Some Guy (3352) | more than 5 years ago | (#28548715)

Thats perfectly fine and well, but can you put two different schemas in two different boxes

First, give us a use case explaining what it is you're actually wanting to do. Second, do db-link and dbi-link (from the GP post) not do exactly that?

Re:Parent is correct (1)

afidel (530433) | more than 5 years ago | (#28549335)

We run into cross database links all the time, from a data consistency perspective it's much better to have one system be the system of record and all others pull from that master server. We even do cross platform links (SQL Server to Oracle) with no problems. Of course db-link and dbi-link seem to fulfill that need just fine. We also have a function where we export a CSV file and an outside vendor uses similar functionality to pull the data in by treating the file as a source table.

Oh, hallelujah! (3, Informative)

Just Some Guy (3352) | more than 5 years ago | (#28547445)

Deadlocked Query Display
No more log detective work to track down which operations deadlocked; the information is right there.

That alone is worth the upgrade to me. Currently, if you have two deadlocked transactions, it's a pain in the neck to figure out exactly which ones are involved.

Re:Oh, hallelujah! (1)

Bovius (1243040) | more than 5 years ago | (#28549401)

Good lord, I didn't see that in the new feature list. Thank you, good sir, for bringing this to our attention. I've spent many frustrating hours trying (and often failing) to track this information down.

Re:Oh, hallelujah! (1)

Just Some Guy (3352) | more than 5 years ago | (#28549527)

We're in the same boat. I had a little script that tried to cobble the information together from a bunch of queries while logged in as admin, but that was so far from ideal that I could hardly stand it.

Windowing Functions (3, Informative)

ProfFalcon (628305) | more than 5 years ago | (#28547553)

OK, the windowing functions are exactly what I was waiting for. RANK and DENSE_RANK are phenomenal.

See the presentation by Hitoshi Harada here: PDF Presentation [pgcon.org]

What compares to Access on PostgreSQL? (1)

bogaboga (793279) | more than 5 years ago | (#28547673)

What I'd like to say is:

Access is to Microsoft's Jet Engine while {name it>} is to PostgreSQL. By the way I would like an Open Source implementation.

Re:What compares to Access on PostgreSQL? (0)

mbenzi (410594) | more than 5 years ago | (#28547869)

pgAdmin III http://www.pgadmin.org/ [pgadmin.org]

Re:What compares to Access on PostgreSQL? (1)

bogaboga (793279) | more than 5 years ago | (#28547999)

Oh really? The last time I checked, I could not implement business logic using this. I will check again.

Re:What compares to Access on PostgreSQL? (1)

ProfFalcon (628305) | more than 5 years ago | (#28548383)

Well, you can use Access against a PostgreSQL database. Other than that, there is Rekall [thekompany.com] as an option. OpenOffice.org's Base [openoffice.org] is available as well.

Re:What compares to Access on PostgreSQL? (1)

mbenzi (410594) | more than 5 years ago | (#28548805)

I had thought Access and Base would do this, but had never tried. Thanks for the confirmation.

Re:What compares to Access on PostgreSQL? (1)

digitalunity (19107) | more than 5 years ago | (#28550539)

OOo Base does indeed with with PG directly or as an ODBC connection.

I've got to say now though, if you're using Base for creating reports on tables that are large, expect it to run painfully slow, even if the data being used in the report is small. It seems to fetch the entire table from the database, regardless of which fields are needed to compile the report.

Re:What compares to Access on PostgreSQL? (1)

floop (11798) | more than 5 years ago | (#28549777)

Access is to PostgreSQL. You can easily use access to query and manipulate data in a pg db. If you searched Access and Postgresql, you'd see there are tons of tutorials around the pair. You can also use OpenOffice.org Calc to do the same thing.

Now if only I could start using it! (0, Flamebait)

spiffmastercow (1001386) | more than 5 years ago | (#28547835)

Unfortunately, my employer is hell-bent on using SQL Server. What's worse, they're hell-bent on not spending any money, which leaves us with a SQL 2000 database running on a server that was "just okay" in 2002 serving ~100 users running CPU intensive reports and performing a few hundred thousand transactions a day..

Um... (1, Informative)

Estanislao Martínez (203477) | more than 5 years ago | (#28548237)

SQL Server has always been a decent database. Going to PostgreSQL would probably be a downgrade. Though if I have to say one thing, it would be this: SQL Server 2005 is a lot better in my experience than 2000.

Re:Um... (1)

Shados (741919) | more than 5 years ago | (#28548455)

Of course. But then again, 2008 is leaps and bounds beyond 2005 too.

2000 was good, but paled compared to some of the more expensive competition, and lacked some "enterprise" features. 2005 was a competitive offering. 2008 is in many ways ahead of the competition. And the dev tools rock :)

Re:Um... (1)

rgo (986711) | more than 5 years ago | (#28549943)

Why is 2008 better than 2005? I've read some whitepapers but I find it to be almost the same when it comes to userful stuff.

Re:Um... (1)

Shados (741919) | more than 5 years ago | (#28550667)

Many more data types, including a datetime type that doesn't suck. A new datatype that acts like it stores files in the database but keeps them in an external folder (that one is probably one of the biggest. This is really a big deal. Much more powerful than rolling up your own solution). Major enhancements of the devtools (native SQL intellisense). ETL and business intelligence stuff got big improvements (ability to use any .NET language in SSIS and better use of multi-core CPU for ETL, enhancements to SSAS, etc). New file formats in reporting services. Much better query planner. All around performance improvements.

The list goes on. I'd say 2005 -> 2008 is a bigger jump than 2000 to 2005. At first glance it isn't, because they didn't rewrite as many parts from scratch (like SSIS and Management Studio), but the new datatypes, enhancements to T-SQL, the souped up business intelligence suite, external file management, and performance improvements are a much bigger leap.

Re:Um... (1)

spiffmastercow (1001386) | more than 5 years ago | (#28548907)

I've been fighting for 3 years now to get them to move to 2005.. Basically the DBA doesn't want to learn how to use the new admin tools, and he makes up excuses for why 2000 is "better". I point out the flaws in that logic, but since the only audience is corporate execs and salesmen, they prefer a simple answer (from the DBA) to a correct answer from me.

Re:Um... (1)

afidel (530433) | more than 5 years ago | (#28549499)

Simple answer, SQL 2000 is EOL, no support unless you bought an extended hotfix agreement back in Q2 2008.

Re:Um... (0)

Anonymous Coward | more than 5 years ago | (#28550229)

How about a nasty regression bug when optimizing self left joins causing it to table scan and a WITH_INDEX caused it to complete index scan followed by full bookmark lookup. SQL 2000 ran it with same dataset with a much better plan.

Next Version Features (0)

raftpeople (844215) | more than 5 years ago | (#28547981)

In the next version the name PostgreSQL is going to be changed to the easier to say XquLck#7SQL

I like this one snippet (1)

goffster (1104287) | more than 5 years ago | (#28548053)

" .... This should help users migrating from MySQL. ... "

Because we know they are sure to be coming in droves whether they know it or not. :)

Recursive Performance (1)

Alethes (533985) | more than 5 years ago | (#28548169)

Is there a significant performance difference between WITH RECURSIVE and a recursive function?

General Thoughts (0)

Anonymous Coward | more than 5 years ago | (#28548197)

Wow the features seem pretty good, but still lacks a couple of things:
    - In place migration tool is still in beta, to be real competitive you need to have a fast in place migration tool (pg_dump for anything over 50GB takes forever)
    - Online replication. Now you mostly have offline filesystem based replication

ANyway great job guys, also the window functions are pretty awesome,

Re:General Thoughts (1)

jadavis (473492) | more than 5 years ago | (#28549797)

In place migration tool is still in beta

Check it in a couple days. As I understand it, it was mostly waiting for 8.4.0 to be released.

Online replication. Now you mostly have offline filesystem based replication

The most common replication schemes in postgresql are probably warm standby (which doesn't currently allow reads on the slave), Slony (not builtin) and londiste (not builtin). All of those are online replication (unless you consider warm standby to be offline). Maybe you already know this, but I am just clarifying.

The next release will prioritize hot standby (same as warm standby but you can read the slave), and sync rep as built-in replication systems.

my opinion (1)

Lord Ender (156273) | more than 5 years ago | (#28548607)

SQLite on the low end, Postgres for mid-range apps, and Oracle on the high end.

Where is the niche for MS SQL and MySQL in this picture?

Re:my opinion (1)

pembo13 (770295) | more than 5 years ago | (#28549029)

The MS SQL niche is for those that loyal to Microsoft, regardless of cost. For those who will write Microsoft any size check because they are already using all Microsoft's other software, so what's a few more dollars... even though their company just cut staff by a few percent, you can't decide to simply not pay your license fees.

That said, save for what I consider to be a lack of data types, SQL server is better than MySQL.

Re:my opinion (1)

afidel (530433) | more than 5 years ago | (#28549621)

SQL Server enterprise is now significantly cheaper than Oracle Enterprise. Back in 2006 we got Oracle for a price that MS wouldn't match, but Oracle is licensed per core while MS is licensed per socket so as hardware advances the MS solution is now much cheaper. Heck the list price for a quad core box (about all you can buy today) is $50k for Oracle vs $25k for MS. SQL Server 2005/2008 are definitely good for all but the biggest of workloads and support all the normal enterprise features.

Re:my opinion (0)

Anonymous Coward | more than 5 years ago | (#28549179)

Microsoft development shops will prefer Microsoft SQL server as the MS tools and languages integrate best with it. So yes, there will always be a market for MS-SQL.

Additionally, Microsoft SQL Server Reporting Services is being used by alot more companies for BI.

Re:my opinion (1)

dave562 (969951) | more than 5 years ago | (#28549585)

The niche for MS SQL comes from all of the developers who have developed apps built on top of it. Most of those apps have been around for longer than a few years. From the point of view of a developer coding a new application from the ground up, it might not make much sense to saddle customers with the cost of MS SQL licenses when something like Postgres could get the job done.

Another issue might come from scalability. MS SQL scales pretty well. Postgres is still waiting into introduce it. Once they introduce it, how long until it is stable?

Re:my opinion (1)

Estanislao Martínez (203477) | more than 5 years ago | (#28549857)

SQLite on the low end, Postgres for mid-range apps, and Oracle on the high end. Where is the niche for MS SQL and MySQL in this picture?

No, it's more like:

  • SQLite for single-user application-specific data;
  • Postgres for low- to mid-range multiuser OLTP;
  • SQL Server for mid- to high-range multiuser OLTP, and for affordable OLAP;
  • Oracle for high-range.

SQL Server is better than Postgres, period, and has tons of features that Postgres doesn't.

Repeatable SQL (0)

Anonymous Coward | more than 5 years ago | (#28549055)

One phrase: repeatable SQL.
INSERT IGNORE,
INSERT .. ON DUPLICATE KEY,
ALTER IGNORE TABLE,
etc.
I searched for these features in Postgress, but could not find any of them. Without the means of making SQL repeatable, a database is completely useless to me.

Off course the fact that such probably must be done with programming on the database server (what could possibly go wrong?) makes it even worse.

Re:Repeatable SQL (1)

rtaylor (70602) | more than 5 years ago | (#28549475)

Take a look at the more general SAVEPOINT feature. They can be thought of as sub-transactions.

Any transactional statement may have the exception caught and effects rolled back within the parent transaction but it will accommodate complex logic:

BEGIN;

SAVEPOINT trying_complex_action;
DELETE ...
UPDATE ...
DELETE ...
INSERT ...
-- INSERT threw exception. Catch it!
-- Determine this is the type (say unique value exception)
-- we don't care about and get rid of this work
-- continuing with the outer transaction.
ROLLBACK TO SAVEPOINT trying_complex_action;

I regularly use this to ignore unique value violations but still pass something like a bad date format upstream to the application. You can opt to rollback on all errors if you choose.

Re:Repeatable SQL (1)

jadavis (473492) | more than 5 years ago | (#28549591)

One phrase: repeatable SQL.

How about: "insert ... select ... where not in (...)"?

[Sigh]... Still waiting for bulk loading... (1)

gbsmith (68154) | more than 5 years ago | (#28549723)

...comparable to MySQL. I think Postgres kicks MySQL's ass (to the extent that DBMSes have asses) in almost every respect. But MySQL wipes the floor with PG when it comes to bulk loading data with possible unique constraint violations. INSERT IGNORE, INSERT REPLACE, and the mysqlimport CLI command wrapping those statements make life soooooooooo much easier when one has to deal with millions and millions of overlapping rows. The typical workaround offered in the PG community is always a clumsy combination of temp tables, rules, triggers, seances and goat sacrifice, usually ending with the phrase, "See? Simple really!".

I think the addition of convenient bulk loading tools could be a game changer for potential enterprise users, or anyone loading high volumes of data.

Re:[Sigh]... Still waiting for bulk loading... (1)

dkleinsc (563838) | more than 5 years ago | (#28550173)

The way I see your problem, you have either a very bad data set (millions of unique constraint violations) or a bad schema (a field that's marked as unique that shouldn't be). Yes, correcting this is a massive PITA, but PostGres is relatively intolerant of those sorts of faults for a reason. Both the INSERT IGNORE and INSERT REPLACE look to me (from the documentation, I'm not a DBA) like they'd be prone to losing some data in a way that you don't notice. Which for anything mission-critical is really really bad.

Re:[Sigh]... Still waiting for bulk loading... (0)

Anonymous Coward | more than 5 years ago | (#28550343)

I agree respect INSERT IGNORE as it's just a way to skip proper staging,
Now INSERT REPLACE is useful as is mostly MYSQL version of the UPSERT or MERGE statement.

Anyway in the context that the OP wants to use them, both are a very bad idea.

Re:[Sigh]... Still waiting for bulk loading... (1)

gbsmith (68154) | more than 5 years ago | (#28550561)

While I see your point of view (to an extent), I have to disagree with you on most all points:

1) It only takes one UCV in millions of rows to ruin the load. Also, the data may come from another source, and it may be dirty with UCV when we got it.
2) The field(s) is/are marked UNIQUE - and they are supposed to be. We know this.
3) I whole-heartedly agree with PG protecting the table from violations and faults, but I am telling PG ***exactly*** how to handle the fault. Either:
  a) Keep the old and IGNORE the new
  b) REPLACE the old with the new or
  c) INSERT All or nothing - the current default (well... only) behavior

  *) I suppose there is a (d) here: "If the row has a created_at value older than 8 days and the qty_sold is 10 but the completed_flg is false then replace the row else..."
        But then, of course, your really are getting into application logic. (a) - (c) are simply, DB oriented actions.

After any one of these, I expect - heck, I am *demanding* - the constraints to be in full effect. But I would like a choice as to how the faults are handled.

4) If they lose data in a way that is consistent with the constraints and the command (e.g. a row is IGNOREd), that is my fault. I know what I am asking for.

You know, in general, I expect the RDBMS and its rules and constraints to *work for me*; not *me to work for it*. ;-) I want the 999,999 new unique rows in the DB. I *want* the 1 UCV kept out (or at least handled properly). Computer, take care of it! Sure, report back to me what was done... but just do it!

But I will agree that "...losing some data in a way that you don't notice... for anything mission-critical is really really bad." ;-)

for beginners? (1)

innocent_white_lamb (151825) | more than 5 years ago | (#28549879)

I have what has become a fair-sized database program that I wrote in a version of Basic several years ago. There were good reasons for doing it that way at the time, mostly related to cross-platform requirements, available hardware and a limited set of options at the time, all of which have since become pretty much irrelevant as the years went by. However, the program keeps cranking and has grown and been expanded and added to (by me) into what now amounts to a "management suite" for the business that I wrote it for.

More and more lately, I've been thinking that while this Rube Goldberg contraption is currently working fine, it might be smart to move it onto a real database. So I'm thinking about rewriting the whole thing over the course of time, and the little that I know about Postgresql makes it seem to be just exactly the tool for the job. The whole business runs on Centos Linux now, anyway.

Accordingly, I guess I should get started learning Postgresql. I've been writing programs for 30 years but the only real database I have ever worked with before was dBase and I haven't touched that for years either. Accordingly, I would appreciate recommendations for relevant books, websites, tutorials, whatever.

I really wish they improved clustering (0)

Anonymous Coward | more than 5 years ago | (#28550359)

I really love using postgres over sqlserver or oracle WHERE possible,
but the lack of real (RAC - LIKE) clustering for postgres makes this difficult...
and by clustering I don't mean replication... I mean multiple active nodes (N+1) serving the same databases

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?