Please create an account to participate in the Slashdot moderation system

 


Forgot your password?
Close
typodupeerror
×

PostgreSQL 8.2 Released 147

Posted by kdawson from the faster-so-they-say dept.
An anonymous reader writes to let us know that PostgreSQL 8.2 has been released (bits, release notes). 8.2 is positioned as a performance release. PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.
This discussion has been archived. No new comments can be posted.

PostgreSQL 8.2 Released More

PostgreSQL 8.2 Released

Comments Filter:

  • Watch out, MySQL. (Score:5, Interesting)

    by Anonymous Coward on Tuesday December 05, 2006 @08:45PM (#17122372)
    MySQL has been the dominant SQL server within the open source community. Between its non-standard SQL and it's lack of advanced features, many developers and DBAs are getting fed up. Thankfully, they've been able to turn to PostgreSQL.

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

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

    • Re:Watch out, MySQL. (Score:4, Insightful)

      by Local Loop ( 55555 ) on Tuesday December 05, 2006 @09:44PM (#17123050)
      It's because MySQL runs like dogmeat on FreeBSD, no matter which threading libraries you use. I know, I just switched from FreeBSD to Linux for our database servers. The performance difference was astounding - approximately 60% gain just from switching to Linux.

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

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

      • Re: (Score:2)

        by jadavis ( 473492 )
        It's because MySQL runs like dogmeat on FreeBSD, no matter which threading libraries you use.

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

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

      • Re:Watch out, MySQL. (Score:4, Interesting)

        by Bacon Bits ( 926911 ) on Wednesday December 06, 2006 @02:55AM (#17125402)

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

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

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

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

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

        • Re: (Score:2, Interesting)

          by nconway ( 86640 )
          Some work has gone into improving Postgres' default configuration in recent releases. With 8.2 on my machine, for example, the default configuration allocates:
          • 1MB of work_mem. This is a reasonable figure: because work_mem can be allocated once for every sort operation, each backend can allocate several times work_mem concurrently, so setting it much higher than 1MB could actually consume more memory than would be desirable out of the box, IMHO.
          • 24MB of shared_buffers (maybe less if initdb can't allocate suff
    • The thing PostgreSQL needs is a phpMyAdmin, it has something similar but it doesn't come close. phpMyAdmin makes MySQL accessible to everyone, and I think if an OSS DB is going to be widely used it needs a good admin CP which doesn't require the user to be fluent in SQL.

    • Re: (Score:2)

      by jadavis ( 473492 )
      A more detailed report, even if anonymous, would be helpful. Can you post your findings on the web, such as workload, hardware, etc?
    • At my firm, we switched some of our MySQL Enterprise databases over to PostgreSQL 8.1.

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

  • Performance? (Score:3, Insightful)

    by Ant P. ( 974313 ) on Tuesday December 05, 2006 @09:09PM (#17122652)
    How fast is it against MyISAM? (MySQL's main selling point for a lot of people)

    • Re: (Score:3, Interesting)

      by El Cubano ( 631386 )

      How fast is it against MyISAM?

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

      • Re:Performance? (Score:4, Informative)

        by phoenix.bam! ( 642635 ) on Tuesday December 05, 2006 @09:44PM (#17123044)
        Not only does mysql silently truncate (and I just tested this on mysql 5) If you insert 2006-2-30 into the date field, i just completes the insert and makes the date 0000-00-00. Go Go Data integrity!

        • Re: (Score:2, Informative)

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

          • Re:Performance? (Score:4, Insightful)

            by jadavis ( 473492 ) on Tuesday December 05, 2006 @10:38PM (#17123470)
            However, shipping with that setting disabled doesn't do much to improve MySql's data integrity reputation.

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

          • Re: (Score:3, Insightful)

            by LurkerXXX ( 667952 )
            That's a defense?

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

            It just shows it's background as a toy, not a real database.
          • It's all well and good to add a "be careful with my data now" setting, but that's pretty much a pyrrhic victory if it makes all the old applications break...

            If using this setting requires major remedies to revise applications and retune them, that may be no less work than redeploying on something that has mature support for data integrity...

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

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

        • Re: (Score:2)

          by jadavis ( 473492 )
          Right. These days you can easily run databases with tens of millions of records on consumer hardware.
          • The audit DB (for all application transactions [healthcare industry]) was a single Opteron 240 with a 12 drive SATA RAID and 4GB RAM. As the DB was mostly used for inserts and a few employee productivity or records research reports, the CPU load rarely topped 3%.

        • Re: (Score:2)

          by Nutria ( 679911 )
          five tables (audit records and various archives) larger than 16,000,000 rows.

          While I'm glad you're using Pg, 16M rows is chump change. Come back when your tables hit cardinality 100M.

          • That was exactly my point. 16M rows is not a "large" table. Maybe 10 years ago that would be a lot for common hardware, but I would imagine that most Mid-size companies see 16M+ rows as a common thing. We only had 10 years of live data, and only about 90 employees. I'm sure there are much larger databases than ours was.

      • Re: (Score:2)

        by tacocat ( 527354 )

        I've been playing with Postgres 8.1 and doing some reading online and I think the differences between MySQL and Postgres come down to this

        • They are both ACID compliant
        • Postgres offers more features than MySQL

          • In addition to this there are a variety of curves that are fairly consistent over different versions, hardware, and database sizes... If you have few users then MySQL does well. As you increase the number of users MySQL will start to decline severly. Postgres does not do this. It's flat or nearly

        • I put in a pair of disks as a RAID0 configuration and started testing.
          On a production setup, you may wish to use either RAID1 to speed up reads, or then leave the disks separate. Then, you can move tables and indexes between the two disks so that their load is balanced. With RAID0 you may end up having one disk as the performance bottleneck with the other sitting idle.

    • Re:Performance? (Score:4, Insightful)

      by hey! ( 33014 ) on Tuesday December 05, 2006 @10:02PM (#17123208) Homepage Journal
      You have to be careful when you ask a question like that.

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

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

    • Re:Performance? (Score:5, Funny)

      by greg1104 ( 461138 ) <gsmith@gregsmith.com> on Tuesday December 05, 2006 @11:08PM (#17123732) Homepage
      How fast is it against MyISAM?

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

      • Re:Performance? (Score:4, Insightful)

        by MadAhab ( 40080 ) <slasher@@@ahab...com> on Tuesday December 05, 2006 @11:33PM (#17124034) Homepage Journal
        Should be modded up.

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

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

        • Re: (Score:2)

          by rg3 ( 858575 )
          Forgive me if I'm wrong, but I heard that SQLite becomes very slow if the database grows too much. This comes from Amarok users. Amarok may store its song database (and playlists?) using SQLite, and people with a big song database have reported it can become very slow, and the issue is apparently solved if you run MySQL and tell Amarok to use the MySQL backend instead of SQLite. So, basically, you may need a dumb data store as you say, but being dumb doesn't exclude being big, and with big databases SQLite
          • Why the hell hasn't ODBC caught on in the Linux world? Really? Different libraries for database access, hardcoded? Ugh.

            Granted, ODBC isn't exactly a panacea, but it's definitely better than dlopen(mysqlclient); or dlopen(pgclient);

          • What kind of values are we using for "big?" I haven't noticed any real slowness, and I'm at 12,378 tracks. Still ripping though (now on "H"), so I'll let you know. :-)

    • Re: (Score:2)

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

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

      Example: You need to go 15 places all over town today. Is it faster to take a Fararri and visit in a rand
      • You do realize that you may spend more time calculating the shortest distance than just visiting them in randrom order?

        • Re: (Score:2)

          by jadavis ( 473492 )
          I didn't say "shortest" I said "shorter". And people do it every day in a few seconds of mentally mapping out your destinations, and visiting destinations in groups.

          You could say the same things about a 15-table join. The database can't determine the most efficient join order in an efficient way, but in a very short time it can figure out a more efficient join order than a random order. Hence PostgreSQL's GEnetic Query Optimizer (GEQO), which determines a better join order with a genetic algorithm.

          My argume

  • Gotta love it... (Score:5, Insightful)

    by chill ( 34294 ) on Tuesday December 05, 2006 @09:35PM (#17122966) Journal
    PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.

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

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

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

      Charles (had enough marketing-speak for this year)

    • Re: (Score:2, Insightful)

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

      Of course, using the extra stuff the databases support (PL/SQL, T-SQL, etc), we manage. But for example, the "workaround" for the window functions are not only ugly, but often quite misunderstood, on top of being difficult to use through dynamic sql (if thats your cup of tea). I keep seeing people using inefficient paging methods in SQL Serv

      • Re: (Score:2, Insightful)

        by Anonymous Coward
        Critical? CSS is critical for the web? Alternative ways of doing something that's been done forever are critical?

        You fail completely to understand the CONCEPT at work here. 'Critical' means you CANNOT DO SOMETHING WITHOUT IT. You have failed -- by your own admission -- to even state, let alone prove, that anything you're talking about is more than a nice alternative, let alone critical.

        Who the hell modded that insightful? Give me a break. dictionary.reference.com, now this post had better get a +5 inf

    • Re: (Score:3, Interesting)

      by rycamor ( 194164 )
      I mean... did this OP rush to push out a lackluster FP on PG, or what?

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

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

    • Re: (Score:2)

      by hey! ( 33014 )

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

      You are making some assumptions here. First, you are assuming that a feature cannot be implemented before it makes it into a standard, which is not necessarily the case. There are other paths, e.g. the idea gets published in a journal, the relational theory geeks at several leading vendors pick it up, several incompatible implementations are created by different vendors, and

      • Re: (Score:3, Interesting)

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

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

        • Re: (Score:2, Interesting)

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

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

          • Re: (Score:3, Interesting)

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

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

            In fact, several solo-developer projects have implemented it on the logical level much better than your typical SQL vendor. The problem is that

            • Re: (Score:2, Interesting)

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

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

              Name one, and make sure it's one that's disallowed NULLs completely. Date,
              Darwen and Pascal's fear of recording states of ignorance is ill-founded in
              real-world conditions. Codifying that f

              • Re: (Score:3, Interesting)

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

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

                > Name one, and make sure it's one that's disallowed NULLs completely. Date,
                > Darwen and Pascal's fear of recording states of ignorance is ill-founded in
                > r

              • Re: (Score:3, Interesting)

                by jadavis ( 473492 )

                Name one, and make sure it's one that's disallowed NULLs completely. Date, Darwen and Pascal's fear of recording states of ignorance is ill-founded in real-world conditions. Codifying that fear isn't even well-founded in last

                CJ Date actually tried to generalize the concept of NULLs into "special values" in a domain. He argued that NULLs cause confusion in 3VL because NULL can mean different things. Sometimes it means "unknown", other times it means "not applicable". And in an outer join, it's not clear at a

        • Re: (Score:2)

          by hey! ( 33014 )
          Well, that's an interesting hypothesis. The question is, is it refutable?

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

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

    • Re: (Score:2, Informative)

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

      • Re: (Score:2)

        by rycamor ( 194164 )
        Why is this modded 0? A nicely relevant post.
        • Off-topic. default mod point for an 'Anonymous Coward' is zero... even if it is a good post :-) Most moderators don't like to give mod points to ACs because they would rather reward or punish registered users... otherwise it's like throwing away good mod points.
    • PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.

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

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

      Far from it. (PostgreSQL lacks many of the advanced fea

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

        True, but if you lack a quality DBA and the hardware necessary to get the maximum performance from Oracle or DB/2, then PostgreSQL is a fine alternative. Oracle in particular needs a lot of care and attention to keep it performing at its best, and if you've forked out for the licenses you probably want to get them most from it. It's in situations where the budget or other resources rule out the big

  • Replication? (Score:5, Informative)

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

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

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

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

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

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

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

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

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

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

    • Re: (Score:2)

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

    • Re:Replication? (Score:5, Informative)

      by nyamada ( 113690 ) on Tuesday December 05, 2006 @10:45PM (#17123508)
      We use Slony. It is a delicate beast, but works quite well if you take time to read the limited documentation. You can use a kludge to keep schema changes in line: if you execute all schema changes through EXECUTE SCRIPT statements on the master server, all the slave nodes will get the schema changes. As for large object support, you're right; it is a problem.

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

    • Re:Replication? (Score:5, Informative)

      by oGMo ( 379 ) on Tuesday December 05, 2006 @10:50PM (#17123544)
      Slony also doesn't replicate "large objects"; I don't know what they are,

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

      but as a MySQL admin

      Oh, right. Not really a DBA

      Let's see:

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

      Others listed are older and not relevant.

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

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

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

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

      In summary: meh.

      • Re: (Score:2)

        by jadavis ( 473492 )
        [ ignoring some of the unnecessary rudeness ]

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

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

        PostgreSQL replication will force you to consider the real consequences of your choices in various situations. MySQL replication will say that it's worki
        • The handling of "large columns" gets a bit better in Slony-I 1.2; in earlier versions, the replication engine would blindly grab groups of 100 tuples into memory to get ready to replicate them. If you have big columns with 50MB of data in them, this could lead to loading 5000MB of data into memory, and then throwing it across the wire to replicate it. What with a couple copies getting made (the copy loaded into memory, then turned into a query to be submitted to the subscriber), this could lead to a prett

      • Re: (Score:1)

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

        • Re: (Score:2)

          by oGMo ( 379 )

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

      • Re: (Score:1)

        by Curly ( 49104 )
        Why anyone modded the parent Informative, I have no idea, but I'd like to respond to this:

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

        [...]

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

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

        From oth

        • Re: (Score:3, Informative)

          by Bacon Bits ( 926911 )
          Binary Large OBjects (BLOBs) are table columns with individual entries are larger than several thousand bytes (typically, those that span more than one page). BLOBs are part of the ANSI SQL standard, AFAIK, which is why it is surprising you'd never heard of them. They differ from MySQL's 'blob' datatype, which is just a big TEXT field. The design of the database (PostgreSQL, DB2, Oracle, T-SQL/MS SQL, etc.) prevents such objects from being stored in the same method that other objects are stored, either b
      • Don't forget the ultimate problem with pgsql: the users.

        GP asked a simple question about what replication strategies are used by pg shops, and some asshole like you responds in a tone like yours.

        You could have just answered the question. It wasn't necessary to be a dick about it.

        Also, you might be interested to read a bit about MySQL Cluster [mysql.com] which is different from their replication solution. Pretty neat stuff.

        Also, I do agree with you that GP gave no indication that MySQL was failing to meet their need

    • Re:Replication? (Score:5, Informative)

      by jadavis ( 473492 ) on Tuesday December 05, 2006 @10:52PM (#17123560)
      "Slony-I", but from what I could find it doesn't replicate schema changes to the slaves

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

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

      Slony also doesn't replicate "large objects"

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

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

  • Awesome (Score:3, Interesting)

    by tcopeland ( 32225 ) <tom AT thomasleecopeland DOT com> on Tuesday December 05, 2006 @11:32PM (#17124010) Homepage
    > 8.2 is positioned as a performance release.

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

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

  • Way to go PostgreSQL (Score:3, Informative)

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

    Cheers,

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

  • Reporting (Score:3, Informative)

    by mccoma ( 64578 ) on Wednesday December 06, 2006 @12:10AM (#17124374)
    PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.

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

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

  • I'm using postgresql since the 5.x days, when it indeed was slower than mysql.
    But as a developper, I never accepted the shortcomings of the non-standard and really incomplete sql syntax of mysql.
    The command line tool psql with tab-completion of sql syntax and less style output of query results convinced me to switch in a second.
    PostgreSQL never let me down, whereas I often had problems with mysql databases. (e.g. non working databases after upgrades)
    Not to mention the semi-free open-source licens
    • Not to mention the semi-free open-source license of mysql.

      GPL [mysql.com]?

      • Re: (Score:1)

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

        • Re: (Score:2)

          by shani ( 1674 )
          Mysql has a GPL/Commercial dual licensing model. And because connection to mysql means linking to the client, which is "derivative work" in terms of the GPL, you can only use GPL'ed software with mysql. Unless you pay them to use their commercial license of course.

          Well, that's GPL. You appear to be arguing that GPL is only "semi-free" (your own words).

          But if you don't like GPL, MySQL allows you to use any of the following licenses with clients:

          http://www.mysql.com/company/legal/licensing/foss- exception.htm [mysql.com]

          • Re: (Score:1)

            by euice ( 953774 )
            By semi-free i was referring to the dual licensing model. If you want to use mysql in a closed source environment (and do not want to reprogram the client libraries) you need to pay royalties. That's what I meant with "semi-free".
            Of course I consider the GPL itself as "free enough" for the server (unlike some of the bsd fanboys).
            But as a freelancing developper, I often have to develop closed-source applications and for that I need at least LGPL client libraries, which mysql doesn't provide AFAIK.
            In my
  • ... is create a smallint index on an int column ;-)

Slashdot Top Deals

It is easier to write an incorrect program than understand a correct one.

Close