Follow Slashdot blog updates by subscribing to our blog RSS feed


Forgot your password?

PostgreSQL 8.1 Available 261

atani writes "PostgreSQL 8.1 has been posted, though not officially announced as of this moment. This release includes two-phased commits, improved SMP and overall performance, a new role system replaces the older user/group, autovacuum is now within the backend rather than a separate contrib module, and various improvements, performance enhancements, and bugfixes. " You can also read the developer notes for the popular database. One thing is clear- with the newest Postresql and MySql, you have much to choose from.
This discussion has been archived. No new comments can be posted.

PostgreSQL 8.1 Available

Comments Filter:
  • by mw ( 16262 ) on Tuesday November 08, 2005 @09:30AM (#13978005)
    I've tested RC1 with some applications, and was really impressed by the bitmap-indices. I hope to see soon a multi-master replication now that 2PC is available. I've heard Slony-2 will have it.
    • Indeed- congratulations.

      I'm using PostgreSQL for many things (custom app, Drupal, Apache logging, back-office) and it's great- this new release adds lots of useful bits.

      Probably the most useful new feature for those who use it to run mini web sites will be the MAX() and MIN() functions now using indices- this has been a point of contention for some time.

      XA will be useful for bigger stuff.

      I'm also really pleased auto-vacuum is now part of the DB.

      Now, Gentoo- let's get cracking!
    • by Anonymous Coward
      PostgreSQL has multi master replication, take a look at PGCluster.
  • Question for Taco (Score:2, Interesting)

    by Anonymous Coward
    Would you consider changing?
    Are you sticking with what you know or are you going to bite the bullet and switch?

    Have you upgraded mysql yet? (i seem to recall thats what you use)
    • Re:Question for Taco (Score:5, Interesting)

      by toofast ( 20646 ) on Tuesday November 08, 2005 @02:09PM (#13980342)
      Here's what I think he'll answer to your first question:

      MySQL works fine on Slashdot. It has all the features and performance we need, it has been running flawlessly for years and we're already familiar with it, so why should we change to anything else? What makes you think there's a bullet that needs biting? Granted, PG looks neat and all, but why exchange a dollar for four quarters?

      Here's what I think he'll answer to your second question:

      MySQL 5 doesn't offer us any features we absolutely need (otherwise we'd be using PG, right?) We will upgrade eventually, but we have bigger fish to fry right now, and upgrading our database is not very imperative.

      Something else he might say:

      Running slashdot is not as simple as running a basement website that gets 3 hits per hour. Thought needs to be put into these decisions. We can't just run off and install something the day it's released.
  • I can't seem to find a Windows binary on the 'ftp browser' on their site. Am I missing something? Or is the Windows version of 8.1 not available yet?
  • The URL for the release notes in the story should be: ease.html#RELEASE-8-1 []
  • Much to choose from? (Score:2, Interesting)

    by ltning ( 143862 )
    I count two (2) things: PostgreSQL and MySQL.

    Since when was two a crowd? ;)

    But neither of the two have a good, cross-platform clustering- or multi-master replication solution, which makes things kinda difficult in our end. For MySQL I can use circular replication, but this is undocumented at best, and very error-prone in extreme situations. The clustering in MySQL doesn't count - in-memory storage limited to half of your physical mem - come on, that's ridicolous. And for Postgres, any multi-master solution
    • by Anonymous Coward
      Well there is also the Firebird Superserver. It's a released under a Mozilla license (not affiliated with the mozilla project, of course.) (and a couple other licenses also). []

      So that's three open source SQL database systems; MySQL, Firebird, and PostgreSQL.

      Here is a comparision between aviable open source relational databases. [] (it's a bit dated)

      Here is the migration guide for MS-SQL to Firebird, in which I doubt your interested.
      http:/ []
    • there's also: []

      Firebird is kind of a bastard step brother of PostgreSQL, since they share the common Ingres heritage... it's got foreign keys, triggers and stored procedures for far longer than MySQL, for instance. It's Borland Delphi's Interbase code gone open-source. However, it's a relative newcomer and most who care are Delphi developers.

      Its license is neither BSD nor GNU, which i think also makes up for some caution people have against it.
    • Its just not part of the default postgresql install, its 3rd party. Search for pgcluster.
  • by mgkimsal2 ( 200677 ) on Tuesday November 08, 2005 @09:53AM (#13978118) Homepage
    Just installed the Windows version. Just a quick couple notes here...

    1. Installer was seemingly faster than 8.0 version.
    2. Installer could do a little bit better job of hiding all the 'options' (ISBN, Fuzzy search, etc) you can install. Put them behind an 'advanced' button or something - it's a little intimidating to see so many options at first. Also the PL language choices are odd - 'pl/Perl' and 'pl/Perl (untrusted)' ??? These are things that could probably be hidden from the majority of people just testing it out for the first time - either install everything by default, or nothing, but put some of these things behind 'advanced' tabs.
    3. pgAdmin III bundled tool is 1.4 - I think I was using 1.3 last time I installed. Visually it looks a bit nicer - I'm assuming they've fixed some bugs or something similar to warrant a number change. :)

    I'll probably get flamed for #2, but I'm just putting out some suggestions. The fact that there *is* a Windows installer at all is a good thing - I'd just like to see it improve to help reach a wider audience for future releases.
    • by dpage ( 566064 ) on Tuesday November 08, 2005 @10:07AM (#13978186)
      pgAdmin III bundled tool is 1.4 - I think I was using 1.3 last time I installed. Visually it looks a bit nicer - I'm assuming they've fixed some bugs or something similar to warrant a number change. :)

      1.3 was the development series for 1.4, the new stable release as of yesterday. There are a heap of new features over 1.2 (which shipped with PostgreSQL 8.0) - see [] for details.
    • by bigHairyDog ( 686475 ) on Tuesday November 08, 2005 @10:11AM (#13978208)
      That's the PostGreSQL philosophy - don't hide the complexity. MySQL did that, and was rewarded by popularity and a generation of people who didn't understand database administration. PostGreSQL makes you pay attention to these options, and educates you in the process.
      • I don't think I quite agree with that. You don't have to understand all the stuff that goes on with indexing, query planning, system tables, etc, to get going with Postgres. But when you need it, it's open, extensible and documented. I say that because I think Postgres is doing an increasingly good job making the software work for people of various levels of expertise.
      • "...don't hide the complexity. MySQL did that, and was rewarded by popularity and a generation of people who didn't understand database administration."

        Nor understand relational database design...kind of like the nightmare ms access has given many of us. Geez...I'd often wished access had been banned from the desktop. I've had to inherit and straighten out so many access "databases" that some PHB started out messing with...usually with only 1 or 2 tables kludged together, that was passed around the office

    • I'll probably get flamed for #2

      That's for sure, you JERK!!!11!!1111111111!!!

      Seriously though, I see it as positive that high quality open source projects are truly cross-platform and also include windows. Even if one is entirely anti-MS, it is still beneficial for that person's goal that there is a very easy migration path: move from OS software running on windows to the same OS software on linux/mac/etc.
    • No flames from me. I happen to agree with you, especially since Postgres is now well-position to be desktop database engine of choice, thanks to its small footprint, unrestrictive license, and ease of manageability. So a simplified installer and some wizards (make a user), would make it a great database to get started doing Access-like things.
  • by Anonymous Coward on Tuesday November 08, 2005 @09:57AM (#13978132)
    PostgreSQL 8.1 has been posted, though not officially announced as of this moment.

    They were probably waiting for their mirrors to finish syncing before some yo-yo posted to /. and caused every other yo-yo to start downloading.

    • They were probably waiting for their mirrors to finish syncing before some yo-yo posted to /. and caused every other yo-yo to start downloading. ..not to mention the /. editor in the middle that put it on the main page before the anouncement, yo.
  • RC1/RC2 (Score:5, Informative)

    by PhYrE2k2 ( 806396 ) on Tuesday November 08, 2005 @10:03AM (#13978166)
    Been using RC1/RC2 for some time now. I'm impressed with the role feature, although it won't add much to the average user. The speed has been night and day with 7.3. I also haven't had any problems with the RC's in the slightest except one: a renamed table then wouldn't let me delete the sequence it depended on after the renamed table was deleted. Apparently I wasn't the only one who found it and it has been reported as fixed, though who knows.

    My only beef with PgSQL has been there since before the 7's. There is still no way to not show the list of databases to users who have no right or access to those databases. Why should userA with rights to databaseA see that there is a databaseB or databaseC? This really seems like a simple feature, yet nobody will accept it into the release.

    • I thought that was common within Oracle? I take it Postgres "databases" are similar to Oracle "schemas." I'm pretty sure in Oracle even Joe Sixpack can see a list of all of the schemas but can't see what's inside. Though perhaps Oracle has a way of hiding a set of schemas from Joe.

      I don't see what the big deal is. Then again I'm not a big Postgres user so I don't know if there's more to this than I'm realizing.
      • Re:RC1/RC2 (Score:4, Interesting)

        by PhYrE2k2 ( 806396 ) on Tuesday November 08, 2005 @10:22AM (#13978287)
        it seems like a simple check, in the same way when it checks for permissions, when doing a list of databases. Seems to make sense.

        In a shared database server, it can be important. Although it in a way is security-through-obscurity, many would rather not have their database name 'companyfinances' visible to those with no access. Additionally, on a shared database services, you don't want your customers to know if there are 20 or 200 databases on that server (the number means nothing depending on the size anyway, but looks bad).

      • I take it Postgres "databases" are similar to Oracle "schemas."
        FWIW, Postgres has schemas. Quite similar to Oracle in that regard.
  • by Anonymous Coward
    I always wondered how one could create a multiuser database frontend reliably without this statement:


    If you do


    you either get exclusive access OR you block waiting for the person editing a record to finish. Obviouslt blocking isn't good in a frontend. Hacks like timeout=100, because this can trigger just because the database is being used heavily at that moment, and at best is an ugly work around.

    Now with the NOWAIT option you can return instantly and say "S
    • by Daniel_Staal ( 609844 ) <> on Tuesday November 08, 2005 @10:32AM (#13978352)
      Most handle it by giving you the view of the data before the other user started to modify it. The person who is editing's edits do not show until all of the edit is finished.

      Postgres docs actually have a chapter on this: Concurrency Control []. Like most high-end databases Postgres can handle this situation in different ways, depending on how it is set up...

      A database should never tell you that the data is not avalible. It should always give you the best version of the data it has.
    • You used to do it using a short statement timeout.

      set statement_timeout = 200; -- 200ms
      SELECT * FROM UPDATE; -- Waits at most 200ms

      Catch the exception indicating the query was cancelled (row locked) or continue.

      Not the greatest but it has worked well enough for me with a few hundred automated processes pulling things out of a queue several times per second.
    • That kind of functionality is rather EVIL if you ask me; it is likely to end up locking someone out because someone selected a record this way and then went out to lunch. For web applications - by far the biggest use of Postgres no doubt - you won't ever want to do this kind of thing anyway.

      If you are paranoid about two people editing at the same time and then overwriting each other's changes, build it into the app some other way, like checking a "last update" timestamp before saving changes to the record a
  • It's pretty sweet (Score:3, Interesting)

    by Mark Round ( 211258 ) on Tuesday November 08, 2005 @10:12AM (#13978215) Homepage
    I've been running my Blastwave packages[1] of the betas and RCs since the first snapshots became available, and I've been massively impressed with this release. Moving autovacuum into the main package is a really nice touch - all you have to do now is uncomment a few lines in postgresql.conf and it handles it all for you.

    The new roles system is also amazingly useful. You can set up a range of roles with a variety of permissions, and then let users "assume" those roles. So you can log in with a day-to-day account, and when you need to do some admin work just SET ROLE [name of your super-user role] and then revert back once you're done. Great if you want to give a junior DBA the ability to create databases, but not the ability to modify other things (such as creating new roles).

    Congratulations to the PostgreSQL team anyway - for doing things "the right way" :)

    [1]= []
  • Time to upgrade my Jabber server [] from PostgreSQL 8.0.4. And with RubyForge getting up past 3.5M records [] now, performance improvements are good news...
  • MySQL vs. PostgreSQL (Score:3, Interesting)

    by ChrisF79 ( 829953 ) on Tuesday November 08, 2005 @10:20AM (#13978279) Homepage
    I run a very small website as a hobby and I've just always used MySQL because that's what my PHP book featured. I hear quite a bit about mysql in forums on PHP that I go to, and on slashdot itself so I'm asking you... how popular is Postgre? If I used forum posts as any proxy, it would look like MySQL is dominant. Is that really the case?
    • You're free to research that yourself...
      But you're not going to find much looking for "Postgre". It's PostgreSQL or "Postgres" for short.
      Just FYI.
    • All indicators seem to point to MySQL having more installed systems.

      However, if you look at the traffic on the PostgreSQL mailing lists and #postgresql on irc, I'm sure you'll find plenty of community members.

      I would say that both databases are well beyond the "critical mass" of informed users to sufficiently support other users.
    • by elp ( 45629 )

      If I used forum posts as any proxy, it would look like MySQL is dominant.

      In the webhosting world that is the understatement of the year. In my business I have a little over 1000 mysql clients and all of 3 postgresql clients. I've been advertising Postgres along side mysql for 2 years now. Its not worth the space or staff training costs.

      I think the problem was that postgres was always much more complicated to set up and it used to have some pretty serious performance problems in the real world. It was

      • I'm and ISP/hosting geek as well and see exactly the same thing. Hundreds of Mysql users and one Postgres user. And pretty much the same thing in the users code and databases. My favorite being the 30 minute query which I fixed by suggesting that a 2 million row tables should probably use a int(11) as a primary key instead of a (128)varchar containing the users email address. They still think I'm a genius.

    • For a website, MySQL is probably the superior option. There is (almost) always a tradeoff between functionality and performance, so it is (usually) wisest to go for the option that is closest to what you want, as that will perform the best.

      If the site is very small and the database code is not particularly advanced, you might even do better with SQLite. However, this does require using PHP 5. If you're on PHP 4 or earlier, that's not an option.

      Now, this changes dramatically if you need very advanced databas

    • If poularity is your criterion, then why not run your website on MS Access, and allow only Internet explorer clients, while you listen to Britney Spears, drink coke and eat a big mac?

      I'm not sure exactly what you mean by "dominant", since both databases are free, so neither takes sales from the other. Both are actively developed. PostgreSQL is IMHO better engineered and has more features, and has had them for longer. But postgreSQL has a reputation as not beeing as drool-proof as mySQL. I wouldn't know, I'v
  • Embedded version? (Score:3, Interesting)

    by Kunta Kinte ( 323399 ) on Tuesday November 08, 2005 @10:27AM (#13978327) Journal
    I would switch to PostgreSQL if there were an embedded version.

    SQLite is great but concentrates on being a small database. This design choice is great for many applications probably, though poor handling of large rows ( can't read partial blobs, etc. ), weak concurrency model, etc. inconveniences others.

  • I am not trolling here, I honestly don't know allot about database software.

    How does this compare to SQL 2k and the newly released SQL 2k5? I already know the comments about open source, free, etc.

    THAT ASIDE, how do the released compiled programs compare? Does anyone know?

    • Re:quick question. (Score:4, Interesting)

      by jadavis ( 473492 ) on Tuesday November 08, 2005 @12:33PM (#13979432)
      PostgreSQL is rock solid, and very extensible (user-defined aggregates, user-defined procedural languages, user defined functions, triggers, user defined types, table functions, and much more). It probably also performs better in many situations due, in part, to MS SQLs locking vs. PostgreSQL's MVCC.

      However, there are more tools available for MS SQL, and there is some form of multi-master replication and probably better table partitioning. MS SQL is not really a bad database, but I think PostgreSQL has it beat except on those two points.

      Any real performance analysis is heavily application dependent, however. If that's what you care about, you need to do your own tests.
    • I am not about to start the holy war here, but you might want to define your question a bit more.

      What do you want to do with your database(s)?
      How many users do you expect?
      How many writes per second do you expect?
      How many reads per second do you expect?
      Are you comfortable with SQL?
      What programming languages do you use to acess data?
      How important is it to you to be flexible in what OS you run your DB on?
      How important is cost to you?
      Do you plan on running this on multipe processors or multi-core chips?
      What rep
    • Re:quick question. (Score:4, Interesting)

      by killjoe ( 766577 ) on Tuesday November 08, 2005 @04:28PM (#13981799)
      1) It's free. SQL server costs 5000 per processor or 16,000 per processor depending on the version
      2) It runs on every platform. SQL server only runs on windows. It's the only database in wisespread use that locks you to one operating system.
      3) It has no limits on how much memory it uses. SQL server standard edition limits itself to 2 gigs as of SQL server 2K (don't know if they fixed that by now).
      4) it supports text fields that are only limited by your OS and uses them extremely efficiently. These are not like SQL server blob fields but they are like HUGE text fields that can be indexed or used in aggregate functions.
      5) It has user definable data types, user definable operators, user definable functions.
      6) It can use perl, python, java, tcl or PG/Pqsl as it's stored procedure languages. YOu can also use C and even compile your C stored procs in with the server if you want super speed.
      7) It has multi version concurrency control. This means readers never block writers, ever.
      8) No lock escalation. SQL server users know the value of this, everybody else takes it for granted.
      9) Lots of built in datatypes like arrays, IP address, geometric types, GIS types etc. Yes it's possible to write a query that asks "select all rectangles that contain this point" or "select all ip addresses in this address mask"
      10) Support for hierarchies (in the contrib) so you can natually and intuitively model graphs without writing code or using complex self joins and such. Look up ltree.
      11) A fantastic rule system. You can make anything look like a updateable recordset if you are willing to code it.
      12) PostGIS.

      I am just scratching the surface. I am sure I have missed some other features but that should whet you appetite.

  • I've been using 8.1 beta releases for a while now, and even compared to 8.0.x (which was really good), 8.,1 is very impressive and well worth the upgrade. Performance improvements alone are worth it (esp on SMP).

    But the biggest thing to me in 8.1, which the blurb didn't mention, is native support for inheritance-based table partitioning optimizations, which is a huge performance win for large and/or ever-growing tables.
  • Sounds great, but with everything up and stable at the moment, I'll wait to switch to it when I'm developing something new again.
  • by markhb ( 11721 ) on Tuesday November 08, 2005 @11:12AM (#13978667) Journal
    Okay, I'm going to bite: what on earth does a red wheelbarrow have to do with databases?
  • by inio ( 26835 ) on Tuesday November 08, 2005 @11:23AM (#13978759) Homepage
    I'd really like to move a few projects at work to Postgres, but there's one essential feature that it still appears to be lacking: FULLTEXT indexes. I don't even need the fancy ranking stuff MySQL does - a boolean word match would be enough.
    • by rtaylor ( 70602 ) on Tuesday November 08, 2005 @11:48AM (#13978972) Homepage
      Full text searching has existed for a number of years and is actively used by a few Russian search engines on large volumes of content (millions of text documents), among other places.

      Take a peak in the contrib directory of the source tree for tsearch.

      Documentation for TSearch []

      • Thanks for the pointer. It looks like it'll work assuming that I can get the DB admin to install it and figure out how to use it. Seems like an awful lot of code for functionality I need though (compared to "FULLTEXT", "MATCH" and "IN BOOLEAN MODE").
      • I never install without tsearch2. It's amazing. Super flexible, super smart, and fast fast FAST.

        I normally attach it to template1 and tune it to my environment, then just basically forget about it.

        However, I must say it's one of PG's big failures of marketing that it hasn't been included in default installations. I know the PG folks are perfectionists, and I love them for it, but in winning users over you would do well to keep websites in mind, almost all of which need an FTI.

        Anyway, if you don't know ts
    • Take a look at openFTS [].

      Having used both MySQL and Postgres pretty close to each system's limitations, for years (~5 years in each case), I must admit that I now prefer PostgreSQL (again). I've gone back and forth.

      One major problem with MySQL is that you have to choose EITHER fulltext indices OR transactional tables (InnoDB). You can't have both. With Postgresql, you can.
    • People bitch about MySQL not having transactions, then other people respond with 'just choose the table type innodb!'. Point is, transactions aren't something someone writing a mysql app can assume will be available without some gyrations on the end user's part. And the same thing exists for Postgres and fulltext indexes. Unless they're default and can be assumed to be on every installation of 8.2 (or whatever) they essentially don't exist.
    • If PostgreSQL's full-text indexing is anything like MySQL's, I urge you not to use it. Things I heard about MySQL's full-text index are horrible! Instead, integrate Lucene [] with your application/database. If you need a book, there is Lucene in Action [] with free code and sheap eBook version. Full disclosure: I'm one of the authors. Simpy [] is a good example of PostgreSQL + Lucene integration.

      Oh, and if you want non-Java solution, there are several Lucene ports available: C++, Python, Perl, C#, Ruby...
  • by RelliK ( 4466 )
    Finally! 2-phase commits. Distributed transactions can't be far behind. Is work already being done on them?


    (I feel like a little kid who just got a new toy as a present).
  • Is the data format the same between 8.0.x and 8.1.x - i.e. can someone just upgrade the software, and stop and restart the daemons? Or will it require a pg_dumpall, stop, upgrade, start, and restore?
  • And it just happens that I have to present about this subject on tomorrow's class of databases 3!
    I saw that and it will help me a lot on the work, because my teacher loves postgreSQL as me ;)

What is algebra, exactly? Is it one of those three-cornered things? -- J.M. Barrie