Follow Slashdot stories on Twitter

 


Forgot your password?
Close
typodupeerror
×
Data Storage

PostgreSQL 7.4 Released 451

Posted by michael from the vacation's-over dept.
Christopher Kings-Lynne writes "PostgreSQL 7.4 has just been released. The list of new features is impressive and includes greatly improved OLAP performance among many other speed improvements."
This discussion has been archived. No new comments can be posted.

PostgreSQL 7.4 Released More

PostgreSQL 7.4 Released

Comments Filter:

  • Ah! Just in time (Score:2, Interesting)

    by hattig ( 47930 )
    For me to migrate my company's systems from MySQL to Postgres. Shame that my nice O'Reilly book won't cover the new features ... but I probably am not so advanced as to need them!

    Now how does it compile and run on FreeBSD / x86-64?

  • Rock on! (Score:5, Interesting)

    by JohnA ( 131062 ) <johnanderson.gmail@com> on Tuesday November 18, 2003 @02:02PM (#7504088) Homepage
    I use PostgreSQL extensively, and I have had a hard time convincing my-mySQL (I'm so clever) exclusive friends to give it a try.

    One thing that should be noted is that the JDBC drivers (http://jdbc.postgresql.org [postgresql.org]) are now among the best I've used. For those developing Java apps, the choice is now even more clear.

    • Re:Rock on! (Score:2, Insightful)

      by Evil Adrian ( 253301 )
      Are they having a hard time convincing you to try mySQL, too? ;-)
      • Yeah, yeah... I know.... Once a zealot, always a zealot.

        In all fairness though, all of the OSS apps I am developing have both a PostgreSQL and a MySQL DAO implementation.

        But I am just as religious as the next guy... :-D

    • Re:Rock on! (Score:5, Interesting)

      by IpSo_ ( 21711 ) on Tuesday November 18, 2003 @02:15PM (#7504216) Homepage Journal
      Show them this:

      MySQL Gotcha's [sql-info.de]

      If they still don't seriously consider at least switching away from MySQL, I would be very scared.

    • Re:Rock on! (Score:2, Informative)

      by mattcasters ( 67972 )
      I beg to disagree on the JDBC claim.
      As long as Statement.setFetchSize() or Statement.setFetchDirection() are not supported, it's close to useless to me.
      Try going over a couple of milion records if you doubt it's really needed.
      Not that MySQL's driver is any better in this regard.
      Real JDBC drivers like the ones for Oracle, DB2 and (gasp) even SQL Server support these hints without a problem.

      Matt

      • Re:Rock on! (Score:3, Informative)

        by slamb ( 119285 )
        I beg to disagree on the JDBC claim. As long as Statement.setFetchSize() or Statement.setFetchDirection() are not supported, it's close to useless to me.

        Cursors are supported now. Nic Ferrier wrote a patch for this back in April, and I think it got applied in the beginning of May. There's a trick I can't remember right now to enable it (maybe setting the result set type/scrollability first with JDBC2 methods), since it's not quite as efficient for smaller queries. I'm sure the people on the pgsql-jdbc mai

    • >One thing that should be noted is that the JDBC drivers (http://jdbc.postgresql.org) are now among the best I've used. For those developing Java apps, the choice is now even more clear.

      1. The quality of drivers are only one part of your decision when choosing a DB.
      2. How do they compare to commerical drivers such as from MS or Oracle?
      • Very true.

        Having used both ORA and MSSQL over JDBC, I can say that when using a Type 4 driver, PostgreSQL's JDBC support is easily at the same level as ORA and MSSQL. Although I have only used JSQLConnect on the MSSQL side.

        As for DB choice, I agree. But I have spent WAY too much time debugging & working around buggy drivers to not make it a significant part of my decision process.
    • I just started getting into open source database development as a hobby. While my work mate recommended postgreSQL, I decided to go with mySQL.

      Call me a lemming, but the huge amount of combined Linux-Apache-PHP-mySQL (LAMP [onlamp.com])documentation is what convinced me to go with mySQL. Once I become more comfortable with this setup, I'll give postgreSQL a try. But for a newbie, mySQL seems friendlier at the moment, if only because of the wide amount of help available.

  • Impressive but... (Score:3, Interesting)

    by slayer99 ( 15543 ) on Tuesday November 18, 2003 @02:02PM (#7504089) Homepage
    ....still no native replication. MySQL has this one single advantage over Postgres.
    Oh, raw disk use would be nice too.
    • Oh, raw disk use would be nice too.

      Why? And how, for that matter. Oracle abandoned this misfeature long ago, how would postgres benefit from it?

      • >>Oracle abandoned this misfeature long ago
        Must be some other Oracle, not the one I have been using for 12 years.
        raw file systems in Oracle provide 10-15% performance improvement. Of course the downside is they are pain to maintain.
        But for something like Oracle online log files this is still the way to go.
      • Last I heard, Oracle still has raw disk as an option, mainly for performance reasons but also for parallel Oracle. With direct I/O or Quick I/O (Veritas), you can get near the performance of raw devices, but raw disk is still marginally faster.

    • Re:Impressive but... (Score:5, Informative)

      by Paul Jakma ( 2677 ) on Tuesday November 18, 2003 @02:31PM (#7504333) Homepage Journal
      still no native replication.

      One of the new features in 7.4 is the replication technology contributed from eServer.
      • Where is the notice for this? The only thing I find in the release notes on replication is:

        "New /contrib/rserv replication toolkit (Vadim)"

        I can't seem to find it any place else? The reason I went with MySQL over PostgreSQL was the desire/need for easy replication. From what I know, PostgreSQL has some third party tools, but I wanted something that was apart of PostgreSQL itself, and this would be nice to see.

    • Re:Impressive but... (Score:4, Insightful)

      by bovinewasteproduct ( 514128 ) <(moc.liamg) (ta) (iikralcg)> on Tuesday November 18, 2003 @02:50PM (#7504525) Homepage
      Huh?

      ERServer was released open source months ago. Check out GBorg [postgresql.org] for more information.

      And why do YOU need raw disk access? The PostgreSQL developers belive (and rightly I think) that the operating system can do the caching better than they can. Why re-write the wheel? Operating systems have came a long way in the last 10 or 12 years. Days of slow access to the disk are long gone.

      BWP

    • Re:Impressive but... (Score:3, Informative)

      by gabe ( 6734 )
      MySQL has raw disk usage thanks to InnoDB. The only draw back is that it's slower than using regular InnoDB files in an actual filesystem... So, MySQL clearly doesn't have any advantage over PostgreSQL since it has raw disk support.

      Looking for replication? Use the eRServer replication that comes with PostgreSQL now.

  • Yummy, or about time. (Score:5, Informative)

    by blanks ( 108019 ) on Tuesday November 18, 2003 @02:03PM (#7504093) Homepage Journal

    IN/NOT IN subqueries are now much more efficient.

    Queries using the explicit JOIN syntax are now better optimized.

    New multikey hash join capability.

    Cursors conform more closely to the SQL standard.

    Sounds like they pushed closer to the SQL standards, good job guys.

    Does anyone here know more about this "New client-to-server protocol" they speak of?

    • Re:Yummy, or about time. (Score:2, Interesting)

      by Anonymous Coward
      I helped debug the new client/server protocol along with Anders Johnson. Query results > 4k (eg, retrieving everything at once, or large binary fields) can be compressed with gzip or bzip2. If you run postgresql on a dedicated box, this can make a significant difference in network transport speed.
    • > IN/NOT IN subqueries are now much
      > more efficient.

      That'll make RubyForge [rubyforge.org] (and other GForge installations) a bit faster, since GForge has a fair number of IN clauses:
      [tom@hal gforge]$ grep -rn " IN (" gforge/ | wc -l
      34
      [tom@hal gforge]$
      Looks like the survey and the searches should both benefit. Sweet.
    • Does anyone here know more about this "New client-to-server protocol" they speak of?

      They needed to change the protocol to support such nifty features as proper error codes (SQLSTATE I belive) and better transaction control. I'm hoping that we'll see multi-stage transactions in 7.5.

      Older clients can still talk to newer servers, they just won't support the new features.

  • Question: discuss among yourselves (Score:4, Interesting)

    by Uma Thurman ( 623807 ) on Tuesday November 18, 2003 @02:03PM (#7504097) Homepage Journal
    I run postgres on my own database servers (when I'm not making movies, that is). Now, there's a distributed database project associated with Postgres, trying to add replication into the databases' bag of tricks.

    Lotus Notes implements e-mail and lots of other things on top of a database engine that performs replication. So, could Postgres be used to develop a Lotus Notes type application with replicated databased for e-mail, calendars, team rooms, etc?

  • So I finally get off my lazy ass, bring out my old P166-MMX, download postgres 7.3.4, compile it (on a 166!), install it, issue my first select statement, check slashdot for a break and find this!

    Grrr, maybe it's time to buy a faster DB server.


  • Not to ignite the flames at all (actually, I would prefer a straight answer)...

    We have one client who uses postgres, the rest are all mysql-based... Is there an unbiased (as far as can be) comparison ?

    Simon

    • No flames... (Score:5, Interesting)

      by Anonymous Coward on Tuesday November 18, 2003 @02:47PM (#7504489)
      We have one client who uses Linux, the rest are all Windows-based... Is there an unbiased (as far as can be) comparison ?

      Now, see your problem?

      Neither PosgreSQL or mySQL are full, complete, and utterly perfect implemtations of a database. Neither is Oracle, BTW.

      mySQL got a HUGE push some time ago. Back then, mySQL couldn't be beat for handling read-only (Actually, highly read-almost exclusively always). mySQL was a champ when you had a web site, mostly static catalog of products (for example), and had really limited demand for SQL (Like one query that read 'select * from catalog;')

      That basis of comparison is no longer true.

      So, at the time, hords of little corporate minions lined up and specified mySQL. Not a bad bet at the time, but open mindedness only seems to happen once in computer circles. Day 1 you have a need, day 2 you actually research available solutions, and day 3 you declare a "winner" and it is forevermore cast in stone as the "one true solution". The fact masses of people tend to go thorugh the same process at basically the same time doesn't help. Thus the broad noise that mySQL is "the Answer(tm)".

      Anyway, postreSQL has always sought to compete in the full function space. Oracle was/is a much better "comparison" to postgreSQL than mySQL.

      Now, both mySQL and postreSQL have improved over time - greatly. postgreSQL seems to be focused on getting things "correct", while mySQL doesn't seem so concerned. Bascially postgreSQL will not provide a feature, while mySQL will hacking it together in some bizzare way (re: early "transaction" handling). mySQL has quite a few anti-social behaviors. Over time, their refinement of those various behaviors drive certain development costs and create some degree of lock-in dependency (a continuing basis for self-justification).

      Bottom line, if you invested in learning and implementing mySQL, and it is still working for you, then there is absolurely no need to be concerned with postgreSQL yet.

      If you are in the database selection mode, you should surely look towards postgreSQL and try to de-hype yourself from any pro-mySQL bias. Hype has inertia and much of the pro-mySQL hype is based on old comparisons and narrow needs. Yes, evaluate both, but don't assume mySQL or postgreSQL is "better" based on what you hear.

    • Do you want speed? Or would you prefer your data to be consistent and safe?

      They are tools for different jobs:

      MySQL is intended for systems where the data is uploaded, and thereafter never changes significantly - eg static data accessed via the web.

      PostgreSQL is intended for things like payroll systems where some values persist for years, while others change daily.

      Without triggers, you cannot expect to maintain data integrity with online data input and a wide range of input methods. ie any system with an

  • postgres isn't used in the enterprise (Score:2, Funny)

    by Anonymous Coward
    because no one can effectively pronounce it.

    Is it pronounced "Post Grays"?

    "Post Grez?"

    "Post Gress?"

    "Post Gray?"

    "Post Gruh?"

    I've also heard people refer to it as "progress"...

    Seriously, though, as an IT manager, you can't 'sell' the concept of using an OS RDBMS that you can't even pronounce. People use MySQL because its pronounceable. My Sequel. Simple. Yellow. Different.

    Those guys over @ Postgreays need to figure out a better friggin name for their RDBMS if they expect it to be used in the enterpris

  • Autovacuum (Score:3, Interesting)

    by Space cowboy ( 13680 ) on Tuesday November 18, 2003 @02:07PM (#7504130) Journal
    Ok, this is nice - One thing that's always put me off postgres is the need for vacuuming. It seems to me it's a labour-intensive task that the computer ought to do on its own, without my help :-)

    Simon.
    • It's still a labour-intensive task - for the dbms. While vacuuming you can't really do much else with the database.

      • Re:Autovacuum (Score:4, Informative)

        by bmarklein ( 24314 ) on Tuesday November 18, 2003 @02:26PM (#7504303)
        While vacuuming you can't really do much else with the database.

        Not true. Starting in 7.3, the default version of VACUUM no longer locks the table. From the 7.3 docs [postgresql.org]:

        Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. VACUUM FULL does more extensive processing, including moving of tuples across blocks to try to compact the table to the minimum number of disk blocks. This form is much slower and requires an exclusive lock on each table while it is being processed.

      • Re:Autovacuum (Score:2, Informative)

        by kcbrown ( 7426 )

        It's still a labour-intensive task - for the dbms. While vacuuming you can't really do much else with the database.

        That's true if you're doing a VACUUM FULL. But that is now something that generally needs to be done only rarely (this will depend greatly on how you use the database). The database will now re-use freed tuples (VACUUM FULL compacts the physical table, thus eliminating such free space entirely and causing new data to be appended to the end of the physical table), so all that's usually n

    • you've got to be kidding. I guess if the couple seconds it takes to set up a cron job to run the vacuum is labor intensive. I didn't think so.

      • Hmm. re-reading my post, I can see why you replied like you did, so let me spell out the case I have:

        There's a DB with (currently) about 30 million rows in it, it's the audit trail for a clients adserver. Queries are run on the db by their clients to figure out their usage, click-throughs etc. over periods. The ad-server gets anything from 0 to ~20 inserts per second across some 200 sites, and depending on the client policy, is cleared daily,weekly,monthly or yearly of that clients data.

        Now, my understand
        • Now, my understanding of the vacuum command was that it effectively took the DB offline (not good with the hit-rate I have), and my understanding of 'auto-vacuum' was that it would negate that effective downtime. It appears that that is not the case.

          Normal VACUUM commands do not lock tables as of 7.3. Only the full vacuum command does this, which you probably only need to use when permanently retiring a client, rather than just rotating out their data, as the lock is used to actually repack the database
    • My postgres system does it on it's own, without my help. Ok, I had to help it by scheduling vacuumdb to run each night, but once I did that, I can forget about it.
    • Any DBMS has to do what Postgres calls Vacuuming. MySQL does it, Oracle does it.

      The difference is that Postgresql allows you to control exactly when you do this. You can choose to vacuum the db when the load is reduced, perhaps overnight.

      Other database are not as flexible.

      Of course, the autovacuum utility in the /contib dir does a fine job if you dont want to schedule your own vacuums.

    • Re:Autovacuum (Score:5, Informative)

      by Zeut ( 24694 ) on Tuesday November 18, 2003 @03:47PM (#7505123) Homepage
      Ok, there is a lot of talk about vacuum what it does / doesn't do and what effect autovacuum has. Here are the details (FYI, I wrote pg_autovacuum).

      Recent versions of postgresql don't take your database offline during vacuum. However, the vacuum process is an I/O intense process and can still, even 7.4, slow the server significantly while it's running. Work is has alredy been done in the 7.5 development tree to address the I/O storm created by vacuum.

      Typically, you setup cron to run vacuum your entire database nightly. This is fine, except it has two main problems. 1) It wastes time vacuuming large tables that probably don't need it (think audit train table that only gets inserted into). 2) It probably doesn't vacuum tables that are constantly updated often enough, which results in bloated data files, and slower queries.

      The new pg_autovacuum daemon addresses both of these concerns by monitoring database activity (using the stats system). When it sees that a table has has been modified enough to warrant a vacuum then it does so, when it sees that a table might benifit from a analyze only, then it does that. And when a large table doesnt' need to be vacuumed, it doesn't vacuum.

  • Windows (Score:5, Interesting)

    by kannibal_klown ( 531544 ) on Tuesday November 18, 2003 @02:07PM (#7504134)
    I'm sure I'm gonna get modded down for this, but does anyone know when is there gonna be a version that can run in windows natively (without using Cywin)?

    I ask because we are FORCED to use Windows boxes at work, and they gave all of the developers 2. We can't reformat and put linux on (or do a dual-boot) because they check to make sure everything is status-quo. And right now the atmosphere around here is not the greatest, so I'd rather not risk anything with the PHB's by trying to trick them.

    I usually have my 2nd machine as a server running mySQL as a testbed for my database apps. I'd LOVE to switch to Postgresql, but I'm limited as to what I can do.

    Any idea when a Windows native version will be available?
  • Can anyone comment on PostgreSQL OLAP? Does anyone use it for day-to-day business operations? How well does it integrate with mainstream OPAL analysis and repoting applications? Hyperion? Congnos? Business Objects?

  • Why use PostgreSQL over MySQL? (Score:5, Interesting)

    by IpSo_ ( 21711 ) on Tuesday November 18, 2003 @02:11PM (#7504169) Homepage Journal
    Because they care about your data, among other things! You could have the fastest database server in the world, but if you find your data is corrupt, or truncated without warning, it doesn't do you much good.

    Here is huge list of MySQL Gotcha's that absolutely floored me when I first read it. In my opinion, a "gotcha" in regards to a database is a "Bad Thing(tm)"

    MySQL Gotchas" [sql-info.de]

    • Mysql speed (Score:3, Informative)

      by vlad_petric ( 94134 )
      Your comment is implying that mysql is actually faster than postgresql. That's simply wrong.

      Yes, if you run simple queries in a single user scenario you do get better performance with mysql than with pg. With more complex queries and more users however, the simplistic query optimizer and concurrency manager that mysql has makes it perform worse than pg.

  • PgAdmin 3 (Score:2, Interesting)

    by DAldredge ( 2353 )
    PgAdmin 3 is also ready and in now multiplatform.

    • Re:PgAdmin 3 (Score:4, Insightful)

      by stoolpigeon ( 454276 ) <bittercode@gmail> on Tuesday November 18, 2003 @02:22PM (#7504274) Homepage Journal
      And it is very, very nice. If you have people resistant to using pgsql (which I like a lot personally) pgAdmin III will give them all the GUI stuff they are used to from programs like SQL Server Enterprise Manager.

      The postgreSQL community is extremely helpful, key developers are very active in helping out users and addressing issues rapidly. It is a project that just exemplifies what is good about open source.

      I am compiling 7.4 on my development server right now in preparation for moving our production server soon. I guess maybe I sound like a fan boy but as a database administrator I just can't over emphasize my joy at getting to work with such an excellent product.

    • I only wish the phpPgAdmin tool was nearly as useful as the phpMyAdmin tool.

  • Full text searching improved and other goodness (Score:5, Informative)

    by SuperBanana ( 662181 ) on Tuesday November 18, 2003 @02:14PM (#7504204)

    Full text searching also got another overhaul- I plan on messing around with it when I get some free time. They've included a .sql file you can just import into an existing DB.

    The real power here is that the index is quick to update, and as a result, can be done in real-time via triggers and stored procedures- neither of which you can do with MySQL :-) The new release is also even more SQL compliant- something else MySQL can't claim. PostgreSQL is both SQL92+98 compliant if I recall.

    It can't be said enough- PostgreSQL is now MUCH faster...and due to features like stored procedures, triggers, and some of the best locking available combined with some of the best transaction support, it's actually far faster at many of the same tasks if you take advantage of these greater abilities.

    Even back as early as '99, PostgreSQL absolutely mopped the floor with MySQL when as little as 10% inserts or updates were thrown into a select test. Why? Piss-poor locking and zero transaction support. The stuff you have to do in the application layer to make up for proper(or ANY) transaction support will make most benchmarks completely pointless.

    MySQL always has, and always will be, a DB best suited for blogs and 2-guys-in-a-garage; it's slapped together, has a low featureset, and is not standard-compliant. PostgreSQL is not an enterprise fish(replication still needs work if I understand it correctly)- Oracle, DB2 etc have that market pretty well covered- but it's great for everyone else who isn't, say, a multibillion $ company...if those people just bothered to have an open mind instead of pointing their fingers at benchmarks showing MySQL running out of an in-ram-only table can select 50,000 rows faster than PostgreSQL can, and whining about how they need to make a cron job to vacuum/vacuum analyze tables at an appropriate time(with autovacuum, also in this release, there goes that excuse!)

    • I hear that. When you're talking about a large database (More than 50k rows, things closer to 400M rows/table) Postgres is the only way to go after you figure out that MySQL has NO functionality in the realm of RDBMS. In fact Postgres is even better when you find all of the different things you can do with your data afterwards (via custom aggregates, custom sequences, functions, triggers, views, etc.) that the dorks in their garages who only 'SELECT * FROM BLOG' are even further pushed out of the picture.
    • If you think PostgresSQL is now faster than MySQL, fine, post a real benchmark, with both databases being properly tuned.

      But MySQL has had transactions and row-level locking for quite some time now, so the fact that you claim that it doesn't indicates that you don't know what you are talking about.

      Futhermore, why must you bring up your dislike for MySQL in a message about Postgres' great new features? If Postgres really is improving, great! Talk about that! Why must you at the same time talk about how suc

      • Re:Enough of the anti-MySQL garbage (Score:2, Insightful)

        by Anonymous Coward
        Untrue? Since when has MySQL had stored procedures? Do you even know what stored procedures are? Do you run 10 database queries on every web page?

        How about subqueries? Do you run a query and then run queries on the results in the application?

        Simple selects might be slightly faster in mysql, but very few applications do a single simple select at a time.

      • Re:Enough of the anti-MySQL garbage (Score:5, Informative)

        by thing12 ( 45050 ) on Tuesday November 18, 2003 @03:12PM (#7504788) Homepage
        But MySQL has had transactions and row-level locking for quite some time now

        Sure, row-level locking is nice -- even MSSQL has that. PostgreSQL has MVCC - so that writers never block readers and likewise. Complete data consistency (i.e. repeated reads give the same results) from the start of a transaction to the end of it. Can MySQL do that? (I am actually asking....)

    • I am truly intrigued about PostgreSQL. I have had a lot of experience with Oracle so I am familiar with higher level functionality. However, my only experience with free databases is with MySQL. While I enjoy its simplicity and speed, I would also like to have the flexibility to upgrade to the more powerful features as needed (triggers, stored procedures, etc.). I also have concerns for stability and security with MySQL. Therefore I have a few questions for those who would respond:

      • Does PostgreSQL hav
      • Yes, there are plans to make a native windows port -- it's actually about 75% of the way there.

        The big issues now are safety. For example, sync() has traditionally been used for checkpoints to ensure everything is on disk. Windows does not seem to have an equivelant (though several close options).

        If your interested in helping out, please send a note to pgsql-hackers for a list of current issues for win32.
      • Does PostgreSQL have a good GUI component for direct database manipulation?

        Several. [postgresql.org] Take your pick. (Check the administrative link off the nav, and also look through the developer tools.)

        I understand that PostgreSQL requires cygwin to run on a Windows platform. Since my company is a Windows shop for now (I have not yet been able to convince them to move to Linux) this is an important issue for me. How big of a performance hit is there for running through the cygwin interface? In other words, is Postgre

      • > Does PostgreSQL have a good GUI component for direct database manipulation?

        Traditionally, this has been the job of third party developers. SQL Server is the one exception as it tries to make you dependent on the GUI tools. Still, if you want a GUI tool, both pgAdmin [postgresql.org] and my own DataDino [datadino.com] are good alternatives.

  • Replication Replication (Score:4, Insightful)

    by augustz ( 18082 ) on Tuesday November 18, 2003 @02:18PM (#7504238)
    For some applications with a chance of growth I've had two issues with Postgresql. One is that despite the fact that they have talked about being an "enterprise level" database for ages, we found that in any kind of swift moving transaction enviroment we had to VACUUM pretty regularly. How they expected folks to leave pgsql running over extended periods of time (months -> years) is beyond me. Looks like they may have solved it. It will be interesting to see if the systems can take a pounding and stay up 24/7 for a while without slowing to a crawl.

    The other issue has been replication. With mysql this has saved our bacon more then once. Nead to do intensive analysis on live data and don't want to disturb active system? Set up a nice slave and query away.

    Want basic fault tolerance? Set up a slave, you have a live mirror of the data.

    Have lots of queries coming in (load balance the reads at least).

    PostgreSQL now has some type of replication available from PostgreSQL Inc, but it looked to me like somewhat of a hodge podge of perl, triggers and who knows what else.

    I think I'll try it out, and if I can get the same replication speed as I do with a mysql array I'd switch over, but first glance it didn't look like I would. Anyone compared the replication performance yet (and ease of setup, I was very impressed with mysql in this regard).

    • re: replication ... the version of eRServer that PgSQL, Inc released OSS earlier this fall is Java based, actually ... only perl is for the admin scripts, and the OSS community is in the process, I believe, of getting rid of that also ...

      See eRServer on GBorg [postgresql.org]

      Jan Wieck is also just in the process of re-writing replication based on the experiences of eRServer, again, as an OSS project, and it can be viewed at:

      Slony-1 on GBorg [postgresql.org]

      • Slony-1 looks very interesting, I browsed the CVS doc description of it all (checked in 4 hours ago). Hadn't seen it before!

        Not a lot of code yet but the document was very thoughtfully worked through. Be curious how quickly helper utilities could fail over to a slave given the configuration changes needed. Good stuff though, look forward to the evolution of the project. I think this may convince me to prototype the next app in pgsql. It's the load balancing portion of things that interests me the most by f
    • PostgreSQL now has some type of replication available from PostgreSQL Inc, but it looked to me like somewhat of a hodge podge of perl, triggers and who knows what else.

      Strange. That's how MySQL looks to me...

  • Best New Feature (Score:5, Informative)

    by corby ( 56462 ) * on Tuesday November 18, 2003 @02:25PM (#7504289)
    New autovacuum tool

    The new autovacuum tool in "contrib/autovacuum" monitors the database statistics tables for "INSERT"/"UPDATE"/"DELETE" activity and automatically vacuums tables when needed.

  • I have never had the pleasure of playing with PostgreSQL - does it allow you to partition your database (multiple buckets) so that a flat search on all fields returns in a reasonable period of time (a la Oracle 9i Rac)? Along those lines does it allow raw mode access, as well as file access?

    I would be interested in getting my hands dirty if it would be worth my while.

  • Did anyone notice today that.... (Score:3, Informative)

    by icejai ( 214906 ) on Tuesday November 18, 2003 @03:04PM (#7504701)
    MaxDB is out?

    MaxDB is a sort of merger between sapdb and mysql. As of today, MaxDB includes features such as:

    * Views * Server-side cursors * Stored procedures and triggers * Automatic failover (to a standby server) * Scheduling and automatic messaging on alerts * Snapshots * Archive tables * Synonyms

    And these are features in addition to mysql's feature set!

    Check it out guys! http://www.mysql.com/products/maxdb/index.html

    But don't be in too much of a rush to upgrade, mysql interoperability is slated for Q1 of 2004.

    Enjoy!

    • Re:Did anyone notice today that.... (Score:2, Funny)

      by Anonymous Coward
      these are features in addition to mysql's feature set!

      Wait - MySQL has a feature set?

      I thought that it's lack of features was what made it better than everything else.

      damn.. now I'm all confused.

  • PostgresQL is slow... (Score:4, Insightful)

    by puppetman ( 131489 ) on Tuesday November 18, 2003 @08:03PM (#7507326) Homepage
    We are in the midst of moving our databases away from Oracle. There were three contenders: MySQL, Postgres, and Matisse (OODBMS).

    Speedwise, PosgreSQL trails the pack by a fair bit. Sometimes it would be comparible to Oracle, and other times it wouldn't be without a fair bit of tuning. Outer-joins, for example; the optimizer can't seem to make heads or tails of it.

    I spent two years lurking on the Postgres lists, and when doing performance testing, was asking for help tuning queries and the database in general; this isn't a statement made based on, "I tried it once, and it didn't work."

    The guys on the list (especially Tom Lane) were very helpful and polite, but I just couldn't get reasonable performance out of the database without doing some serious SQL-rewriting (our CTO thinks that relational databases require too much tweaking already; putting optimizer hints into the queries is just too much).

    Overall, the database is great - great feature set, great developers, and a good support community, but the optimizer is not efficient enough (search for the word optimizer in the PostgreSQL lists, and you'll find hundreds of posts where the optimizer is doing a sequential scan and ignoring indexes when it should be using those indexes).

    MySQL (4.0.16, using InnoDB tables) has foreign keys, transactions, etc. I haven't been able to crash it yet (I miswrote a query on purpose, and let it run over 2 days at 99% CPU, and the machine stayed up, and is still up a week later).

    MySQL doesn't have triggers or stored procs, but as a DBA and senior developer, I can honestly say that's a good thing.

    - if you modify a table that a trigger or stored proc uses, chances are the trigger and stored procedure are invalidated quietly behind the scenese - the database doesn't tell you until you call the stored procedure or execute a statement that causes the trigger to be executed.

    - debugging a stored procedure or trigger is not easy.

    - people tend to forget about triggers and stored procedures; they're hidden logic that can cause no end of problems.

    - triggers and stored procedures are (in most cases) database-dependant; they are a huge hinderance when moving to another database. We have 12,000 lines of Oracle stored procedures. I dislike them.

    - the database is for data storage. It's not for application develoment. Keep the business logic in the application, and the data-storage logic in the database. Oracle is trying to sell their RDMS as a development tool to justify the price. Don't believe the hype.

    PostgreSQL is trying to position themselves as an Oracle replacement, and thus have a similar feature set. PostgreSQL is also very good at very large databases (probably even more so than MySQL, at least until InnoDB gets multiple tablespaces in the next release).

    Databases with simple queries where results are not needed instantly would do well with PostgreSQL.

    • on the other hand (Score:3, Interesting)

      by kpharmer ( 452893 ) *
      Might not always be the fastest...but I can't imagine developing a database application - and putting all the join code in the app - that could instead be a subselect, inline view, etc.

      I'm not a huge fan of triggers - but often find circumstances in which their careful application has saved a huge number of hours.

      Stored procedures are another example of a technology that when used wisely can result in a huge improvements to flexibility and adaptability. My favorite use of them is when on a fast-moving

Slashdot Top Deals

Real programmers don't bring brown-bag lunches. If the vending machine doesn't sell it, they don't eat it. Vending machines don't sell quiche.

Close