Beta

Slashdot: News for Nerds

×

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.2 Released

kdawson posted more than 7 years ago | from the faster-so-they-say dept.

147

An anonymous reader writes to let us know that PostgreSQL 8.2 has been released (bits, release notes). 8.2 is positioned as a performance release. PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.

cancel ×

147 comments

positined? (0)

chris_mahan (256577) | more than 7 years ago | (#17122066)

I should know better than expect correctness in AS, but come on...

also "PostgreSQL it is still missing" (0, Flamebait)

larry bagina (561269) | more than 7 years ago | (#17122356)

PostgreSQL it is still missing.

bitmap? (0, Troll)

cxreg (44671) | more than 7 years ago | (#17122090)

Sure would be nice to get bitmap indexes one of these days

Re:bitmap? (1, Funny)

jrockway (229604) | more than 7 years ago | (#17122380)

Sure would be nice if you sent in a patch.

Re:bitmap? (0)

Anonymous Coward | more than 7 years ago | (#17123254)

wow that reply is so interesting and original! no doubt your major contributions to postgres would amaze and astound.

Re:bitmap? (4, Informative)

nconway (86640) | more than 7 years ago | (#17122466)

Bitmap indexes will almost definitely be in 8.3. Gavin Sherry submitted a revised patch [postgresql.org] for them a few days ago.

Re:bitmap? (1)

jadavis (473492) | more than 7 years ago | (#17123336)

PostgreSQL does use bitmap indexes, just not on-disk (as nconway said, should be in 8.3).

PostgreSQL currently uses bitmap scans to combine indexes (which means fewer multi-column indexes are necessary), and also to reorder the results of an indexscan in disk block order so that it can get blocks in disk order with better cache behavior.

Re:bitmap? (-1, Flamebait)

Anonymous Coward | more than 7 years ago | (#17124442)

LOL, pointing out a major missing feature is a troll? good one, idiots

Real Men don't use Window Functions (-1, Flamebait)

Anonymous Coward | more than 7 years ago | (#17122120)

Their program-level code calculates the "sliding window" of rows, and creates the correct "where" clause of the query. AND THAT'S THE WAY THEY LIKE IT. Real Men are not concerned with finding new and "innovative" ( in the Bill Gates sense) excuses to take functionality out of the programming level and into the DB level, because Real Men can program both as necessary.

The monetary savings of writing your own sliding windows code, instead of paying Oracle's licenses, are to be spent on BOOZE and HOOKERS.

Re:Real Men don't use Window Functions (4, Funny)

larry bagina (561269) | more than 7 years ago | (#17122384)

According to the MySQL fanbois, Window Functions are bad for performance and not even useful. Just like subselects, data integrity, triggers, and transactions. Oh wait, MySQL 5 supports subselects. Subselects are no longer bad for performance.

Re:Real Men don't use Window Functions (1)

theelectron (973857) | more than 7 years ago | (#17122640)

Correct me if I am wrong, but wasn't MySQL 5.x supposed to include transactions and triggers among other things? I'll be the first to admit that I don't really keep up on the Postgre/MySQL battle, but you might want to keep up on current technology if you are going to make an inflammatory post like that.

Re:Real Men don't use Window Functions (0)

Anonymous Coward | more than 7 years ago | (#17122884)

*whoosh* Parent post was trying to be sarcastic. Yes, the later versions of MySQL has most of those.

Re:Real Men don't use Window Functions (1)

fimbulvetr (598306) | more than 7 years ago | (#17123282)

I don't know if I fall under the fanboy sign in this case. I know the differences between postgres and mysql, and use mysql more frequently than postgres.

In any case, I never argued that those things were bad for performance, but I did argue this:

More often than not, subselects and triggers make people lazy and generally patch up cases where non normalized data should be fixed. They encourage things like db/app bleedover, not fully understanding joins, and not fully implementing data normalization (where appropriate).

Fairly often, and certainly less so in mysql (but probably now moreso), I run across cases where postgres and oracle queries used slower subselects, bad non-normalized data or should-have-been-the-applications-job triggers where they were completely inappropriate and really demonstrated a lack of knowledge of any RDBMS.

Re:Real Men don't use Window Functions (1)

euice (953774) | more than 7 years ago | (#17125882)

If you really have the knowledge of DBMS, you should be thankful for the options! You sound like abandoning features to force you into discipline.
Sometimes you have to balance development time against performance, not to mention the statements you as an administrator type by hand, where performance might not be an issue.
And in addition to that, I can assure you that there are lots of cases where subselects are REALLY fast in postgresql. Even faster than aggregates and group by. Never underestimate the power of the query optimization in postgresql, since 8.0 this is really good.

Re:Real Men don't use Window Functions (-1, Offtopic)

Anonymous Coward | more than 7 years ago | (#17122474)

Tough call for the mods on this one. Imo, this is +3 Insightful wrapped in a somewhat -1 Flamebait with a touch of -3/4 Troll on the side.

What did the AC win, Johnny?

Re:Real Men don't use Window Functions (1)

TranscendentalAnarch (1005937) | more than 7 years ago | (#17122584)

Armchair moderating ftw.

Watch out, MySQL. (5, Interesting)

Anonymous Coward | more than 7 years ago | (#17122372)

MySQL has been the dominant SQL server within the open source community. Between its non-standard SQL and it's lack of advanced features, many developers and DBAs are getting fed up. Thankfully, they've been able to turn to PostgreSQL.

At my firm, we switched some of our MySQL Enterprise databases over to PostgreSQL 8.1. What we found was pretty amazing: PostgreSQL outperformed MySQL by approximately 23% in terms of the number of queries it could handle per second. And this was with a very basic level of tuning! Our MySQL installations, on the other hand, had been tuned by three different consultants. Keep in mind that both were running on exactly the same system, under the same installation of FreeBSD. Were not sure exactly why there was such a remarkable increase in performance when using PostgreSQL, even without much tuning, but we're happy with it nonetheless. We're also happy to no longer being paying MySQL for support.

We're actually quite happy to get away from MySQL. The other developers I work with were quite sickened by the deal MySQL AB reached with SCO a while back. While we're strictly a BSD shop, we still think SCO's actions are quite distasteful, and we are willing to move away from companies that enter into deals with them.

I think you're full of it. (0)

Anonymous Coward | more than 7 years ago | (#17122656)

I'm a fan of PG, but your post sounds like you're just trash-talking MySQL. (Don't get me wrong, MySQL is a joke for sure.) I'm also doubting the 23% increase in performance, but I haven't made any comparisons personally.

Re:I think you're full of it. (5, Informative)

szap (201293) | more than 7 years ago | (#17123002)

... I'm also doubting the 23% increase in performance...
FWIW, and YMMV, when you get hammered with many concurrent queries, it's much, much faster. At about 100 concurrent hits, about 50% faster: http://tweakers.net/reviews/657/6 [tweakers.net] Benchmark method here: http://tweakers.net/reviews/646/9 [tweakers.net]

Yes, it's missing description on how exactly they set up MySQL. MyISAM? innodb? So take it with a grain of salt.

Re:I think you're full of it. (3, Informative)

innosent (618233) | more than 7 years ago | (#17124686)

actually, they used innodb, and yes, Postgres scales much better than MySQL, but MySQL is a little more streamlined for low-volume jobs.

Re:I think you're full of it. (1)

H Tuuri (1036258) | more than 7 years ago | (#17126438)

Hi!

In the upcoming MySQL-5.0.30, we have improved InnoDB's scalability under multiple concurrent threads that insert, update, or query the database as fast as they can. It would be interesting to see the Tweakers' benchmark re-run with the new version.

Best regards,

Heikki Tuuri
Innobase Oy / Oracle Corp.

Re:Watch out, MySQL. (-1, Offtopic)

Anonymous Coward | more than 7 years ago | (#17122738)

This is why I hate the Slashdot moderation system. I get mod points maybe once a month, when I'm too busy to read /. and my "average posts per day" drops to within the norm. So I drop an "interesting" on a genuinely interesting (and relevant) post from an AC, and not a minute later, some douchebag wanders by and drops an "offtopic" on it, obviously without even reading it. Meanwhile another (make that two) mod just came by and gave it another "interesting" because it is! Feh.

Yo! Douchebag! Blow another point modding this offtopic!

Posted AC because I've modded other posts on this article also.

Re:Watch out, MySQL. (3, Insightful)

Local Loop (55555) | more than 7 years ago | (#17123050)

It's because MySQL runs like dogmeat on FreeBSD, no matter which threading libraries you use. I know, I just switched from FreeBSD to Linux for our database servers. The performance difference was astounding - approximately 60% gain just from switching to Linux.

For us, PostgreSQL is a lot slower than MySQL on the same hardware. But our workload is not typical by any stretch so YMMV.

Try comparing PostgreSQL and MySQL, both running on Linux and I'll think you'll be surprised.

Re:Watch out, MySQL. (1)

jadavis (473492) | more than 7 years ago | (#17123428)

It's because MySQL runs like dogmeat on FreeBSD, no matter which threading libraries you use.

Well, PostgreSQL launches a process per connection, so I don't see how that could explain the difference. Or are you saying that threading is slower than using processes?

Why are you so sure it's the threading, when he gave no details? If he had consultants coming in, most likely he would have a connection pool if that would have helped. You appear to have latched onto this explanation because MySQL must always be faster, and if it's not, it must be the OS's fault, right?

Maybe he just had a lot of concurrent connections, which is one of many areas where PostgreSQL can show a major improvement over MySQL.

http://tweakers.net/reviews/657/6 [tweakers.net]

There are a bunch more with similar results at tweakers.net. It could also be the PostgreSQL planner, which has had major improvements recently. Or, it could be one of the myriad other amazing things about PostgreSQL (which are often written off as "unecessary features").

They moved to FreeBSD from Linux. (-1, Troll)

Anonymous Coward | more than 7 years ago | (#17124462)

While it was before I joined the company, they were reportedly using Linux on this very same system, with MySQL Enterprise. One of the DBAs had been using FreeBSD at home, and had found it to outperform Linux in many cases. From what I hear, he set up FreeBSD and MySQL on a test machine at work, and they found it blew Linux out of the water in terms of performance.

I'll ask Fred tomorrow about what sort of performance they originally got under Linux. Maybe I'll see if I can even find the documentation they would have had to have prepared to justify the switch.

It sounds like you just don't know how to deal with FreeBSD. That would explain the poor performance you experienced, and how it is completely contrary to what we've found.

Re:They moved to FreeBSD from Linux. (3, Interesting)

shani (1674) | more than 7 years ago | (#17126466)

It sounds like you just don't know how to deal with FreeBSD. That would explain the poor performance you experienced, and how it is completely contrary to what we've found.

For the heavest application at my last job, the load pattern was very query heavy, although the application stored intermediate results in temporary tables. This application is heavily threaded, creating two threads per user connection, plus the MySQL thread, so we're talking like 150 threads created & destroyed per second.

Our original platform was Solaris, and performance was excellent (well, excellent considering the dog-slow CPUs that Sun makes).

We eventually migrated to Linux, but this was possible only after the new thread libraries (well, new at the time). Performance then was quite good.

We found MySQL under FreeBSD basically unusable under heavy loads.

We never tweaked any of the systems. We did try a few thread libraries under FreeBSD, but they all sucked.

Re:Watch out, MySQL. (3, Interesting)

Bacon Bits (926911) | more than 7 years ago | (#17125402)

IMX, since about 7.3-7.4 PostgreSQL runs just as fast as MySQL under any significant load. It simply scales a lot better than MySQL seems to.

I will say that if you've just recently switched to PostgreSQL that you should be sure you read the documentation on configuring the server [postgresql.org] . While the default installation of MySQL is to use as much resources as necessary, PostgreSQL's default install is extremely conservative. By default it only allocated 1 MB (yes, one megabyte) for working memory. If you've got more than 32 MB of RAM, you're probably going to need to edit some config files to see any reasonable performance. Try running a VACUUM VERBOSE to determine how many pages or entries you need in your FSM. That's something that needs to be reconfigured on a production system after it's been in place for some time. If you do strange things like mass DELETEs or TRUNCATE TABLE, you'll also need to VACUUM more often.

The .org root DNS servers run on PostgreSQL, so it's not a problem with the RDBMS itself. Postgre has been repeatedly criticized for being so conservative with the default installation settings. I think they should have some configuration tools (in the Windows installer especially) that helps you to make somewhat more sane configuration settings.

The typical response from PostgreSQL devs on the subject is "yeah, if we turned off fsync [opengroup.org] on our DB it'd run real fast, too". This is partially why PostgreSQL seems to run slower than MySQL on databases that have lots of INSERT and DELETE queries.

I no longer see any reason to ever use MySQL. It's more popular, but I find PostgreSQL, Firebird, and SQLite cover the range of needs so much better. MySQL is great to learn on, but, well, it's just annoying once you really understand the first things about relational databases.

Re:Watch out, MySQL. (0)

kestasjk (933987) | more than 7 years ago | (#17123344)

The thing PostgreSQL needs is a phpMyAdmin, it has something similar but it doesn't come close. phpMyAdmin makes MySQL accessible to everyone, and I think if an OSS DB is going to be widely used it needs a good admin CP which doesn't require the user to be fluent in SQL.

Re:Watch out, MySQL. (2, Informative)

Bluesman (104513) | more than 7 years ago | (#17123538)

Do you mean like this? [sourceforge.net]

Having used both, I can tell you phppgadmin is a bit more polished than phpmyadmin. Neither are particularly wonderful ways to interact with a database, but if you're stuck on a no-console web host, I'd much prefer to have the posgres/phppgadmin combo.

Re:Watch out, MySQL. (1)

Tweekster (949766) | more than 7 years ago | (#17124452)

No I think he meant a quality version, last time i checked phppgadmin seriously lacked.

Re:Watch out, MySQL. (1)

sxpert (139117) | more than 7 years ago | (#17125414)

you can also connect to your postgres remotely and even use ssl encryption from your home machine with psql...

Re:Watch out, MySQL. (1, Informative)

Anonymous Coward | more than 7 years ago | (#17123572)

http://pgadmin.org/ [pgadmin.org] is the most used open source tool.

What PostgesSQL really needs. (1)

Shawn is an Asshole (845769) | more than 7 years ago | (#17124014)

Equivalents to Query Browser [mysql.com] and DBDesigner4 [fabforce.net] /Workbench [mysql.com] .

Use them. They rock. Query Browser does everything I used in phpMyAdmin and much more. DBDesigner4 and and it's (currently rather unstable) replacement, Workbench, are extremely useful for designing/modifying databases. I prefer PostgreSQL for speed, stability, and features, but I develop in MySQL just because of those tools.

Re:Watch out, MySQL. (1)

jadavis (473492) | more than 7 years ago | (#17123352)

A more detailed report, even if anonymous, would be helpful. Can you post your findings on the web, such as workload, hardware, etc?

Re:Watch out, MySQL. (1)

greg1104 (461138) | more than 7 years ago | (#17123702)

At my firm, we switched some of our MySQL Enterprise databases over to PostgreSQL 8.1.

Every time I see the words "MySQL" and "Enterprise" next to one another, it really gives me a good laugh. Why, it's almost as ridiculous as suggesting that SQL:2003 Window Functions are critical for business reporting.

Better than MySQL 3.23? (0)

Anonymous Coward | more than 7 years ago | (#17122594)

So how does this release compare with MySQL 3.23? Because my webhost is still using it, and I need to be able to argue that PostgreSQL 8.2 is infinitely better than MySQL 3.23 for them to provide this also.

Re:Better than MySQL 3.23? (1)

deepestblue (206649) | more than 7 years ago | (#17122628)

Another route (that I took) is to switch to a host that provides Postgres support. I can recommend csoft.net

Re:Better than MySQL 3.23? (1)

jadavis (473492) | more than 7 years ago | (#17123590)

Or get shell access and install it yourself. It works nicely without any special privileges.

Performance? (2, Insightful)

Ant P. (974313) | more than 7 years ago | (#17122652)

How fast is it against MyISAM? (MySQL's main selling point for a lot of people)

Re:Performance? (0)

Anonymous Coward | more than 7 years ago | (#17122906)

Sure, turn fsync off.

Re:Performance? (2, Interesting)

El Cubano (631386) | more than 7 years ago | (#17122934)

How fast is it against MyISAM?

I can't remember where I heard it or who said it, but I once heard someone say words about MySQL to the effect of "if you ignore all the things that make a real database a database, you can make it really fast." Now, I get that lots of web hosts use MySQL and that it is the dominant free database out there. However, there is lots of insight in that statement. Now, in 99% of the cases where MySQL is used, it probably works great with few hitches. However, I'd rather trust my data to a something that values data integrity over speed.

Recall that not too long ago, right here on slashdot we all got to see first hand what happens when MySQL craps out. All the threading was gone. I mean seriously, what sort of database accepts invalid and then silently truncates it and moves on? Again, I don't think that the number of people with MySQL tables with 16,000,000+ rows is very large, but it is still disturbing.

If you are going for something small and light and fast and you are not too concerned about standards, then MySQL is great. Note, I am not trying to troll, I am simply pointing out that for all the people who endlessly bash on one or the other DB, that there is a market space for each.

Re:Performance? (3, Informative)

phoenix.bam! (642635) | more than 7 years ago | (#17123044)

Not only does mysql silently truncate (and I just tested this on mysql 5) If you insert 2006-2-30 into the date field, i just completes the insert and makes the date 0000-00-00. Go Go Data integrity!

Re:Performance? (2, Informative)

Anonymous Coward | more than 7 years ago | (#17123074)

In defense of MySql 5.x you can actually toggle a setting to make it reject invalid data instead of silently mangling it and continuing as if nothing had happened. However, shipping with that setting disabled doesn't do much to improve MySql's data integrity reputation.

Re:Performance? (3, Insightful)

jadavis (473492) | more than 7 years ago | (#17123470)

However, shipping with that setting disabled doesn't do much to improve MySql's data integrity reputation.

Not only that, one of the major selling points of MySQL is that it has many applications. If you deviate from the standard configuration, many of those apps will break. That's one of the problems with the "configureware" mentality, just like in PHP, except that MySQL is lower on the stack so it's worse.

Re:Performance? (2, Insightful)

LurkerXXX (667952) | more than 7 years ago | (#17123706)

That's a defense?

'Real' databases don't have a setting for 'screw data integrity'. Data integrity is kind of one of the central points of a relational database.

It just shows it's background as a toy, not a real database.

Re:Performance? (-1, Troll)

Anonymous Coward | more than 7 years ago | (#17124002)

Quit your fucking whining and read the goddamned instructions for a change.

Oracle and MS SQLServer both have such a setting (1)

brokeninside (34168) | more than 7 years ago | (#17124132)

I'd be surprised if DB/2 didn't also have it.

Re:Performance? (1)

innosent (618233) | more than 7 years ago | (#17124760)

Again, I don't think that the number of people with MySQL tables with 16,000,000+ rows is very large, but it is still disturbing.

Are you kidding me? At my last job, we had five tables (audit records and various archives) larger than 16,000,000 rows. PostgreSQL 8.1 worked fine.

Re:Performance? (1)

jadavis (473492) | more than 7 years ago | (#17125206)

Right. These days you can easily run databases with tens of millions of records on consumer hardware.

Re:Performance? (3, Insightful)

hey! (33014) | more than 7 years ago | (#17123208)

You have to be careful when you ask a question like that.

What's faster, a Ferrari or a semi-trailer truck? If you are transporting a bunch of bannanas, the Ferrari. If you are transproting 50,000 pounds of bannanas, the semi wins.

In other words, the problem with your question is there is no single thing that is "speed". There's only speed to do a certain class of tasks.

Re:Performance? (-1, Offtopic)

Anonymous Coward | more than 7 years ago | (#17124748)

What's faster, a Ferrari or a semi-trailer truck? ... there is no single thing that is "speed"

What if you're transporting a few million dollars worth of amphetamines?

Re:Performance? (5, Funny)

greg1104 (461138) | more than 7 years ago | (#17123732)

How fast is it against MyISAM?

I've managed to get my PostgreSQL installation tuned to very high speeds simply by switching the database disk over to /dev/null. It runs fast as hell, and the data integrity is basically the same as MyISAM.

Re:Performance? (3, Insightful)

MadAhab (40080) | more than 7 years ago | (#17124034)

Should be modded up.

Now for the MySQL fanboi's, I do have to ask: why not use SQLite for the same purpose? Either you need a dumb data store or you need a Real Database. If you need a dumb data store, why not go for the one that does the best job of being a minimal data store - and use SQLite? If you need Real Database features (and I do), MySQL just hasn't caught up to PostgreSQL, and is even losing ground, after all this time.

The hole in what I'm saying, of course, is replication. PostgreSQL 8.2 looks like it's making progress in this respect. I haven't played around with warm stand-by's, but I'm sure someday I'll need it. When I do, log shipping looks like it will do nicely!

Re:Performance? (1)

rg3 (858575) | more than 7 years ago | (#17126500)

Forgive me if I'm wrong, but I heard that SQLite becomes very slow if the database grows too much. This comes from Amarok users. Amarok may store its song database (and playlists?) using SQLite, and people with a big song database have reported it can become very slow, and the issue is apparently solved if you run MySQL and tell Amarok to use the MySQL backend instead of SQLite. So, basically, you may need a dumb data store as you say, but being dumb doesn't exclude being big, and with big databases SQLite is not apropiate.

Re:Performance? (1)

jadavis (473492) | more than 7 years ago | (#17123746)

How fast is it against MyISAM? (MySQL's main selling point for a lot of people)

Well, you should probably consider the planner too. After all, if it's using a dumb plan, or if it is lacking a "feature" that allows it to choose an efficient plan, even a "slow" database will be faster. Remember, optimizing the algorithm is usually much more important to performance than reducing the parsing time of a query.

Example: You need to go 15 places all over town today. Is it faster to take a Fararri and visit in a random order, or to plan the route to travel a shorter total distance and avoid traffic?

And you should probably consider a million other things, as well, but I don't think they'd fit in a /. post.

Gotta love it... (5, Insightful)

chill (34294) | more than 7 years ago | (#17122966)

PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.

Bullshit, pure and simple. This is nothing more than marketing-speak and you should be ashamed.

I'm not saying that SQL-2003 Window Functions are useless, I'm saying your statement about them being "critical" in business reporting is bullshit. Did no one do business reporting before this standard came out? What the hell did people do in 2002? Are all those MS-SQL Server 2000 and Oracle 8i servers going to fall down in shame? I think not.

I see these comments all the time, usually in marketing brochures from a software vendor touting a new feature. They make it sound like all other products are steaming piles of shit if they don't have whiz-bang-feature #16. They like avoiding any conversation that goes "But, I've been using your product and it works great. Are you telling me your product (last rev) is a steaming pile of shit? That implies if I upgrade, next year you're going to be telling me how THIS rev you are so loudly praising is also a steaming pile of shit."

  Charles (had enough marketing-speak for this year)

Re:Gotta love it... (2, Insightful)

Shados (741919) | more than 7 years ago | (#17123066)

It IS a critical feature. Like how CSS support is a critical feature for the web. But in both cases, no one has all the critical features, and its annoying as all hells.

Of course, using the extra stuff the databases support (PL/SQL, T-SQL, etc), we manage. But for example, the "workaround" for the window functions are not only ugly, but often quite misunderstood, on top of being difficult to use through dynamic sql (if thats your cup of tea). I keep seeing people using inefficient paging methods in SQL Server 2000 for example, when (while not supporting the actual function to do it "right") there are a few extremely efficient ways. So those features are indeed critical.

A bit like a certain quite popular database engine that shall remain nameless didn't support stored procedures for like ever. People work around it just fine, but...

Database engines are almost consistantly -behind- user's needs, even the fancy commercial ones, nevermind the incomplete ones.

Re:Gotta love it... (2, Interesting)

rycamor (194164) | more than 7 years ago | (#17123120)

I mean... did this OP rush to push out a lackluster FP on PG, or what?

Practically the only informative part of this post is focusing on the perceived negative (which is a dubious one, IMHO).

Never mind that Postgres has actually turned out some nice feature advances in this release, although they don't make for good marketspeak bullet points. There have been advances in performance, table partitioning, clustering, query logic, user-defined functions, etc... pretty much every area of "enterprise" database development except for the one area the OP chooses to focus.

Re:Gotta love it... (1)

hey! (33014) | more than 7 years ago | (#17123162)


What the hell did people do in 2002? Are all those MS-SQL Server 2000 and Oracle 8i servers going to fall down in shame? I think not.


You are making some assumptions here. First, you are assuming that a feature cannot be implemented before it makes it into a standard, which is not necessarily the case. There are other paths, e.g. the idea gets published in a journal, the relational theory geeks at several leading vendors pick it up, several incompatible implementations are created by different vendors, and since there is no real competitive advantage any more the standards folk come and tidy up. Outer joins were like that. The feature existed well before the standard.

The second assumption is that you can either do, or not do something. However it is entirely possible to do something in a way that is awkard, or slow, or awkward and slow. A language that implements the relational calculus and is turing equivalent can do practically anything you'd want, but it is not guaranteed to do it in a practical way.

I've been in the database business for nigh on twenty five years now. OLAP itself is the kind of thing we thought we'd never have to do because of the power of the relational model. Well, we were wrong.

Re:Gotta love it... (2, Interesting)

rycamor (194164) | more than 7 years ago | (#17123280)

OLAP itself is the kind of thing we thought we'd never have to do because of the power of the relational model. Well, we were wrong.

How would we know? We have never yet seen a DBMS that really implements the relational model (at least, not in the normal world of business software). Show me the word 'relational' in the SQL standard, anywhere. What we have is all sorts of incredible complication to work around the fact that SQL itself is a damaged and confused (and at times contradictory) approach to the problem.

The serious theorists I have read argue that the reason we need all the performance workarounds is *precisely because* we are not really working with a relational system, and all the vendors conflate logical levels and physical storage levels to various degrees.

Re:Gotta love it... (2, Interesting)

dfetter (2035) | more than 7 years ago | (#17124052)

You mean the magic Darwen/Date/Pascal relational model? The one nobody has managed to implement despite the 25 years it's been around?

Maybe it's because the thing can't be made to work, and its limitations (i.e. being equivalent to first-order logic, a limitation not in SQL DBMSs) make it silly even to keep trying.

Re:Gotta love it... (2, Interesting)

rycamor (194164) | more than 7 years ago | (#17124790)

You mean the magic Darwen/Date/Pascal relational model? The one nobody has managed to implement despite the 25 years it's been around?

Ahh yes, the old canard. Actually, several companies and individuals have implemented the relational model MUCH more faithfully than the typical SQL vendor. The problem is not one of difficulty, but rather of popularity and marketing.

In fact, several solo-developer projects have implemented it on the logical level much better than your typical SQL vendor. The problem is that those guys don't have a) the marketing budget and 20 years of industry buy-in, and b) the developer team to implement all the "enterprisey" features like clustering, failover, etc... And by the way, there is nothing about the "true relational model" that makes those things harder to implement. They are if anything LESS difficult to implement with a true relational DBMS than with an SQL DBMS, which has to handle all kinds of oddities like duplicate rows, position-dependent syntax, pointers, and many other nonsensical rules of SQL.

I know lots of you database pros out there hate to hear from guys like Date,Darwen and co. but the thing is they are right: the DBMS world has opted for mediocrity and over-complexity. Of course, that's the way it is with most things in life :(.

Re:Gotta love it... (2, Interesting)

dfetter (2035) | more than 7 years ago | (#17125280)

> > You mean the magic Darwen/Date/Pascal relational model? The one nobody has
> > managed to implement despite the 25 years it's been around?

> Ahh yes, the old canard. Actually, several companies and individuals have
> implemented the relational model MUCH more faithfully than the typical SQL
> vendor.

Name one, and make sure it's one that's disallowed NULLs completely. Date,
Darwen and Pascal's fear of recording states of ignorance is ill-founded in
real-world conditions. Codifying that fear isn't even well-founded in last
century's mathematical theory. Yes, it's true that multi-value logics are
just a teensy tad more complicated theoretically than 2VL. That does
not imply that they're less useful, or that the systems built around
them are more complicated than the truly wackily byzantine things D, D & P
suggest as workarounds for not having NULLs.

> The problem is not one of difficulty, but rather of popularity and
> marketing.

Nope. See below.

> In fact, several solo-developer projects have implemented it on the logical
> level much better than your typical SQL vendor. The problem is that those
> guys don't have a) the marketing budget and 20 years of industry buy-in, and
> b) the developer team to implement all the "enterprisey" features like
> clustering, failover, etc...

> And by the way, there is nothing about the "true relational model" that
> makes those things harder to implement.

That it's been 25 years and nobody has implemented it, despite
resources in industry, government, academia and open source, flatly
contradicts your assertion.

> They are if anything LESS difficult to implement with a true relational DBMS
> than with an SQL DBMS, which has to handle all kinds of oddities like
> duplicate rows, position-dependent syntax, pointers, and many other
> nonsensical rules of SQL.

> I know lots of you database pros out there hate to hear from guys like
> Date,Darwen and co.

Nonsense. It's not that we don't like to hear from theoreticians. It's that
we don't want to hear from doctrinaire ideologues like D, D & P, especially
when they have only "angels dancing on the head of a pin" to show for their
side. One theoretican whose stuff is actually worth reading is Leonid Libkin
. There are plenty of others.

> but the thing is they are right: the DBMS world has opted for mediocrity and
> over-complexity.

You know, this is really dumb prima facie. Something that you need to
have a 130 IQ and a math degree to use even at the most basic level is
something that's pretty fragile. A *really* well-designed tool is one that a
person who's not very bright can pick up and use, while not muzzling the
expression of somebody who is bright and has lots of experience. SQL
qualifies.

> Of course, that's the way it is with most things in life :(.

Oh, puh-lease!

Re:Gotta love it... (2, Interesting)

rycamor (194164) | more than 7 years ago | (#17125964)

> > > You mean the magic Darwen/Date/Pascal relational model? The one nobody has
> > > managed to implement despite the 25 years it's been around?

> > Ahh yes, the old canard. Actually, several companies and individuals have
> > implemented the relational model MUCH more faithfully than the typical SQL
> > vendor.

> Name one, and make sure it's one that's disallowed NULLs completely. Date,
> Darwen and Pascal's fear of recording states of ignorance is ill-founded in
> real-world conditions. Codifying that fear isn't even well-founded in last
> century's mathematical theory. Yes, it's true that multi-value logics are
> just a teensy tad more complicated theoretically than 2VL. That does
> not imply that they're less useful, or that the systems built around
> them are more complicated than the truly wackily byzantine things D, D & P
> suggest as workarounds for not having NULLs.

You're mischaracterizing the argument. I said "MUCH more faithfully". I know there's no perfect implementation, nor will there likely ever be. Name one perfect implementation of the SQL standard. But, there have definitely been *better* implementations, ones that attempt to fit the concepts of the relational model more closely. You know the ones I'm going to talk about: Duro, Rel, Alphora, etc... The fact is very few people care about these, for the same reason that very few people care to be told to eat their vegetables, or in fact to be told there is a better way to do whatever it is they are doing. That doesn't make my argument wrong.

Meanwhile, I'm not a slavish ideologue about this. I personally don't care about the NULL thing, because I think there are sensible arguments on both sides, and no easy resolution. But, supporting duplicate rows, rowIDs, positional attributes, etc... seem to me such blindingly obvious bad choices. This is without even getting to the more abstract stuff like transitive closure. Of course there are trade-offs in the real world, but why trade off things that are useful to gain things that are not?

> > The problem is not one of difficulty, but rather of popularity and
> > marketing.

> Nope. See below.

> > In fact, several solo-developer projects have implemented it on the logical
> > level much better than your typical SQL vendor. The problem is that those
> > guys don't have a) the marketing budget and 20 years of industry buy-in, and
> > b) the developer team to implement all the "enterprisey" features like
> > clustering, failover, etc...

> > And by the way, there is nothing about the "true relational model" that
> > makes those things harder to implement.

> That it's been 25 years and nobody has implemented it, despite
> resources in industry, government, academia and open source, flatly
> contradicts your assertion.

In other words, it can't be done because it hasn't been done? Fallacy. Tell me a logical *reason* why it can't be done.

BTW, I have a hard time believing that someone with your .sig would think that big government, modern academia, and big business are the standard bearers for logic and the limits of human endeavor ;).

> > They are if anything LESS difficult to implement with a true relational DBMS
> > than with an SQL DBMS, which has to handle all kinds of oddities like
> > duplicate rows, position-dependent syntax, pointers, and many other
> > nonsensical rules of SQL.

> > I know lots of you database pros out there hate to hear from guys like
> > Date,Darwen and co.

> Nonsense. It's not that we don't like to hear from theoreticians. It's that
> we don't want to hear from doctrinaire ideologues like D, D & P, especially
> when they have only "angels dancing on the head of a pin" to show for their
> side. One theoretican whose stuff is actually worth reading is Leonid Libkin
> . There are plenty of others.

> > but the thing is they are right: the DBMS world has opted for mediocrity and
> > over-complexity.

> You know, this is really dumb prima facie. Something that you need to
> have a 130 IQ and a math degree to use even at the most basic level is
> something that's pretty fragile. A *really* well-designed tool is one that a
> person who's not very bright can pick up and use, while not muzzling the
> expression of somebody who is bright and has lots of experience. SQL
> qualifies.

Having a 130+ IQ (which I have) is no big deal. It puts one somewhere inside the top 1%, I believe, which is exactly who should be handling the world's databases. However, I have no math degree, and still have had no problem understanding the concepts of how one would use a decent relational system. I don't see it as any harder than SQL. The only problem is, those who learn SQL first have a mindset to overcome.

On the other hand, based on the databases I have had to fix, it seems to me that the average person would be better served by not ever touching SQL. Just spend a few days reading www.thedailywtf.com.

> > Of course, that's the way it is with most things in life :(.

> Oh, puh-lease!

Good argument. Yes, I quoted a sophomoric trope which is essentially true by definition. But I thought you would get the point. Just because "most things" are a certain way doesn't mean the individual should accept mediocrity in the specific.

Re:Gotta love it... (1)

hey! (33014) | more than 7 years ago | (#17127158)

Well, that's an interesting hypothesis. The question is, is it refutable?

In its shorthand form, it is pretty close to being tautological: a REAL RDBMS never requries us to use OLAP strategies; any commercial product X sometimes requires us to use OLAP strategies; therefore any commercial product X is not a REAL RDBMS.

I think it is too strong to say that the products we have to day are not "true" relational systems, although it would be fair to say they aren't pure relational system. If the product implements the relational calculus, I think it can reasonably be called relational.

The question you raise, of logical/physical design separation, is a matter of best practices; and it is true that to the degree that an RDBMS does not implement a clean separation, the more often practicality will encourage user to treat data non-relationally.

However -- and this is an important point -- we should not conflate necessity with sufficiency. Separating logical and physical design means that the functional requiremnts of any two applications that need the data can be met with ease. But it doesn't mean you can meet the kind needs that would fall under the scope of "non-functional testing". Specifically I'm talking about performance. If two applications have critical performance needs that dictate different physical database designs, then they cannot be served by the same database, although they could be served by two databases of identical logical design.

It's not true that all commercial products conflate physical and logical design. Oracle is an example of a commercial product that does a pretty good job. A skilled Oracle DBA can get a single database to support more usage patterns than, say, a skilled MS SQL DBA. But it doesn't mean he can get it to support every possible application with adequate performance. Just more than he could with a simpler system.

Re:Gotta love it... (2, Informative)

Anonymous Coward | more than 7 years ago | (#17123338)

So true. Today, I finished rolling out an OLAP/reporting system for a mid-sized mining company, and guess what's under the hood?
Postgres rocks (or keeps track of them in this case). It works, and it was done 100% free of window functions.

Re:Gotta love it... (1)

rycamor (194164) | more than 7 years ago | (#17123596)

Why is this modded 0? A nicely relevant post.

Off topic (1)

theshowmecanuck (703852) | more than 7 years ago | (#17126156)

Off-topic. default mod point for an 'Anonymous Coward' is zero... even if it is a good post :-) Most moderators don't like to give mod points to ACs because they would rather reward or punish registered users... otherwise it's like throwing away good mod points.

Re:Gotta love it... (0)

Anonymous Coward | more than 7 years ago | (#17125172)

You can emulate windowed analytic functions using extra joins or postprocessing, but it's a lot slower. People who need to do lots of real analytics aren't going to stand for it.

Re:Gotta love it... (1)

Zetta Matrix (245803) | more than 7 years ago | (#17123832)

PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.

I would contest the assertion a different way than the parent post.

It's not like this one particular feature is the only thing holding PostgreSQL back from kicking Oracle and DB2's asses, respectively (even considering just the OLAP/data warehouse applications, as it was phrased).

Far from it. (PostgreSQL lacks many of the advanced features of those products, and is not as fast either.)

Re:Gotta love it... (1)

LizardKing (5245) | more than 7 years ago | (#17126012)

PostgreSQL lacks many of the advanced features of those products [Oracle, DB/2], and is not as fast either.

True, but if you lack a quality DBA and the hardware necessary to get the maximum performance from Oracle or DB/2, then PostgreSQL is a fine alternative. Oracle in particular needs a lot of care and attention to keep it performing at its best, and if you've forked out for the licenses you probably want to get them most from it. It's in situations where the budget or other resources rule out the big commercial DB's that PostgreSQL really shines. It has excellent documentation (which I find much more readable and complete than MySQL's, especially when it comes to tuning options) and is not very complex to administer. In fact, unless there is a compelling reason to go with another DB, then PostgreSQL is always my first choice.

Re:Gotta love it... (0)

Anonymous Coward | more than 7 years ago | (#17124572)

I find it more interesting that MS is being so brazen in using /. for a marketing site. Over the last 6 years, /. has been slowly converted from a thinking mans site to a site that is being overrun by marketers and republicans.

Re:Gotta love it... (1)

chill (34294) | more than 7 years ago | (#17126680)

Dude, we're talking about the SQL 2003 standard, NOT Microsoft SQL Server 2003. The last standard was SQL 1999.

http://www.wiscorp.com/SQLStandards.html [wiscorp.com]

Replication? (5, Informative)

Curly (49104) | more than 7 years ago | (#17123116)

What do PostgreSQL users do for replication? I'm a MySQL admin who would really like to be able to switch to PostgreSQL, but we need to be able to have several slaves hanging off a master, and have everything replicated in as real-time as possible (but asynchronously) to the slaves. I have spent some time looking for how to do this in PostgreSQL but have found each solution lacking. The "most popular free" one, according to the PostgreSQL faq, is "Slony-I", but from what I could find it doesn't replicate schema changes to the slaves. What happens to your replication when the slaves sees an update to a column/table that doesn't exist on the slave? Slony also doesn't replicate "large objects"; I don't know what they are, but as a MySQL admin who has been replicating our databases for many years, I have a hard time imagining adjusting to limitations like these.

Most of the other options I found were abandonware, undocumented, didn't work with PostgreSQL 8.x, etc. I looked at commercial solutions, but they were similarly a mess. Specifically, here is my survey:

* pgpool -- Max 2 servers, and they're not really in sync---commands like now() or rand() will be executed independently on the mirrored machines, causing them to have different data.

* Slony I -- DB schema changes not replicated, nor are "large objects"

* PGCluster -- Synchronous multi-master. We don't want synchronous, and don't need multi-master. Documentation patchy, didn't appear to be currently maintained.

* CommandPrompt "Mammoth" -- Documentation "in the works". PostgreSQL 8.0.7. Tables can't use "inheritance". Schema changes not replicated (at least not table creation, not sure about the rest). Only 1 db replicated, not all dbs. Tables must have primary keys. Have to list tables in config file.

* Bizgres/GreenPlum -- Buzzword-compliant website, but website was broken when I looked for details. The "Community" is inactive---forum is barely used, questions are unanswered.

* PostgrSQL Replicator -- Poorly documented. Only mentions up to 7.x. "News" is from 2001.

I'm not ragging on PostgreSQL: I'd really like to be able to migrate to it. I just fear that when replication is done in a third-party fashion, it loses the tight integration with the dbms necessary to make it work truly seamlessly, and that it isn't maintained as well as the core product.

Perhaps this comment is off-topic, since the post is about a new release of PostgreSQL, not asking for questions about its individual features. But this is the one feature I look for in each new release, and the fact that I couldn't find any good solution makes me wonder if it's because I missed the one great one that people actually use.

Re:Replication? (1)

rsax (603351) | more than 7 years ago | (#17123410)

I love PostgreSQL but I must side with you here. It needs solid, native, asynchronous replication supported by the main dev team.

Re:Replication? (5, Informative)

nyamada (113690) | more than 7 years ago | (#17123508)

We use Slony. It is a delicate beast, but works quite well if you take time to read the limited documentation. You can use a kludge to keep schema changes in line: if you execute all schema changes through EXECUTE SCRIPT statements on the master server, all the slave nodes will get the schema changes. As for large object support, you're right; it is a problem.

PITR recovery and log replication may work in 8.2; but I agree with the posters who complain that there is no easy replication for postgresql.

--more-- (0)

Anonymous Coward | more than 7 years ago | (#17123518)

Along that line, what do PG users use for full text searching? Like for ~5mil rows each containing ~12 words.

fts - pretty simple (it stores duplicate words, and breaks down words too much, like AMAZING will store: MAZING AZING ZING ING NG which takes up way too much disk space)

tsearch2 - afaik doesn't support wildcard searches, like for AMAZ*

Do you use those? Or roll your own? Or what?

Re:--more-- (2, Informative)

mikaelhg (47691) | more than 7 years ago | (#17126996)

... what do PG users use for full text searching?

The same as everybody else who stores text in a relational database. Use external indexing, such as Lucene, which actually has some features you'd want for non-trivial full text indexing and searching, such as stemming.

Re:Replication? (5, Informative)

oGMo (379) | more than 7 years ago | (#17123544)

Slony also doesn't replicate "large objects"; I don't know what they are,

You're a DBA and you don't know what large objects are?

but as a MySQL admin

Oh, right. Not really a DBA

Let's see:

  • "pgpool -- Max 2 servers, and they're not really in sync---commands like now() or rand() will be executed independently on the mirrored machines, causing them to have different data." One: keep your clocks in sync. Two: how can you tell if rand() isn't "in sync"? You run it on each server and you get different results? You know what rand() means, right?
  • "Slony I -- DB schema changes not replicated, nor are "large objects"." One: how often does your schema change, and do you really need automatic replication? Two: If you don't even know what large objects are, why do you have a problem with this?
  • "PGCluster -- Synchronous multi-master. We don't want synchronous, and don't need multi-master. Documentation patchy, didn't appear to be currently maintained." So don't use it.
  • "CommandPrompt "Mammoth" -- Documentation "in the works". PostgreSQL 8.0.7. Tables can't use "inheritance". Schema changes not replicated (at least not table creation, not sure about the rest). Only 1 db replicated, not all dbs. Tables must have primary keys. Have to list tables in config file." One: MySQL doesn't have inheritence, you're not losing anything. Two: see above about oft-changing schemas. (Otherwise, this sounds like a very high-level replication of tables, probably using simple scripts or triggers. If it doesn't suit, don't use.)

Others listed are older and not relevant.

I just fear that when replication is done in a third-party fashion, it loses the tight integration with the dbms necessary to make it work truly seamlessly, and that it isn't maintained as well as the core product.

Funny, I fear a database that has only rudimentary data integrity checks. Here's the real question for you: Why do you need replication? It doesn't magically work the way you think it does, even in MySQL [mysql.com] (see under "Problems Not Solved"). Quote: "MySQL's replication isn't the ideal vehicle for transmitting real-time or nearly real-time data". Every replicated database can lose synchronization and no one can honestly guarantee otherwise. Even Oracle.

Slony-I will pretty much give you what you already have. My guess is that you don't really need replication at all; hot standby servers will suffice in case of failure. The rest comes down to query tuning or faster hardware (or a database that does faster nontrivial queries, like PostgreSQL). (And don't complain about costs if you're already buying servers for replication. If you have real data that's making you money here, hardware is cheap; if you don't, you probably don't really need any of this to begin with.) If you need true realtime synchronization, replication is not an option.

Finally, while I'm not a MySQL fan, since you don't seem to give any real reason for wanting to migrate, why bother? You already have a working system and hardware investment. If it ain't broke, don't fix it. If it comes time to upgrade down the line, and the features justify the move, then maybe consider it.

In summary: meh.

Re:Replication? (1)

jadavis (473492) | more than 7 years ago | (#17123958)

[ ignoring some of the unnecessary rudeness ]

Two: If you don't even know what large objects are, why do you have a problem with this?

Perhaps he thinks he misread that as the direct english meaning: "something large". Slony can store and replicate big stuff, it just won't replicate things that aren't tuples. But tuples in PostgreSQL can be big and efficient.

PostgreSQL replication will force you to consider the real consequences of your choices in various situations. MySQL replication will say that it's working, but you won't really know what actually happens (i.e. where your data is, and what it means) in event X. I think he could benefit from learning about Slony-I.

Re:Replication? (0)

Anonymous Coward | more than 7 years ago | (#17124214)

Look, if you're just going to be a smart-alecky cunt who posts useless shit like that, then stop wasting everybody's time and don't post at all.

Re:Replication? (0)

Anonymous Coward | more than 7 years ago | (#17124960)

Pot. Kettle. Black.

Feel like pointing out exactly how it was "useless shit"?

Re:Replication? (0)

Anonymous Coward | more than 7 years ago | (#17124386)

And this is one of the friendlier PG users.

The Right Thing would be to replicate also the timestamp and random seed for now() and rand(), but who needs convenience?

Re:Replication? (1)

swbrown (584798) | more than 7 years ago | (#17124676)

Despite your angst, PostgreSQL /is/ currently weak when it comes to replication. Trying to talk around that isn't going to lead anywhere. The only stable and well-maintained option right now is basic asynchronous replication which is unsuitable for many types of applications that require data integrity (e.g., read Slony's section on Failover and note the caveats). PostgreSQL is a great database, so I'm hoping that more sophisticated replication strategies will be in its near future.

Re:Replication? (1)

oGMo (379) | more than 7 years ago | (#17125252)

PgSQL replication is weak, but then, everyone's replication is pretty weak. They can---and don't get me wrong, they should---work on it. But at the same time, they can't work magic. They can't make everything magically synchronous all of the time, or efficiently try and do every now() or rand() across the network against a single source or other silly things the parent poster wants.

Re:Replication? (1)

Curly (49104) | more than 7 years ago | (#17125418)

Why anyone modded the parent Informative, I have no idea, but I'd like to respond to this:

If you don't even know what large objects are, why do you have a problem with this?
[...]

MySQL doesn't have inheritence, you're not losing anything.

It's the law of least surprise. Every exception to "set it up and forget about it" is something our programming team is going to have to keep in mind. That's why it almost doesn't matter what "large objects" are in PostgreSQL, or whether we're using inheritance now.

From other postings, which were actually informative, it sounds like "large object" means something in PostgreSQL that might not come up for us in practice, and that the schema changes not being replicated can be worked around (by expressing them with "execute script", documented in the Slony project). Those two being the only issues I noticed in Slony, we could probably reasonably work with it.

Re:Replication? (2, Informative)

Bacon Bits (926911) | more than 7 years ago | (#17126528)

Binary Large OBjects (BLOBs) are table columns with individual entries are larger than several thousand bytes (typically, those that span more than one page). BLOBs are part of the ANSI SQL standard, AFAIK, which is why it is surprising you'd never heard of them. They differ from MySQL's 'blob' datatype, which is just a big TEXT field. The design of the database (PostgreSQL, DB2, Oracle, T-SQL/MS SQL, etc.) prevents such objects from being stored in the same method that other objects are stored, either because the SQL standard defines maximum sizes for fields or because the physical structure of the database makes it impractical or unreasonable. In the case of PostgreSQL, the objects are internally stored in different tables with different physical files, although that is not seen by the DB developer at all. They're typically used for storing pictures and documents in the DBMS when you cannot or do not wish to use the file system instead, or for literally storing large binary data. it also supports data streaming, AFAIK.

Table inheritance is like a reverse VIEW, and was defined in SQL:1999. Given table A and table B, let's say table B inherits from table A. Table B will then have all the fields from table A plus it's own. PostgreSQL also supports multiple inheritance. It's standard SQL, but it's very weird, IMO. It has some pretty specific uses, like being able to essentially have indexed VIEWs and such, or making a permanent JOINed table.
http://www.postgresql.org/docs/8.2/interactive/ddl -inherit.html [postgresql.org]

As far as schema changes, the argument goes like this: replication is only necessary on productions systems. Schema on production systems should be static. If you're changing your schema, you probably did something wrong.

Re:Replication? (5, Informative)

jadavis (473492) | more than 7 years ago | (#17123560)

"Slony-I", but from what I could find it doesn't replicate schema changes to the slaves

That's a feature, not a bug. That means you can have DB1 be master for Table1 and slave (subscriber) for Table2, and DB2 be master for Table2 and slave (subscriber) for Table1. You can also chain subscriptions to make a hierarchy, which allows for very good scalability.

Oh, and if you want to replicate schema changes, use the Slony-I "execute script" command. It will lock down all the tables as necessary and synchronize the changes so that nothing gets out of order. Slony-I keeps everything transactionally consistent.

Slony also doesn't replicate "large objects"

Ignore that. A large object is basically an interface to a file over the PostgreSQL protocol. You don't need them to efficiently store large amounts of data. Put a GB into a text type if you want (or bytea type for binary data).

I encourage you to take a closer look at Slony-I. It's what the .org and .info registries use. It's good software. It's also great for an upgrade path when you have a lot of data and don't want to be down for a dump/reload.

PG question. (0)

Anonymous Coward | more than 7 years ago | (#17123320)

In mysql, you can do something like this: update x set y=@k:=@k+1 order by z (syntax is probably a little off)

Can you do something similar in pg in a single query?

Re:PG question. (1)

jdew (644405) | more than 7 years ago | (#17123900)

What is that supposed to accomplish?

I know you can do something like update x set a=b, b=a; in postgresql.

Re:PG question. (1)

Harik (4023) | more than 7 years ago | (#17125360)

from my reading of it, it updates y=(serial counter), ordered by z

An iteration on set data.

so for
y, z
0, 1
0, 2
0, 3
0, 5

you end up with
1, 1
2, 2
3, 3
4, 5

             

Awesome (2, Interesting)

tcopeland (32225) | more than 7 years ago | (#17124010)

> 8.2 is positioned as a performance release.

We've only got a small database (17 million records [blogs.com] or so), and PostgreSQL 8.1 has been handling it fine. But I'm still looking forward to seeing how 8.2 improves things.

And we're using it in another production system [getindi.com] , too, which is going to get pretty big (I hope). Lively times!

Way to go PostgreSQL (3, Informative)

greengarden (1036194) | more than 7 years ago | (#17124296)

I worked a lot with Oracle, and then joined an open source project that started using PostgreSQL. The project is a billing system, so is data intensive. What a great little database PostgreSQL is. And that was back in th 7.x version.
Actually, jBilling http://www.jbilling.com/ [jbilling.com] now runs in many databases but still PostgreSQL is holding its ground against Oracle and other heavyweights. Those extra features that Oracle says you need and charges you an arm and a leg, are really not needed in most applications.

Cheers,

Paul C.
Sr Developer
http://www.jbilling.com/ [jbilling.com] - The Open Source Enterprise Billing System

Reporting (2, Informative)

mccoma (64578) | more than 7 years ago | (#17124374)

PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.

Apparently the submitter has not been visited by any of the plethora of reporting tools vendors who will tell you (without you asking) how crappy the built-in stuff is and how great their stuff is.

Also, given the text, isn't Oracle and DB2 also missing those critical SQL:2003 Window Functions?

I love postgresql (1)

euice (953774) | more than 7 years ago | (#17125766)

I'm using postgresql since the 5.x days, when it indeed was slower than mysql.
But as a developper, I never accepted the shortcomings of the non-standard and really incomplete sql syntax of mysql.
The command line tool psql with tab-completion of sql syntax and less style output of query results convinced me to switch in a second.
PostgreSQL never let me down, whereas I often had problems with mysql databases. (e.g. non working databases after upgrades)
Not to mention the semi-free open-source license of mysql.
What's all the fuss about mysql again? Mysql is a commercial product that is and was inferior to postgresql since the very beginning. The performance gain was small compared to the missing features.
That's just my two cents, but I think the mysql guys did a great job marketing their product and fooling everybody into using mysql.

MySQL license (1)

shani (1674) | more than 7 years ago | (#17126488)

Not to mention the semi-free open-source license of mysql.

GPL [mysql.com] ?

Re:MySQL license (1)

euice (953774) | more than 7 years ago | (#17126620)

Mysql has a GPL/Commercial dual licensing model. And because connection to mysql means linking to the client, which is "derivative work" in terms of the GPL, you can only use GPL'ed software with mysql. Unless you pay them to use their commercial license of course.
OTOH PostgreSQL is released under the BSD license, which has none of these restriction.

One thing you cant do in PostgreSQL ... (2, Funny)

euice (953774) | more than 7 years ago | (#17126002)

... is create a smallint index on an int column ;-)

Re:One thing you cant do in PostgreSQL ... (0)

Anonymous Coward | more than 7 years ago | (#17126270)

CREATE TABLE si(a int);

INSERT INTO si value (1);
INSERT INTO si value (3);
INSERT INTO si value (70000);

CREATE INDEX si_idx ON si((a::smallint)) where a < 65536;
Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...