Slashdot Mirror
PostgreSQL Inc. Open Sources Replication Solution
Martin Marvinski writes "PostgreSQL Inc, the commercial company providing replication software and support for PostgreSQL, open sourced their eRServer replication product. This makes PostgreSQL one step closer to being able to replace Oracle as the de facto RDBMS standard. More information can be found on PostgreSQL's website."
That will be an excellent and much appreciated addition to this excellent database. FP
Use MySQL! Who cares if it doesn't support transactions, corrupts at every chance, is not scalable, and a resource hog! It is your duty to use MySQL!
There is an enormous distance between "viable alternative" and "defacto standard" and the path between them is not paved with features.
While this is a good thing, pseduo replication is possible at the application layer. Think using PHP to squirt a table in one DB into anther one.
Now that Postgress can replicate at DB level other, more interesting things are possible. You can use replication for both failover and performance clustering.
I can't think of anything witty right now
Check monster.com. More companies look for people with mySQL experiences. Check the book stores. You will see more books about mySQL. Even though PostgreSQL has more features and is more promising and powerful, mySQL gets more publicity. This means that mySQL will be the open source database that will replace most commercial databases. It's sad but true.
So we can get them neatly sorted out of the way of the interesting comments. Thanks.
Oracle is getting to be as bad as the Evil Empire Microsoft. We run an Oracle shop and pay boatloads of money a year to support.
I can't say a bad thing about postgresql; this was really the only thing I felt the need for. For anyone who hasn't tried it you really should. Although I don't want to start a MySQL v postgresql flamewar, after trying both I think that postgres has the edge. Mysql was undisputably easier to work with and (at the time) was faster. PostgreSQL has moved on at a much faster rate though. In particular postgresql has solid support for transactions, large objects, subselects, object oriented tables. I'm convinced that if you use databases long enough you'll want every last one of these and won't be able to do without.
Carpe Daemon
This is indeed good news, as free software always is. But eRServer can only operate in single-master mode, which makes it unsuitable for high-availability kind of work. Single-master systems are good for load-balancing on installations where most of the queries to the DB are SELECTs.
eRServer comes a bit late. We already have PostgreSQL Replicator, which is multi-master. Unfortunately PG Replicator is not supported anymore. The latest version it can work with is 7.1, and the project's latest news are timestamped nearly two years ago.
Whatever, dude, I know that everything OS-ed is saint, but why can't we just one single time stay realistic????
Anyway, this thing above.... not gonna happen real soon now.
1. No sig. 2. ???? 3. Profit!!!
A Good Thing(tm)
Corporations are the people Open-Source needs to get on its side. (And, I might add, the OS community is doing a very good job here). They give a project name-recognition, thousands of users, good infastructure, and credibility. PostgreSQL will hopefully begin to compete seriously with Oracle. Another feather in the Open-Source cap.
((lambda x ((x))) (lambda x ((x))))
PostreSQL still dosent support inverse and shadow keys. Until they support them, We will continue to fork out 100.000 a year for Oracle.
They also have been working on a procedual language for PostgreSQL for server side triggers, and functions. Information can be found here, plPHP.
PostgreSQL has made some pretty nice advancements post version 7+, performance and feature wise. I worked on a intranet where the company spent lots of money trying to get an Oracle solution to work, but found it was way to slow. The suggestion of PostgreSQL, and MS SQL came up. We tested PostgreSQL, and it was acutually faster, and easier to maintain then our Oracle database. The best part was, it was free!
Every Super Villan uses Linux.
So I get up and take a step toward the coffee machine, I guess I am ALSO "one step closer" to China!
While this is a nice step forward, the real reason large sites utilize Oracle is because of synchronous replication.
The replication needs to be able to keep all data consistent across multiple servers, without any conflicts. Then, if a particular server goes down, the DNS can simply fail over to a second server.
Once the above has been achieved, then we have a viable alternative to Oracle.
Hehe, you're silly.
Where I work, we have a data warehouse, which grows about 8 GB per day, and holds 13 months of live data. Lets see you hold that in RAM !
> This makes PostgreSQL one step closer to being able to replace Oracle...
Please! While this may help win the hearts and minds of OOS geeks, it does little to improve their standing in the business world.
Oracle is as established in the database world as Microsoft is on the desktop. This alone would doom any OOS wannabe to quiet places like web server back ends where they already do well anyway ( e.g. mySql ).
Put aside the technical considerations, support, client base, etc and PostgreSQL still offers as much of a threat to Oracle as mySql or dBase. The only real threat I've seen to Oracle supremacy is Microsoft's SQL Server but, of course, that's only in MS shops.
Starting with the next release SAPDB will be rebranded as MaxDB by MySQL AB.
This will probably mean that PostgreSQL will have a very hard time competing with MySQL ! (also see the info on the SAPDB webpage)
At the same time the licencing will change to pure GPL (no more LGPL libraries !!!)
Please let me know if I'm wrong...
I visited the site, and the commercial site too and it seems this is only simple replication with the master being a single point of failure. F.E.
1. update a row in the master
2. master replicates the update to multiple slaves
3. clients perform select operations against the slaves (nice load balancing opportunity)
4. the master crashes
5. No one can write until the master comes back online.
Here are the steps that seem to be missing:
6. the slaves elect a new master
7. if the old master comes back up it must realize a new master is present and become a slave.
8. clients using JDBC would need some mechanism of finding out what the new master is when an update/insert/delete fails.
Cheers.
Schedule your world with ScheduleWorld.com http://www.ScheduleWorld.com/ (Java Web Startable)
I like PostgreSQL, and Open Source deserves capitalization, but I'd like to hear an enterprise DBA's perspective on if this really compares to Oracle's configurability, clustering capabilities, or the seamless swapping of redundant database packages when deployed on, say, an EMC 1000, for reliability and failover. BTW, for this request, "enterprise" = Fortune 100, not Joe's Web Hosting.
Like the subject says, I'm not a DBA, but I know some pretty heavy-duty ones that say nothing beats Oracle running on HP Superdomes with EMC storage.
Comment removed based on user account deletion
We use PostgreSQL a lot. Since all the of the sites related to this story are in the process of being /.ed, can someone tell me what this replication thingy does?
...until it provides a proper explanation of its name.
Yes, PostgreSQL is a concatenation of Postgres SQL - its former name, but Postgres is not a word.
WTF is it supposed to mean? Or is it just nonsense and I'm to view the product in the same light?
This makes PostgreSQL one step closer to being able to replace Oracle as the de facto RDBMS standard.
About 500 more to go kids.
MySQL and PostgreSQL have entirely different purposes driving them. MySQL = fast, small, low-footprint at the sacrifice of features. PostgreSQL = full-featured at the sacrifice of performance (though, just a little performance). So when you say a leap "ahead" of MySQL, you'll have to qualify that. Myself, I prefer PostgreSQL, simply because of the features. I USE MySql because it is fast a 99.9% of hosting providers offer no other alternative.
-----
Web Hosting @ HostForADollar.com
Mnesia.
It's not perfect for everything, but if you want scalability, failure resilience and excellent query time, it's well worth a shot.
As an added bonus, it gives you a good excuse to learn erlang, so that LISP-weenies can sneer at you.
And then watch the DBA break down and cry as the UPS fails during a blackout...
I am TheRaven on Soylent News
"PostgreSQL Inc, the commercial company providing replication software and support for PostgreSQL, open sourced their eRServer replication product"
Many thanks you guys....we've been waiting for this a long time
What a rotten party, have we run out of beer or something?
Until SAP, PeopleSoft, and Oracle applications support it, which will happen, respectively, probably no time soon, probably never, and never, it won't "replace Oracle as the de facto RDBMS standard."
Orange whip? Orange whip? Three orange whips.
[ shameless_plug ] /shameless_plug ]
StarFish is a block-level storage system allowing on-the-fly geographic replication, that would work with any database. It was OpenSourced by Lucent a few months ago. It won the Best Paper award at Freenix '03.
[
shameless plug
Since Football season is here and a lot of OS workers hang out in offices come check out a site which allows you the functionality of Blogger, Yahoo Groups and allows you to run your own Football Pools. The whole site architecture is OS using AOLserver, PostgreSQL, Postfix, on GNU-Linux
PostgreSQL kicks butt and so will the Redskins this year. You can check out the some of the technical details at www.23pools.com/sh/co/tech and make your predictions using PG to store your picks or check out a sample blog at Never Give Up
end of shameless plug
And you clueless. There are some technologies that can dump the hd storage devices (no pun intended) and unify the whole memory speed hierarchy. If that happens, say goodbye to rdbms's and swallow something like object prevalence.
From the press release:
Does anyone know what the other four components are and whether they're already here?What do you mean they cut the power? How can they cut the power, man? They're animals!
By the time PostgreSQL is capable of replacing Oracle as the de facto standard, MS SQL Server will already have done the job and be the new standard to beat. No matter how much people despise MS SQL Server, nor how much they over rate Oracle, it is now the standard for companies needing a low or mid range solution. As with all MSFT products, it improves with each iteration. Our needs aren't high-end, but it handles our 300GB databases with 170 million row tables remarkably well.
This looks like an interesting policy. They mentioned they are going to open source their products in two years. What it does is unless the other companies are innovative enough, they will be playing a catch up game with latest technology introduced by postgresql people.
But if they are copy cat. They will have to fight with the *free* software since the related technology will be released as open source by then.
So, freshness of technology wins ! Not the closely gurded dead old unix code.
But....but....but....it's easy and free.
You'll have that sometimes...
1. update a row in the master
2. master replicates the update to multiple slaves
3. clients perform select operations against the slaves (nice load balancing opportunity)
4. the master crashes
5. ???
6. Profit
So now we have two major OSS databases with 99% of the features that commercial offerings have, and lots of features that they don't (I'm a MySQL guy, so I know what those extras are for that database, but knowing OSS development paces, I'm sure the same is the same for PostgreSQL).
I listen to folks at work talk about why we "need to move to a *real* database at some point", and it always comes down to the fact that they've bought into the marketting, and when they examine their reasons (if they are willing to), solutions like PostgreSQL or MySQL are a whole lot better choices than the "real" database choices out there.
Bravo guys!
And then watch the DBA break down and cry as the UPS fails during a blackout...
Geez... Now what whould be the point of object prevalence? Perhaps the idea is to make sure the objects prevail, no matter what happens. Perhaps you don't know what you are talking about?
You're correct, and I think you saw my point, even though the crackhead moderators thought it was flamebait.
To spell it out: It's nice that there is an open-source "database" that can actually be called a RDMBS. I realize that MySQL has its uses, and it wouldn't be so popular if that weren't the case. But the lack of features place it in a completely different category than PostgreSQL.
I'm from an Oracle background, so I want to take this away from being about MySQL or any other DB. However, your statement about transactions really gets to me.
I work hard when I write code that talks to a database to NEVER use transactions or rollback unless there is just no way around it (and while I've come up with some hypothetical situations where that would happen, I've never run into them in the real world).
The problem with transactions is that they cultivate an attitude about your data which is very, very dangerous. I can't count how many reasonable-looking applications have caused untold damage because the developer stopped thinking about the consequences of their actions.
By forcing myself to deal with those consequences manually by doing my own locking and my own data-integrety management, I find that I can rely on my data far more than most people can, and the likelihood that one of my programs is going to "go bad" and rip out whole transactional units just because an non-essential field was initialized oddly is much, much lower.
Now, of course, going back to MySQL, you have the option today: do you want to use transactions? If so, your schema will reflect that, and you'll take the performance hit that everyone else does when you enable them. I never do, but you're welcome to. That's what software should be all about: giving the user all of the choices they need.
All that said, I love PostgreSQL, and I hope that both databases continue to kick the commercial databases' butts for decades to come!
YHL. HAND.
MS marketing says the same about IIS, right?
As with all MSFT products, it improves with each iteration.
Wow, Bob 7.3 must be awesome!
Perhaps you don't know what you are talking about?
That's by far the best example of the pot-kettle phenomenon I've ever seen.
Here's a pretty good article comparing MySQL and Postgres.
I've got about 17 years experience with RDBMS', covering oracle, db2, postgresql, sql server, etc. So....
Postgresql looks like it's better positioned to eat SQL Server's lunch than oracle's to me. First Off, back in the day (3 years ago), when oracle was licensing by 'power unit' - it cost about $1000 / CPU / Mhz. So - a single CPU license for a 1-ghz machine would set you back $100k! Since then they've had to drop prices - because of the market, and because of DB2 - which is far less expensive.
Anyhow - if you're going to invest a cool million or two in a top-end, enterprise box - a Sun E10K or an IBM Regatta - then you don't rig up some cheap AC solution, use surplus wiring, or a free database on it typically. You put Oracle or DB2 on it. Sure - Postgresql is fine database and it'll save you some bucks, but when you're putting your reputation on the line and have build a business case that justifies (say) $2m in hardware, and probably $4m in labor - it's foolish to try to save $1m on oracle by going with postgresql.
Without *any* support for parallelism, without stronger replication, without better 3rd-party support (think of Toad for instance), without thousands of experienced develpers & dbas out there, without more robust availability functionality - it simply isn't ready to tackle the biggest projects. Or those projects with extremely high availablity requirements. Or relatively large reporting projects (no parallelism). Or just about any projects in a really dedicated single-vendor shop with its act together.
But that's ok - that leave 30-50% or so of the other database work that it can do just fine right now. That's a huge market. And unlike mysql - if your construct a database application using postgresql and then later want to port it to oracle, db2, or sybase - it's just a normal porting of the application. You construct the architecture in just about the exact same way (for most applications anyhow), so the porting is straight-forward. Not so in the case of mysql - where it's severe limitations result in applications doing a ton of the database work - and porting ends up being a complete rewrite.
Even though the article is about an improvement in the PostgreSQL community, the comments are mostly pgsql vs mysql. People in the bazaar need to have personal motivations to work on opensource projects, mostly to have something against Microsoft, but increasingly, it is becoming a series of team wars. Linux vs BSD, then we had KDE vs GNOME and now qmail vs postfix and mysql vs pgsql. More than a decade ago we had vi vs emacs and BSD vs SYSV.
What the posters here need to realize is that it is exactly this competition that is driving the projects. If MySQL was not given the press and did not have its cult following, we would not see this pace of development for pgsql. The developments for FreeBSD really improved to compete with Linux although their developers claim they are not competing... they do have the fear Linux will supplant them.
What is interesting to note is that in most of these project wars, both projects really survive and get two different niches of their own. This was true of bash vs csh, BSD vs SYSV, BSD vs Linux, KDE vs GNOME, and now MySQL will become the standard entry level database and pgsql the higher level.
I use pgsql because my databases have complicated requirements that MySQL cannot meet. Yet MySQL is the quick and dirty solution when I have to set things up fast. For all new learners I always suggest MySQL. For people thinking of replacing or duplicating their ERP systems, I always suggest pgsql. I even know how to program in sleepycat's db and know where it should replace mysql in smaller embedded systems and where the mysql license cannot be used.
I believe this competition is coming to a close since pgsql has taken a big lead over MySQL in features, and therefore made itself more difficult to deal with especially for newbies. All I can say about the postgresql replication is bravo, and hope MySQL doesnt follow suit so it remains the simple fast and easy database in its own niche.
"Give orange me give eat orange me eat orange give me eat orange give me you." -Nim Chimpsky
PostgreSQL still needs better transaction handling before I'd call it ready for prime time. I found its limitations the hard way, attempting to port 13K lines of Oracle Embedded SQL. Imagine transaction A searching a database. Now you want transaction B to do something. It might be just two user queries open at the same time or one transaction reading one table while the second writes to another table. All well and good until one transaction closes. Then all transactions close! That just plain sucks.
However, I was able to port the entire application to Firebird--IMHO a far better database.
"Love is a familiar; Love is a devil: there is no evil angel but Love." --William Shakespeare ('Love's Labors Lost')
two questions:
Is it a bug or just a known limitation?
Is this still an issue with current version?
*sigh* back to work...
What if your network connection flakes out for a second? Then what? That's why there is such a thing as replication, for all the times when simple copying doesn't work for some reason - there needs to be extremely thorough error handling and methods for getting the data back in synch.
This is like shooting fish in a barrel. I often don't like to harp on the OSSDB fanboys, but Oracle's database solution is second to none, and continues to pull away from the pack.
First things first. Online replication is generally considered by professional DBAs a fools errand. You have to babysit and it fails at the drop off a hat for a variety of reasons. The are no good reasons to do replication in the manner they are talking about, unless that is your ONLY option.
There are however, reasons to replicate data. The reasons you want data replicated are usually for one of two reasons: availability or scalability.
To address availability Oracle provides several options that are just plain better than regular/triggered snapshot logging or materialized view refreshing over a network.
The best option is Oracle's Dataguard, which applies redo/archive logs to a duplicate remote databases. You can perform this option at the logical and at the physical level, and you can choose to maximize/guarantee the protection all the way down to best effort. This option provides the ability to have an absolutely current very warm site, a simple command and you're database is up and running.
As for scalability, again Postgres or mysql doesn't hold a candle. There are too many options to list, so I'll discuss the big ones.
Paritioning/sub-partitioning of data. The way Oracle lays out it's logical database block layer and physical OS block layer is absolutely perfect for being able to do anything you want with the database file layout. I can put my OLTP indexes and tables on fast raid10 devices, the historical and warehousing data on raid5 devices, but that's not all. I can increase parallelization of the hardware by putting a single table or index across N devices. The ability to sprinkle files and chop up data anywhere you want, is just one thing that makes Oracle configurable, scalable, and great.
Real Application Cluster (was Oracle Parallel Server). This is a for REAL clustering solution. Oracle allows several servers (can be dissimiliar in capabilities, i.e. some can have 64gig of memory and 12 processors, and then the others could be smaller dual processor machines.) to connect to the same storage (usually shared over a SAN or SCSI direct connect to EMC gear). Each of the servers is connected to a crossover/ipc LAN (we use gigabit) and now each of the servers has access to the same data. One node goes down or needs to go down for maintenance or reconfig, that's ok, the other nodes are online and traffic can be configured to automatically transfers over to the other nodes MID-TRANSACTION and picks up where it left off and the application is none the wiser (i.e. happens in seconds). The nodes share cached data over the fast network, so there is often little need to go to disk. This kind of scalability can not be found on any other database.
<rant>
The real gain for OSS and Oracle, is Linux and Oracle running on Linux. OSS databases are too immature to be let anywhere near real money. I'm not talking about ecommerce money, I'm talking about the millions and bajillions of dollars that flow like water through companies. Linux has Oracle validation and certification, which goes a LONG way in getting Linux into the real datacenters. The price point for the hardware, and the OS and the special deals that Oracle cuts for it are the true win for OSS. The performance is more than there for Linux/Intel solutions, and the price point for Intel hardware is very attractive to companies looking to cut expenses. You still have to pay homage to the Oracle and EMC gods, but even they have felt the crunch, and they too are providing competitive price points.
</rant>
So Postgres is one feature closer to what Oracle was several years ago. So what, this is embarassing. Mysql has had transactions for how long? a few days? Please people, Oracle is not resting on it's laurels waiting for anyone to catch up. They have real companies, with real money, that are real threats to them. IBM and Microsoft. Oracle, is pushing the edge on the database front, and doesn't show any signs of stopping.
No.
Support contracts would seem to be a good way to gauge enterprise use and give some idea where the money^h^h^h^h^h I mean where people were putting their attention, and 'voting' in a way for better future releases. Support experience should ideally trickle back into new releases.
Be Free: Free Software Tuition
It didn't have row-level locking before? Okay, I can see that. But it didn't have FOREIGN KEYS??? And people used to bristle whenever someone suggested it wasn't a real database....
This wouldn't affect RDBMSs at all. Object prevalence is very fast, but the hierarchical databases before RDBMSs were very fast as well. The reason that RDBMSs took precedence was not speed, it was data integrity and data manipulation. It still has these advantages. If you just view an RDBMS as a bit-bucket, you should have just used hierarchical databases all this time, as they are much faster.
Engineering and the Ultimate
It is a limitation with PostgreSQL 6. I thought at first that I must be doing something wrong. Then I found Postgresql: Developer's Handbook by Ewald Geschwinde, et al (published by SAMS) at a local B&N and looked up transaction management. It spelled out the bad news in a way the PostgreSQL docs only hinted at--sure enough, you can't independently manage transactions. Close one and you close them all.
I haven't heard of anything being done to add this capability to PostgreSQL 7. I hope someone puts it on top of their list. There were things I prefered about embedded SQL with PostgreSQL vs. Firebird but PostgreSQL's limited transaction management killed all my work with it.
"Love is a familiar; Love is a devil: there is no evil angel but Love." --William Shakespeare ('Love's Labors Lost')
It is a known issue. However, nested transactions are currently being worked on...
LedgerSMB: Open source Accounting/ERP
Mr. or Ms. Coward is on to something here. Competition drives open source development teams, but, ideally, the competition doesn't drive them into head to head battles in the end, but drives them into different niches.
For my part, I run a site on top of MySQL. I started using it as a newbie to Linux RDBMSes. It's never going to be so big that I'll need to run anything else. However, I've got a new project on the horizon where Postgres is the perfect solution. The best thing is that in a Linux environment, there's much less lock-in than in other environments, so you aren't forced to be a partisan about things like this.
Online citizen journalism from the inner city: The View From The Ground
Not functional yet, but heh, every project's got to start somewhere. </shameless plug>
SQL Server will never run on any version of UNIX. AFAIK, there aren't even (MS-supported) SQL Server client libraries for non-windows platforms. I realize that FreeTDS is available, but such a library would never be used in a highly critical sector.
If you have to integrate multiple platforms, you cannot use SQL server. Closest similar product is Sybase ASE, but Microsoft broke Sybase compatibility on purpose.
I think if people understood how irrationally obstinate SQL Server's platform dependence was, they would look elsewhere. I hope that this attitude holds them below 10% penetration - it certainly has up to now. They are a bit player.
In any case, there is a cheap, new version of DB2 out for $500/copy.
Way cool.
However, the only other RDBMS's to do this (to the best of my knowledge) are all closed source or otherwise very much non-free.
File under 'M' for 'Manic ranting'
All oracle tools are freely downloadable from the Oracle website
http://otn.oracle.com/software/content.html
This is not Highlander there can be more than one.
MySQL is a good solution for some tasks. Postgres is a good solution of some other tasks. I swear people are so odd. There can be room for more than one OS, Database, Office Suite, and CPU. I really like Postgres and use it for our in house database. I use Mysql for our website's database. Why? because it is what our ISP provides and it works.
How about this... Learn Both.
See my blog http://ilovecookes.blogspot.com/ for light hearted technical information.
Does mySQL have an embedded SQL preprocessor yet?
Until it does, my C-with-embedded-SQL code will contine to run fine with Postgres, Oracle, Sybase, DB2 and other real datatabase servers, but not mySQL.
(Okay, there are a couple of very minor differences between some of the above, in particular slight variations on the EXEC SQL CONNECT syntax between Oracle and Postgres, but that's easy to code around. The rest is the same.)
-- Alastair
I find it funny that so many journalists always say that KDE/GNOME or Postgres/MySQL should be stopped and only 1 solution should be allowed. Yet, at the same time, they quickly point to all the different applications in the windows space.
Myself, I use Linux/KDE/pgsql ~ 95% of the time. But there are times where I like BSD (awesome security), Gnome (i like their simple interface and their apps are nice in a number of areas), and Mysql (want a fast mostly read DB? Nothing beats Mysql in the true relational arena (dbm/gdbm/sleepycat can for simpler relations)).
Lets hope that real compitition never stops.
I prefer the "u" in honour as it seems to be missing these days.
My only hope that someone in PostgreSQL Inc will read what you are saying. Postgresql ORDBMS needs the same hype as Linux already got. otherwise, sooner on /. we'll see stupid comments "Postgresql is dying" in a same style as they are posted about *BSD.
Seriously, BSD is not that bad, but why it is not that popular as Linux? B/c there is no virtually any publicity around it. Say "open source operating system" to any boss, the reaction will be "Linux?". Now say "BSD". The answer will be "BS-who?". In a same way if you say "open source DBMS" to any boss, the reaction will be "MySQL?". But if you say "Postgresql" the reaction will be "Postgres-who?".
I understand that for piblicity PostgreSQL Inc needs money, and the only way to get a big money is to make PostgreSQL to be interesting for big software corps, like IBM or HP. Why IBM has chosen Linux vs BSD to support? B/c BSDL doesn't protect their source code cotributions. Here might be a key: the license. Perhaps PostgreSQL Inc should reconsider their BSDL license. Well, that might be impossible as most of their core team hate GPL (that's why they usually hate Linux).
Less is more !
Replication is nice, when you need it, but AFAICT there is still no easy way to make a static snapshot of a PostreSQL database, take this in a file to another server, import with "psql databasename -f exported_file" and have it working. Sometimes you get incoherent snapshots, with inserts relying on tables which are not created yet.
Just to clear up any confusion:
I'm developing a high volume, high availability site where SELECT speed and scalability are very important. I originally investigated Postgres because it supposedly uses less locking than MySQL does (when using the InnoDB transactional tables) but I decided against Postgres because of lack of a free replication solution. It would be easier to do a huge, multi-server site like Hotmail, Ebay, finance.yahoo.com, etc. if you have replication.
I'm now wondering if I should make the switch to Postgres. Where are some of the most recent, reliable benchmarks for MySQL vs. Postgres SELECT performance using transactional tables for typical web applications?
Also, I'm using some MySQL specific features like AUTOINCREMENT. Is the Postgres trick to doing AUTOINCREMENT just as fast as MySQL (or fast enough, anyways)?
Does Postgres have something like MySQL's convenience variables?
I think I might be much happier with Postgres's feature richness if I can learn more about it . . . .
More apps should do this, such as DB2. Point is gotta think outside the box a little.
Hardly - I'd say that is MySQL which owns the low end, not MSFT. PostgreSQL is competing with MySQL and MSFT on the low end, and Oracle, DB2 on the high end.
I work for the City of Newport News, Va. We're using PostgreSQL for some stuff here and getting ready to use it for some high profile application. Unfortunately, we're using SQL Server along with Laser Fiche. Did you guys write your own imaging software?
Perhaps I'm too supid to understand, but here goes.
Why would you need/want a transaction for a query. I don't see any benefit there. If you're not updating any data, you're wasting time and resources using a transaction.
I'm not arguing that a lack of concurrent transactions doesn't drastically reduce the usefulness of the database. I just think that you used a bad example...
They who would give up an essential liberty for temporary security, deserve neither liberty nor security
Maybe that was a bug in earlier versions, but it works for me using Query Analyzer and SQL Server 2000.
DECLARE @cnt INT
SET @cnt = 2
PRINT CAST(@cnt AS VARCHAR) + ' RECORDS TO GO'
Returns: "2 RECORDS TO GO" (and no error messages)
Transaction? No. But a LOCK can be handy (if implemented correctly, of course) to do a query of data from a fixed point in time. Perhaps that's what the OP meant. I sincerely hope so.
I feel better getting that off my chest. Now let me school you little punk beotch newbie dba-wannabes on the what's and wherefore's of enterprise class database administration. Rule #1: Protect Your Data! There is no rule #2. The real reason so many corporations use Oracle is that, if you know what you're doing (and so many of you obviously haven't the first clue), Oracle RDBMS will always be able to recover any committed transaction no matter how severe or catastrophic the failure. Can you say, "archive log mode?" If not, I cannot in good conscience say that you can protect a companies most valuable asset--it's data.
And another thing, while I'm hot on a rant, protecting the data against server or disk failure is one thing, but protecting it against the vagaries of doofus programmers is quite another thing entirely. A good dba accomplishes this via something called database constraints, at which Oracle excels. Can you say, "foreign key?" If not, eventually your database will resemble swiss cheese, with less referential integrity than President Bush's Iraqi WMD speeches.
Finally, everyone is always comparing MySQL and PostgreSQL's runtime performance to Oracle's. Please keep in mind that a database with only a handful of tables and no referential integrity is little more than a file system, and I do not care how many millions of records you stuff into the tables. Grownup databases contain hundreds of tables with multiple schemas and very complex data models. Can MySQL or PostgreSQL handle thousands of requests per second on a grownup database? I would really like to find out for myself.
Finally finally, does PostgreSQL's feature list truly compare with Oracle's? Here is a short list of Oracle features that I cannot live without:
pull your head out of your ass. PostgreSQL is quickly suplanting MySQL.
So Postgres is one feature closer to what Oracle was several years ago.
And, while this may come as a surprise to you, most people today are running Oracle using the same feature set that Oracle had several years ago: software doesn't get hacked up to use every latest feature every time Oracle ships a new version. Therefore, when you (a UNIX/Oracle specialist) are saying that Postgres is where Oracle was a few years ago, that means that Postgres is technically good enough for most applications.
Oracle, is pushing the edge on the database front, and doesn't show any signs of stopping.
Perhaps not. But the ability to absorb, and need for, new features in database based applications is slowing down.
A transaction can provide the ACID properties throughout the duration of the transaction. Most importantly for read-only operations, you have Isolation. Isolation let you perform your queries without being affected by ongoing writes.
This is better than a lock, which another poster suggested, as a lock will block all writers, while a transaction need not block anybody.
I honestly do not see MySQL as a serious RDBMS engine. It doesn't support many things necessary in order to really manage the data in the backend. THe whole "integrity is optional" stance regarding transactions, foreign keys, etc. is a problem for many serious applications, and PostgreSQL fills this need very well.
OTOH, MySQL is an amazing lightweight datastore system exactly because it treats integrity as optional. Memory-only tables are certainly not ACID compliant, but they can be nice programmers' tools.
LedgerSMB: Open source Accounting/ERP
Oracle has free, fully-functional downloads for current versions of all of their software at http://otn.oracle.com/... For instance: http://otn.oracle.com/software/products/oracle9i/c ontent.html
Free account signup neccessary. That's about it.
Online replication is generally considered by professional DBAs a fools errand.
Not if you use a capable replication solution (see rep server and open switch).
Thanks,
--
Matt
Strictly speaking, it still doesn't. Using InnoDB tables kills most of the advantages of using MySQL, so what's the point?
sic transit gloria mundi
That's the reason MS SQL Server is gaining ground over Oracle, and what PostgreSQL should target in order to get a bigger annual growing.
In plain relational databases Oracle is better and Postgres is more cost effective, however now the hot stuff is bussiness intelligence and data-mining.
We are Turing O-Machines. The Oracle is out there.
2. PostgresQL does support different concurrent transactions on different connections to the database or different users
This is the kicker. I'm still on 7.2, maybe this is better in 7.3 and 7.4, but if you have connection A to database and connection B to database, you wind up with
A: BEGIN
A: DELETE FROM FOO...
A: INSERT INTO FOO...
B: SELECT * FROM FOO... (or maybe an INSERT)
A: >
before you can commit, so you lose the transaction. Depending on the frequency of data writing, you might have to give up transactions entirely if you want to write, or forever be betting the race condition.
My God, it's Full of Source!
OUTSIDE_IP=$(dig +short my.ip @outsideip.net)
the last 'A:' said 'TRANSACTION ABORTED' before slashcode had its way with it...
My God, it's Full of Source!
OUTSIDE_IP=$(dig +short my.ip @outsideip.net)
I think you may have misconstrued what I was saying. Read what I said, "...Postgres is one feature closer to what Oracle was several years ago." In other words Postgres today, isn't even close to what Oracle was several years ago.
You bring up another good point that only surprises me in that it is in support of Oracle. Oracle has backwards compatibility, and a migration option to get customers on the new version. The same feature sets are available across all newer versions of Oracle. You are right software doesn't get hacked up to use the latest features, but I'll be damned if I have to hack around incomplete SQL support, not good enough backup and recovery options, limiting/poor performance tuning variables and views, and virtually non-existant scalability.
Oracle is there for when organizations need to grow up, and take advantage of real features such as rock solid point in time backup and recovery, built in scalability (not something that is bolted on and an after thought). These are features that are absent from open source offerings, and demanded by companies that recognize a need for them.
In favor of Postgres, there's is the only open source contender I have ever seen that as a development culture kind of "gets it" as far as where a database engine needs to be. (i.e. Postgres seems like Oracle compared to MySQL.) However, they are still on the D- end of the bell curve.
Postgres is technically good enough to provide a SQL interface for manipulating data for most applications. However, this is only half the answer for professional organizations, but worse it's akin to saying "Look, I can brush my teeth and tie my own shoes." It's not impressive, it's expected.
<letmetellyouastory>
I used to be a mysql fan boy. It was really cool. I felt cool, because I could do all of these things, and feel really clever because I could do them for free. Then I got a job as a webmaster (used to mean *nix/oss/web/network ninja) for a real company, and all they used was Oracle. They just wanted me to make those annoying web customers shut the hell up. It was about a months worth of work, and then it was boredom.
I started volunteering to do other work that popped up during the Monday meetings. Applying patches doing admin work to Unix OSes I hadn't touched (i.e. HPUX, AIX, OSF). I then started watching the Oracle guys, and figured out what they did. I told them, that looks like MySQL. They thought I was cute, when I explained to them what it was. They then explained that MySQL sounds like a SQL interface to flat files, which it mostly was, and still is.
Long story short, I started installing Oracle on our sandbox machines, and trying to do things with it. I thought it was incredibly complex, for what it was doing. The DBAs took notice that I was becoming midly interesting, and had the company pay for Oracle training.
When I went to training I was enlightened. Not because the training is stellar, but because I then realized all of the stuff I had given no thought to. Every day I was thoroughly impressed with what was going on. "This was how a database was suppose to be run" I thought. MySQL is light years behind. If you really think about what is going on and how they are doing things, you begin to marvel at the ingenuity and understand the reason someone is rightfully asking for money.
I had my classes, I took the certification tests (mandatory to be in the DBA department), got my pay raise bump, and I haven't looked back.
</letmetellyouastory>
No.
I know. I know. It's like I said. Embarassing. For a long time the MySQL culture didn't recognize the need or even understand ACID or anything transaction related. Was "a waste of time", I think they said, since an application developer could attempt to build their own implementation into their application if they needed it.
<sarcasm>
No need to put cruft like transactions or SQL92 support into an RDBMS
</sarcasm>
No.
I'll have to a look at this. I don't know enough about Sybase to speak intelligently about it.
However, my experience with online replication is the wierd things that bite you in the ass, like storage constraints, network problems, changed passwords, wierd locking issues, and other bug a boos that made it a big pain in the ass.
It was a far better idea to replicate the entire database offsite via redo/archive logs, than to try to run many online at the same time and keep the transactions in sync between them. This is just my experience your mileage may vary, and Sybase may have a for real solution to this problem, however, I am suspect when they mention store and forward queues as "the answer".
No.
And this needs to be repeated far and loud: If you aren't using 100% InnoDB tables, you are royally screwed. Folks still running any MyISAM tables take note!
- I don't need to go outside, my CRT tan'll do me just fine.
It should be noted however that PostgreSQL was proportionally even further behind Oracle in the past. They're far behind, but think they're running faster.
- I don't need to go outside, my CRT tan'll do me just fine.
I have experience with Oracle replication, Sybase replication and MS SQL replication and frankly Sybase is the best without a doubt. It is exceptionally reliable and easy to manage.
Unfortunately, Sybase just isn't going to make it on having a good product. Nobody gets fired for buying Oracle (or the inferior to both Oracle and Sybase - MS SQL Server).
Whether or not Sybase's replication works is irrelevant. It's an MS/Oracle world and it is likely to stay that way.
ahh, love the you dont need a dba statement . youre data has got to be total shit.
Id trust data integrity in mysql with a dba than youre installation. and that is a very saaad statement.
"defacto standard" is a bit of hyperbole. I like the PostgreSQL team's insistance on transactions and integrity, unlike MySQL's original denial that these mattered (which pretty much destroys any credibility they might have had in my mind, even if they now support transactions with InnoDB). But PostgreSQL is surprisingly primitive in some respects.
I was trying to write an OLTP application with 7.3.4 and the current API does not support bind variables. Most OLTP queries will use the same SQL repeatedly, with some variables changing for each transaction.
The difference between sending:
('insert into table foo values (:1)', 42)
('insert into table foo values (:1)', 137)
('insert into table foo values (:1)', 69)
and
'insert into table foo values (42)'
'insert into table foo values (137)'
'insert into table foo values (69)'
is that in the second case, as the SQL text varies for each request, it has to be reparsed and a new optimizer plan recomputed for each query, adding tremendous overhead.
PostgreSQL 7.4 (currently in beta) fixes this,but to me it shows a certain level of immaturity in the product for high-performance applications.
I am sure PostgreSQL will get there eventually, but it will take a while. UNIX did not become an enteprise-class OS overnight either.
Transaction? No. But a LOCK can be handy (if implemented correctly, of course) to do a query of data from a fixed point in time. Perhaps that's what the OP meant. I sincerely hope so.
Yes, a transaction. I suggest you read up on MVCC, which is supported by PostgreSQL, Firebird, and Oracle.
I don't understand what you mean. Can you post some SQL code, along with the result compared to what you expect?
Here's what I did:
A: create table foo(i int);
A: insert into foo values(1);
A: insert into foo values(2);
A: insert into foo values(3);
A: BEGIN;
A: delete from foo where i = 2;
A: insert into foo values(5);
B: select * from foo;
A: COMMIT;
and everything went through without errors. I now have a table 'foo' with tuples 1, 3, and 5.
What would you expect?
Jeff
Social scientists are inspired by theories; scientists are humbled by facts.
Two other MySQL products I found interesting (neither of which is open source at this time):
- CLUSTERING IN TUNE WITH APACHE AND MYSQL (Free registration might be required. Also see Emic Application Cluster
for MySQL)
- InnoDB Hot Backup (with point in time backup)
The rest of this comment is quoted verbatim from InnoDB NewsMySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK. February 27, 2002 - In the benchmark eWEEK measured the performance of an e-commerce application on leading commercial databases IBM DB2, Oracle, MS SQL Server, Sybase ASE, and MySQL/InnoDB. The application server in the test was BEA WebLogic. The operating system was Windows 2000 Advanced Server running on a 4-way Hewlett-Packard Xeon server with 2 GB RAM and 24 Ultra3 SCSI hard drives.
eWEEK writes: "Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems."
The whole story. The throughput chart.
Trusted Computing FAQ | Free Dawit Isaak!
i'm trying to decide if you have a legitmate complaint or are just spreading fud, cause i surely am able to do this (though i am in 7.3, but i always remember this working)