Slashdot Mirror

And how do you convert the duplicate key / ignore inserts?

Do you mean deferred constraints? If that's not what you mean, then you screwed up on your schema definition. And if you didn't screw up on your schema definition, then you've screwed something else up entirely. You can do lots of dumb things with any database - its just that doing dumb things is typically much harder with PostgreSQL. Either way, I seriously doubt a migration to PostgreSQL is all that difficult for you. Or if its a simple duplication issue, use INSERT WHERE NOT EXISTS and let transactions do their part; whereby you should fix your broken data and/or schema and/or DBA before doing so.

Honestly, I can't conceive your issue to be anything other than ignorance for a lacking feature (deferred constraints) or general stupidity. Agile development does not require inconsistent data nor does it require lazy, inept, or ignorant developers.

Having migrated a few applications from MySQL to Postgres, the truth is, it will be painful, but the result is you will be a LOT more confident in the quality and manageability of your data. Start here

PostgreSQL has all the datatypes that MySQL has, and more (see especially the ), and usually with far less limitations. Any character type can be up to 1GB in size if needed, for example.

The biggest sources of pain will likely come from character set handling (some MySQL oddities there) and MySQL's ready acceptance of NULL where it really shouldn't occur. Learn to love grep, sed and the 'iconv' utility.

There are conversion utilities that will get you part of the way there with a DB structure dump, but they will hardly do justice to a complex database. You will have to reengineer your system--in fact you will want to, since PostgreSQL has many capabilities that you cannot take advantage of in MySQL.

Triggers and stored functions are a little less 'wieldy' in Postgres, but far more capable once you get the concepts. For one thing, MySQL re-compiles all stored procedures for every instance of a connection (making SPs in PHP web-based apps almost pointless). One caveat: Postgres does not have 'true' stored procedures in the sense that they cannot span multiple transactions. Thus they are called functions, but aside from the transaction limitation, they are far more capable than MySQL stored procedures. For example, result sets from functions can be treated exactly like result sets from tables, and nested inside other queries and views, even in the FROM clause in ways that MySQL still does not allow. Full referential transparency.

Postgres has many more indexing options than MySQL, which you will want to explore for performance tweaking.

Postgres has the RULE system which allows for query rewriting in much the way that Apache's mod_rewrite allows for URL rewriting. It is a powerful tool, allowing you to potentially get rid of tons of stored procedure code. In some ways it works like an alternate triggering mechanism, but it is very lightweight.

BTW, one way you might consider handling your migration is using PostgreSQL's new foreign data wrapper or SQL/MED functionality, which can allow you to query a MySQL table from within a PostgreSQL DB (read-only for now, but excellent for importing tables interactively)

Having migrated a few applications from MySQL to Postgres, the truth is, it will be painful, but the result is you will be a LOT more confident in the quality and manageability of your data. Start here

PostgreSQL has all the datatypes that MySQL has, and more (see especially the ), and usually with far less limitations. Any character type can be up to 1GB in size if needed, for example.

The biggest sources of pain will likely come from character set handling (some MySQL oddities there) and MySQL's ready acceptance of NULL where it really shouldn't occur. Learn to love grep, sed and the 'iconv' utility.

There are conversion utilities that will get you part of the way there with a DB structure dump, but they will hardly do justice to a complex database. You will have to reengineer your system--in fact you will want to, since PostgreSQL has many capabilities that you cannot take advantage of in MySQL.

Triggers and stored functions are a little less 'wieldy' in Postgres, but far more capable once you get the concepts. For one thing, MySQL re-compiles all stored procedures for every instance of a connection (making SPs in PHP web-based apps almost pointless). One caveat: Postgres does not have 'true' stored procedures in the sense that they cannot span multiple transactions. Thus they are called functions, but aside from the transaction limitation, they are far more capable than MySQL stored procedures. For example, result sets from functions can be treated exactly like result sets from tables, and nested inside other queries and views, even in the FROM clause in ways that MySQL still does not allow. Full referential transparency.

Postgres has many more indexing options than MySQL, which you will want to explore for performance tweaking.

Postgres has the RULE system which allows for query rewriting in much the way that Apache's mod_rewrite allows for URL rewriting. It is a powerful tool, allowing you to potentially get rid of tons of stored procedure code. In some ways it works like an alternate triggering mechanism, but it is very lightweight.

BTW, one way you might consider handling your migration is using PostgreSQL's new foreign data wrapper or SQL/MED functionality, which can allow you to query a MySQL table from within a PostgreSQL DB (read-only for now, but excellent for importing tables interactively)

I just had to do a little bit of this, I use plsql from the client, create a function that handles this case: ...EXCEPTION WHEN unique_violation THEN ...

people love to lie and troll about.

- more people saying that I am trolling, yet again. Yet again, incorrectly, I don't troll ever.

With regards to LIMIT, Oracle does have a couple of equivalents:
1. WHERE rownum < end

2. select * from (select S1.*, ROWNUM rn FROM ( real query ) S1 WHERE ROWNUM <= end) WHERE rn >= start

Obviously, the second syntax is a bit painful, but it works, and it has the important behavior that it doesn't calculate any of the rows that aren't returned (as Postgres says it does for LIMIT...OFFSET in 8.1, see the docs). This is important when you're selecting items 4990-5000 of a 10,000 row result set.

I've used both PostGres and Oracle - they're both pretty good at their jobs, both have their quirks, upsides, and downsides.

Your use of "it", such as "if it were C", reads like you're talking about PostgreSQL--when apparently the "it" you actually mean is the person's experience.

There's no formal bug tracker for PostgreSQL. Following the mailing list traffic about it on pgsql-bugs is the only way to know what happened right now. It's hard to justify the overhead of a better bug tracking system when the project has a zero tolerance policy for bugs, meaning there's very few of them open at any time. It has been looked at and that investigation isn't closed; just waiting for resources.

Your use of "it", such as "if it were C", reads like you're talking about PostgreSQL--when apparently the "it" you actually mean is the person's experience.

There's no formal bug tracker for PostgreSQL. Following the mailing list traffic about it on pgsql-bugs is the only way to know what happened right now. It's hard to justify the overhead of a better bug tracking system when the project has a zero tolerance policy for bugs, meaning there's very few of them open at any time. It has been looked at and that investigation isn't closed; just waiting for resources.

Why is my message confusing? The story is about a student who specified he wants to look at C++ projects. PostgreSQL is a good project to look at, but it's not C++. What's confusing here, really?

By the way, when I submit a bug report, how do I know if it's ever addressed? Thanks.

Your message is a confusing. PostgreSQL is written in C, with a few Perl scripts for building the code and some other scripting language bits. Were you trying to say only writing C++ code is worthwhile?

I work on PostgreSQL projects and hacking the source code for a living. The development community has extremely high standards, but there is a pretty formalized process to bring new people up to speed. The recommended way to start is doing patch review. Reading a patch that's similar to what you're interested in, and experimenting with it, teaches more of the real-world skills needed to work productively on an open-source project than writing new code does. Testing, code reading, and writing code that's easy to merge are the things new people don't know how to do, things that are critical to working on a real distributed development project. I even went to the trouble recently of writing an entire article on patch cleanup due to how often the project was running into issues there from new contributions; that's a very underappreciated skill.

Your message is a confusing. PostgreSQL is written in C, with a few Perl scripts for building the code and some other scripting language bits. Were you trying to say only writing C++ code is worthwhile?

I work on PostgreSQL projects and hacking the source code for a living. The development community has extremely high standards, but there is a pretty formalized process to bring new people up to speed. The recommended way to start is doing patch review. Reading a patch that's similar to what you're interested in, and experimenting with it, teaches more of the real-world skills needed to work productively on an open-source project than writing new code does. Testing, code reading, and writing code that's easy to merge are the things new people don't know how to do, things that are critical to working on a real distributed development project. I even went to the trouble recently of writing an entire article on patch cleanup due to how often the project was running into issues there from new contributions; that's a very underappreciated skill.

Well, if it were C and not C++, I'd suggest this project. There are so many concepts involved in database development, that it can give a run for its money to OS kernels, graphics libraries and probably most specialized math/astronomy/healthcare related software.

But it's C.

"REPLACE INTO" is NOT insert/update. it is insert and delete if there's a duplicate.
http://dev.mysql.com/doc/refman/5.6/en/replace.html

If you want insert/update on mysql you use this: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

There is a difference between updating if there's a duplicate and deleting and inserting if there's a duplicate. The differences might cause pain :).

The Postgresql bunch appear to be working on implementing MERGE instead:
http://wiki.postgresql.org/wiki/SQL_MERGE

You'll want to check out the clustering systems, anything stating to provide write-scaling is probably multi-master. PG-XC describes itself as a "write-scalable synchronous multi-master postgresql cluster". Interestingly 'synchronous' implies that this system scales out without sacrificing consistency. I haven't used it so I can't say for sure. My experience is with commercial RDBMSs and the eventual consistency model.

Data & Reality looks like an interesting read for sure, I will add it to my queue thanks!

Thanks. It is suggested here that "Multi-master replication" is not supported, which is what CouchDB excels at.
    http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

Contrast with what is easy in that regard for CouchDB:
  http://wiki.apache.org/couchdb/Replication

Anyway, everything has strengths and weaknesses in different situations. But in general, I agree SQL systems can do a lot as a mature technology, and maybe more than some NoSQL advocates understand or are willing to admit.

As in the book, Data & Reality, it can be pretty hard to model data well. Every model make simplifications of reality, and sometimes we find out the choice of simplification was not a good one, especially if needs change. But I also agree with you that good tools can make that easier to handle. I think one big advantage of SQL systems over NoSQL Schema-free systems is that the database schema serves as form of documentation of intent of what is going on. That documentaiton of intent then is traded off against flexibility at the application level.

Ideally, one could use a system without those kind of documentaiton/flexibility tradeoffs.

If you've modeled your information well and not let specifics of your application creep in then changes in your application need not be a problem at all. Even if you do need to refactor your information any modern RDBMS has great tools to help. I really do wonder what people with very large object stores are doing when their objects change.

PostgreSQL supports all manner of replication, I imagine it's not as easy as something called "easy replication" but would count on it being more flexible. Or use one of the many clustering systems to trade some flexibility for ease of use.

If you've modeled your information well and not let specifics of your application creep in then changes in your application need not be a problem at all. Even if you do need to refactor your information any modern RDBMS has great tools to help. I really do wonder what people with very large object stores are doing when their objects change.

PostgreSQL supports all manner of replication, I imagine it's not as easy as something called "easy replication" but would count on it being more flexible. Or use one of the many clustering systems to trade some flexibility for ease of use.

When you choose which file system to use, you should consider what the purpose of the storage is. If it's to run a database, you may want to rethink the decision to go with a journaling file system, because databases often their own journaling (like PostreSQL WAL), which actually means the performance will get reduced if you put a journaling file system underneath that. Just my 0.0003 grams of gold.

SERIALIZABLE is not truly guaranteeing anything. A common misunderstanding is that SERIALIZABLE is equal to running the transactions in a serial fashion. That is not true! Those levels are defined using phenomena (dirty reads, non-repeatable reads and phantom reads) or rather lack of those phenomena. But that does not mean there are no other problems - the ANSI just decided those three and defined the isolation levels. There are other phenomena / dependencies that make the transactions 'not serializable'.

See this excellent post on this topic http://iggyfernandez.wordpress.com/2010/09/20/dba-101-what-does-serializable-really-mean/

The only DB (I'm aware of), implementing true SSI (Serializable Snapshot Isolation) is the upcoming PostgreSQL 9.1. See this http://wiki.postgresql.org/wiki/SSI

If you have any problems with (or concerns about) PostgreSQL, then I suggest that you either file a bug report or post to the appropriate PostgreSQL forum or mailing list. You can see all the mailing lists for PostgreSQL at http://archives.postgresql.org/ and anyone can post to them. The PostgreSQL developers appreciate feedback and thoughtful suggestions.

I have run various versions of PostgreSQL on both 32 & 64 bit machines, for over 10 years - and never had strange problems running ininitdb that were not PEBKAC (a fancy term for user error!). Strange things happen to the best of software. Did you raise a bug report? was it reproducible? I once had a weird problem with another software package - that after much investigation, was put down to a transient RAM failure.

There are other software packages that do not store their config files under /etc, such as moodle and ultimatestunts.

Note that the rpm for Fedora does use /etc for the postgresql.conf. However, for evaluation purposes, I have run several different versions of postgreSQL installed concurrently, which I downloaded and compiled myself - so it not automatically on insisting on storing its config file under /etc is good thing.

I have had no problems with configuring it to use MD5 passwords - once I read the documentation properly, but then it was quite simply to do so. What were your problems?

You have not mentioned anything really significant. Whereas, there are plenty of gotchas setting up and running MySQL - like choosing the right engine, and the numerous 'quirks' in its SQL. Both MySQL and PostgreSQL require modifications to their configuration files for proper production running.

I read the mailing lists for PostgreSQL on a daily basis, so I am aware of problems with it. However, in my experience, all major software packages have problems, including the Oracle RDBMS. I am sure that if I read the MySQL forums or mailing lists that I would find further problems with that RDBMS as well. Note that several big corporations have, or are in the process of, migrating for an Oracle RDBMS to PostgreSQL - you can be assured that they did due diligence first.

Over the last 30 years I have used at least 6 different RDBMS, not counting different versions of each under a variety of different Operating Systems, and I much prefer PostgreSQL. I know that whatever one's preferred RDBMS, one can always find at least one feature that is better on some other RDBMS (for an example, there is one aspect of using variables in stored procedures that is superior in Sybase transactSQL than in PostgreSQL pgSQL). I am quite happy to accept that in some situations, that MySQL is better than PostgreSQL.

The key is to use the most appropriate database technology for the business case, allowing for current and likely future needs.

The people who donate systems to the PostgreSQL buildfarm go out of their way to include some weird architecture/compiler combinations whenever possible for this same reason. Some of the bugs that show up on these platforms more spectacularly exist on the most popular AMD64 version, too, they're just harder to come across. Processor diversity is great for flushing out some types of subtle bugs.

PostgreSQL will stay open, and stay strong.

Until Oracle buys them up too.

Unlike MySql, there seems not to be a single entity that owns the copyright for PostgreSQL. Meaning: Oracle would have trouble to buy all the copyrights, probably it will think twice before doing trying to do it (and at the secind round of thinking, will actually stop of even attempting).

MySQL is great for general web purposes. I'd say MongoDB is fine for much web data, and it makes MySQL look like a fuddy-duddy. But not for banks.

If you want a robust open source RDBMS, go for PostgreSQL .

Heck, I've seen cases where some fairly large Oracle installs were converted to PostgreSQL, and fairly painlessly, as that there is significant similarities (good procedural language capabilities in PG).

If you want a real open source database, with a real commitment to not being evil, check out PostGres. I've preferred it over MySql for a really long time, for technical as well as political reasons.

MySql might have had better marketing, but for most purposes it was never the best open source database.

Just out of interest - i've always wondered why Postgres seems to trail in popularity to MySQL. I know the limitations of the latter having used it far too much, does anybody know where Postgres trails MySQL?

Specifically - what's the clustering support like? Can you do hotbackup without LVM hacks? etc. I'd love to use Postgres, but need confidence that it's not got a massive deficiency somewhere.

If you want a real open source database, with a real commitment to not being evil, check out PostGres. I've preferred it over MySql for a really long time, for technical as well as political reasons.

MySql might have had better marketing, but for most purposes it was never the best open source database.

If only more people ditched MySQL, nobody would bother offering it, and would set some reasonable prices on VPS hosting. Oh, wait... http://www.postgresql.org/support/professional_hosting_northamerica

Official pronounciation recording. Postgres-Q-L. "Postgres" is also fine.

Clickable link. The summary of that publicly available benchmark was that just turning off the normal data integrity features in PostgreSQL, specifically its aggressive use of the fsync system call, was enough to make PostgreSQL run as about as fast or faster as any of the popular NoSQL approaches. Some of the NoSQL alternatives had significantly lower data loading times however. But as a whole, only MongoDB really had any significant performance gain, everything else was hard pressed to keep up with boring old Postgres when comparing fairly--MongoDB certainly doesn't use fsync for example.

There are some intermediate steps between "no integrity" and "full transactional integrity" available in PostgreSQL as well, so you can adjust how much you're willing to pay per commit on a per transaction basis. Combine this with the fact that a key-value store with good performance is available, and you can get most of what NoSQL promises with it when that's appropriate, while still having full database features available when you need those too.

Thanks to Joshua for the nice review here. There are actually a few more samples from the book than just the one chapter; here's a full list of them:

• Database Hardware - sample chapter PDF
• Reliable Controller Disk Setup - see also Reliable Writes
• Balacing Hardware Spending
• Server Configuration Tuning - see also Tuning Your PostgreSQL Server
• UNIX Monitoring Tools for PostgreSQL
• PostgreSQL Tips and Tricks

In addition to this one and the customer reviews at Amazon, there have been two other reviews by notable PostgreSQL contributors: Buy this book, now and PostgreSQL 9 High Performance Book Review.

As alluded to in the intro, the book tries to cover PostgreSQL versions from 8.1 though 9.0, with a long listing of what has changed between each version to help you figure out what material does and doesn't apply. So most of the advice applies even if you're running an older version. There is also a companion volume to this one of sorts also available, PostgreSQL 9 Admin Cookbook, that was written at the same time and coordinated such that there's little overlap between the two titles. That one focuses more on common day to day administration challenges, less on the theory.

Thanks to Joshua for the nice review here. There are actually a few more samples from the book than just the one chapter; here's a full list of them:

• Database Hardware - sample chapter PDF
• Reliable Controller Disk Setup - see also Reliable Writes
• Balacing Hardware Spending
• Server Configuration Tuning - see also Tuning Your PostgreSQL Server
• UNIX Monitoring Tools for PostgreSQL
• PostgreSQL Tips and Tricks

In addition to this one and the customer reviews at Amazon, there have been two other reviews by notable PostgreSQL contributors: Buy this book, now and PostgreSQL 9 High Performance Book Review.

As alluded to in the intro, the book tries to cover PostgreSQL versions from 8.1 though 9.0, with a long listing of what has changed between each version to help you figure out what material does and doesn't apply. So most of the advice applies even if you're running an older version. There is also a companion volume to this one of sorts also available, PostgreSQL 9 Admin Cookbook, that was written at the same time and coordinated such that there's little overlap between the two titles. That one focuses more on common day to day administration challenges, less on the theory.

Time and time again, the question of Oracle-like hints for PostgreSQL pops up on the PostgreSQL mailing lists. I thought I share some links as I find the topic fairly interesting. Hopefully the DBAs out there will too.

Why PostgreSQL Doesn't Have Query Hints
Why PostgreSQL Already Has Query Hints
Plan Tuner - Ripped from the above link

And in case you don't know, this is a great place to stay current with PostgreSQL development and technology.

It's quite reasonable to expect real databases to handle things like login and storing the passwords, especially if they're already storing users. This is, after all, what databases are for: data management. If you want multiple applications to use the same database (and you should, because databases are integration technology, not merely storage technology) then you want them all to be able to authenticate the same way, easily, and you want that functionality managed centrally with the data itself. PostgreSQL's supplied pgcrypto extension has a crypt function that works like bcrypt and uses whatever salt is stored in the password hash. The documentation shows how to use this to implement secure password storage and authentication.

Of course, if you're using MySQL, you already have an architecture problem.

The biggest problem with making a mysql compatibility layer on top of postgres is dumbing down the whole interface to be compatible. It would involve removing so many items and breaking many standards. You might as well just keep using mysql in the meantime while you port your app to postgres.

And... the whole point of a compatibility layer is so that you can either 1) run your whole app as-is and keep developing as-is on the layer on top of the system. So now you have zero gain from moving to postgres, you're still using the same old cruft... or 2) you want to run some code in the compat layer and some code in the native layer. Now you open up a whole huge can of worms. Now you need to specify which tables/functions/etc are running in which platform, the screwy mysql one, or the real postgres one. Unforeseen interactions between the two would be full of fun surprises I can assure you.

Off the top of my head I can think of a few horrid problems with mysql that would have to be ported. Silent truncation for instance. Insert 30 characters into a 20 character field and mysql will gladly accept it, and not even throw you an error. Now there is a flag you can turn on that will give you errors on some of these issues, but not all of them.

There's several other silent behavior type problems in mysql that would need to be ported over. See: http://code.openark.org/blog/mysql/but-i-do-want-mysql-to-say-error

There's a bit of a history of silently doing nothing with bad data, or silently doing nothing when there's a problem in general. See: http://use.perl.org/~Smylers/journal/34246

This next writeup is a year old, and obviously some shortcomings of mysql have been fixed up... but many of these issues still remain. The issues listed that have not been fixed.... and then some, would have to be ported.

See:
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009

In conclusion... why waste time in porting such an array of limitations and buggy behavior to such a great platform.

You might say 'but EnterpriseDB is an oracle layer on top of Postgres'. Yeah... Oracle... a real database, not mysql.

Someone did get out their checkbook, which is why MERGE has been under development for months already, with working prototypes being tested since late August. The hope is that this makes it into PostgreSQL 9.1, due to be released next summer. Right now trivial cases work, the main bugs found in the last round of review involve concurrency issues that are still being ironed out.

That's a great list. One item on the list, "External security provider", has huge security implications for PostgreSQL down the road.

Or either MySQL or PostgreSQL could implement support for SQL:2008 MERGE syntax which is the appropriate method for handling this scenario, as well as countless others.

http://en.wikipedia.org/wiki/Merge_(SQL)

It's target for PostgreSQL is 9.1

http://wiki.postgresql.org/wiki/PgCon_2010_Developer_Meeting#Development_Priorities_for_9.1

Oracle on a single system doesn't scale much better (if even) than PostgreSQL on a single system. Oracle's cluster solution is nice, but the expense is crazy.

Yep. I just have to add that I'm following the development of Postgres-XC (that's "extensible cluster") with more than a small bit of interest because of this, even though they're still missing quite a bit of functionality that'll be required for a 1.0 release. Even the improvements to the replication solution they delivered in 9.0 (that "Active Data Guard"-ish thing) that they've got planned for 9.1/9.2 is tickling my interests since it'll mean that PostgreSQL will have a good solution to things where previously the crazy expensive Oracle solutions were the only really nice ones.

Oracle on a single system doesn't scale much better (if even) than PostgreSQL on a single system. Oracle's cluster solution is nice, but the expense is crazy.

Yep. I just have to add that I'm following the development of Postgres-XC (that's "extensible cluster") with more than a small bit of interest because of this, even though they're still missing quite a bit of functionality that'll be required for a 1.0 release. Even the improvements to the replication solution they delivered in 9.0 (that "Active Data Guard"-ish thing) that they've got planned for 9.1/9.2 is tickling my interests since it'll mean that PostgreSQL will have a good solution to things where previously the crazy expensive Oracle solutions were the only really nice ones.

Your link is about collations - what is used to sort data in ORDER BY queries. It has absolutely nothing to do with case sensitivity of SQL keywords and identifiers.

I have not seen any SQL implementation with case-sensitive keywords so far. They are certainly not that in MSSQL. For identifiers, it's more problematic - quoted ones ("foo") are always case sensitive, but unquoted ones (foo) are handled differently. The standard says that foo should mean the same as "FOO", and that's what MSSQL does, but e.g. PostgreSQL deviates from that, lowercasing instead of uppercasing.

Buy it? Read the license, they can have it!

Many cheap hosting companies don't offer PostgreSQL because there's not enough demand for it; there's not enough demand because people don't know where to host the result, and therefore don't develop against it. You have to break that dependency one person at a time to start reversing the network effect here. There's a list of PostgreSQL Hosting companies that includes multiple entries in the sub $10/month range. So while it's still true that most cheap hosting companies don't support it yet, if you demand true software freedom from your database there are inexpensive hosting options available. And more people are waking up to realize this is an important enough reason to start migrating to PostgreSQL every day.

But you can set it at the transaction level:

http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously.

It sounds like you want to use PostgreSQL as something like a (non-durable) cache layer. I'm not sure anyone has tried removing WAL entirely from the system, but people have definitely played around with the idea. See e.g. here. With a little tweaking, you should be able to get a large speedup by sacrificing durability/crash recovery.

Well, the GP was probably asking more about materialized views integrated into the Postgres core itself (I think Oracle has these). So that you could materialize an existing view just by saying "CREATE MATERIALIZED VIEW ... ", instead of cooking up the triggers yourself.

Now, The page you linked to actually says:

I do not recommend that PostgreSQL add Materialized Views in its core.

because of their inherent complexity and customizable nature. There actually has been some work recently attempting to do just this: see e.g. http://archives.postgresql.org/pgsql-hackers/2010-04/msg00479.php.

As for the GP's gripe about "those two ridiculously huge undo-type files", I have no idea what he's smoking. Postgres's "undo-type" files are its WAL logs, and you can configure how many it keeps around. They are 16 MB each by default. I've had no problem running Postgres on tiny hardware (cheap VPSes), and it comes configured out of the box to do just fine on minimal hardware.

Postgres development team, if you're reading this, I lost my old ash-grey PostgreSQL shirt and would be glad to buy a replacement from you.

You've got the performance part backwards for PostgreSQL; it goes up with every release, sometimes a little, sometimes in a big way. See PostgreSQL history for a comparison covering versions 8.0 to 8.4. The mild regression in 8.4 shown there is actually reversible; it's mainly because a query related parameter for how many statistics to collect and use for query planning was increased by default. That results in better plans for most real-world queries, but it detuned this trivial benchmark a little bit. You can get performance back to 8.3 levels just by turning the parameter back to the "optimized for trivial queries" default of the older versions if you care about that. Most people prefer the new default. In the real world, 8.4 is actually faster due to improved handling of background VACUUM tasks too, which don't show up in simple benchmarks either.

I'm the current lead architect on building a PostgreSQL Performance Farm to prevent regressions from popping into future versions of the code too. There is a recently completed beta client for that purpose. We're in the process of working out how to integrate into future development, starting with 9.1, so that potential regressions are spotted on a commit by commit basis. I haven't seen any performance regressions between 8.4 and 9.0, only moderate improvements overall and large ones in specific areas that were accelerated.

Now, if you use some of the new replication features aggressively, that can add some overhead to slow down the master. But that's true of most solution; the data coming off the master has to take up some time to generate. The way PostgreSQL 9.0 does it is is pretty low overhead, it just ships the changed blocks around. Theoretically some statement based solutions might have lower overhead, but they usually come with concerns about non-determinism on the slaves when replayed (random numbers, timestamps, and sequence numbers are common examples).

Given the non-disclosure terms of most of the closed source databases, nobody can publish benchmarks that include them without going through something like the TPC or SPEC process. The last time that was done in 2007, PostgreSQL 8.2 was about 15% slower than Oracle running the same database-heavy workload. And note that it was PostgreSQL 8.3 that had one of the larger performance increases, so that was from just before a large leap forward in PostgreSQL performance.

At this point, Oracle and most other commercial databases still have a large lead on some of the queries run in the heavier TPC-H benchmarks. Links to more details as to why are on the PostgreSQL wiki. It just hasn't been a priority for development to accelerate all of the types of queries required to do well in that benchmark, and nobody so far has been willing to fund that or the subsequent certification via the TPC yet. Sun was the only one throwing money in that direction, and obviously the parts of that left within Oracle will no longer do so.

You know that you can point your MS Access client to any supported back-end right? Just create an ODBC connection on your Windows machine to your PostgreSQL server and you can use Access with pretty much all the features that work for the Microsoft JetEngine (PostgreSQL has ODBC drivers here; http://www.postgresql.org/ftp/odbc/versions/)

Earlier this year we converted a huge Access application from MSSQL to PostgreSQL and the technical conversion, using ODBC to PostgreSQL instead of connecting to MSSQL, was a piece of cake.

hrm? The documentation is regularly updated... http://www.postgresql.org/docs/9.0/static/

sql = malloc(ONE_MEG);
sql = sprintf(%s;%s;%s;%s;%s;%s;%s;%s ...", query1, query2, query3, query4, query5 ....);

Basically, any thread that had a request added it to the string. A thread might have 10 different selects. Why not do them all at once, along with 50 other threads?

Put 1000 queries into that single string, then call the c api to execute it. Then "peel off" the results. You can't do this with php, and you may have some difficulty doing it with java (I'd have to check the API), but under c with mysql, it works like you wouldn't believe. A word of caution - use ON DUPLICATE KEY as much as possible - this way, if you have two statements that would both try to append the same key value, you can use the latter one to do an update. This solves the biggest problem of multiple-statement execution. Unfortunately, pg doesn't have on duplicate key yet.

Obviously made-up example "insert into users (name, id, access_time) values "blah" "blah" "blah" on duplicate key update users set access_time=now() where id="id" limit 1";

"on duplicate key" is one of those miracle statements - when you need it, it works miracles.

Looking at the pglibs it isn't able to return everything for a multi-statement query, only the last result - darn, that kind of sucks, but it can still be useful - for example, you might have 4 or 5 inserts, a few updates then a select. You could do them all, saving the select as the last statement so you get the results. "insert into blah; insert into foo; insert into yuck; update sess; select from pages;" would return your select (or an error if a previous statement failed).

Here's the docs for mysql c api multiple-statement execution and result retrieval.

BTW - since everyone's been on my case claiming that "java can be faster than c" and that "gcj compiles code" I figured I'd , and I also tested gcj and took a look at the "compiled" program.

Java has its place, but not where you need high performance. It's a dog, really :-)

Oracle has a serious hard-on for Java, which you can see because it is the only major database I know of that allows you to use Java in place of PL/SQL.

Most databases have similar features, for example Sybase ASE & PostgreSQL.

IBM is going to have something to say about this eventually I'm sure. They've also bet heavily on Java and open source. Yes they completely missed the bus on the purchase of Sun. However, that doesn't mean they'll sit idly by while Oracle pisses in the Java sandbox.