PostgreSQL 8.2 Released

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.

Re:bitmap?

[nconway]

Bitmap indexes will almost definitely be in 8.3. Gavin Sherry submitted a revised patch [postgresql.org] for them a few days ago.

Re:I think you're full of it.

[szap]

... I'm also doubting the 23% increase in performance...

FWIW, and YMMV, when you get hammered with many concurrent queries, it's much, much faster. At about 100 concurrent hits, about 50% faster: http://tweakers.net/reviews/657/6 [tweakers.net] Benchmark method here: http://tweakers.net/reviews/646/9 [tweakers.net]

Yes, it's missing description on how exactly they set up MySQL. MyISAM? innodb? So take it with a grain of salt.

Re:Performance?

[phoenix.bam!]

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:Performance?

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

Replication?

[Curly]

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:Gotta love it...

[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:Replication?

[nyamada]

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:Watch out, MySQL.

[Bluesman]

Do you mean like this? [sourceforge.net]

Having used both, I can tell you phppgadmin is a bit more polished than phpmyadmin. Neither are particularly wonderful ways to interact with a database, but if you're stuck on a no-console web host, I'd much prefer to have the posgres/phppgadmin combo.

Re:Watch out, MySQL.

[thebeatgoes.com]

You mean like this [sourceforge.net] or
webmin anyone? [webmin.com] or
this if you want a non-web version [pgadmin.org]

Re:Replication?

[oGMo]

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:Replication?

[jadavis]

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

Re:Watch out, MySQL.

[Anonymous Coward]

http://pgadmin.org/ [pgadmin.org] is the most used open source tool.

Way to go PostgreSQL

[greengarden]

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

[mccoma]

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?

Re:I think you're full of it.

[innosent]

actually, they used innodb, and yes, Postgres scales much better than MySQL, but MySQL is a little more streamlined for low-volume jobs.

Re:Replication?

[Bacon Bits]

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 because the SQL standard defines maximum sizes for fields or because the physical structure of the database makes it impractical or unreasonable. In the case of PostgreSQL, the objects are internally stored in different tables with different physical files, although that is not seen by the DB developer at all. They're typically used for storing pictures and documents in the DBMS when you cannot or do not wish to use the file system instead, or for literally storing large binary data. it also supports data streaming, AFAIK.

Table inheritance is like a reverse VIEW, and was defined in SQL:1999. Given table A and table B, let's say table B inherits from table A. Table B will then have all the fields from table A plus it's own. PostgreSQL also supports multiple inheritance. It's standard SQL, but it's very weird, IMO. It has some pretty specific uses, like being able to essentially have indexed VIEWs and such, or making a permanent JOINed table.
http://www.postgresql.org/docs/8.2/interactive/ddl -inherit.html [postgresql.org]

As far as schema changes, the argument goes like this: replication is only necessary on productions systems. Schema on production systems should be static. If you're changing your schema, you probably did something wrong.

Re:--more--

[mikaelhg]

... what do PG users use for full text searching?

The same as everybody else who stores text in a relational database. Use external indexing, such as Lucene, which actually has some features you'd want for non-trivial full text indexing and searching, such as stemming.

Re:They moved to FreeBSD from Linux.

[Anonymous Coward]

I'm a rabid FreeBSD advocate, but MySQL performs badly under FreeBSD. This isn't so much a problem with FreeBSD as it is with MySQL, which is very Linux centric. I have no gripes however as I dumped MySQL before I dumped Linux, but I would recommend that if you are going to have a stand-alone server for MySQL that it should be running on a linux distro.