PostgreSQL 8.1 Available

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.

Comments

Question for Taco

[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

[toofast]

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.
"

Much to choose from?

[ltning]

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 there would (currently) dramatically reduce performance. Not the best thing either.

Oh well. Maybe by this time next year.

Just installed Win32 version

[mgkimsal2]

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.

Re:Just installed Win32 version

[LDoggg_]

THEY USE IT BECAUSE IT WAS THE FIRST THING THEY GOT WORKING...

I think there is some merit to this statment.

I'd love to see postgreSQL added to the xampp package.

The nice thing about xampp is that you simply unzip it into a directory and suddenly you have a huge technology stack of apache, mysql, perl, php all ready to go for stand alone development.

I'm betting there are plenty of windows only developers that wouldn't mind giving either database a shot if they understood how easy it was to get started.

at last "SELECT ... FOR UPDATE NOWAIT"

[Anonymous Coward]

I always wondered how one could create a multiuser database frontend reliably without this statement:

SELECT ... FOR UPDATE NOWAIT;

If you do

SELECT ... FOR UPDATE;

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 "Someone else has the row locked", and give control back to the user.

Nice feature to have, but people have been using postgres (and other databases) sucessfully for years. How did they work around it?

It's pretty sweet

[Mark Round]

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]=http://www.blastwave.org/testing/

MySQL vs. PostgreSQL

[ChrisF79]

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?

Re:MySQL vs. PostgreSQL

[elp]

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 also much harder for a beginner to learn with, error messages like: syntax error near "'" on a 20 line create statement dont help either.

I think too many people started on mysql and stayed with it as their skill improved, to the point where postgres has become an also-run, no matter what impression slashdot might give you. As for the postgres features, looking through some of my customers databases and code it looks as though a lot of them have never heard of indexes much less normalised data, stored procedures, views etc. SELECT DISTINCTS on 30000 row tables... <shudder>

I would love to hear from other people with some real world usage stats.

Re:RC1/RC2

[PhYrE2k2]

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).

-M

Embedded version?

[Kunta Kinte]

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.

Headline wording

[d_54321]

It's interesting that this is worded as "PostgreSQL 8.1 Available" and just a short while ago there was a story entitled "MSSQL 2005 Finally Released." Would it have been so painful to name the MS story without bias?

/. Meta question: a wheelbarrow?

[markhb]

Okay, I'm going to bite: what on earth does a red wheelbarrow have to do with databases?

Still no FULLTEXT indexes?

[inio]

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.

Re:Still no FULLTEXT indexes?

[Anonymous Coward]

Huh, of course there are. What do you think The Release Notes mean when they speak of "PostgreSQL's full-text indexing".

Is there a distinction in the hyphonization or ALLCAPS between "full-text" and "FULLTEXT" that has you concerned?

Or is it simply the case that not every odd index type (fuzzy, array, text, spatial, etc) is built-in in the default ./configure options. IMHO that's a feature rather than a bug -- because that way people who used one of the other full-text engines like this one built on inverted trees rather than GiST indexes can do so.

Re:Still no FULLTEXT indexes?

[rtaylor]

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

Re:Still no FULLTEXT indexes?

[jabbo]

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.

Re:Still no FULLTEXT indexes?

[otisg]

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...

Re:Still no FULLTEXT indexes?

[frostman]

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 tsearch2, check it out. Great stuff.

Re:quick question.

[jadavis]

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.

Re:quick question.

[killjoe]

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.