PostgreSQL 9.0 Released

poet writes "Today the PostgreSQL Global Development Group released PostgreSQL 9.0. This release marks a major milestone in the PostgreSQL ecosystem, with added features such as streaming replication (including DDL), Hot Standby and other nifty items like DO. The release notes list all the new features, and the guide explains them in greater detail. You can download a copy now."

Comments

Thank you!

[bjourne]

Congratulations to all the Postgres developers and a big thank you from me for an amazing job! Postgres is a wonderful RDBMS and one of the best free software projects there is. Rock on!

Re:Thank you!

[WoLpH]

Second that. By far the most pleasant database I've worked with so far and if you don't have everything you need with the build-in features, it's easy to build them yourself.

Re:Thank you!

[Anonymous Coward]

Thirded. There is absolutely no reason for anyone to be using MySQL any more other than the old silly excuse "my hosting provider doesn't have anything else". PostgreSQL is now faster than MySQL in all but the most trivial of contrived cases, doesn't require you to choose between table types for different load types, is just as easy to use and install, has all the features that MySQL has and runs on a Windows server (for those idiots who think that is a good thing). Also, the PG community is vastly more helpful and knowledgeable than the rabble that is the MySQL user base.

Finally, PostgreSQL is a proper independent open source project with a structure that all other open source projects should be judged by. MySQL has gone from hand to hand in the corporate world and has a future that is far from certain.

Down with the joke that is MySQL, and down with all the idiots that make me work with it.

Re:Thank you!

[Anonymous Coward]

Good. Then all three of you will be cutting checks tomorrow to support the project, right?

Re:Thank you!

[h4rm0ny]

Good. Then all three of you will be cutting checks tomorrow to support the project, right?

If my company becomes more successful, then I will have no problem making donations to Postgres from the company. And I think they deserve it. As a private individual... I've donated to fund projects here and there (such as Wikipedia) and various developers who've written some widget or theme that I've used. I'd feel a bit inadequate donating the sort of money that I am able to privately (around a tenner) to something like Postgres. Sort of like sticking 2 pence in the collection flask for Third World Aid. It's a drop in the ocean. Maybe we could start some pledgebank thing where everyone could agree to donate £5, £10, something, if 500 other people do as well. That way individual contributions are significant.

Re:Thank you!

[Chrisq]

Congratulations to all the Postgres developers and a big thank you from me for an amazing job! Postgres is a wonderful RDBMS and one of the best free software projects there is. Rock on!

Apart from that it now really is just about the only alternative to Oracle or Microsoft.

Re:Thank you!

[jdfox]

They used to sell t-shirts at expos to raise money from low-rent types like me, but I've not seen them for sale for some time. There's talk on the wiki of resurrecting branded merchandise sales.

Postgres development team, if you're reading this, I lost my old ash-grey PostgreSQL shirt and would be glad to buy a replacement from you.

Re:Thank you!

[TheRaven64]

Also, the PG community is vastly more helpful and knowledgeable than the rabble that is the MySQL user base

You realise, I hope, that encouraging people to switch from MySQL to PostgreSQL will not improve this...

Re:Thank you!

[HyperQuantum]

it now really is just about the only alternative to Oracle or Microsoft.

Any idea how I can convince by boss (and coworkers) that we should migrate to PostgreSQL instead of MS-SQL? (we're currently using MySQL)

When I said that it's free, they said that MS's Express edition is free as well.

Re:Thank you!

[Glendale2x]

Built-in replication based on log shipping. This advance consists of two features: Streaming Replication, allowing continuous archive (WAL) files to be streamed over a network connection to a standby server, and Hot Standby, allowing continuous archive standby servers to execute read-only queries. The net effect is to support a single master with multiple read-only slave servers.

Wait, what? Holy crap it's about time.

Down with the joke that is MySQL, and down with all the idiots that make me work with it.

Postgres was a joke for being extremely late to the replication party and lacking even the simplest of built-in replication. That alone made it a non-starter for a lot of environments.

Re:Thank you!

[Anonymous Coward]

Here you go: http://postgresqleu.spreadshirt.net :-)

Re:Thank you!

[zuperduperman]

You forgot one other huge reason to use Postgres that for me blows away all others: MySQL's owners (now Oracle) still consider that the client drivers are GPL and if you link to or ship the client drivers with your application you must either GPL your application OR purchase proprietary licenses from them.

For the life of me I cannot understand why any company that builds any software that they may one day wish to sell or distribute in any form (even for free) would use MySQL over Postgres. The unknown future liability of having to pay license fees for MySQL (or do whatever other bidding Oracle tells you) are enough to keep me well away.

First

[theskipper]

First Post!

(...gresql post)

Cool

[iONiUM]

I read the notes, noticed the Column and WHEN triggers. Is this in other SQL databases? If it is, I haven't seen it before. In any case, it's pretty cool that you can setup triggers on a conditional statement. That would really help me out in a lot of scenarios, as I work in the BI space, so alerting is a big deal.

Re:Cool

[jwpye]

Not sure if it's in other DBMSs, but it's in the SQL spec.

Re:Cool

[rsborg]

I read the notes, noticed the Column and WHEN triggers. Is this in other SQL databases? If it is, I haven't seen it before. In any case, it's pretty cool that you can setup triggers on a conditional statement. That would really help me out in a lot of scenarios, as I work in the BI space, so alerting is a big deal.

Isn't this just syntactic sugar? What's the difference between logic in the trigger determining when to issue the payload logic, and the logic outside the trigger... especially if the trigger (re)uses a parametrized stored proc for it's payload?

Not that this isn't nice, but it may also lead to code scattering, increasing maintainability issues.

Re:Cool

[MichaelDavKristopeit]

Not that this isn't nice, but it may also lead to code scattering, increasing maintainability issues.

i agree completely... when the OP said he would use it "in a lot of scenarios" my red flag of maintainability went up hard.

for example, if you're doing column when triggers, and initially you want to do something on a boolean conditional, you'll probably code the trigger as such... but now you add in a new column type and in debugging you find out the trigger isn't going off, and now you have to leave the code you're adding for the new condition to exist, and find and update the trigger code. i simply find that whatever process that allowed you to create the new condition should have also handled the payload of the trigger. code scattering is my biggest pet peeve in system engineering.

Re:Cool

[lanner]

I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.

Once upon a time I worked in the entertainment industry and was working on a big MMO game project.

Company X could not scale up their game clusters past about 1000 players. Somewhere between 1000 and 2000 players, the game would just start bogging down and in-game events piled up and everything trainwrecked and was unplayable.

So, it turns out that most of the game logic was built off of complicated SQL stored procedures, triggers, logic, etc. Basically, they were using their hard drive as a processor.

The problem was with the MS-SQL server disk IO Wait. CPU was okay on all of the systems, but they could just not imagine that the disks in the database server (only one DB server per cluster) could be the source of the problems. Every time there was an item dropped, crafted, or certain other special things happened, there was an atomic commit and that basically required writing to disk on the spot. Get enough of that going and you're whole 20-something CPU cluster sits with idle CPU while the DB server works it's hard drives.

Company went chapter 11, all staff eventually let go, and later was sold off for nothing.

I had pointed out this problem to them, but it was late in development and when you tell the people who are responsible for designing the product that they are idiots, well, they behave like idiots and don't really listen. Not that they could have fixed it anyway due to time and intellect restraints.

Anyway, point of the story is that cool SQL features are cool. But don't use your hard drive as a processor.

Re:Cool

[OlRickDawson]

Yes. Oracle has those.

Re:Cool

[GooberToo]

What's the difference between logic in the trigger determining when to issue the payload logic, and the logic outside the trigger...

No! Its far more than syntactic sugar. Performance, readability, and maintainability are what this brings to the table.

The difference between PostgreSQL's new column trigger feature and traditional triggers is they are only called when the column is modified rather than when any row is modified. This means, in many cases, the trigger will never be called and therefore, the DB isn't having to run at PL/SQL interpreter speeds during the execution of the trigger, to then determine there is nothing for it to do. Furthermore, a big headache which is extremely common to trigger code are IF/THEN/ELSE or long CASE statements to determine which columns are modified, or to determine if the trigger even cares that the row in question (example, columns which the trigger doesn't care about) has been modified.

The above combination of traditional triggers means lots of overhead, lots of needless PL/SQL code execution, and hard to read/maintain triggers for non-trivial actions. Whereas with the new feature, you can now have a single trigger relate to specific column, which is only ever executed when the trigger should actually execute. Its a win, win, win for all PostgreSQL users. And best of all, this means you can have smaller triggers when you need to perform different actions based on different column changes.

Re:Cool

[GooberToo]

i agree completely... when the OP said he would use it "in a lot of scenarios" my red flag of maintainability went up hard.

That's FUD, plain and simple. This is called granularity, not "code scattering." Being able to maintain a trigger which only deals with column x without having to test if your changes break when column y is changed is a big win. Not to mention, you're no longer wasting cycles when column z is updated and x and y are not. Which also means you can throw away traditional CASE code which further improves readability, maintainability, runtime performance, possibly lowers test complexity matrix, and is generally all around good.

Re:Cool

[GooberToo]

I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.

Hate to burst your bubble, but PostgreSQL's column trigger feature could have actually increased performance many times.

Anything can be abused. Negatively portraying a powerful feature which can dramatically improve performance, readability, and maintainability, in a polithera of use cases, is nothing but FUD and ignorance.

Re:Cool

[MichaelDavKristopeit]

i'm not saying conditional triggers shouldn't exist... and i'm agreeing that more cycles would be used if a trigger was used where a conditional trigger could have been used, but my point is that in such situations the same job could be done without using any triggers whatsoever.

granularity is an odd label to denounce and replace scattering, as i can think of nothing that scatters more than grains.

generally, conditional triggers could be used by a platform layer that would automate the creation and updating of these triggers, but in the name of portability, that layer could handle such scheduling and processing on it's own.

if you're a farmer, do you want to "harvest the field" or "pick up every grain of corn"... as a programmer, do you want to "update the codebase" or "find every piece of the codebase you need to update"...

it's not FUD... it's a waste of time.

Re:Cool

[GooberToo]

You clearly have no idea what you're talking about. Granularity is widely accepted for such linguistic applications. The reason being, its completely applicable.

Basically you're foolishly arguing that monster triggers are preferred over small, terse, highly readable, and much more maintainable triggers. Lots and lots of code is good. Less code which does the same thing is bad. That's simply ignorant, foolish, and FUD. "Scattering" is clearly being used to imply a negative connotation. Given there isn't a single negative with their use and that "granularity" is accepted and proper, making such statements squarely implies you're trolling.

Re:Cool

[MichaelDa.Kristopeit]

actually you clearly have no idea what i'm talking about.

i'm claiming that numerous, small, terse, highly readable triggers are LESS MAINTAINABLE, in regards of time required to change feature requirements, than other options.

claiming that statement is wrong is IGNORANT. relying on a platform layer to offset that amount of time INVALIDATES THE ADVANTAGES AND NECESSITY OF HAVING CONDITIONAL TRIGGERS.

Re:Cool

[aztracker1]

I think this is probably offset if you have lazy update procs, or systems where all columns in a row are part of the update statement, which is often the case.

Re:Cool

[Anonymous Coward]

whoa whoa easy now no need to bring out the caps

Re:Cool

[MichaelDa.Kristopeit]

but there is a need to bring out the whoa whoa?

you're an ignorant hypocrite.

Re:Cool

[Anonymous Coward]

..stupid DB guys who can't program.

no offense but this sounds like something a stupid programmer guy who can't DB would say :)

when you tell the people who are responsible for designing the product that they are idiots, well, they behave like idiots

name calling isn't very productive no matter what you're doing. well argued constructive criticism is usually well received. it's nice working with well mannered adults.

Re:Cool

[Anonymous Coward]

GT is not ignorant ... you (MDK) have clearly not spent enough time in BI on both sides of the fence (DataModeling in a Project team versus Supporting it post go-live). In the end the fiscal costs for maintaining highly granular (smaller/more pieces) is cheapier and easier to maintain because of the reduce regression testing on things you did not want to change (but your tweak impacted) and the up skilling required for new support personal is less. Also, when the business changes it's mind later (which invariably does happen with a change in management and new corporate goals) it is a lot easier to modify an existing DataModel that is more granular = more Malleable; which in turn is more cost effective to implement the changes. "Keep it simple" is not just a catch phrase, it is part of the time and effort equation as well for the live of the DataModel.

Re:Cool

[butlerm]

A good optimizer could easily partition a traditional trigger into internal triggers that only ran when certain columns were updated, and in most cases maintenance would be much simpler.

Re:Cool

[butlerm]

So, it turns out that most of the game logic was built off of complicated SQL stored procedures, triggers, logic, etc. Basically, they were using their hard drive as a processor.

For a game? That is positively insane. Most of the overhead of a well designed SQL database server is designed to preserve ACID properties for business transactions, on large databases that won't fit in memory. Without those requirements any well designed game algorithm should somewhere between a hundred and a thousand times faster.

Re:Cool

[MichaelDa.Kristopeit]

i have spent 20 years on both sides of the fence.

you are presumptuous and ultimately wrong in your assertions, as i'm sure you also are quite often in all of the deliverables you produce or contribute to.

passing regression tests is only a confirmation of fit as far as the regression test modelers and supporters and maintainers make it to be.

it's "cheapier" and easier to hire a competent development team that doesn't require a regression testing team, as they never deliver a faulty deliverable.

now you'll argue that isn't possible. then i'll call you an incompetent idiot.

you'll probably claim i'm being childish, then i'll claim ur mum's face is childish.... the simple truth is YOU ARE WRONG.

choosing to continue to argue MAKES YOU AN IDIOT.

you are NOTHING

Re:Cool

[butlerm]

By the way, some databases support a feature called "asynchronous commit", which sounds like exactly what these folks needed. No disk wait on commit because you give up the durability guarantee. The other way to resolve that problem is to use solid state disks for your commit logs.

Re:Cool

[afidel]

Triggers *never* increase readability IMHO, and tying features to the RDBMS rarely increases maintainability, so that leaves performance which can be enough of a reason to use them but analysis should be done to determine if there aren't other much more significant areas where the code can be optimized to bring overall system performance up to where they are unneeded. Then again most of the software I do care and feeding for is platform neutral COTS that can be run on any of MSSQL/Oracle/DB2 with Postgres or *shudder* MySQL sometimes being options. We do little inhouse coding and what little we do is usually running through frameworks like IBM's Castiron or Oracle Fusion so it's again platform neutral.

Re:Cool

[plopez]

I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.
Or programmers who abuse DBs and handroll crapp code to emulate what DBs do well.

More than once I was over ruled by heads of programming teams who said "the programmers don't have time for that, we'll do in a {stored procedure|trigger|PL\SQL} etc." despite my warnings of slower speeds due to the interpreter, inflexibility, or lack of maintainability.

Business logic never belongs in the DB. Even triggers are suspect. They can be horribly inefficient. But writing a huge amount of code to insure ACID compliance when DBs do most of it for you is also unforgivable (except for widely distributed applications with race conditions, see Jim Gray for the primer on that).

Re:Cool

[Anonymous Coward]

Get back to us when you have a legitimate point?

Re:Cool

[Anonymous Coward]

So implementing the SQL specification is now a bad thing? The trolls know no bounds.

Re:Cool

[Anonymous Coward]

You are correct only if you don't know how to properly do things and like to constantly have bugs, debug, and force lots of extra code on everyone else. As goober said, you have no clue what so ever!

You are the reason why so many projects are late, incomplete, and way ovder budget. You really have no clue as others have already pointed out.

Re:Cool

[totally bogus dude]

it's "cheapier" and easier to hire a competent development team that doesn't require a regression testing team, as they never deliver a faulty deliverable.

Hahaha, classic!

Re:Cool

[Mike D. Kristopeit]

YOU are the reason why so many projects require complete rewrites when a single feature is added or removed or altered.

relying on regression testing as a tool of the development process rather than a tool of the final product testing, and thus relying on the regression tests themselves to dictate when code is deliverable instead of relying on developers who have ensured regressions CAN NOT EXIST can NEVER be the right decision.

keep pushing the idea that developers are idiots... why could you possibly benefit from pushing such an idea? perhaps you are an idiot developer, or an idiot that knows they are incapable of developing.

Re:Cool

[caerwyn]

Business logic never belongs in the DB. Even triggers are suspect. They can be horribly inefficient.

The fact that triggers *can* be inefficient is no reason not to use them when there's a good implementation and competent DBAs to make sure they *aren't*. Also, business logic never belongs in the DB? To the contrary- a lot of business logic is sets of rules to maintain consistency between various things. That sort of logic is *precisely* what belongs in the DB, rather than scattered throughout a variety of applications running on top of it.

Re:Cool

[Mike D. Kristopeit]

it's been a long time since slashdot appreciated THE TRUTH.

you might be the only person who remembers.

Re:Cool

[Anonymous Coward]

Lol....the cat is out of the bag!!!

He's an idiot because he believes in regression testing? Statements like that is like watching the retarded kid call ederyone else an idiot. You really are completely fucking clueless and should not have anything to do with any technology design decisions as its a given you are always wrong based on your completely idiotic statements.

What an idiot! Feel sorry for your employer.

Re:Cool

[Anonymous Coward]

YOU are the reason why so many projects require complete rewrites when a single feature is added or removed or altered.

YOU are the sole cause of global warming. If it weren't for YOU, the Cuban Missile Crisis would never have happened and the global economy would not be in a recession.

Re:Cool

[MichaelDa.Kristopeit]

he's an idiot for the reasons i stated. not the reasons you made up, idiot.

you're sorry that i don't deliver regression bugs?

ur mum's face is an idiot.

the cat is back in the bag as you are as confident in your opinion as you are in divulging your identity.

you are NOTHING

Re:Cool

[theshowmecanuck]

This means, in many cases, the trigger will never be called

Whether a trigger is called or not, on an update, something has to check to see if the column was updated. What is the difference between you checking with an IF 'OLD.column1 != NEW.column1' THEN (pseudo code... so don't flame me) or the database engine checks to see if a column was updated? I think this just adds more things to learn with little added value. This could be achieved with an if statement ON UPDATE. Keep it simple applies to "don't add more unnecessary stuff to learn if you don't need to", too. You state that with this change, one won't have messing if/then statements when you are interested in more than one column. But it isn't that much cleaner since you still have to tell it to look for a change for the various columns you are interested in. You just do it using a slightly simpler syntax at the cost of having more things to remember in order to maintain code. Personally, I like having less things to remember. Don't get me wrong... I like postgres and have pushed for its use at most places where I have worked (when Oracle isn't mandated or needed for its very mature failover capabilities... flame me if you want, but I am not a fan of MySQL). And really, this is not that big a deal. I probably won't use it though.

Re:Cool

[Kristopeit, Mike Dav]

ur mum's face are the sole cause of global warming

Re:Cool

[theshowmecanuck]

Agreed. With the exception that I have seen some applications where the volume of transactions is so high (telecom), the cost for the database to check for referential integrity is high enough that the key columns were indexed but not with primary key or unique constraints. This is so the DB wouldn't have to eat up time checking for uniqueness. It was up to the application to ensure referential integrity. Since most applications will never see this volume of transactions, yes it is best left to the DB for referential checking etc, but not always. And as far as business logic, for sure you will make maintaining your applications more complicated if you splash your business logic across various platforms including triggers and stored procs on the database. The hardest thing to debug is not syntax, but the logic. If your business logic is split up all over the place, it is more of a pain to debug these kinds of issues. The larger the project and/or the larger the app, the more so (I'm talking about something enterprise-like, requiring from dozens to hundreds or more programmers, analysts, etc.). Not to mention the performance hit it can cause. Better to let the app handle the business of business logic. IMHO, stored procs and triggers are useful but should be used sparingly if at all. Too many are mesmerized by the siren song of marketing and consultants and college instructors who haven't worked on big systems who will tell you how good triggers and stored procs are.

Re:Cool

[GooberToo]

There is a difference between the engine checking a constraint versus a call into an interpreted language. One is doing less work. The other is doing more work. Which is ideal? Obviously less work is better. And that's before you even get into the PL/SQL code which is essentially doing the same work, but slower. Furthermore, all too often, triggers are called when there is no work to be done but you don't know that until the PL code decides this is the type of row change its interested, otherwise it should have really been a NOP. Whereas with the column trigger, the call to the PL code simply never takes place. So we not only save on the call but all of the wasted time inside of a trigger which ultimately decides its has nothing to do.

Also, when the trigger should be called, in a row trigger, triggers frequently must evaluate which columns have changed before it can even determine if it cares about this row. Should it then decide it does care about this row, likely you've already passed through a mass of CASE and/or IF/THEN/ELSE codes, which ultimately states yet more CASE and/or IF/THEN/ELSE to determine exactly what it should now be doing now that its decided it does need to process this row. Or, you can call a much smaller section of code which is dramatically simplified because one, its only called when its pre-qualified (saving the creation of much redundant code) and two, since its now pre-qualified, we can immediately get to performing whatever logic the trigger in question should do when the column in question has changed.

Those are worlds apart in performance, readability, maintainability. Not to mention the added granularity makes possible a reduction in the test matrix, regression tests, and even makes it more difficult (though far from impossible) to create a regression.

Re:Cool

[GooberToo]

YOU are the reason why so many projects require complete rewrites when a single feature is added or removed or altered.

You really have no idea what you're talking about do you.

That's entirely the point of doing it inside the database. By doing it your way, you are ensuring that everything outside of the database must be changed, including all applications. Or, you can do it inside the database, update one trigger, and everything continues to run, bug/error free.

In other words, what you advocate is well known to be the completely wrong way to do things. Basically, your way is the absolute best possible way anyone can go about creating the exact problem you accuse others of creating by doing it properly. Basically you're proving you are completely ignorant of the subject matter. Worse, you're proving in 20 years of experience, you've not learned how to do it right - which is extremely scary. If this was twenty years ago, you might get people to buy into your BS, but this day in age, anyone skilled with databases and application development is not going to want to have anything to do with your horrible and frankly, idiotic, suggestions.

The recommended practice - create and maintain in one place. Debug one. Test once. Fixing requires on place.
Your recommendation - create and maintain in unlimited locations. Debug everywhere. Test everywhere. Basically, world's dumbest fucking idea.

Re:Cool

[Mike Da. Kristopeit]

you're wrong. you can't even make your recommendation with a misspelling... i believe you meant "debug once" as your next sentence was "test once"... but i guess i'll have to wait for your ever powerful regression testing engineers to finish their test case implementations before i can test my assertion.

you just created a job! perhaps an entire department! great idea! well, for people that need jobs... not for people that need to pay to get something implemented correctly done for a price, or developers that take pride in the the perfection of their work and require no such additional external verification or confirmation.

Re:Cool

[Anonymous Coward]

Or, update the IO pipeline so it's not a bottleneck??

It depends on the problem. Keep in mind that a database is NOT just storage. If it were, why not just use a flat file? Flat file is faster and almost any idiot can make a tailor made database that is orders of magnitude faster than SQL.

The point of SQL is actually the features you wrote you indicated that were boggling down that project. It is also data integrity.

The bottom line is their design was not bad, but just required tweaking for their workload. Either turn off syncing for commits and risk data loss, or add more IO bandwidth to the server.

PS. Most MMOs go belly up anyway.

Re:Cool

[jadavis]

The biggest argument for using WHEN on a trigger is for per-tuple AFTER triggers, to avoid queuing up an event to fire later.

Re:Cool

[kmoser]

The problem was with the MS-SQL server disk IO Wait.

MS-SQL server? That's your problem right there.

Re:Cool

[theshowmecanuck]

I wish there was more information available on the performance of a Postgres trigger to evaluate this versus the built in code without having to test it, which I don't have time for. My thinking for this came from my understanding of Oracle select statements. They are implicit cursors, i.e. a select statement is translated into a cursor internally. So my thinking was that once a stored proc is compiled, its plan and query tree are already done and there really is very little interpreting being done at this stage. That is, possibly similar to Oracle (i.e. you don't save much time if any running a 'regular' select statement versus opening a cursor) in this case possibly the engine in Postgres might not be any faster than if/else/case statements. Internally it has to be doing something similar to the trigger. That is, the engine has to determine that a change has happened on the table (say an update), and then search for that change to make sure it is or isn't on the column you are interested in. And if it is on that column, I would really expect that you would only be interested in the specific tupple(s) that changed (that is the column for the particular row that was changed). And it has to be doing these checks on the parsed/rewritten (by the engine) statements in the transaction. As for the code: I don't think the code is going to be that much cleaner. You make it sound like there are hundreds of lines involved in doing things the old way, when there likely would only be one or two lines/conditions to do a check. In any case, I don't think the performance advantage will be really great enough to warrant using this. But hey, if it floats your boat... I am curious though, is there a slight performance effect from a new trigger type in that the database will now always have to check to see if there is a column trigger on the tables involved on every transaction that is run now? BTW, back to Oracle for interests sake, at one time with Oracle IIRC, writing an explicit cursor instead of using a select statement was actually faster... I am pretty sure it doesn't make any difference now.

Re:Cool

[lanner]

I totally agree with you there.

Re:Cool

[h4rm0ny]

I can imagine the headlines now: "Two DBA's shot each other dead yesterday morning after they fell out over the maintainability of column-based conditional triggers. A police officer at the scene remarked: 'If only they had been using MySQL with the default ISAM tables which support no such functionality, then none of this would have happened."

I love the arguments in C++ and DB stories. They're so much better than watching two people slug it out over a football team in a bar. :D

Re:Cool

[Anonymous Coward]

Complete asshat

Re:Cool

[Kristopeit,MichaelDa]

coward.

you are NOTHING

Re:Cool

[georgeb]

What is the difference between you checking with an IF 'OLD.column1 != NEW.column1' THEN (pseudo code... so don't flame me) or the database engine checks to see if a column was updated?

Well for batch updates it can be a huge thing. The expression can be fed in the query planner, the optimizer can use indexes where available to determine which rows require the trigger and which don't. I'm not saying this happens now (I would doubt it) but the devels have the option to play around with trigger condition expressions whereas procedural code is opaque as far as the query planner's concerned.

Re:Cool

[TheRaven64]

A good optimiser can only do that when the underlying infrastructure supports it. This is only possible now that the engine supports firing triggers when a particular column is modified. You seem to be arguing that they should have implemented this support in the engine but not exposed it to the user. As the AC said, this means that you are recommending that they not implement the SQL spec, which is an interesting perspective. You're not a MySQL developer, by any chance?

Re:Cool

[Splab]

We aren't using triggers for your readability, your maintenance or your speed, we are using triggers and constraints to ensure our dataset is correct.

OP was bashing the programmers for always committing to hard drive, what was supposed to go there - and is going there in most MMOs - to me it sounded like the hardware setup was their downfall.

Re:Cool

[Enleth]

An idiot prepared the server hardware requirements, then. A simple PCI-E card with a few RAM slots, a LiIon battery pack and a faux SATA controller (they are available from a few vendors and cost a few hundred bucks a piece, pretty cheap for such a thing), configured as the WAL store - the database had a write-ahead log, right? - would increase the capacity of a single such server at least tenfold.

The problem wasn't that the databse was used in a wrong way. Rather, it was a lack of a systems integration person in the team, who grasps all the general aspects of the deployment from the frontend down to the bare metal and can identify such problems and find remedies right when they occur.

Re:Cool

[Anonymous Coward]

Incompetent employees that don't understand how to build their systems properly is not an argument for or against a particular RDBMS feature.

I've seen my fair share of poor developers don't understand even the fundamentals of data access kill system performance by doing dumb things like selecting individual rows one at a time instead of fetching them as a group, making too many round trips to the database, writing a row at a time instead of batching, writing bad queries, the list goes on.

Re:Cool

[Anonymous Coward]

Procedures can be written in many other languages than plpgsql. Can you show an easy way to find out when a trigger procedure written in C modifies a certain column? Then do the same for triggers written in Python, Perl, R...

Re:Cool

[Anonymous Coward]

a coward calls someone a "complete asshat" and is responded to by that someone that calls them by their provided name, and then points out they have no identity, AND THEN THAT SOMEONE IS PENALIZED AND CHARGED WITH STARTING THE ARGUMENT.

you're all idiots.

slashdot = stagnated

Re:Cool

[GooberToo]

The reviews I've read have all been very favorable from a performance perspective. As you point out, simple row triggers which work on every row won't see much of a performance advantage, if any. The entire focus in my comments have been for large, complex triggers which frequently branch based on column evaluation. This is the environment in which column triggers can deliver big returns, especially in the cases where after lots of evaluation and branches, it turns out no work on the row is to be done.

I did not mean to imply this will improve performance for everyone, without consideration. Hopefully you didn't get that impression. It clearly will not. But for many systems which are built around the database and have complex triggers, column triggers can be a real silver bullet given the right work load.

Re:Cool

[marcosdumay]

The point was that column triggers will improve readability over what current triggers give you. Anyway, triggers may not increase readability of the code, but they surely can increase the readability of the dataset, and the later is way more important, since normally lots of pieces of code depend on the same data.

Anyway, as the GP said, anything can be abused. The most certain way of improving readability of your code is hiring better coders (may them be programmers, DBAs, or whatever) and giving them flexible tools. That is an example that a tool that got more flexible, not less.

Re:Cool

[marcosdumay]

When you have a performance critical application, you often have to choose between reliability and speed. Maybe those developers didn't want to choose, and ended up committing to the database lots of data that could be lost without much repercussion if ever the game had a problem.

That said, the most probable situation is that the game used the database for synchronization. That is a normal, and often preferred way to code multi-threaded applications, but on this specific case that can lead to commits every second or so for every single player. That is a huge performance bottleneck that would go away if the developers used a cache layer between the application and the database, for example.

Re:Cool

[butlerm]

this means that you are recommending that they not implement the SQL spec, which is an interesting perspective

No need to make things up. There is no reason why they, or the developers of any other database can't do both.

Re:Cool

[butlerm]

The biggest argument for using WHEN on a trigger is for per-tuple AFTER triggers, to avoid queuing up an event to fire later.

Assuming the database engine isn't smart enough (and most certainly aren't) to factor out a when condition from the actual trigger code, absolutely.

Re:Cool

[Rysc]

Should it be suspicious that his approach sounds a lot like the MySQL Way? "Who needs a fancy DB, just stuff it all in to the application layer!"

Re:Cool

[Dalcius]

The problem wasn't that the databse was used in a wrong way.

While the database platform may have been a problem, the first, biggest problem wasn't the hardware. It was (bolded for effect): business logic does not belong in your DB, excepting a handful of cases.

I wouldn't normally have replied -- I agree with your point about hardware -- but this deserves to be underscored because so many people fundamentally do not get it.

I work for a company that was just bought out. Our new parent company develops a well known (in its industry) enterprise desktop application. The entirety of their business logic is written in the DB.

It's a maintenance nightmare, difficult to integrate with outside systems, and the system does not scale. Scalability is kicking their ass... because they can't.

Our company spends much less on hardware (and software, cheers for PostgreSQL), and our application is a billion times easier to develop and maintain. We use triggers - but only a handful.

People develop middle layers for a reason. Better code organization and flow control, much, much better speed, scalability, and flexibility.

Use a DB for what it's good at (ACID, data integrity).

Re:Cool

[Rysc]

Suppose you're right and you take pride in your work and write bug-free code not requiring testing or revision. In such a scenario greater granularity is harmless.

Suppose you're wrong or someone is working in an environment where testing and review are mandatory whether they're needed or not. In such a scenario greater granularity is helpful and worth almost any price.

Suppose that you get your head out of your ass, where I'm sure everything is only done correctly and once and a way with which you're familiar, and take a look at the rest of the world. Perhaps in some environments which are blessed by small projects, simple requirements and no end of quality developers it can be beneficial to write things your way. In most places in the world the projects are huge, the requirements are an ever-changing series of absolutes about which grey-suited men are gravely serious and the developers are mostly mediocre.

Get a little perspective before telling other people they don't know what they're doing.

Re:Cool

[Rysc]

Triggers may not improve readability but I believe the discussion was about whether or not per-column triggers improve on regular triggers, which for readability is "maybe" and for performance is "yes" from where I sit.

There are two broad ways to approach DB design: smart and dumb. The mysql folks advocate the dumb design and are less likely to want or use triggers. A hard core DBA will like the smart design. In a smart design the DB and its triggers and procedures are set up to, at as fine a level as possible, defend the integrity of the data. Triggers for people designing this kind of DB are not in any way bad. On the other hand for people who have designed for a dumb DB triggers are almost always a horror.

It sounds from what you're saying like you prefer a dumb DB, which I gather from your desire for DB-agnostic applications. I can understand the desire and it's not a bad one; for you triggers are probably something to use only if you must. For some scenarios, though, being cross-DB is useless (even stupid!) and it is naturally and purely helpful to use advanced DBMS features such as triggers to help defend integrity and aid performance.

Re:Cool

[Mike Da. Kristopeit]

i'm talking about a specific issue: changing the finite state machine the drives the data model. when you have 1000 conditional triggers and they can all trigger 1000s of other conditional triggers, and now you want to drastically change a single reaching procedure which will conditionally trigger 1000s of triggers.

now you're asked to update that procedure drastically uprooting the order and correctness of all existing triggers... are you going to trace all those triggers to edit them, or would you rather have blocks of procedural code driving the entire process in one place?

who did regression testing on the regression test architects? i've built systems in and out of lead roles using both methodologies... the problem isn't your way won't work... it's that you don't understand any better ways to do the same thing easier with equal or better results in any measurable category. i do understand such ways.

Re:Cool

[bmomjian]

For Postgres, that would be synchronous_commit = off.

Re:Cool

[red_dragon]

Without those requirements any well designed game algorithm should somewhere between a hundred and a thousand times faster.

Of course, you still need to preserve enough resilience into the algorithm to avoid accidentally entire event messages. Otherwise you could have people walking through walls or some such weird shit.

Re:Cool

[JumpDrive]

I agree. I read this and thought , damn , I've got to get my bullwhip out to prevent the developers from doing this.

Re:Cool

[JumpDrive]

Yes, but given a 100 developers. How many of them think about the proper usage of a trigger. Most developers I have run into with regards to triggers never think of anything more than "Oooooh, this is something we could do different".
I think the number would come to around 10.

Re:Cool

[Enleth]

In regard to the placement of the business logic, I think the truth - as always - lies somewhere in the middle and contains a disclaimer to the effect of "not applicable when implemented by morons" and "common sense not included".

Sometimes, the application is all about data and most aspects of the business logic can be summed up as maintaining data integrity. For example, a typical bulletin board updates various counters and lists every time someone posts something - the post counter for the user, the topic, the forum and the category, information about the latest posts for those entities, maybe lists of unread posts for the users (depends on wether those lists are inclusive or exclusive) and loads of other things. Most of those can be moved into triggers, maybe even views with rule-based updates of the actual data, with a great benefit for interoperability. In fact, I did co-author one such bulletin board and the decision to move as much business logic to the database as sensibly possible (and not a thing more) turned out to be correct. Right now, we're implementing some auxiliary functionality in Python (the original code is PHP) and it couldn't be easier. Just about everything is selected using views (which will get a dramatic speedup once I move everything to Pg9, which supports join removal - not that it's slow right now, just noticeable on the server load graphs) and most complex updates are performed using triggers and rules, so the frontend code can be kept simple, and even major updates to the actual business logic and data structure can be contained in the database and completely invisible to the outside world.

There were, however, things that I kept out of the database on purpose, generally due to poor state of interoperability of database engines and programming languages (which is kind of strange, considering that relational databases are such a damn old and mature technology), primarily when it comes to errors. The most prominent is acces control logic. I could engineer the database so that it just refused to accept a post in a forum where the author had no write access (while retaining the possibility of revoking him the access he had and keeping posts he wrote to date without any data integrity problems, of course), but I couldn't think of any elegant, sensible and clean way of turning complex database-generated errors, such as those thrown by constraints or manually in the pl/SQL code, into appropriate exceptions in the application and keep everything in line with transaction handling at the same time. I tried and what I came up with was a sorry kludge, so I just gave up and kept those bits outside of the database. Of course, that meant I had to reimplement some of this in Python and now I have to remember to update two codebases, but the code is simple enough that I can put up with this.

Of course, everything's documented, both in text and diagrams, just in case someone would inherit the code down the road. It's been quite helpful right now, too - there are parts that Just Work and the last time I even looked at the code was a few years ago, right now it is kind of new and unknown even to me when changes are to be made.

In short, I don't think this is about some holy rule that is unconditionally true and you have to "get it" or else. IMHO it's about common sense, practical knowledge in neighboring fields (you're a DBA and you don't know how a CPU cache, a memory controller or a physical hard drive works? Well, you're not going to be a good DBA for high-performance systems, regardless of your knowledge of databases) and experience. All of those together, not just one or two. Only then rules become just guidelines to step over when appropriate.

Besides, with Pg9 and its stream replication and hot standby mode, scalability just shot up through the roof and stopped at about the actual limits of the hardware, where contraptions such as the battery-backed RAM "disk" for WAL store I mentioned earlier come into play to push it even further.

Re:Cool

[theshowmecanuck]

Hopefully you didn't get that impression

I like to bang ideas to see what falls out. Kind of to make sure that I am not using something new just because it is new. :)

Re:Cool

[theshowmecanuck]

Oh and also, I tend to think of triggers as being small since I don't really approve of them and want to see them as minimalist as possible if used. I think they make trying to follow the business logic in the code much, much more difficult, especially if they contain a lot of the logic. As well, when they are allowed in a project, they are often abused. I would rather see logic on the application side and not the database. But if they are used (and sometimes they are really necessary and do provide a best solution to a problem), I really only want to see very few lines of code in them.

I have seen and been involved in debugging old production code... many years old... and seen fixes delayed because someone had decided to use a trigger that would update values in only certain cases. I generally look to see if there are triggers on tables when I first look at a database, but some people who are very application (as opposed to database) centric do not. And since you don't call a trigger they are hidden to them unless they do look. Recipe for SNAFU.

:-)

Re:Cool

[butlerm]

It would be nice to be able to set this on a transaction by transaction basis. Sort of like this perhaps, or with some sort of session variable.

Re:Cool

[bmomjian]

But you can set it at the transaction level:

http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously.

Re:Cool

[Anonymous Coward]

It is a rather old feature, from SQL-99 probably from even older standard. I am not saying they did nothing but to claim that this is a "cool" thing is a bit of stretch.

I found several bizarre problems even in 8. 4: internal support for only two isolation levels, lack of support for BIGINT and TIMESTAMPDIFF in ODBC driver, to name a few. I am not sure if they are addressed in 9.0 but what I am sure is they go after more bells and whistles rather than fixing some of the vexing problems. Apart from this annoying thing, they are doing a good job.

Re:Cool

[Rysc]

I am mystified by your belligerent shortsightedness that means anyone who thinks your wrong doesn't understand what he's talking about.

What's the difference between a single trigger with 1000 conditionals and 1000 column triggers?

If there was something *order dependent* in it you would never have done it with independent column triggers, now would you?

Any idiot can make a hundred interdependent triggers that can cause cascading changes every-which-way and royally screw up everything. If you were trying to object to this kind of spaghetti you should object elsewhere; no one disagrees.

Re:Cool

[Mike Da. Kristopeit]

you can't disagree with contracts that require such sequential processing.

Re:Cool

[Dalcius]

A sincere thank you for the reply.

Respectfully, I believe you're going down a bad road. For a small application, and a developer who isn't asleep at the wheel, what you're suggesting can work OK; I imagine it hasn't been a problem for you.

Generally, for everything else, it tends to blow up down the road. And even for smaller projects, the benefits of keeping your logic out of the DB (faster, cleaner, clearer, easier to learn, easier to update) are significant.

I sincerely want to make an effort to be useful to folks who may not have worked with larger projects before. Please take this post in that spirit. It's a long, and I hope informative, post.

Make your code's intent clear ... alternatively, write logic in the right context
The biggest problem is code intent and flow control. Databases model data, GUIs model user actions, but neither necessarily model business logic. You've got business logic code for that -- like your permissions handling.

Code has a logic-centric view. Databases have a data-centric view. If you write logic in the DB, you're abstracting what you're writing from what you're actually trying to accomplish.

Error handling is a great example for flow control. Trapping an error in the DB, correctly identifying the problem and bubbling it up in a user-appropriate way in a clean fashion, as you said, is difficult.

Keep your code together
Another big problem is finding all the right code. For a newbie or a dev who hasn't looked at a bit of code in 6 months, this is very important.
Breaking your code up into different buckets based on what data it modifies makes your logic - your flow - hard to follow for anyone who isn't intimately familiar with the project.

Consider two implementations for a feature:

1) Application code triggers an update to three tables. A bunch of magic spread across several triggers on those three different tables and written in (depending on your implementation) three different schema files, or one huge file with your entire schema, updates three additional tables. E.G.: "most complex updates are performed using triggers and rules"

2) One function in one file that, in a transaction, updates six tables.

Which is easier to follow? From which implementation can you get a clear vision of the intent of the code? Which is easier to skim? If a new developer looks at your code, in which implementation is he more likely to miss something the feature touches?

Don't make interoperability harder
If you push logic into your DB, interoperability becomes harder, not easier. Before, you had just data, and two different applications could use it differently and have different requirements -- as long as what they did was consistent with the data model (your schema).

Now, with logic in the DB, anyone wanting to share your data has to work around your triggers, or co-opt them, or write entirely new triggers, procedures, and generally muck up your application to accomplish their goals.

Keep your platform flexible
Pushing logic into the DB, you may have made it easier to switch languages (although you still have a bunch of "front end" code you'd have to convert/maintain), but now it is much harder to switch databases. What was once merely data is now jumbled up with your application. If Oracle, MSSQL, etc. becomes a necessity, you're not just switching databases, you'll likely be refactoring significant chunks of your app.

Most business needs won't demand you switch languages (worst case, cross-compile or use IPC). Some business needs will demand you switch databases -- my company is now porting our app to MSSQL.

Your database is likely your bottleneck
In my experience, the first serious scalability problems most apps encounter are in the database. And the solution is always "more hardware." Beefier machines.

Then comes horizontal scaling ("moar serve

Re:Cool

[Splab]

*Ahrem* I'm averaging around 37 million transactions a day, the system is almost in idle state (2 servers with 4 cores, 4 GB ram, 4 hard drives in a hot standby with real-time replication - very very cheap and simple setup), we expect to cope with 20-30 times more users on our system. It's build for reliability and speed (HA and HP).

Synchronizing over the database might be what most idiots do (and they have done it here), but it is by no means a preferred way of doing it - it's what happens when you got people with lack of threading and/or clustering experience programming.

And you have obviously never played an MMORPG other than maybe casual, there is nothing in the world of MMORPG gamers that is allowed to be lost. Turbine once dropped a whole day of transactions due to faulty SAN on their DDO servers, trust me, they lost a lot of money on that.

Re:Cool

[marcosdumay]

Two thousand users making a commit every second means you'd get 170 milions of transaction per day. Of course, those 2000 users won't be playing the entire day, but your 37 milion transactions isn't even near. They also won't all make a commit every second, some may be faster.

Synchronizing over the database is the preferred way for creating web applications and other kind os systems with a low throughtput. That simply puts most of the work on the database developers, and your programmers can focus on solving problems that are specific to them. It just can't be done on this specific case.

Also, I've never played a MMORPG, but I can't in any way think a lot of people would be angry if you lose a minute or so of playing once in a while. An entire day is something different.

Re:Cool

[badkarmadayaccount]

Who's the idiot using interpreters everywhere? If there ever was a place and time for native code, it's in triggers. Or at least use a descent VM, not some handy-dandy cludgetastic POS.

Re:Cool

[caerwyn]

I'm not sure what a native/non-native discussion has to do with this: Postgresql allows for native c-language triggers.

Re:Cool

[badkarmadayaccount]

Then what is the performance problem?

Re:Cool

[Splab]

Do you actually read what people write or do you just settle on a viewpoint and stick with that regardless of how stupid you might seem?

Let me cut this out so you understand it, I'm averaging 37 million transactions a day on 2 servers with a total of 8 cores and 8 GB ram and 8 harddrives, we expect to be able to cope with some 4-500 million transactions a day on this hardware. When we need to upgrade it will be the hard drives that goes first, change to a 10GBit SAN with 2-3 shelves of drives and the sky is the limit, when we need more CPU's we can enable the other 4 cores in both servers (need license for that) - when RAM is the issue we can go all the way to 64GB.

Handling 2000 users with one commit per second per user is a walk in the park.

Now for your programming statement, yes it is what people do, but that doesn't mean it's a preferred way, it is what uneducated people does - and trust me, you really don't want to hope for a guy like me to come save your arse after you are done screwing with the database, we are expensive and doing it the right way around from the get go will save your company and your own arse.

And for the love of god, don't think you know anything about MMORPG's then. Having people lose a minute with thousands of people online will mean someone just got cheated of his yber item. Take the lich king for instance in WoW, this is a fight that will wipe your average guild 95 out of 100 times, if you get him, you get a title that is hands down the most sought after. Should you happen to lose a minute of transactions where a guild just took down the LK, you got 10 or 25 extremely pissed of people, this is a deal breaker for most gamers - I sure as hell would stop playing if I got cheated out of that. If you make a habbit of losing a minute every once in a while you are dead in the water.

In fact, it is a very limited set of applications where you don't mind losing some data, most situations data is the single most valuable asset in a company, you should probably change your view and the way you handle data, sounds like you are a disaster waiting to happen.

Re:Cool

[einhverfr]

You know, it really depends. Hammers are great tools for example. You can use them to drive nails, shape metal, combine them with chisels to carve stone, and the like, but not everything should be done with a hammer. Same thing with db logic. I wouldn't say "avoid using a hammer whenever possible" just like I wouldn't say "avoid putting your logic in the db wherever possible." A lot of business logic belongs in the db but not everything.

Business logic belongs in the db where the rules are either inherent in the data (i.e. data constraints) or need to be consistently enforced throughout a set of applications. A good DBA will be looking at this carefully and asking what is really appropriate at any given tier (and the DB is not one tier but at least three-- physical data layout, logical data layout, and procedural interface).

So I will give you two differing scenarios.

1) Suppose you have an accounting system. Data needs to conform to strict rules, be accessed only in specified ways regardless of the application doing the accessing, etc. Business logic needs to be in the db.

2) Suppose you have an application which does a large number of things. It serves web content, takes e-commerce orders, tracks customer contacts, etc. In some cases, you have rigid business requirements. In other cases you don't. In this case, you have to be very careful about what you put where. Some logic will be in the db by definition. Other logic should be put in the db in terms of either procedural interfaces or logical data layouts (views). Quite a bit more logic should be left up to the application, which should be free to access either logical layouts or procedures as necessary.

But this gets into proper db design which is not a simple field. There are a lot of fads out there. In reality it's better to have a full shop of tools than to rely on any one or two.

Re:Cool

[caerwyn]

Triggers that are poorly written (in *any* language) and perform too much work too frequently. Lots of possibilities here; everything from just poorly written trigger code to triggers that cause huge amounts of extra work to be done unnecessarily or too often.

As I said in my original comment, though, the fact that this *can* happen doesn't mean they can't be very useful when written and engineered by someone competent.

Re:Cool

[GooberToo]

The fact this idiotic post was moderated "insightful" is fucking scary. Other posts I can respect someone who may have your wayward point of view. But that post - complete stupidity and nothing but troll. The fact that it was moded up means either you have another account with mod points (very, very, very likely) or that we can now easily confirm /. has absolute fucking idiots with mod points.

After thinking of it - there may well be an intersection here.

Re:Cool

[GooberToo]

I knew there was a reason I +1 Insightful' ed you before. ;)

Re:Cool

[GooberToo]

+1 Insightful.

I completely missed that until you pointed it out. Good call.

Re:Cool

[Kristopeit, M. D.]

ur mum's face has absolute fucking idiots with mod points.

"Great leap forward"

[XanC]

The "great leap forward" for Postgres is live replication over the network? Get with the times! MySQL has had this for literally years!

Re:"Great leap forward"

[catmistake]

LMAO... unless my sarcasm detector is malfunctioning, comparing Postgres to MySQL is extraordinarily absurd... like comparing megaliths to legos.

Re:"Great leap forward"

[Anonymous Coward]

Yes, but MySQL has a shoddy parser (needs a space after the -- comment tag), poor trigger failing (you have to do a kludge and dump a varchar into an int to get it to fail), apparent lack of direction (how many forks and engines?!), no CTE support and the list goes on. I am constantly banging my head against a wall with MySQL. I use MSSQL for work, Postgres at home and MySQL on hosting.
I am truly surprised that most web hosting companies do not offer Postgres. Postgres also allows writing of DB functions in C, Java, PHP etc. like Oracle, which is useful for bundling code into the DB (making the DB the application) without everyone having to see your SQL source for functions. It is also licensed on BSD which is good for using their libpq library in commercial apps; MySQL's C API is GPLd or licensed expensively from Oracle, although there are moves toward making it free for use in commercial apps (as far as I can tell from the mishmash of info coming from their sales rep via email).
Also, as far as I know, MySQL puts all of its indexes in memory for replication which is a problem if the node goes down. Can anyone enlighten me?

In any case, well done to the Postgres team. Not only is their software package neat, their documentation is some of the best I have ever seen.

Re:"Great leap forward"

[sourcerror]

Nested queries can cause a lot of headaches as well. (needs some redundant 'select*' inbetween)

Re:"Great leap forward"

[C_Kode]

| Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.

I'm not sure how you got modded to +5 with this statement, but your statement is uninformed and completely false. While MySQL isn't in the class of Oracle for HA, MySQL with InnoDB is damn well is a competent database and I don't just mean for LAMP.

Re:"Great leap forward"

[Alex Zepeda]

| Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.

I'm not sure how you got modded to +5 with this statement, but your statement is uninformed and completely false. While MySQL isn't in the class of Oracle for HA, MySQL with InnoDB is damn well is a competent database and I don't just mean for LAMP.

Eh. It's *okay* for lightweight work where you don't care about data integrity or don't add or modify a lot of data. Beyond that it falls apart quickly.

At a previous job we used ActiveRecord hooked up to MySQL to handle an influx of temporal data that was meant to be quickly processed and usable for reading back in real time. ActiveRecord uses sequences (so, auto increment fields in MySQL -- since proper sequences are lacking in MySQL) for the primary key. With Postgres this is not a problem at all. InnoDB, OTOH, locks *the entire table* to update an auto increment field. The sysadmin/dba was averse to using Postres, so the result was a series of complex and tedious to debug performance problems and queues. We spent countless hours dancing around the performance problems inherent to table level locking.

Of course we could have gone with MyISAM... but data integrity was important. There were other seemingly basic features that were lacking in MySQL (timezone support and a useful explain command come to mind). As far as I can tell there aren't a lot of good reasons to actively choose MySQL. The lightweight cases are well handled by SQLite, and the heavier stuff will almost certainly benefit from what Postgres has to bring to the table.

Re:"Great leap forward"

[Anonymous Coward]

not really. the management tools alone are bad enough to disqualify it from my arsenal.

Re:"Great leap forward"

[sourcerror]

it wasnt me

Re:"Great leap forward"

[JohnnyBigodes]

It's not 2000 anymore. 99% of the problems people have historically with MySQL are simply not present in recent production versions. PostgreSQL and MySQL roughly have feature parity nowadays, Stop treating MySQL as if it's some toy. WikiVS has a good, up-to-date comparison: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

I also find it amusing that an AC below complains about "how many storage engines"? Whoosh, that's the sound of the point flying over his head.

By the way, I'm not dissing PostgreSQL in any way, I think it's great. But it's about time some meaningless mantras stop being chanted.

Re:"Great leap forward"

[caerwyn]

Part of the reason MySQL gets treated as a toy is its release discipline- or lack thereof. At least one of the 5.x releases came out with *known* data-loss bugs; that's just not even remotely acceptable in a database, and that's the sort of impression that's hard to shake: people aren't just going to look at subsequent releases and go "oh, well, they say they're paying more attention this time, I guess that's good enough".

Re:"Great leap forward"

[Anonymous Coward]

@JohnnyBigodes #MySQL is roughly #PostgreSQL like your #ass is roughly a #vagina -- good if you're a #faggot but just a stinky mess for everyone else.

Re:"Great leap forward"

[Anonymous Coward]

That's odd that you say that because if you look at all the MySQL forums you see it's being used for all sorts of things, and the company was worth a lot of $$ to both Sun and Oracle. They must be complete fools.

Re:"Great leap forward"

[catmistake]

Stop treating MySQL as if it's some toy

It's not a question of features. It's a question of scale. 300,000 queries per second? That would be a major milestone for MySQL. How about a billion? 50 billion? Will it ever get there? MySQL is probably the right application for your 30,000 member forum site, probably be better than Postgres in that instance (maybe). But for the big stuff, the really big stuff, where you need more than a screwdriver and a hand truck, you need literally need a forklift and a crane, you don't even bother trying to use a screwdriver and a hand truck. Either you get a crane, or it doesn't move.

Re:"Great leap forward"

[JohnnyBigodes]

I could see your point if MySQL weren't being used in some high-profile instances. However, even that isn't the case anymore. For instance, Google has submitted quite some patches of its own to MySQL.

See MySQL's case studies here: http://www.mysql.com/why-mysql/case-studies/

Disclaimer: I am not in any way related to MySQL as more than a web developer. I'm even contemplating a move to PostgreSQL somewhere down the road due to the recent Oracle shenanigans. But nowadays, it is a pretty good product.

Re:"Great leap forward"

[JohnnyBigodes]

You bring up a good point there, and I won't try to dismiss it as it's certainly valid. Misfired releases, so to speak, have hurt MySQL in recent history and created division even in its own community.

I'm just trying to shake down these age-old misconceptions that no longer have any base in reality :) (no foreign keys! no transaactions! no ACID!).

Re:"Great leap forward"

[gullevek]

The fact that mysql still lets me insert "0000-00-00 00:00:00" into a datetime field is just crazy. But even more horrible and wrong is if you enter a wrong date into a datetime field and it accepts it and sets it to 0000-00-00 00:00:00. This is just plain wrong and horrible. How can a database do no integrity check. It feels like using varchar for everything.

Re:"Great leap forward"

[vagabond_gr]

InnoDB, OTOH, locks *the entire table* to update an auto increment field.

This is fixed in MySQL 5.1, just use innodb_autoinc_lock_mode=2

As far as I can tell there aren't a lot of good reasons to actively choose MySQL.

Tell that to facebook and google, cause they think otherwise.

I totally agree that MySQL lacks lots of features. But it's a pretty solid db that is successfully used by lots of people in production environments.

Re:"Great leap forward"

[asdf7890]

no foreign keys! no transaactions! no ACID!

One of the things that put me off mySQL some years ago was people both within the wider community and within the project team themselves seeming to claim that if you wanted such things you were doing things wrong. Not "we don't support that (yet)" but "you're being stupid" and if pressed the best you could raise them to was "here's a workaround that will achieve more-or-less the same thing with a chunk of extra work".

I may be about to be told I'm being wrong headed (and perhaps petty) here as no doubt the entire development team has changed several times over the years, but I can't quite shake the thought that maybe those features were implemented just to shut people up rather than because the team actually understood their importance to those asking for them. If I need/want those properties, other things being equal, I'd rather use a database that has had those properties baked in for much longer.

Re:"Great leap forward"

[Anonymous Coward]

Feature parity my ass.

Re:"Great leap forward"

[TheRaven64]

See Slashdot? This is what you get for putting Twitter buttons on the front page. Can we admit that it was a mistake now please?

Re:"Great leap forward"

[TheRaven64]

Not sure about FaceBook, but Google's uses of MySQL are quite limited. They use their own BigTable for all of the serious stuff.

Re:"Great leap forward"

[georgeb]

I will second that. I remember a discussion with a mysql dev where I was trying to raise the point that the db should not accept 30 feb as a valid date. A quite senior dev, backed up by numerous voices on the mailing list, was trying to convince me that it wasn't the db's job to check for that, quoting performance concerns. This was at least 10 years ago. But nonetheless, it's not a good start for a DB to have core developers like that. I don't like MySQL primarily because it cares about standard SQL just about as much as Microsoft does. I find the documentation to be abhorring. DDL is cumbersome. Working in commandline mysql is pure torture compared to psql (on linux! psql for windows is rubbish; not surprising considering the mess that windows commandline terminal is).

Re:"Great leap forward"

[lucifron]

There is no feature parity. MySQL does have decent range of features these days, but they are often tied to some specific storage engine, which means a lot of them are mutually exclusive.

Want full-text search and GIS? Sure, but you lose ACID and are stuck with table level locking..

Re:"Great leap forward"

[dshk]

Also, as far as I know, MySQL puts all of its indexes in memory for replication which is a problem if the node goes down. Can anyone enlighten me?

I am using MySQL replication for so many years that I don't remember the exact time. The size of the indexes in our database is larger then the memory allocated to MySQL, so there is evidence that this is not true. It is an absurd assumption anyway, if you know MySQL. On the other hand there is another MySQL product, about which I don't know much. You are probably talking about that. That is an in memory clustered database. I believe usually they use regular MySQL instances to have a permanent copy of the data of the cluster.

Re:"Great leap forward"

[Anonymous Coward]

MySQL is still behind postgres in terms of features and SQL compatibility. For example you cannot use an index to search in descending order and you cannot index using a custom function. Postgres is also much better at server-side languages (more languages and better support).

If MySQL offers all the features then use it by all means. However just because you don't use any in-postgres-but-not-in-mysql features it doesn't mean they are not useful or even essential for many projects.

Re:"Great leap forward"

[Decibel]

The Feb 30 issue gets *even better*. For years I used that as a prime example of what's wrong with MySQL, so I was a bit disappointed when I found out they'd fixed it.

Then I discovered that Feb 35th is *STILL* a valid date! The only thing they fixed was Feb 30th and 31st!

MySQL clearly just doesn't get it.

Re:"Great leap forward"

[Decibel]

Even better than 0000-00-00 is 2010-02-35.

Re:"Great leap forward"

[catmistake]

I only repeat what I've been told. Frankly, I think all databases are painfully slow. I'd like to see LDAP expanded into a fullblown DB implementation, and become to working DB what firefox was once to browsers, a long time ago, or what Chrome was... idk, a couple months ago: the regression of the Swiss Army knife, not with every tool imaginable, but it's super thin and light, and ffffast, but a one trick pony (so to speak).

Re:"Great leap forward"

[Anonymous Coward]

@AnonymousCoward #sun were fools and went out of business. #fail

Re:"Great leap forward"

[Toze]

I switched from MySQL to Postgres fanboyism around the time I tried PG and realized I didn't have to choose between (InnoDB) integrity and (MyISAM) fast I/O in FOSS RDBMS. And I could have honest-to-god booleans. I love PG so much. :3

Re:"Great leap forward"

[rycamor]

It's not 2000 anymore. 99% of the problems people have historically with MySQL are simply not present in recent production versions. PostgreSQL and MySQL roughly have feature parity nowadays, Stop treating MySQL as if it's some toy. WikiVS has a good, up-to-date comparison: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

Big emphasis on the "roughly". The features may look the same as tick points on a list, but when you actually try to use them, vast differences show up. Roughly bundled-together features without a comprehensive plan is what it looks like to me. For example, no referential transparency or transitive closure. You can't just nest expressions, views, function calls and procedure calls transparently. You can't alias temp tables in procedures, etc... Lots of odd restrictions and "can't get there from here" scenarios.

I also find it amusing that an AC below complains about "how many storage engines"? Whoosh, that's the sound of the point flying over his head.

By the way, I'm not dissing PostgreSQL in any way, I think it's great. But it's about time some meaningless mantras stop being chanted.

Yeah, we shouldn't expect that old bugaboo "conceptual integrity" to intrude on modern software design, right? Can't use full-text indexing and foreign key constraints on the same table? What kind of compulsive freak would want that, anyway...?

Re:"Great leap forward"

[marcosdumay]

"no foreign keys! no transactions! no ACID!"

I don't know if it changed, but I checked it recently and one still needed a plugin to get foreign keys on MySQL (I wasn't even concerned about transactions, if I were I wouldn't be looking at MySQL). Now, maybe it got fixed, or maybe MySQL is trying to get the same internal organization of Postgresql (making everything a plugin), so I might be wrong, but the database not having it as a core feature simply trowed me out.

Re:"Great leap forward"

[marcosdumay]

MySQL is quite good at handling big amounts of transactions. What it doesn't seem to be able to do is handling a big number of transactions wile guaranteeing the integrity of the data.

Re:"Great leap forward"

[JohnnyBigodes]

No, not really.

MySQL has the concept of storage engines, in which, for every table type you create, you pick which storage engine you want to use: MyISAM, InnoDB, etc. That will determine what features one gets. However, most people don't even bother reading a single bit and get a knee-jerk reaction because the default type is the old MyISAM. Granted, it shouldn't be the default anymore, but still... bliss is only one click away for changing the table type.

InnoDB is the second most-common storage engine (the first being the old/kludgy MyISAM) and is ACID-compliant, supports foreign keys, etc. The only thing it lacks is full-text support which is only available on MyISAM tables, but that can be worked around of relatively easily.

There are also other storage engines available, some free, some commercial, and some that enable some neat tricks (like the Blackhole storage engine for replication purposes).

Re:"Great leap forward"

[psyclone]

Google's AdWords platform supposedly uses MySQL.

Re:"Great leap forward"

[Anonymous Coward]

InnoDB, OTOH, locks *the entire table* to update an auto increment field.

This is not the case (anymore) when the number of rows being inserted is known ahead of time, which is the case for most transactions not designed around a "insert into ... select from" statement.

Source: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Has the Documentation Been Improved?

[careysub]

The documentation (just links to web pages) has gotten out-dated and inconsistent, and hard to use over the years. Does the new release come with a clean up so that it is actually easy to use and understand?

Re:Has the Documentation Been Improved?

[caerwyn]

Err, have you actually used the PostgreSQL manual? It's one of the best manuals I've ever seen for a software product.

Re:Has the Documentation Been Improved?

[Anonymous Coward]

If you have actually read the documentation, you would find that it is one of the finest pieces of software documentation out there. If one could have any complaint it could be that there is just so much of it.

Re:Has the Documentation Been Improved?

[catmistake]

The documentation (just links to web pages) has gotten out-dated and inconsistent, and hard to use over the years. Does the new release come with a clean up so that it is actually easy to use and understand?

No. But there is the Postgres Primer at O'Reilly, and Amazon has Postgres for Dummiez.

I've looked from time to time for good documentation... most DBA's I've asked tell me not to bother; it's 30 years they'll never get back, and they want to save me the trouble, at least that's what they say.

Re:Has the Documentation Been Improved?

[jwpye]

hrm? The documentation is regularly updated... http://www.postgresql.org/docs/9.0/static/

Re:Has the Documentation Been Improved?

[shutdown -p now]

It's actually one of the best manuals for SQL in general - at least, in my experience, it has the most clear explanations of many more advanced SQL constructs that are common between various RDBMSes.

Re:Has the Documentation Been Improved?

[GoChickenFat]

Um...please point me to the copy of the PG manual you are using. being both a PostgreSQL and MS SQL user...MS BOL is significantly more useful...

Re:Has the Documentation Been Improved?

[hibiki_r]

The copy? You go to their website, click on documentation, and select the version you want. I've been using it for years, and the info you want is always there, in a sensible format. I've never wanted more.

Re:Has the Documentation Been Improved?

[schmiddy]

The documentation just links to web pages

Eh? Not sure exactly what you mean, but the postgres documentation is built from SGML into several formats, such as a giant PDF or the web documentation. It's pretty darn good, and if you have quibbles with any of it, post to pgsql-docs and you'll have someone on the case pretty quickly.

Re:Has the Documentation Been Improved?

[GooberToo]

The fact he makes such a statement means two things. One, he's never bothered to look. Two, only interested in FUD'ing.

Re:Has the Documentation Been Improved?

[dskoll]

You are kidding, right? The PostgreSQL documentation is the finest documentation I've seen for a free software project, and among the best I've seen for any software, free or proprietary.

Re:Has the Documentation Been Improved?

[Anonymous Coward]

Two comments of the ever so helpful type that are typical of OSS. Way to win hearts and minds guys.

Re:Has the Documentation Been Improved?

[gmhowell]

Dear lord, this many replies, and only one person has the decency to supply a link?

Kudos to you sir.

Re:Has the Documentation Been Improved?

[miggyb]

RTFM!

Where is the manual?

RTFM!

Re:Has the Documentation Been Improved?

[turing_m]

Err, have you actually used the PostgreSQL manual? It's one of the best manuals I've ever seen for a software product.

AFAIC, it is the standard by which other software manuals should be judged. Good call.

Re:Has the Documentation Been Improved?

[tyrione]

Dear lord, this many replies, and only one person has the decency to supply a link?

Kudos to you sir.

Most likely because we all assume you can get off your ass and visit the site where the Documentation link is readily visible to the naked eye.

Re:Has the Documentation Been Improved?

[Anonymous Coward]

Pointing out where the documentation is gives you a typical sarcastic "oh, that's helpful" comment from MS users. Way to be typical MS-loving assholes, guys.

Re:Has the Documentation Been Improved?

[afidel]

Dude, seriously, it's 2 clicks from the homepage! Documentation and then version either with or without comments....

Re:Has the Documentation Been Improved?

[Anonymous Coward]

Too bad the eye-candy version wasn't polished in time:

http://pgweb.darkixion.com:8081/docs/9.0/static/datatype-numeric.html

Expect to see it for 9.1

Re:Has the Documentation Been Improved?

[gmhowell]

I really hope there is no confusion as to why the Nick Burns character on SNL resonated with so many viewers.

Re:Has the Documentation Been Improved?

[Anonymous Coward]

While I don't hold it a big improvement, that "eye-candy" is present in the latest 9.0 docs here: http://www.postgresql.org/docs/9.0/static/

Re:Has the Documentation Been Improved?

[gmhowell]

Dude, seriously, year of the linux desktop yet?

Hrm, wonder why that is...

Re:Has the Documentation Been Improved?

[afidel]

lulz, I'm primarily a Windows/Citrix admin and datacenter architect who hasn't maintained Linux systems as a major part of my job description in almost 7 years, but incompetence is incompetence. Heck I haven't run Linux on my desktop since RHL 6 =)

Re:Has the Documentation Been Improved?

[Anonymous Coward]

I've heard a lot of reason for why Linux won't "Make it on the desktop" in the past. But this is the first time the reason has been the difficulty in clicking two links from the homepage of an advanced Relational Database Server product....

Re:Has the Documentation Been Improved?

[gmhowell]

The haughty arrogance knows no limitations in the OSS community.

Re:Has the Documentation Been Improved?

[gmhowell]

But how do we know that the poster who asked for a link was incompetent? Why is that the first assumption? Why not look at it as a sincere request for help? Why not drop the snarky bomb AND the link? Or leave the snark behind. What purpose does it serve?

Re:Has the Documentation Been Improved?

[aiht]

Perhaps you missed that there is a link to the docs in the fine article summary on this very page?

Re:Has the Documentation Been Improved?

[Galactic Dominator]

Maybe he doesn't belong to the hearts and minds club, AKA Ubuntu.

Re:Has the Documentation Been Improved?

[Galactic Dominator]

But how do we know that the poster who asked for a link was incompetent? Why is that the first assumption?

Because if you have a /. account, you know what google is at the very least. Communication forums like this exists to promote discussion, not answer rudimentary questions. Questions of that type are so fundamentally basic, it's hard stretch not to associate it will trollish intent. Where you do think the fucking docs are? At the documentation link that's right on the homepage of EVERY major OSS project. In addtion the OP(documentation requester) clearly made known he was smart enough to find docs for different db, one more clear indication of trolling.

Re:Has the Documentation Been Improved?

[GoChickenFat]

The fact he makes such a statement means two things. One, he's never bothered to look. Two, only interested in FUD'ing.

Of course, you are wrong. I've spent quite a bit of time in both SQL Server BOL and PG 8.1 manual. The PG manual may be one of the best OSS manuals but it is far from THE best manual. It's extremely weak in the areas of administration. And if you're brave enough to make a suggestion on one of the PG boards you are usually met with elitist resistance as if their acceptance of a suggestion is somehow a indication of failure. This is especially true if you reference a MS SQL example.

Re:Has the Documentation Been Improved?

[GoChickenFat]

I get rated a Troll and the original parent also because we don't love the PG documentation? What gives? Clearly the troll in this equation is the idiot with the mod points.
TAKE IT

Re:Has the Documentation Been Improved?

[georgeb]

Well first they're wrong. PostgreSQL documentation is awesome and easy to understand. It makes your life so much easier. And secondly.... 30 years? Were they reading the klingonian translation? While high on LSD?

Re:Has the Documentation Been Improved?

[LaissezFaire]

Great documentation makes a great open source software project. Postgres has it, and just gets used more and more. It's a reason why Django is so popular -- the documentation is fantastic.

Spend time loving your documentation and your users will love you back.

Re:Has the Documentation Been Improved?

[marcosdumay]

The documentation beats Oracle's hands down, but I've never looked at MS SQL docs. That's because I always assumed it was just like most of their documentation, and didn't need to because I was working with some top DBAs by the time I had any contact with it. Nice to know that my assumption was wrong, now I have one more source to use.

Now, it is completely understandable that any FOSS developer refuses to accept things that are based on a Microsoft product. There are all kinds of threats by their part, why should the developer choose to make himself vulnerable? Not touching anything that comes from near Microsoft is a very sensible rule.

Re:Has the Documentation Been Improved?

[Anonymous Coward]

Totally. Compentant DBA's can be pumped out in about a week's worth of seminars, or, if the money isn't there, following the recipe directions usually works just fine. And if you have the knack, you can just tinker your way right through it. SQL is no more difficult than html, and dba's are no better than webmasters. That $130K-$400K/year position is just hanging there for you to grab it! What are you waiting for?

Waiting for a capable PostgreSQL front-end

[bogaboga]

Yes first, congratulations to those folks. I am still waiting for a front-end to PostgreSQL that is as functional and easy to program as Microsoft's Access.

I might be flamed here but there is nothing that bests Access in the open source world. Being able to program business logic into a form is something that Access and VB are pretty good at.

What open source program can replace these two Microsoft beasts?

Re:Waiting for a capable PostgreSQL front-end

[TheFuzzy]

Why not just use .NET with PostgreSQL? You can put whatever you want on the back end.

Or you could use Once:Radix or Servoy, both of which integrate with PostgreSQL.

https://sourceforge.net/projects/onceradix/
http://www.servoy.com/

Re:Waiting for a capable PostgreSQL front-end

[dugjohnson]

No flame, but there are a lot of front ends that can work with SQL backends, including PostgreSQL. Depends on what you are looking for and what you are trying to create. Many web frameworks can use PostgreSQL as the backend. Heck, you can even make VB talk to PostgreSQL if you want. Java does just fine. Delphi, since you are obviously willing to pay something. But it comes down to what language(s) you want to work in and then looking around a little bit.

Re:Waiting for a capable PostgreSQL front-end

[abigor]

Probably OpenOffice Base is your best bet. I've never tried it, but I know it's often put forward as an Access alternative. But like most things OpenOffice, it probably falls short in several (or many) respects.

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

you know there is an odbc driver - i've been using access as a quick data manipulation, query and load tool for postgres backend for many many years.

EMS make a reasonable interface if you need to build tables, views etc... on the backend

There are many quick and dirty web front end builders for databases which are better suited than MS access for multi users - "code charge studio" comes to mind

none of the above i would call "programing" though so my guess is more homework is needed. ;)

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

OpenOffice.org Base is alright for a very simple system. You can link it to an external data source using JDBC, ODBC or ADO (on Windows).
For queries, the SQL parser doesn't like simple things (like declare @myvar int for MS SQL) but you can use the button on the far right in the SQL editor to force it to accept the SQL you type in. Also, it doesn't like where you select from a temporary table and return the results - it thinks that there are no results returned.

For added frustration, once you have set up a query and want to create a chart from the results (for example), if you open Calc and press F4 to show the 'databases' view, if you drag the results of a query onto the spreadsheet, it *always* chops off the first 4 rows, or if you haven't navigated to the end of the resultset, it doesn't bother fetching them. Sometimes it works, using the Data to Text button the query view, but as to when this button is enabled is a mystery to me. Sometimes it doesn't let you drop the results you're dragging.

All in all I wasn't very impressed with it, but for form design and very very very simple queries etc., it might be alright, I suppose.

Re:Waiting for a capable PostgreSQL front-end

[h4rr4r]

Access is too easy. It lets people who have no idea what they are doing make a half working solution that then has to be replaced with real code and a real DB.

Re:Waiting for a capable PostgreSQL front-end

[MichaelSmith]

then has to be replaced with real code and a real DB.

Oddly enough I spoke to somebody last weekend who makes her living doing exactly that. I suspect that without access to kick these projects off she would have less work overall.

access -> sqlite -> mysql -> postgres -> oracle

Everybody looks down on the tools to the left.

Re:Waiting for a capable PostgreSQL front-end

[mysidia]

There is an ODBC driver for PostgreSQL. You can probably access a PG database using MS Access just fine...

Re:Waiting for a capable PostgreSQL front-end

[h4rr4r]

I suspect they would call her earlier and the job might be done right from the start.

Re:Waiting for a capable PostgreSQL front-end

[MichaelSmith]

Prototyping tools are important for marketing. Problems start when the go too far.

Re:Waiting for a capable PostgreSQL front-end

[schmiddy]

Indeed. About 0.0005 seconds of Googling brought me this as the first link:

http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html

Though I think there are probably a grand total of 3 people on Earth who have used MS Access in any serious capacity and don't loathe it.

Re:Waiting for a capable PostgreSQL front-end

[guusbosman]

You know that you can point your MS Access client to any supported back-end right? Just create an ODBC connection on your Windows machine to your PostgreSQL server and you can use Access with pretty much all the features that work for the Microsoft JetEngine (PostgreSQL has ODBC drivers here; http://www.postgresql.org/ftp/odbc/versions/)

Earlier this year we converted a huge Access application from MSSQL to PostgreSQL and the technical conversion, using ODBC to PostgreSQL instead of connecting to MSSQL, was a piece of cake.

Re:Waiting for a capable PostgreSQL front-end

[LukeWebber]

Oddly enough, when I go from MS SQL or Postgres to Oracle, I end up looking down on Oracle. Sure it's fast, but its standards compliance is lousy.
But yes, let's all point and laugh at the Access user, by all means. ;^)

Re:Waiting for a capable PostgreSQL front-end

[mysidia]

Not. I hate Access.
I loathe Access.
I'm sometimes forced to use it, usually as a result of stupid decisions made by n00b devs or sadistic PHBs, in the past.

Re:Waiting for a capable PostgreSQL front-end

[bogaboga]

Agreed. Only that I was looking for open source applications. Know of any?

Re:Waiting for a capable PostgreSQL front-end

[bogaboga]

There is an ODBC driver for PostgreSQL. You can probably access a PG database using MS Access just fine...

That is not the bone of contention. What I wanted was an open source app just like PostgreSQL is. Do you know of any?

Re:Waiting for a capable PostgreSQL front-end

[mysidia]

Depends on what you need to do... pgAdminIII is pretty useful; many of the 'fancy' tools are commercial.

Re:Waiting for a capable PostgreSQL front-end

[croftj]

Personally, I find that Qt (C++ api from Nokia) has a nice sql api which will were with several database engines. Maybe not as mind numbingly simple as VB, but very versatile and easy to use.

Re:Waiting for a capable PostgreSQL front-end

[domatic]

Better that it be Access rather than FileMaker Pro. There is an upgrade path of sorts from Access to SQL Server. So if you have one of those unfortunate cases where it was mandated that a dinky workgroup app be shoved out enterprise wide then at least there are options to move the data and app logic to platforms that can take the load. I'm not saying that it's easy but someone who knows what they are doing can get started on fixing it pretty quickly.

That situation with Filemaker Pro is much uglier and Filemaker Pro does even more to encourage marginal developers. FM Pro is an unholy glop of database, scripting language, and a widget set. Trouble with any of those domains tends to impact the other two. And one commercial FM Pro app I was saddled with would corrupt data when too many users hit the server at once. "Too many" being around 12 users. The other cute thing about that app was every person who used it had to have FM Pro client installed on the machine. The developer was a reseller for FileMaker too. Maybe THAT is why he didn't create a standalone runtime that could connect to the server. I'm happy to say that nasty thing is mostly phased out now.

Re:Waiting for a capable PostgreSQL front-end

[turing_m]

The last time I looked at ooo.org Base (at least a year ago, if not longer), I found it surprisingly capable, even workable. Give it a go, and have some patience. I only really had a look at the forms though, but I used to use subforms a lot and I could do what I wanted to do with it. Did not really look at reports though.

As far as business logic, put that in PostgreSQL.

Re:Waiting for a capable PostgreSQL front-end

[bogaboga]

As far as business logic, put that in PostgreSQL.

Well, in many cases of mine, programming this logic right into the form is faster and easier to manage than a full DB.

Case in point: While developing a healthcare app, I'd like to redraw part of the form that asks about pregnancies if the sex chosen earlier is 'male'. We all know males do not get pregnant for example. Putting this logic into the actual Db engine just slows things down in my opinion.

Here's another: Input masks. For example, the USA has a string of integers. On the form, I can program the mask to 'refuse' input other than 0 to 9. Or for countries like Canada that have zip-codes in the form 'A9A 9A9' where A represents an alphabetical letter and 9 represents a number between 0 and 9 inclusive, a form with an appropriate input mask is the best tool.

Though such logic can always be put into the back-end, letting the DB throw an error is typically characteristic of poor use of resources.

Agree?

Re:Waiting for a capable PostgreSQL front-end

[colinrichardday]

I'd like to redraw part of the form that asks about pregnancies if the sex chosen earlier is 'male'. We all know males do not get pregnant for example.

http://en.wikipedia.org/wiki/Male_pregnancy#Pregnancy_among_intersex_and_transgender_people

You may wish to reconsider.

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

OpenOffice Base is a barely functional toy - I tried it early this year to do some processing I would normally have used access for. Just couldn't do it. Maybe in a couple of years.

Re:Waiting for a capable PostgreSQL front-end

[PhrstBrn]

Who cares? PostgreSQL isn't trying to convert Access users. They're two different market shares. There aren't any Access like front ends for Access because that's not the market share they are trying to capture.

As other people have pointed out, if you really wanted to, you can still use Access to connect to a PostgreSQL database with ODBC already, or use OpenOffice Base, a usable open source alternative which you seemed to have conveniently ignored. Access may still be better for those users, but Base is usable. Before you rebuttal with "I still like Access better", that's great, use what you like best. Base is still usable for those who want to use it. Now go astroturf somewhere else.

Re:Waiting for a capable PostgreSQL front-end

[wiredlogic]

A lot of the utility of Access comes from its built in set of data aware GUI controls that aren't available in other .NET applications that use winforms or VBA apps with userforms. Beyond language issues, the low level GUI tools for creating and managing tables and data and the query builder are more advanced than anything in the opensource world.

Re:Waiting for a capable PostgreSQL front-end

[dbIII]

Many, most with a web browser interface, and unfortunately you are going to have to replace all of that stuff in MS Access with a complete rewrite when another version comes out if Microsoft's past behaviour is any indication. Do you want a pile of obsolete books on MS Access scripting for something like putting your car on blocks?
On the other hand you can keep old machines around to run legacy MS Access stuff or virtual machines to do it, but it is very much a moving target and a suprisingly expensive product for the desktop client.
1992 happened a long time ago, just do something with whatever backend you want that users can access via a web browser instead of having to make sure that everyone is on the same version and patch of the client software and you'll save a lot of pain.

Re:Waiting for a capable PostgreSQL front-end

[dbIII]

So if you have one of those unfortunate cases where it was mandated that a dinky workgroup app be shoved out enterprise wide

That is exactly what killed my linux migration, a crappy little flat file "database" for inventory management that required admin access just so that the name of the user locking the "database" could be written to the root of the system drive. Thus the sort of users that only really needed a web browser suddenly had admin rights and proceeded to bring in pirated software, pirated games and for some reason bring in spyware and viruses on a USB stick when squidgaurd and a virus scanner wouldn't let them download it directly. There are worse things out there than MS Access.

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

access -> sqlite -> mysql -> postgres -> oracle -> DB2

Everybody looks down on the tools to the left.

FTFY.

Re:Waiting for a capable PostgreSQL front-end

[Kjella]

Funny, I've used both sqlite and postgres extensively but I certainly don't look down on sqlite. I might look down on the DBA who tried using it where it really doesn't fit and you want the full workhorse database, but for being a single-threaded (sqlite does multi-threading but there's some huge locks), low-complexity database (= need for triggers and language bindings) embedded in an application then sqlite wins with flying colors.

Re:Waiting for a capable PostgreSQL front-end

[MORB]

There is this:
http://www.kexi-project.org/

It's based on Qt/KDE, can use SQLite, MySQL or PostgreSQL as the back end, and can be scripted in python or ruby. Runs on Linux, MacOS, Windows.

Re:Waiting for a capable PostgreSQL front-end

[h4rm0ny]

Yep. You got that right on both counts. OpenOffice Base is the only thing that I'm aware of that is similar to Access that could sit on top of Postgres and as you say, it falls short in many respects. Admittedly it's a year since I used it, but I found it pretty flakey and it is certainly much inferior to Access in terms of functionality and intuitiveness.

That said, Access is a tool for throwing open database design to people who don't understand database design and a grand invitation to build a monstrously unmaintainable and ugly kludge of business logic turned into form objects which hang together through a tenuous web of forgotten relationships and functions. It's legitimate selling point is its easy integration with the rest of the MS Office suite and for simple one-person projects it's very friendly and appropriate. My concern with Access is not with the software itself which does what it's supposed to do (at least in the modern incarnations) very well, but rather that only in around 10% of the cases that I've seen it used, should it have been used. If a project's limited enough that Access is an adequate solution, then often you'd be better off getting someone to bang you out a MySQL database and some plugins to connect to it. If a project is not limited enough (or wont remain limited enough) that Access is an adequate solution, then for pity's sake, please don't use it! But of course, people do.

Anyway, the point has to be conceded. If GP is after something that does what Access does, then there's nothing that can compete with Access. All the rest of us can do is say: "that solution is a nice hammer, but before you use it just check your problem is a nail and not a screw?"

Re:Waiting for a capable PostgreSQL front-end

[the_womble]

Access and Postgres are entirely different things.

Access is a GUI front end for Microsoft RDBMSes. Postgres is an RDBMS server.

What you actually want to look at are GUI front ends (Kexi, Open Office Base), and the database APIs that various languages have (for the VB side of it).

As I mostly code for websites I use Python with Django which has a fairly nice ORM bundled with it, which is very easy to use for most things. I have no idea about how well creating a database schema in a GUI works, but what little I have seen of it makes it look harder than writing a few lines of simple code. YMMV.

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

It definitely falls short in many respects.

Re:Waiting for a capable PostgreSQL front-end

[Inda]

That would be me.

My current Access DB is only 10,000 records big(?). It's actually duplicated (copy and paste) across four projects. It's a document management DB. I also have half a dozen Word and Excel documents that link to it for reporting.

Cost to the company was less than £3,000. Maintenance has been minimal. Training needed to use it has been minimal. The only show stopping errors have been generated by network failures.

Since I wrote it 3 years ago, the company have looked to replace it. Microsoft's EBP has been looked at by a focus group - I alone attended half a dozen meetings with a dozen more people chipping-in their opinions. We even had one of those expensive MS Gold partners on the project. It was ditched for not having enough functionallity. The cost of looking at it was well over £100,000.

Four other of-the-shelf packages are currently being looked at with none of them matching the functionality of my Access DB, with Excel and Word reporting.

My Access DB cost the four projects £3,000. Don't be so quick to dismiss it.

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

That is great! I gives the users with a real understanding of their problem a possibility to test their ideas.

The technical result is likely to be crap, but the user might have learned more about their problem space and developed a prototype more competent developers can learn from.

It is nice to have a prototype and documentation as a starting point. It might be total bogus, but it might also help with the understanding of the needs.

I have seen stock traders with little or no programming education do their own small applications. Technically crap, but help them do better decisions when they move the $ billions. The main systems was running on AS/400 and S/390. The traders used that plus what they made for them self on their own PC.

Re:Waiting for a capable PostgreSQL front-end

[MichaelSmith]

low-complexity database [...] embedded in an application then sqlite wins with flying colors.

Yes, I have been using it on my openmoko applications and I agree. Its a good database.

Re:Waiting for a capable PostgreSQL front-end

[TheRaven64]

I don't think SQLite belongs in that list. For most tasks I'd pick either SQLite or PostgreSQL, but I can't think of many applications where I would consider both. They are very different projects. The only time I have considered using both is when I wanted to have a large concurrently-usable data set stored in PostgreSQL and then a small single-user subset stored in SQLite on a handheld device. SQLite gives really great performance for single-user applications, but it lacks a lot of the more advanced parts of the SQL spec, doesn't really do concurrency well, and doesn't provide any kind of RDBMS, and is quite sloppy about data types. PostgreSQL provides a good implementation of the SQL spec and a lot of optimisations for massively multi-user setups.

There are cases when PostgreSQL is too heavyweight to be the correct solution, and SQLite is good for these. There are cases when SQLite is too simple, and PostgreSQL is good for these. There are no situations where MySQL is the right tool for the job. There are probably situations where you need something beyond what PostgreSQL can provide, and Oracle or DB2 would fit there, but I've not encountered any so I've not had the excuse to play with either.

Re:Waiting for a capable PostgreSQL front-end

[TheRaven64]

You're missing the point. Access can use its own crappy DB engine, or it can use MS SQL, which is a vaguely reasonable database. PostgreSQL can replace MS SQL as the back end, is cheaper, better, and open source. The question was whether there is an analogous project that can replace Access as the front end and is cheaper, better, and open source. It doesn't have to be part of, or even affiliated with, PostgreSQL, the original poster just wants to know if it exists.

Re:Waiting for a capable PostgreSQL front-end

[PhrstBrn]

I'm not missing the point. There was no point being made by the OP.

OP never asked for something "cheaper, better", just an open source alternative (or in his words, a replacement). Somebody above me replied to OP about OO.org Base, OP ignored him and continued to rant about how there are no alternatives.

Re:Waiting for a capable PostgreSQL front-end

[LizardKing]

My Access DB cost the four projects £3,000. Don't be so quick to dismiss it.

I assume you're just including license costs - otherwise, if it includes the cost of your time I can only assume you work cheap. If this is the license cost, then a real database like PostgreSQL would have cost you nothing. As for Excel and Word reporting, last time I checked Excel still imported CSV files (that PostgreSQL can generate automatically) and likewise for Word and HTML.

Re:Waiting for a capable PostgreSQL front-end

[MichaelSmith]

I really just intended to remark on the attitude I see around database stories. Its worse than operating system prudes. You get the big database people sneering down at the mysql people and ignoring the fact that different applications require different tools.

Re:Waiting for a capable PostgreSQL front-end

[fredrated]

Works for me. We have been using Access as a front-end to postgreSQL for about 10 years, through ODBC, and except for one strange circumstance which we ended up programming around, the relationship has been excellent and productive. The DB has never failed, even in the face of the occasional flame-out by Access. Just be sure you keep the Access part well backed-up for when it burns down periodically.

Re:Waiting for a capable PostgreSQL front-end

[TheNinjaroach]

Well, Access may be too easy but that's not to say some people aren't great at it. We used to own a company in New Jersey that didn't have their own developers. My boss flew out there and hired an Access guy who really knew his stuff. You can argue that it wasn't "real" code (but it was definitely solid) and the data wasn't saved in Access, it was nothing more than a front end to an MSSQL store.

Re:Waiting for a capable PostgreSQL front-end

[Decibel]

Actually, I don't know of anyone in the Postgres community that looks down on SQLite. And most people don't look down on Access either, at least as a front-end.

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

Don't think so. Access allows people to both see how it could be done and simultaneously do it. By comparison, Business will spec out and contract for a project only when it has to, not on whim.

Re:Waiting for a capable PostgreSQL front-end

[tehcyder]

Access is too easy. It lets people who have no idea what they are doing make a half working solution that then has to be replaced with real code and a real DB.

A hammer and nail are too easy for hanging a picture on a wall. They let people who have no idea what they are doing make a half working solution that then has to be replaced with a new wall, if not an entirely new house, by a team of qualified architects and builders.

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

here's another: http://younicycle.com.

Hasn't switched over to PG 9 yet, but will soon.

Re:Waiting for a capable PostgreSQL front-end

[Peter Mork]

Wow! I didn't realize I was in such rare (elite?) company. I love starting development in Access (because of the intuitive interface), moving the backend to a more powerful database system as needed. The ease with which one can perform cross-database queries using Access is particularly welcome.

Re:Waiting for a capable PostgreSQL front-end

[fusiongyro]

This is because MySQL and PostgreSQL are in the same class. If you need a small, embeddable database, SQLite fits the bill better than MySQL or PostgreSQL. If you need a multi-user database and are willing to run a server, there's no technical reason to choose MySQL over PostgreSQL. And indeed, the only reasons I see MySQL being chosen are:

• It's the only thing the developer knows, or
• The business people want the software to run on the lowest-end web hosts, or
• Some third-party depends on it (WordPress), or
• Lack of information about MySQL or its alternatives

I see a lot less misinformation about PostgreSQL these days, just a lot of ignorance of its existence and capabilities.

Database prudes are the way we are because there's a lot riding on the decision, and people choosing MySQL almost invariably have neither the education nor the experiences we have had and simply don't understand the issues.

Re:Waiting for a capable PostgreSQL front-end

[orasio]

access -> sqlite -> mysql -> postgres -> oracle

I have only recently learned of a reason why someone in their right mind would migrate to Oracle from Postgres. SLAs.

The only thing that is easier with Oracle than with Postgres is getting a good support contract that also covers your ass.

If covering your ass is not needed, or is not worth the additional cost in money and time involved in using Oracle, the list should stop at PostgreSQL.

It's easier to set-up, easier to learn, easier to administer, easier clustering/HA (no need to worry about getting someone to approve the purchase of additional licenses) . It's easier to debug yourself, because it's easier to understand how it's supposed to work (hint: it's thoroughly documented).

Re:Waiting for a capable PostgreSQL front-end

[GooberToo]

This is because MySQL and PostgreSQL are in the same class.

But that's the thing. Everyone except MySQL fans insist they are not. MySQL is squarely in a class of its own. Its somewhere between Access and all the other big-boy databases; which includes PostgreSQL. This is why there is constantly so much push back from everyone else who has used grown up databases and had a need for their features.

I'm not advocating PostgreSQL is a database for all datasets and use cases. It absolutely is not. Just the same, MySQL isn't even close to being in the same class with PostgreSQL or any of the other big-boy databases.

MySQL is the big block Gremlin which is constantly interjected into conversations about Ferraris, Porches, and Lamborghinis. Sure a Gremlin can be really fast, but no matter what you do, its still not even close to being in the same class.

There - my car analogy quota for the day is complete.

Re:Waiting for a capable PostgreSQL front-end

[orasio]

Though I think there are probably a grand total of 3 people on Earth who have used MS Access in any serious capacity and don't loathe it.

I am one of those.
I used Access as a frontend to a huge Oracle database (social security data for a small country) back in 1997.
I loved it. I could make reports for management after-hours, when there were no IT people around, before I knew enough SQL to write the queries myself.
The query specification language was great, programming forms was really easy, professional reports were pretty easy, and looked great.
I believe MS had the opportunity to compete as with Oracle Forms at the time for rich clients, had they wanted to.

Of course, 13 years later, it's not as impressive, but there is a certain type of user that benefits a lot from it.

Re:Waiting for a capable PostgreSQL front-end

[WuphonsReach]

(sigh) No, I looked at OOBase as well (last look was OO.org 3.1).

Frankly, they're still 5+ years behind MSAccess - or rather, they don't understand the things that MSAccess is good at.

There are a lot of situations where the heft and bulk of putting a short-lived data set in pgsql or Oracle is a bad idea. Situations where the data doesn't need to stay on a live server after a few short weeks, but needs to be kept in a format where you can easily query or produce reports months or years later - without having to reload the data into some central DB server. I can stuff that binary blob into a version control system or other file sharing system and be assured that the person on the other end can open it up and run quick ad-hoc queries against the data set.

OOBase does not yet cope well with that. Or rather, it comes closest here.

OOBase also sucks as a central meeting point for your data. If you need to pull data from another OOBase file while simultaneously pulling data down from a DB server and then do a bit of local table massaging to get output, OOBase fails because OOBase can only have a single data source. Unless you decide to make all of your OOBase files global data sources. I've seen Access used for almost 2 decades now as a way to move data from point A to point B, with a bit of massaging along the way.

Lastly... in 2010 - OOBase still cannot easily import/export from CSV - without going through OOCalc. Hell, this applies to just about all the braindead and missing import/export functionality in OOBase. A database tool that can't help me get data in/out of the system is useless and a toy.

I'd love to replace MSAccess - it would let us get off of Microsoft Windows. But there's nothing in the open-source world that fits the niche. And we're not even using things like macros, forms or code (or very little) as we move stuff like that up to an application/web server.

Re:Waiting for a capable PostgreSQL front-end

[turing_m]

OOBase does not yet cope well with that. Or rather, it comes closest here.

That was my point. It's worth a look, it may suit your needs, it may not. Obviously not, in your case. But I did find it closer to Access than anything else I could find, and easier to get working. YMMV.

Re:Waiting for a capable PostgreSQL front-end

[turing_m]

You've got a point - for many things it is good to make things easier on the front end. But if you ignore constraints on the back end, remember that databases are often long-lived, and someone else may/will code up a front end for it. And years later when someone has to try and make sense of the gobbledygook that has entered the database, the curses will reverberate throughout the land.

Re:Waiting for a capable PostgreSQL front-end

[EsbenMoseHansen]

DB2, the only database I've encountered so far that is more inconsistent than Mysql. (Note that I have never used access)

Re:Waiting for a capable PostgreSQL front-end

[badkarmadayaccount]

Set it up as system service.

Re:Waiting for a capable PostgreSQL front-end

[dbIII]

It took Win7 and many annoying security popups when attempting to use the software that finally convinced the "developer" to put his attempt at a lock file somewhere else.

Re:Waiting for a capable PostgreSQL front-end

[einhverfr]

Yes first, congratulations to those folks. I am still waiting for a front-end to PostgreSQL that is as functional and easy to program as Microsoft's Access.

Microsoft Access works as a frontend to PostgreSQL.....

Re:Waiting for a capable PostgreSQL front-end

[Anonymous Coward]

access -> sqlite -> mysql -> postgres -> oracle

Everybody looks down on the tools to the left.

Those people have clearly not used Oracle for an

Meh

[Anonymous Coward]

When postgres strictly checks data before inserting it into the database maybe I will give a try. Until then, I will stick with mysql which has been had this compliance since what, 1999?

Re:Meh

[caerwyn]

I think you've got your databases backward when it comes to integrity and verification...

Re:Meh

[Anonymous Coward]

Heh, what are you talking about? Postgres didn't even support foreign keys until version 5 and then even then it wasn't acid compliant. Mysql had all these things (pretty much on par with oracle) years before postgres.

Re:Meh

[mysidia]

Perhaps, but PostgreSQL had the single most important extension AUTO_INCREMENT as a column type which easily trumped all of MySQL's advantages. While MySQL just had this clunky 'sequences' concept.

Re:Meh

[GooberToo]

LOL! That's either one of the funniest or most ignorant things stated on /. in a while.

MySQL has a long, long reputation for poor ACID conformance. PostgreSQL, on the other hand, has a long and well respected reputation for both ACID conformance and a variety of lock/update methods which allow for varying degrees of integrity.

Re:Meh

[Daniel_Staal]

Um, yeah. MySQL, out of the box, using the defaults, doesn't support foreign keys now. You have to specifically create the tables with a non-standard SQL code to get them to use the right database backend to get foreign key support.

Unless you mean by 'support' 'Will silently accept and throw away'...

Foreign keys have been enabled and working by default in Postgres since version 7. (There was no version 5...) That was released just over ten years ago at this point.

Re:Meh

[TheSunborn]

It's called Serial instead of auto_commit but the semantic is the same and used with

MyPrimaryKey serial not null primary key, ...

Re:Meh

[mysidia]

auto_commit? What's that? No such thing in ISAM, the model is [was] called atomic operations.. also known as LOCK TABLES :)

Re:Meh

[JohnnyBigodes]

You have to specifically create the tables with a non-standard SQL code to get them to use the right database backend to get foreign key support.

The what to the who, now? Dude, if you're using MySQL and you have issues because you can't get past the default storage engine, I can't wait to see what happens when you have to do actual work.

Re:Meh

[Anonymous Coward]

No, I'm fairly sure you have that backwards.

PostgreSQL 6 was the first version. PostgreSQL 7 (which added foreign key support, apparently) seems to have been released around 1999 / 2000. It's been enabled by default ever since. Same goes for ACID - the PostgreSQL 7 line had proper transactions support, and I believe it may have had that from the first version (~1996).

InnoDB didn't show up in MySQL until late 2001. The default table type in MySQL is still MyISAM, which doesn't support foreign key constraints, and isn't ACID. InnoDB is... barely. It still acts as a dumb data store though - you can't really enforce any useful constraints on the data.

Re:Meh

[h4rm0ny]

The what to the who, now? Dude, if you're using MySQL and you have issues because you can't get past the default storage engine, I can't wait to see what happens when you have to do actual work.

You can't imagine my bemusement with MySQL when I discovered that the tables I had explicitly instructed it to create as InnoDB had silently been converted to MyISAM because InnoDB wasn't enabled on that MySQL server. One of those little quirks of MySQL that make it so charming. :)

Anyway, GP is indulging in a bit of Anus Loquacious. He's saying that "Postgres didn't even support foreign keys until version 5" as if that's a comparison to MySQL version 5. PostgreSQL didn't even have a version 5. It began as a re-write of a previous academic system and begins at version 6.0 and that was released in 1996, fourteen years ago! I'm not sure exactly when foreign keys became supported, but they were in by 7.0 which was released what, in 2000? In any case, that was over ten years ago. So even if you want to compare which database got to a particular function first (which is pretty pointless), Postgres has had it for ten years. MySQL is technically not far behind as InnoDB was first distributed with MySQL about a year later in 2001. But it was far, far from mature back then. Anyway, to summarise, the GP is (a) wrong about (b) an irrelevant argument.

Firebird is better

[Anonymous Coward]

Firebird is a better Postgres than Postgres. It has similar or more advanced features and is hella faster and cleaner.

The fight is really Firebird versus MySQL, PostgreSQL isn't even in the game.

Re:Firebird is better

[h4rr4r]

Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.

Re:Firebird is better

[Anonymous Coward]

Firebird is substantially less capable than Postgres *or* MySQL, and is full of ridiculous arbitrary limits (i.e. per-table cap on *combined* column lengths in the 2KiB range? Really?)

If "anyone suggesting MySql for real work should just be laughed at", then anyone suggesting Firebird for real work should be deported (from Earth).

Captcha: sucked

Re:Firebird is better

[Mitchell314]

Given the numbers, you better start laughing at much of the world. (note: not that I'm taking sides - I'm too stupid enough to figure out how to install postgreSQL the right way - though I'd prefer it over MySQL from what little I know)

Re:Firebird is better

[Anonymous Coward]

If you're running Windows, there is a one-click installer available.
If you're on Linux, fetch a build from your repos (although it will be old). Set up the DB directory, and modify the conf file for access, and you're good to go. Check out pgAdmin if you like GUIs.
In any case, the only bit you need to read to get started is here: http://www.postgresql.org/docs/9.0/static/runtime.html

If you're used to MySQL and the "show databases" command etc., these don't exist on Postgres. Commands available via the command line tool psql are the best way to do it.
Also, if you're used to MS SQL or MySQL dumping all of their DBs in one or two files, this is different on Postgres. It spreads the data into lots of little files. Just thought I'd warn you! But it is easy to get going, thanks to the superb documentation.

Re:Firebird is better

[h4rr4r]

From the packages ought to be easy enough for anyone. Rpms or debs available to anyone who knows how to use a web browser.

Re:Firebird is better

[Anonymous Coward]

MySQL can be used for many things. Dismissing it as something that cannot be used for "Real Work" is ridiculous and a stupid statement. That is not too say there are things it should not be used for. But dismissing it out of hand is rather stupid. I think it does very well providing me with Wikipedia articles.

Re:Firebird is better

[neoform]

"Real Work"? What's that? MySQL was for a very long time the DB used by adsense and youtube...

How many projects use MySQL and how many use PostgreSQL?

PostgreSQL might be a good db, but that doesn't make MySQL a piece of shit...

Re:Firebird is better

[fnj]

-1000, out in left field. Cuckoo. Woo-oo-oo. Twilight zone. Needs brain scan.

Re:Firebird is better

[JohnnyBigodes]

2000 has called, they want their knee-jerk mantras back: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

Re:Firebird is better

[JohnnyBigodes]

Dude, this is Slashdot. For many of the "old timers" here (and a good portion of the new-timers), PHP is still a toy language, MySQL doesn't even have transactions, and Windows 95 is horrible. For the rest of the world, times have changed.

Re:Firebird is better

[schmiddy]

"Real Work"? What's that? MySQL was for a very long time the DB used by adsense and youtube...

Uh-huh. That's because if Youtube loses a video of a cat with 3 views, who gives a flying fuck? Ask your bank or your credit card company what database they use to store their financial transactions. Probably won't be PostgreSQL, but it sure as hell won't be MySQL. And they're only using Oracle because $10M to them is chump change.

Re:Firebird is better

[TooMuchToDo]

The AdWords system was initially implemented on top of the MySQL database engine. After the system had been launched, management decided to use a commercial database (Oracle) instead. The system became much slower, so eventually it was returned to MySQL [3]. The interface has also been revamped to offer better work flow with additional new features, such as Spreadsheet Editing, Search Query Reports, and better conversion metrics.

http://en.wikipedia.org/wiki/AdWords

Just sayin'.

Re:Firebird is better

[neoform]

Nice, so basically, only banks and financial institutions do 'real work' then? Strange world you live in...

I work for a $100M company that generates all its income from a website I maintain, and it's running on PHP and MySQL..

MySQL might not be a flawless DB, but it gets the job done just fine.

Re:Firebird is better

[jwpye]

How often do you spend time dealing with a mysql blowup that's not HW related? Also what kind of data are we talking about here? (size, xacts/sec, other pertinent info. tho, no need for a full profile of your rig or anything) I don't use MySQL myself, but that's because the screams of my friends keep me from venturing into those woods.

Re:Firebird is better

[theshowmecanuck]

He is saying that the data integrity of Oracle and DB2 type RDBMS engines is far superior to MySQL. I think you will find many IT people who believe this in billion dollar companies who are concerned about maintaining database integrity. And so is the integrity of Postgres's engine. I think where Postgres falls down is with 'high availability'. It now has its new hot standby feature, but this new feature still doesn't support reasonable failover functionality. And in fact, after watching an EnterpriseDB webinar about the new hot standby feature, my impression is that it is still rather kludgy. I think a few more iterations and improvements are necessary, and they might have to change the way the transaction logs are captured/stored in order to make it really useful. I could be wrong, it happens, but it doesn't pass my own sniff test with respect to 'ready for prime time'.

Re:Firebird is better

[Reality Master 101]

We used to be on on PostgreSQL. We switched to MySQL about 2 years ago, and never looked back. PostgreSQL had a few features that were nice, but MySQL was SO much faster and easier to deal with. And best of all, it's in the mainstream, so there are oodles of resources out there for it. And yes, we use full transaction processing.

I suspect your knowledge of what MySQL can and can't do is out of date.

Re:Firebird is better

[Anonymous Coward]

"How many projects use MySQL and how many use PostgreSQL?"

This argument actually doesn't say much of the quality of either. Using the same reasoning one could say that shit must taste really good since hundreds of billions of flies like it.

Other factors affect how much something is used, not quality alone. Quality might not even be an important factor! Unfortunately.

I am not saying that MySQL is a piece of shit, just that this kind of argument isn't always valid.

Re:Firebird is better

[GooberToo]

Its extremely ironic that you changed just as PostgreSQL become considerably faster than MySQL. PostgreSQL has always been far more scalable. To now hear you brag that you've never looked back at a superior and faster database because of your steadfast and likely false belief that MySQL is faster, is rather amusing.

One of the biggest problems with the MySQL user base is that they don't have any idea what "faster" means nor do they typically understand how to benchmark. Made worse, they constantly confuse speed with scalability. And made ever worse, most MySQL users take the MySQL benchmarks to heart when time and time again they are nothing but marketing lies. Most independent tests have historically had lots of problems even getting MySQL to stay running until the end. And when it actually does finish, its normally somewhere between the middle of the pack to dead last - and that's with all the other databases forced to use the lowest common feature which prevents them from using their advanced, much, much faster features.

The bottom line is, MySQL is popular because it has buzzword compliance for people who almost always don't know any better; but most of all, was readily available on Windows at a time when everyone was looking for a free database to go to. PostgreSQL is popular because it has both buzzword compliance, is far more feature rich, almost always out performs MySQL, and underscores, not to mention truly understands, what ACID is all about - while providing a very rich set of features which MySQL is unlikely to ever match. And that's ignoring that MySQL's optimizer absolutely stinks for anything but the most simple of queries.

The best rule of thumb is, think of MySQL as a really fast Access database. If you wouldn't use Access, ignoring database performance in the comparison, you should think really hard about using MySQL. There are so many superior and still free RDBMs compared to MySQL, its easy to see why so many get so frustrated when others insist on injecting an dramatically inferior solution into the equation, just because it has buzzwords.

Re:Firebird is better

[gullevek]

Thought there is a dump all for postgresql too.

Re:Firebird is better

[jimicus]

Lots of projects use MySQL by default, though that's probably more for historical reasons these days. (Postgres used to be way behind in the performance stakes, which means if you're offering hosting and you wanted to offer similar performance to MySQL for your customers you had to purchase a hell of a lot more hardware).

The thing is, it's not uncommon with MySQL to find that in order to get everything working just so, you have to fight the damn thing. Foreign keys weren't properly supported for years, and even today you have to explicitly use non-standard SQL syntax or the engine silently throws them away.

Once you've used Postgres for any length of time, it's clear that the entire application is designed with one thing foremost: Data integrity. You really have to work to store your data in such a fashion as to lose ACID compliance - I honestly can't remember the last time I saw a Postgres system seriously messed up, and you don't generally get sections of the Postgres manual for the stable version prefaced with warnings like "Look out, while you can do this we haven't really tested it and there may still be bugs that result in serious data loss".

It takes a great deal of determination and will-power on top of blind ignorance to mess up Postgres, whereas with MySQL (even today) it's really rather easy to mess up simply because you didn't turn over the page in the Junior Colour Encyclopaedia of Databases and read the paragraph that says "Here be dragons!".

MySQL fans will say "What are you doing setting up a live database without having at least some familiarity with the manual?". Postgres fans will say "What is the database doing making it so easy to balls it up so royally?".

Re:Firebird is better

[h4rm0ny]

Please tell me you're kidding. Anyone suggesting MySql for real work should just be laughed at.

I haven't seen a car analogy yet, so I'm going to be the bastard and first use one. MySQL is like a small engine car. Postgres is a bigger 4x4. If you're driving through town in a 30mph zone, they're equivalent. Once you start going up a steep hill, you see the difference. It's pretty obvious which DB is which car in this analogy. The hill represents either a need for advanced functionality, serious robustness or, in some scenarios more complex than just simple queries, scalability. The thing of it is, that there are a lot of scenarios out there that are just "driving around town in a 30mph zone". I fully agree that Postgres is superior in a number of ways. MySQL is marginally simpler to develop for at an low-professional level. But MySQL is adequate to a lot of people's needs. It doesn't put Postgres down to acknowledge that.

What really confuses me in the GP's post is introducing Firebird to the equation. That's definitely a comment that requires supporting.

Re:Firebird is better

[arndawg]

It is not possible that a video of a cat has only 3 views. Learn the laws of the internet.

Re:Firebird is better

[Anonymous Coward]

Oh look, here's someone who'd like to tell us about his very own definition of "real work".

Lets hear it.

Re:Firebird is better

[Anonymous Coward]

PostgreSQL might be a good db, but that doesn't make MySQL a piece of shit...

Correct. MySQL was a piece of shit completely independently of PostgreSQL.

Re:Firebird is better

[Anonymous Coward]

"Real Work"? What's that? MySQL was for a very long time the DB used by adsense and youtube...

How many projects use MySQL and how many use PostgreSQL?

PostgreSQL might be a good db, but that doesn't make MySQL a piece of shit...

Nor does PostgreSQL being a good db preclude MySQL from being a piece of shit...

Re:Firebird is better

[Reality Master 101]

To now hear you brag that you've never looked back at a superior and faster database because of your steadfast and likely false belief that MySQL is faster, is rather amusing.

You clearly have little experience with MySQL. Look, I understand your smugness and arrogance. I used to feel the same way, based on what I "knew" about MySQL, which is pretty much based on its early reputation.

When I'm talking about speed, I'm talking about real world performance on a REAL application -- our own. It was ridiculously faster. And when we considered switching, I was pleasantly surprised by how much MySQL had grown up into a full-featured database.

In short, these are all excellent products these days that can all do the job. I'm not knocking PostgreSQL. I'm sure it has improved, just like they've all likely improved. But at the time, we had some good reasons for switching. It's fine to prefer PostgreSQL, but your snobbery regarding MySQL is simply out of date.

Re:Firebird is better

[Reality Master 101]

How often do you spend time dealing with a mysql blowup that's not HW related?

In the couple of years we've been running MySQL (after converting from PostgreSQL), 10s of millions of rows, 21 gigabytes of data, we've never had any hiccups with MySQL of any kind. It Just Works. And I've never heard of anyone else having a non-hardware-related problem.

Re:Firebird is better

[Lennie]

AFAIK, some examples:

Skype and PIR (they handle the .org top level domain ) use PostgreSQL

Yahoo and Youtube use MySQL

Re:Firebird is better

[Lennie]

Wikipedia is also an obvious MySQL user.

Re:Firebird is better

[GooberToo]

You clearly have little experience with MySQL. Look, I understand your smugness and arrogance.

Whoosh!!

This isn't smugness or arrogance. Seriously, think about it. If you heard someone bragging about using x because its faster than y, despite y now being much better than x almost immediately after the selection x, you would enjoy the humor. That's what this boils down to.

As you then went on to boast you've never looked back, additional information was provided so you could realize that you really should be looking back to at least re-evaluate if your braggart position is still viable.

If you don't want to look back, that's fine, but don't get upset when your position puts a smile on other's faces.

Re:Firebird is better

[Reality Master 101]

If you heard someone bragging about using x because its faster than y, despite y now being much better than x almost immediately after the selection x, you would enjoy the humor.

IF that's true. I highly doubt you have exhaustive metrics to prove it. And even if it is true, performance was only one reason we switched over. It was the right decision for us.

My broader point was not to knock PostgreSQL, only to make the point that MySQL does not deserve the reputation it has in certain quarters, particularly among PostgreSQL advocates.

Re:Firebird is better

[jwpye]

Do you use mysql's built-in replication(master-master)? Out of curiosity, what prompted the switch to MySQL?

Re:Firebird is better

[Anonymous Coward]

You're right, Postgres being a good DBMS doesn't make MySQL a piece of shit. What makes MySQL a piece of shit is the lack of full-text search in InnoDB, the fact that it ignores many DDL clauses it doesn't know instead of complaining about them and tons of other similar things I've forgotten about.

Re:Firebird is better

[Anonymous Coward]

Yep, pg_dump.

Re:Firebird is better

[GooberToo]

It was the right decision for us.

It may have been. But since you've never looked back, it may not, and is likely, no longer true. If its good enough, its good enough, but bragging doesn't normally center around the notion of "good enough."

My broader point was not to knock PostgreSQL, only to make the point that MySQL does not deserve the reputation it has in certain quarters, particularly among PostgreSQL advocates.

Hate to tell you, but it has a universally poor reputation amongst the majority of high end RDBMS DBAs. In other words, the same group which typically has high regard for most of its competition, including MS SQL Server, frequently has a very low regard for MySQL. This alone should be reason enough to wonder why that might possibly be.

Re:Firebird is better

[Reality Master 101]

But since you've never looked back, it may not, and is likely, no longer true.

It may have improved. But who cares? Why would I go back when what I have works well? I generally don't rip out entire database infrastructures unless I have a reason to do it.

This alone should be reason enough to wonder why that might possibly be.

I know the reason(s). It's one of: a) Have never used it, b) Have a vague memory that it doesn't support transactions (which has been wrong for a long time), c) It doesn't support (Feature X) that has nothing to do with reliability.

Google AdWords uses MySQL. If it's good enough for a huge volume, critical task like that, it's good enough for you and anyone else.

The only reason people dislike MySQL is simple ignorance.

As always...

[jd]

The new features are much admired by all (and deservedly so), but a heavier footprint typically means poorer performance overall even if there's accelerated performance in specific areas or improved programming. I'd like to see a performance plot, showing version versus performance versus different types of system load, in order to see how well new stuff is being added in. It might be merged in great and the underlying architecture may be superb, but I would like to see actual data on this.

Also, PostgreSQL and MySQL aren't the only Open Source SQL databases. Including variants and forks, you really need to also consider Ingres, Drizzle, MariaDB, SAP MaxDB, FireBird and SQLite. If you want to also compare against Closed Source DBs, then you'd obviously want to look at DB/2, Oracle, Cache and Sybase. I'd love to see a full comparison between all of these, feature-for-feature, with no bias for or against any specific development model or database model, but rather an honest appraisal of how each database performs at specific tasks.

I like PostgreSQL a lot. I rate it extremely highly. However, without an objective analysis, all I have is my subjective perception. And subjective perceptions are not something I could credibly use in a workplace to encourage a switch. For that matter, subjective perceptions are not something I would consider acceptable for even telling a friend what to use. Perceptions are simply not credible and have no value in the real world.

Re:As always...

[Anonymous Coward]

So, what is keeping you?

Re:As always...

[jwpye]

I think there has been some effort to bring a PostgreSQL "performance farm" online to show the differences in performance across versions of PostgreSQL, and to quickly identify regressions during development. I don't think it's up yet, but a search should reveal some details on the project.

Re:As always...

[GoChickenFat]

Why do you like PostgreSQL so much and why did you leave MS SQL out of your closed source list? Subjective perception?

Re:As always...

[Anonymous Coward]

MS SQL is actually pretty much the same as Sybase. Interestingly, they both rely on the TDS protocol for getting data from the server. No matter how much people dress up the ways of getting data from servers, you basically only need to be able to:
1. Send some SQL
2. Get a result code and row count
3. Iterate through the results if > 0
The efforts people go through to map variables to ODBC fields in C programs etc. is phenomenal, and long winded.

Just thought I'd say.

Re:As always...

[jadavis]

I'd love to see a full comparison between all of these, feature-for-feature, with no bias for or against any specific development model or database model, but rather an honest appraisal of how each database performs at specific tasks.

I intend this comment with sincerity: everyone would like that. But it's not very realistic, because there are so many variables in play. Even when you try to pick one aspect, like performance, it explodes into all different angles very quickly, and you can't really do an apples-to-apples comparison.

You can try to pick extremely simplistic measures, like how many simple INSERTs per second can be executed on a given machine, but that's really not representative of most real workloads.

The only thing you can really do is pick a few systems that appear to be of high quality, and understand them as best you can. Then, you will at least know what to expect in different situations.

However, version to version comparisons of the same system are a good idea -- still not easy, but it's more realistic to get apples-to-apples comparisons between versions. I think someone is working on it.

Re:As always...

[Anonymous Coward]

Please make that performance plot you are thinking about, and collect that actual data you are referring to, and write up that full comparison. I look forward to reading that very much.......... :)

Re:As always...

[TheFuzzy]

JD,

You're absolutely correct that such a comparison would be a real asset to users. However, it would also be a Herculean task. Several people have tried to do similar things, but the number of indexes you need to compare (features, reliability, performance, etc.) is too large. And some things are so different it's hard to compare them meaning fully. Imagine trying to do a head-to-head comparison of all OSes in every way.

Here's a few comparison links, but they just scratch the surface:
http://troels.arvin.dk/db/rdbms/
http://en.wikipedia.org/wiki/Comparison_of_SQL_database_management_systems

Re:As always...

[jd]

A broken laptop and two dead drives on my desktop. I'm a decent coder and can work my way round old-style rats-nest electronics with a soldering iron and a multimeter, but I'm not so good on rebuilding crashed drives or a cracked motherboard. Meh. I'll get them replaced sometime. If nobody has done the comparison by then, maybe I'll do one. But frankly, you're better off with an expert DBA designing such a test system, not a coder. A DBA =knows= what to look for and what to stress. That is their job and their training. A coder merely has an API. I've done enough DB design to do a decent job on developing databases, but I don't pretend to have the skills necessary to work the kind of magic that really exposes what a DB can and cannot do. That kind of wizardry is expressly in the domain of the creme-a-la-creme of DBAs.

Whilst I appreciate you thinking I should be in said category, the fact is that there are very very few such people. We are talking serious Database Black Magic, not just on one engine but a whole set of them. There's probably more people living in the same block as you than there are DBAs who have that level of expertise. Sure, there'll be plenty who are good on ONE engine (though not necessarily in enough aspects and enough case uses to really understand the complete range of loads), but to be sufficiently skilled across multiple styles of database over all manners of system load? That's a bit rarer. You no more want a software engineer attempting that than you want a database engineer working on a FIPS-180 crypto library in assembly. Whole 'nother animal.

Re:As always...

[jd]

I fully agree it would be Herculean, which is why it would be good if we could find a Hercules to assign the task to. :)

In practice, you're right, there are some thing that are too different to compare readily. How do you compare an OO database with a Relational Database? For that matter, how do you compare a Star Database with a Relational Database? Even if they used an identical command language, the beasts are very very different. To an extent, that is a good thing - it means you can pick a database that's good for the problem, rather than forcing the problem to suit the database. In practice, precisely because they are so different, there's very limited metrics to make such a choice.

This has generally led to some assumptions. It is assumed relational databases is the most flexible but the slowest of the designs. There's next to no real proof of this, for the very reason you've noted, which means people are left with what amounts to folklore. It might be right, it might even be right for the right reasons, but it's simply not possible to know if this is the case right now.

And that leads back to the frustration that I feel. I am a software engineer with a strong science background. To be told that I have to accept folklore as a source of database knowledge - that is just so very very wrong. I can't accept that that's the best that can be done, even if I lack the skills to do better.

Re:As always...

[Anonymous Coward]

MS SQL is actually pretty much the same as Sybase.

Circa SQL 2000 maybe.

That was the last version where MS and Sybase cooperated with that agreement.

Re:As always...

[turbidostato]

"You're absolutely correct that such a comparison would be a real asset to users. However, it would also be a Herculean task."

I don't think so. I think that it even would be quite easy and cheap because, for the most part, it's already done!

I think that it's not done exactly because what you stated: it would be a real asset to users. RDBM vendors don't want that because RDBM choice is greatly based on gut feelings, which are much better handled by marketing than hard data.

Think of it: don't you think that, specially the big vendors like Microsoft, IBM or Oracle, don't already have their own internal benchmarking teams? So, from a naive point of view all that would rest is for them to public their results.

Given that the hard part is already done (knowing what to measure and having the tools for in fact measure it) releasing it to the public should be quite easy from the technical standpoint: you just open a competition and allow for each team to bring up their own technicians (so they can't say, "yeah, but it was not properly tunned") to test under loads defined from a trusted authority upon comitee from the vendors themselves.

That's how, say, all motorsports competitions work and it does work.

Re:As always...

[turbidostato]

"How do you compare an OO database with a Relational Database? For that matter, how do you compare a Star Database with a Relational Database? [...] To an extent, that is a good thing - it means you can pick a database that's good for the problem"

Easy: you throw a typical problem from each class and then test all the engines against all of the problems. The fact that a relationally-oriented engine will do worse at an OO problem than an OO-oriented one doesn't preclude the test from being made anyways.

"I am a software engineer with a strong science background. To be told that I have to accept folklore as a source of database knowledge - that is just so very very wrong."

That's my point: this is the case not because is too dificult to produce proper benchmarks but because database vendors decided long time ago that they don't want you, the technician, to choose the database but the PHBs, which are much more open to marketing tactics than you (current license price tags for best renowned database engines are the success hallmark of their strategy).

Re:As always...

[afidel]

Actually SQL 4.21 was the last codeveloped version, version 6.0+ were all MS run, and by SQL 2000 most of the Sybase code was gone.

Re:As always...

[afidel]

TPC-C and TPC-H are a fairly good vendor neutral way to measure DB performance for OLTP and BI type workloads though there are plenty of games played (read the detailed reports!).

Re:As always...

[greg1104]

You've got the performance part backwards for PostgreSQL; it goes up with every release, sometimes a little, sometimes in a big way. See PostgreSQL history for a comparison covering versions 8.0 to 8.4. The mild regression in 8.4 shown there is actually reversible; it's mainly because a query related parameter for how many statistics to collect and use for query planning was increased by default. That results in better plans for most real-world queries, but it detuned this trivial benchmark a little bit. You can get performance back to 8.3 levels just by turning the parameter back to the "optimized for trivial queries" default of the older versions if you care about that. Most people prefer the new default. In the real world, 8.4 is actually faster due to improved handling of background VACUUM tasks too, which don't show up in simple benchmarks either.

I'm the current lead architect on building a PostgreSQL Performance Farm to prevent regressions from popping into future versions of the code too. There is a recently completed beta client for that purpose. We're in the process of working out how to integrate into future development, starting with 9.1, so that potential regressions are spotted on a commit by commit basis. I haven't seen any performance regressions between 8.4 and 9.0, only moderate improvements overall and large ones in specific areas that were accelerated.

Now, if you use some of the new replication features aggressively, that can add some overhead to slow down the master. But that's true of most solution; the data coming off the master has to take up some time to generate. The way PostgreSQL 9.0 does it is is pretty low overhead, it just ships the changed blocks around. Theoretically some statement based solutions might have lower overhead, but they usually come with concerns about non-determinism on the slaves when replayed (random numbers, timestamps, and sequence numbers are common examples).

Given the non-disclosure terms of most of the closed source databases, nobody can publish benchmarks that include them without going through something like the TPC or SPEC process. The last time that was done in 2007, PostgreSQL 8.2 was about 15% slower than Oracle running the same database-heavy workload. And note that it was PostgreSQL 8.3 that had one of the larger performance increases, so that was from just before a large leap forward in PostgreSQL performance.

At this point, Oracle and most other commercial databases still have a large lead on some of the queries run in the heavier TPC-H benchmarks. Links to more details as to why are on the PostgreSQL wiki. It just hasn't been a priority for development to accelerate all of the types of queries required to do well in that benchmark, and nobody so far has been willing to fund that or the subsequent certification via the TPC yet. Sun was the only one throwing money in that direction, and obviously the parts of that left within Oracle will no longer do so.

Re:As always...

[nixNscratches]

In the real world, your subjective perception is often all you will EVER have to go on. Make as reasonably informed a decision as you can, and live with those consequences. A lot of so called objective studies especially in technology are quite biased. A DBA whose primary focus is Oracle would end up with completely different results than one who works primarily with DB/2 or PostgreSQL or MySQL. That's because each of these databases has different strengths, weaknesses, idiosyncracies, and yes, SQL idioms that make them perform better when done a certain way. The parsers are different, the optimizers are different, the table structures are different, why are you expecting an apples to apples comparison on features?

Re:As always...

[jadavis]

OO problem

In the real world, there are real problems. There's no such thing as an OO problem unless it's a problem created by OO.

Easy: you throw a typical problem from each class and then test all the engines against all of the problems.

If it were a typical problem, hopefully you'd just be able to avoid the whole thing and pull a typical solution off the shelf (think grep).

A DBMS only matters for complex problems that come from complexities in the real world organization that the database represents. You could try to implement two solutions in two different systems, assuming that you're equally knowledgeable in both. But that's tricky, too, because you'd have to use them for long enough that you read the data, not just write it.

Re:As always...

[jadavis]

And that leads back to the frustration that I feel. I am a software engineer with a strong science background. To be told that I have to accept folklore as a source of database knowledge - that is just so very very wrong.

Any better ideas?

Decisions are complex. Any big decision is guided by objective, quantifiable things; but often decided by very subjective considerations. If it were totally objective, the decision would essentially have already been made. All you can do is learn a lot about the system, weigh the considerations, and make a judgment call. Or you can trust some people along the way to help you make a decision.

Re:As always...

[the_womble]

I think some of the proprietary databases have licences that forbid publication of unauthorised performance test results, so that part of the comparison is not possible.

In practice your choice is often going to be constrained enough that you only need to compare two or three (what does your employer/hardware/OS/hosting provider support? What has good tools? Cost? Supported by your choice of language? Risk or project company collapsing? Major required features that allow you to immediately rule stuff out?)

Its no often that you are really going to need to compare, for example, SQLite and DB2.

Re:As always...

[jd]

The "better idea" is that you associate specific types of underlying database-level task with some meaningful quantity or quantities (I propose maximum, minimum, mean and variance on the memory consumed and the time taken) and then associate the way in which tasks interact with similar quantities, and finally generate values for how the system scales (doing N tasks will not take the same time as N x one task, even on a strictly sequential system - and sequential systems haven't been used since the Dark Ages). You do this for each database engine you want to model. This isn't a perfect representation, but it's good enough to say database engine X will perform better/worse/about the same as database engine Y on doing the same set of tasks.

You then produce a cookbook, which maps database models and high-level database tasks onto the associated underlying database tasks. You can now feed this cookbook an OO database model + OO database code or an ER diagram and SQL code and get a good feel for what a specific database will do when fed that. Each cookbook would need to be specific to a given database, as the underlying engine will be different. This is where the first problem creeps in - to compare apples with apples, the tailoring can't unfairly optimize for a given underlying engine. However, to compare best with best, you've got to use what the engine provides and not unfairly bias by ignoring the engine's strengths.

(In short, this would be something analogous to ERwin or Dezign for Databases, but it would be independent not only of the underlying database but also of the database methodology used, and it would need to know about the tricks and tips for getting the best out of each system. It would actually be closer to GCC, where the language passed to the frontend is completely independent of the language delivered by the backend. The "cookbook" would then be analogous to the bit that converts the data generated by the gcc frontend into something some given backend can parse.)

You then take any number of real-world solutions that exist, "compile" them for each database in turn, then "run" them to get estimates for how each database would handle that specific solution. You could also "compile" them to actual code digestable by the database in question to see how the estimates compared against actual usage. With enough solutions within each type of problem-space, and enough problem-spaces, you can get a feel for how to adjust the estimates for real-world dynamics.

Finally, you provide a means for a user to feed in some approximation of what they imagine their solution will end up looking like. The thing about the software life-cycle is that the idea of what the solution is, or indeed what the problem is, changes - though if it's a good design it'll not change at the high-level by a huge amount. The compiler then shows how the databases it can compile to would stack up for that approximation. If the coding is good, it won't be optimized prematurely, so you can use the first-round recommendation for a test platform which should give a good feel for what you will end up with. Once you reach the point of wanting to do optimizations, if you've changed the model you re-compile it and see if it still recommends the same engine. Whether it does or it doesn't, it should be a drop-in replacement. You then optimize for this "ideal" system.

There you have it. And the odds of this ever being done? Pffft! You know how long it took developers to write GCC? Nobody has written a down-to-the-nuts-and-bolts compiler with pluggable frontends and pluggable backends for compilers. Nobody knows how to do it, which is the same state compiler writers were in when GCC first came out. What is suggested here, basically a Fifth Generation compiler ("Fifth Generation" because it has a high-level specification input and you've an expert system involved in compiling it), is possible but people have tried to write Fifth Generation compilers for regular programming for 20 years now and have got absolutely nowhere. It is a

Re:As always...

[Anonymous Coward]

Cache! Wow, you don't hear very many people mentioning that database. I remember developing in DTM and DSM prior to InterSystems buying them out. They tried to get SQL people interested in their M technology through their M/SQL layer. Later it became Cache, the post-relational database.

Re:As always...

[turbidostato]

"In the real world, there are real problems."

Take it one you deem characteristic, then, an let others criticise it till you find a common ground agreement.

"If it were a typical problem, hopefully you'd just be able to avoid the whole thing and pull a typical solution off the shelf"

You ain't too experienced, are you? There are *a lot* of typical problems. That's why you can find "best practices" which basically are 'of the shelf' conceptual solutions. There are two problems, though:
  1) While logically typical, devil is in the details so while 90% of your solution is common, it is the other 10% which makes the deal. That, of course, doesn't go in the way of allowing for metrics on the 90% "bussiness as usual" part.
  2) In order for a "typical problem" being described as such, the one in charge must be able to recognice it as such. Given IT is basically a "lemon market"*1, bad professionals (defined here as those unable to recognice a pattern a palm off their nose) is no surprise that a lot of environments are uneedly managed 'by exception' instead of being 'normalized' to a standard.

"A DBMS only matters for complex problems that come from complexities in the real world organization"

Being complex doesn't preclude being common: there a lot of common complex problems. Anyway, I already gave a useful common representation of how could it work: car competitions. It really doesn't matter what the exact problem could be as much as there can be an agreement on a given problem to be representative (as there could be quite a lot of different race rules, a matter that doesn't preclude *a* fixed set of rules, say F1, to be accepted by enough incumbents).

"You could try to implement two solutions in two different systems, assuming that you're equally knowledgeable in both. But that's tricky, too, because you'd have to use them for long enough that you read the data, not just write it."

So you are supporting my second point upwards: it is not that it can't be produced a common framework but that you declare yourself being savvy not enough to discover it. That can be resolved by an incumbents' agreement.

*1 http://en.wikipedia.org/wiki/The_Market_for_Lemons

Wait a sec...

[fyngyrz]

An engine like PostgreSQL is so complex, there are few standard tests that could really give you the data you're looking for, unless your application is so vanilla the KKK would endorse it. The only way to understand -- beforehand -- how a new version of a DB like this would work in an existing environment is to set up a test server, set up your database on it, and test it against the real-world operations the production server is experiencing, then compare the two in areas like execution time, memory utilization, thread count, etc. After you'd carefully evaluated what benefits any new features might bring you.

Seems to me that if you want to get beyond "perception" and into a worthy objective analysis, complaining is a waste of your time, because only you can do such a thing. If you want general info, the PostgreSQL team already gave you that -- they claim performance hasn't been hit. And a canned analysis doesn't, in my experience, reflect your own results. Benchmarks may give great results, but you write a simple little join a little funny, and oh brother, can your results differ. And so forth.

*** I've been using PostgreSQL for years; I'm not affiliated with those people, but I am pitifully grateful to them.

Re:Wait a sec...

[jd]

To a degree, I agree. There will also be a number of things in database design that a DBA wizard could suggest that go beyond my knowledge. However, let's take a trivial example - basic SELECT, INSERT and UPDATE operations. What can you do with these? For any of the Open Source databases, you can compile with instrumentation and then measure the average length of each arc through the program that you can hit with just those three statements. With this, you can determine the maximum, minimum, mean and variance for the arc length for each of the three statements for some determined load level. You then do a series of load levels and obtain these four parameters for each statement at each load. This tells you something about the scalability on workload as well as the behaviour of the three functions.

(I pity the fool who actually tries to run a database with maximum instrumentation - you'll need hellish horsepower to get any kind of response even under light workloads. Under really heavy loads, the fountain of youth would be handy as well.)

Re:Wait a sec...

[greg1104]

Performance level instrumentation for PostgreSQL is normally collected with kernel-level tools including oprofile and DTrace. The overhead for collecting it with those two is really not that bad, only a few percent on most workloads. About the only place you can really see bad instrumentation in PostgreSQL is when you're running EXPLAIN ANALYZE to get really detailed timestamp level information about every row processed by a query. That can execute dramatically slower than the uninstrumented query.

Re:Wait a sec...

[jd]

If the instrumentation could be used on other database engines, all database engines would take (proportionally) the same hit and therefore the ratios should remain unchanged.

I guess we really are the leader now

[TheFuzzy]

PostgreSQL *must* be the leading open source SQL database, now. People are bashing us on Slashdot. That's always a sign of success.

Thanks, guys!

--Josh Berkus
    PostgreSQL contributor

Re:I guess we really are the leader now

[Anonymous Coward]

Nothin' but love here, buddy. And all the bashing is coming from those who don't know what a real database is.

Re:I guess we really are the leader now

[Anonymous Coward]

I concur. PostgreSQL is one of the few examples of exceptional software engineering I provide as an example when somebody asks.

fp troolkore

[Anonymous Coward]

is busy infighZting the party in street

medical practice

[Anonymous Coward]

would it be possible to store my information in this database? i am a medical doctor, see patients daily and need to record what drugs they are on. thanks. please email me ready solution to download for my medical PC desktop. thanks.

Re:medical practice

[croftj]

While we're making silly requests, please email me a big bucket of money along with your requirements.

Re:medical practice

[Anonymous Coward]

i also need to record patient data of birth and phone numeber.

thanks.

Re:medical practice

[h4rr4r]

Hey doc, how about you share the wealth and maybe someone will.

I think Sun was giving them access to some...

[Anonymous Coward]

sparc servers, but that went poof about 10 minutes after the Oracle merger.

Re:I think Sun was giving them access to some...

[greg1104]

The servers Sun supplied that Oracle recently yanked were for the regular PostgreSQL build farm, used to run basic regression tests. They've since been replaced, the project is unmoved. As I mention in more detail in my upthread post, work on the PostgreSQL performance farm continues unaffected by that. It is expected that some build farm machines will also run the performance farm client periodically too, that's the only overlap there ever was between the two pieces of work. If Oracle still had hardware in the build farm it could have been used for performance tests too eventually. But they don't, and we in the PostgreSQL community don't care; we don't need their contributions.

Hypocrisy

[Anonymous Coward]

Blah... I started to think that slashdot is full of angry fools, who think that ANY other software besides the software they use is bad and evil. PostgresSQL ? anyone? Where is it when the REAL life is dominated by SQL Server, ORACLE and maybe DB2?

Hate all this Hypocrisy.

All other SQL implementations are shite..

[Anonymous Coward]

Yes its a good database, but please stop being such ass-holes and dissing any other SQL implementation that doesn't fit your mold. Like anyone's going to change their technology base based on a few fan-boy comments, nobody cares and nobodies listening!

If you didn't start off every thread by slagging MySQL, firebird etc then somebody might actually bother to read your comments.

PostgreSQL is a stinking sack of shit

[Anonymous Coward]

80s database...

Thanks!

[RAMMS EIN]

Congratulations and a big thank you to all PostgreSQL developers!

I'm especially excited about the new replication features. The new trigger functionality looks exciting, too; I will have to look into that.

Keep up the great work!

Table Design and Query Tools

[KJACK98]

Is there any recommended table designer or query tools for postgress? I'm not expecting Toad but something functional like MySQL Administrator and Query browser.

Re:Table Design and Query Tools

[spatialguy]

www.pgadmin.org

Re:Table Design and Query Tools

[loom_weaver]

For querying I like using SQuirreL Easy to construct your queries and execute which ones you want with very little retyping.

Re:Table Design and Query Tools

[Xest]

PG Admin is the closest but it's still utter crap compared to MySQL's tools, unfortunately MySQL itself is quite shit, so basically you've got a choice of good dataabse, shit tools, or shit database, awesome tools.

Some numbers

[Anonymous Coward]

Here are is a small performance comparison which was taken during a staged hardware and OS upgrade. Unfortunately there are two factors which changed (OS and DB), so its not entirely possible to attribute all the performance gain on the same hardware to just PG 8.0 -> 8.2. Despite that, it was impressive to see OS and DB releases running ~30% faster on the exact same hardware.

* FreeBSD 7.3 on Xeon 5520, pg8.2 : 483ms
* FreeBSD 7.3 on P4 3.2GHz, pg8.2 : 646ms (same hardware as below)
* FreeBSD 6.0 on P4 3.2GHz, pg8.0 : 998ms

Haha

[Anonymous Coward]

You can't seriously comment on a database engines performance and in the same breath mention that you use ActiveRecord. No database is fast enough to compensate for a really crappy ORM. If you are using ActiveRecord and MySQL/innodb I guess that switching to postgres might give you a slight improvement but learning SQL and ditching the ActiveRecord crap can give you several magnitudes.

Re:Haha

[georgeb]

Ok GP offered a valid technical criticism of a database (one component in a bigger project) and your solution, typical for MySQL users and developers I might add, is to change the other components of the project. Cool. Glad to see you give constructive feedback.

Can I drop a db being accessed now?

[kikito]

You know, like in MySQL?

Thankz bye

So..

[bytesex]

Does it have materialized views yet ? Oh, and can I configure it so that it doesn't have those two ridiculously huge undo-type files so I can use it on smaller hardware (hey - it's fully replicatable now, right ? That includes scenario's where you replicate from a big master to several small slaves) I know, I know. Keep pushing the bar. Congratulations guys, from a happy user.

Re:So..

[LizardKing]

Does it have materialized views yet ?

First hit in Google: http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views

Re:So..

[schmiddy]

Well, the GP was probably asking more about materialized views integrated into the Postgres core itself (I think Oracle has these). So that you could materialize an existing view just by saying "CREATE MATERIALIZED VIEW ... ", instead of cooking up the triggers yourself.

Now, The page you linked to actually says:

I do not recommend that PostgreSQL add Materialized Views in its core.

because of their inherent complexity and customizable nature. There actually has been some work recently attempting to do just this: see e.g. http://archives.postgresql.org/pgsql-hackers/2010-04/msg00479.php.

As for the GP's gripe about "those two ridiculously huge undo-type files", I have no idea what he's smoking. Postgres's "undo-type" files are its WAL logs, and you can configure how many it keeps around. They are 16 MB each by default. I've had no problem running Postgres on tiny hardware (cheap VPSes), and it comes configured out of the box to do just fine on minimal hardware.

Re:So..

[bytesex]

Yeah, but you can't have *zero* WAL logs (even though you have no use for them), and their size is *compiled in*. And yeah - materialized views are a good feature.

Re:So..

[Peter Mork]

I love Postgres, but I think the author of that article is overselling Postgres' support for materialized views. Sure, a developer can hand-roll a materialized view using triggers. That doesn't mean Postgres has materialized views, only that it has triggers. A developer can construct many features from atomic capabilities, but that doesn't mean the platform natively supports those features.

What the GP and I would like is the ability to say "CREATE MATERIALIZED VIEW AS ...". It's up to the database system to convert that declaration into the equivalent set of triggers so that I don't have to think about the logic.

In fact, there was a great paper from 2000 ("Practical Applications of Triggers and Constraints: Success and Lingering Issues" by Ceri, et al.) that basically concludes that triggers can be a nightmare for developers, but a great way to implement advanced features such as materialized views.

Re:So..

[schmiddy]

It sounds like you want to use PostgreSQL as something like a (non-durable) cache layer. I'm not sure anyone has tried removing WAL entirely from the system, but people have definitely played around with the idea. See e.g. here. With a little tweaking, you should be able to get a large speedup by sacrificing durability/crash recovery.

Re:So..

[bytesex]

I was making a database that would replicate (using slony) to a (very small, embedded) client during its (the client's) boot-up. Here the database lived on a ramdisk. It would only monitor changes (upon other events). If anything would happen during operation, I would just have rebooted (nothing ever happened). Those WAL-files and their apparent obligatory nature really got under my skin at one point.

Re:So..

[TheFuzzy]

Well, you can always do ad-hoc materialized views ... I'm coding some now.

However, better matview management is currently under development. We had a Google Summer Of Code project to write code to manage the updating of materialized views. In a couple of releases, we might have matviews as good as Oracle's.

Of course, if you're a PostgreSQL user and you want better matview support, then we could really use your help developing it.

--Josh Berkus
      PostgreSQL Project

Upgradeability a huge boon

[rorycl]

My company has been working with Postgres for 5 years. We've built several large sites with it for others and run a webapp for hospital professionals backed by Postgres (with a fair amount of use of stored procedures). Not only have we found that Postgres has been incredibly stable over the years, but we have also found that upgradeability an enormous boon. The incredibly smooth upgrade process between major versions have allowed us to seamlessly move between versions. Also, the excellent release notes allow us to easily pick up any changes that are likely to affect our systems. Postgresql is both a wonderful product and an excellent community.

Open Source Defined

[r7]

Now that MySQL is owned by Oracle it looks like Postgres may, over time, become the only truly FOSS RDBMS.

When I read that there is a major FreeBSD replication bug that MySQL developers have not fixed for some time I have to wonder whether these are the same dirty tricks that Sun employed to advantage some OSs over others. If so this would tend to validate the rumor that Oracle may buy RedHat. Then the gloves would come off no doubt, and Oracle's preferred platforms would get all the bug fixes while other distributions and OSs would get crumbs, like they've done with the Oracle DB for years.

As always, software that is developed cross-platform, on multiple OSs, will be better than software that is developed on a single or smaller number of distributions and OSs. Oracle (and IBM's) efforts to secure vendor lock-in will only work short-term. In the long run their plans won't work out so well but until then I'm sticking with Postgres (and Ubuntu, Debian, FreeBSD, and OpenBSD).

Please don't use MSSQL

[psyclone]

If you are using hand-rolled SQL, most MySQL queries will execute on Postgres without much modification. However, MSSQL will be vastly different.

For example, look at these ugly MSSQL queries with explicit locking, which you will probably have to use as developers and DBAs can't seem to agree on a standard isolation mechanism:

SELECT COUNT(UserID) FROM Users WITH (NOLOCK) WHERE Username LIKE 'foobar'

and

UPDATE Users WITH (ROWLOCK) SET Username = 'fred' WHERE Username = 'foobar'

Also, there is no LIMIT / OFFSET keywords in MSSQL, you have to do crazy shit like:

WITH results AS (
        SELECT
                rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
                , *
        FROM tableName
)
SELECT *
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

Source: http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server

You will soon realize that the Express version is super-limited (4GB max size, 1 GB ram, 1 core, no replication, etc.)
Source: http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx

Postgres is highly tunable, but the defaults (that ship with many OSes) are for small footprints. This is an older document, but still relevant with explanations and the annotated config guide (bottom of page). Throw 8 cores and 16GB ram at Postgres, tweak the conf a tiny bit, and the feature set and performance will surprise you.

Tune Postgres: http://www.varlena.com/GeneralBits/Tidbits/perf.html

There's no reason to use MSSQL unless all of your development and applications are on Windows, and your development team can't use anything other than their IDEs in a limited way. Once you start using Postgres, and realize the power behind it, you'll never want to use anything else.

If, for some strange reason, your company wants to spend money and buy DB support, go for a commercial vendor of postgres. Enterprise DB has some nice management features: http://www.enterprisedb.com/products/index.do

Donate anyway

[Safety Cap]

The fine folks at CodeOffsets.com will donate to PostgreSQL if you so designate.

Tried Glom?

[toby]

http://www.glom.org/wiki/index.php?title=Main_Page

Review from 2006...
http://www.xaprb.com/blog/2006/09/04/a-review-of-the-glom-graphical-database-front-end/