What's new in PostgreSQL 8.1

Sxooter

Hey, PostgreSQL 8.1 is coming out, and it just keeps getting better. With new features like bitmap indexes, 2 phase commit, GiST indexes, 64 bit shared memory, integrated autovacuum, and faster aggregates, it's better than ever. And don't forget, it runs on Winders now since V 8.0.

What's New

thorpe

man... ive just started playing with postgres a few days ago, loving it. reading through the PL/pgSql manual as i type.

what i really want to do is create a nested sets tree using (hopefully) just triggers and PL/pgSql. there doesnt seem to be too many simple tutorials around for this sort of thing though. (PL/pgSql i meen) the manual seems to cover it all but theres not too many real world examples.

can you recommend any decent books?

Sxooter

Take a look at the ltree package in the contrib directory.

thorpe

sorry... where is this contrib directory?

Sxooter

In a source install, it's in something like /usr/local/src/postgresql-8.1/contrib/ltree.

There should be a contrib rpm if you are using redhat type rpms. If it's not included, you can just grab the tarball from the main site.

Slowly, everything in the contrib directory is being moved to pgfoundry or gborg to clean up the distro so it doesn't include a dozen or so little utils that are hard for most users to find.

csn

Yeah, 8.1 sure appears to have a lot of improvements - many of them performance-wise (not that I ever had performance problems). There's a lot I have to read up on: roles, IN/OUT/INOUT params in SQL/plpgsql functions, bitmap indices, autovacuum, ...

I guess with the new IN/OUT/INOUT function params, each param returned would appear as a column.

I read the note on bitmap indices - not sure I fully understand. Think they increase the performance of queries that could use two or more indices.

As for PG books, the docs are the best thing out there. Bruce Momjian (good book) and some others have books too, but they're dated to varying degrees.

thorpe

ok, found ltree. i run a few gentoo machines, found it in /usr/share/postgresql/contrib. shame i really dont understand what its about as yet. hehe, but thats cool, id'e really like to write my own anyways. its the only way to learn anything really.

i'll say it again though. im loving postgres. seems to have so much more to offer than mysql.

Sxooter

Yeah, if you like tinkering around with a database, PostgreSQL is like some kind of insane fun house play land area with more rides than you can get to in a week, and MySQL is kinda like a small church fair with a hayride and a kissing booth. 🙂 They're both fun, but they're just different scales of fun. The first time you put some weird bit of logic into a user defined function and realize that the total change to your app layer code was zero, but you just increased data reliability by a thousand times, you'll know why you started using it.

I'd strongly suggest reading the admin docs though. Much like owning a Ferrari or Porshce, there are things you need to know to keep it happy.

thorpe

I'd strongly suggest reading the admin docs though.

I'll take that advise. well, eventually. at the moment im just browsing. a little overwhelmed. i know i shouldn't browse, it really is one of those things i need to read from start to end, but....

im just about to start a new version of my framework/cms from scratch, and im really tossing up the idea of building it exclusively for postgres. i meen, there seems to be allot of logic i could build right into the database so i dont really see how i could make it mysql/postgres compatible. the only problem is i dont know postgres at all well enough yet, and i know im going to need this new framework for a pretty big project come end of january.

descision descisions..... shall i tred the deep, or....

guess thats my descision. postgres seems so hard to refuse though.

csn

PostgreSQL all the way. I coded to MySQL for years and found that once I ran into its limitations, grew frustrated with them, then decided to switch to another database, MySQL was more of a deadend which needed to be backed out of (due to all its MySQL-specific SQL) before porting to another database. Porting from PostgreSQL to other databases (Oracle, SQL Server, etc.) should be much easier, but I've never thought of switching from PG.

As for frameworks/cms's, PHP feels more and more like MySQL to me. I've started Ruby On Rails and it's very very nice.

csn

Heh, MySQL is like a playground too 😉 - just with fewer and more basic toys and plenty of sharp edges!

DoctorWho

The pain of setting up a AutoNumber Increment field in portgres.......should have never taken a lookup at the Admin Docs to do that.

I much prefer the underlying document structure in MySql so far. I know where my data is in MySql and it consists of 3 files usually, very easy to back up and move.

Still gonna play with portges but I don't really see myself bothering with it anytime soon. Sticking with MySql and it's simplicity.

And speed claims regarding portges seem to be slower then MySql. We'll see....

Sxooter

DoctorWho wrote:
The pain of setting up a AutoNumber Increment field in portgres.......should have never taken a lookup at the Admin Docs to do that.

Ummm. It's not in the admin docs. It's right in the regular docs under serial types. You go to the SQL docs, look at numeric types (I'd assume auto incrementing types are numeric by nature) and there it is. This is more that you're familiar with MySQL and not familiar with PostgreSQL. Of course, documentation patches are always welcome if you can see a good way to improve them.

I much prefer the underlying document structure in MySql so far. I know where my data is in MySql and it consists of 3 files usually, very easy to back up and move.

Again, this is more a philosophical issue. And it's not even accurate. Ever tried backing up individual innodb tables? This statement only holds for myisam types. However, the real issue is the fact that you really shouldn't have to worry about how the data are stored on the hard drive. You should have solid, reliable tools to take a backup with. In PostgreSQL, you can backup the whole database in one command, and it's a consistent snapshop, i.e. no chance that one table has data referring to another table that wasn't quite there at the time of the backup. You can backup your database in the middle of the day, with 1,000 transactions in flight, and your backup is guaranteed coherent.

Still gonna play with portges but I don't really see myself bothering with it anytime soon. Sticking with MySql and it's simplicity.

Simplicity is a good thing. Data coherence is a much better thing.

And speed claims regarding portges seem to be slower then MySql. We'll see....

That really depends on what you're doing. For OLTP stuff, MySQL, even with Innodb, is no match for PostgreSQL. For OLAP stuff, PostgreSQL is still getting a lot of features it needs to compete with other databases, as it's just not designed from the ground up for running one or two queries fast, it's designed to run hundreds or thousands reliably, and reasonably fast.

It's all about fitting your tools to what you're doing with them. For most simple php/db apps, MySQL is a fine fit.

If you're gonna do accounting, I'd suggest almost any other database out there.

csn

Ah, they added lastval() finally. Though the early/late binding might cause some funky problems if you change stuff around. I'll probably start using it. I guess libraries can start adding functions like '_last_insert_id()' now for PG.

Sxooter

Hehe. Yeah, I'm already kinda going crazy as I test 8.1 on my workstation while we run 7.4 in production. Arggg! There are so many little things that I'm now wishing I had in production. I'm already planning an upgrade to 8.1, but it'll take at least 6 months before I can get that pushed through.