PostgreSQL 8.3 Released

jadavis writes "The release of the long-awaited PostgreSQL version 8.3 has been announced. The new feature list includes HOT, which dramatically improves performance for databases with high update activity; asynchronous commit; built-in full text search; large database features such as synchronized scans and reduced storage overhead; built-in SQL/XML support; spread checkpoints; and many more (too many major new features to list here). See the release notes for full details."

Comments

PostegreSQL 8.3?

[Anonymous Coward]

Would that be POSTGR~1.SQL?

Re:PostegreSQL 8.3?

[Anonymous Coward]

"pgBouncer
This multi-threaded connection pooler allows a single PostgreSQL database server to support up to 100,000 application server connections. "

I've always wondered how people can get more than 2^16 simutanous connections to any IPv4 host using TCP?

Re:PostegreSQL 8.3?

[Anonymous Coward]

Maybe using more two or more network interfaces? (Not sure if it is possible, just speculating.)

Re:PostegreSQL 8.3?

[Anonymous Coward]

By having the connections come from more than one source?

Re:PostegreSQL 8.3?

[corsec67]

Unix sockets?

Re:PostegreSQL 8.3?

[sparks]

There are 2^16 port numbers, but there are no theoretical limits on the number of connections incoming to a single port.

A TCP connection is uniquely identified by *all of* the source IP address and port, and the destination IP and port. There can be many connections to a server on a particular port, distinguished by the client address and port number.

Of course to get more than 2^16 connections you would need more than one client machine.

Re:PostegreSQL 8.3?

[Anonymous Coward]

IPv6, multiple IP addresses, etc.

For a smart fellow you aren't that clever.

Re:PostegreSQL 8.3?

[fbriere]

There are 2^16 port numbers, but there are no theoretical limits on the number of connections incoming to a single port. Actually, given that IP has a finite address space, there is a theoretical limit: 2^48 connections for IPv4, and 2^144 for IPv6.

Re:PostegreSQL 8.3?

[sparks]

That's what I get for posting at 4am ;) You're right of course. But I don't suppose too many databases are engineered for two hundred trillion simultanous users...

Yeah, it's HOT, but is it FAST!

[Reality Master 201]

Like MySQL?

Fast is all that matters, you know.

Re:Yeah, it's HOT, but is it FAST!

[0racle]

Yes

Nice.

[LWATCDR]

I will probably wait for a while before I update but this looks great.
HOT and the full text search are two features that I could use.
Postgres is a good reliable database server. I just wish more projects supported it as an equal to MySQL.

Re:Nice.

[plague3106]

Indeed. I like Postgres much more than MySql, and I think its a great database engine.

Re:Nice.

[Seumas]

8.3 had me at "full-text search".

Now, please excuse me while Postgres 8.3 and I go take a little alone-time in a dark closet.

Re:Nice.

[ianare]

Agree. For an in-house project tracking system we decided to go with MySQL, not because it is better, as we found Postgres superior, but because of lack of support from various software components.

Re:Nice.

[ashridah]

Don't get your pants off too quickly, you might want to just settle for a crotch-grab. It's TSearch2 integrated in-tree, with some extra SQL features to make using it easier. It's not completely new, you could do most of it before 8.3.

ash

Re:Nice.

[tacocat]

You have made two very serious flaws with your thinking about how and why you chose MySQL. In general it's rampant with PHB thinking.

There is the obvious discussion you didn't have when you decided MySQL was better for your company. Why would you take something as important as your company database and leave it to a vendor to support? You have no in-house knowledge of your database. You have no back-up in the event that vendor gets into a contract dispute with you. And every time you need their support on anything you have to go through this slow interface of vendor management rather than just leaning over to the company employee named Roy who lives and breathes MySQL or PostgreSQL and say, "Hey Roy! Somethings wrong with the database. I need you to fix it right now. I'll get you a pizza for lunch."

The other error in your thinking is more elusive but this reminds me of a conversation I had with a room full of people regarding the expansion of Java at the potential cost of Perl developers.

"I can get a whole boatload of Java developers from overseas with a phone call and 72 hours. It's very hard to find 20 or 30 Perl developers for a project."

Nobody in that room had ever worked on a project requiring more than 5 developers. And they had worked on some largest Perl projects out there.

Subsequently I have found a lot of really bad Java developers.

I can have a similar discussion with MCSE in place of "Java developers".

The flaw is that you assume popular support equates itself to good support. I think the exact opposite is more likely to be true. If someone is in need of a quick job and quick bucks, they will take the fastest and lowest entry barrier solution. First comes Am-Way. But with a week of sitting in classes for each they can quickly become a Java certified developer, MySQL DBA, or dot-NET developers. All this in three weeks time and with the credentials to prove it. Are they any good at what they do? Not likely.

The contrary group of people are those who use PostgreSQL over MySQL or SQL Server, Linux over Windows, Perl/Python/Ruby over dot-NET or Java. They stay here and learn this less popular software because they enjoy it and find it interesting. It's generally not a decision based purely on money. It could be argued that Ruby isn't in the same camp as Perl & Python because it's having something of a feeding frenzy in the PHB camp. But that's Rails, not Ruby, and they are different.

It is fair odds that if I put out a couple inquiries I could find PostgreSQL support in Detroit, MI (not a software haven) within 72 hours and based on it's lack of a Marketing and PR campaign the person who shows up at the door will know more about PostgreSQL and how to get it working smoothly than the MySQL counterpart who shows up at my door. And I will only need one of them as opposed to hiring 2 or 3.

Re:Nice.

[SL Baur]

It is fair odds that if I put out a couple inquiries I could find PostgreSQL support in Detroit, MI (not a software haven) within 72 hours and based on it's lack of a Marketing and PR campaign the person who shows up at the door will know more about PostgreSQL and how to get it working smoothly than the MySQL counterpart who shows up at my door. PostgreSQL is quality software and the guys who develop it seriously impress me. It really doesn't take a lot of specialized knowledge to support it because they follow standards. Trade books in standard SQL work just fine, in my experience, developing applications with it.

The PostgreSQL source and developers were worth more than a billion US dollars a long time ago, but that's my opinion.

Re:Nice.

[SolitaryMan]

Postgres is a good reliable database server. I just wish more projects supported it as an equal to MySQL.

The thing I dislike about MySQL is that it holds back many projects that are trying to be database independent.

For a quick example: how most universal ORMs handle creation of the new object?

1. SELECT obj_id_sequence.nextval AS new_id
2. INSERT INTO my_table(id, ...) VALUES (new_id, ...)

While Postgres can do fine with single statement: INSERT ... RETURNING (There are UPDATE ... RETURNING and DELETE ... RETURNING too ). All ORMs I know do not use this feature and end up doing two times more transactions than required.

Re:Nice.

[LWATCDR]

The real advantage that MySQL has right now is the number of packages that are MySQL centric.
You need a Wiki? Odd are that it supports MySQL and can be made to work with Postgres. Need a content management system? Odds are that it was written for MySQL and might work with Postgres. So you probably already have MySQL in house so people tend to go with MySQL even when Postgres is a better solution even if for no other reason than why maintain two databases.
The only reason why we use Postgres for several of our in house programs is because I wrote those before we put up an in house Wiki or other web stuff. Postgres offered row locking back then when MySQL didn't. I loved transactions when I finally got to use them.
The sad thing is that if I did it over today I would probably pick MySQL since it is so popular and now supports row locking and transactions.
Postgres is the better system but it lacks the depth of support that MySQL does. Sort of like BSD vs Windows.

Re:Nice.

[poot_rootbeer]

You need a Wiki? Odd are that it supports MySQL and can be made to work with Postgres. Need a content management system? Odds are that it was written for MySQL and might work with Postgres.

AFAIK there are no features which MySQL has that could not be replicated using PostgreSQL. Any code -- I'm assuming a PHP platform here -- that assumes the database is MySQL should be easily adaptible to use PostgreSQL, even if it requires a translation layer to be written and a search-and-replace to convert PHP-native mysql_real_*() functions with properly abstracted equivalents.

Postgres is the better system but it lacks the depth of support that MySQL does. Sort of like BSD vs Windows.

And that's why most websites run on Windows servers -- there's just so many more MSCE certificate-holders than competent *nix admins!

Re:Nice.

[ahodgson]

And that's why most websites run on Windows servers -- there's just so many more MSCE certificate-holders than competent *nix admins!

+5 funny

Re:Nice.

[LWATCDR]

Not every office has someone that knows PHP. I do but not everyone does.
Not every office has the time to hack a wiki to use Postgres.
Not every office will want to have to re hack the wiki to use postgres when an update is made available.
Very few people will want to hack Sugar CRM to work with Postgres. ....
Yes there isn't a single job that MySql does that Postgres can not. Too bad that it is treated like a second class citizen.
Postgres is better, MySql is the path of least resistance right now.

Re:Nice.

[jadavis]

A feature is a feature. Before 8.3 people criticized postgresql for not having full text search because it was an add-on. I think people didn't understand how powerful an add-on can be in postgresql.

Anyway, it's integrated now, so that's one big check mark for postgresql.

Re:Nice.

[Seumas]

I'm not a professional DBA. I'm just a guy with a non-profit website that handles thousands of auctions a day and I really wanted full-text search, but figuring out the available solutions was kind of a hassle. Having some form of FTS built-in as an out of the box function is fantastic.

Re:Nice.

[aevans]

But both of your finds willing to support PostgreSQL live in their mother's basement. One of them is a social outcast, but a technical whiz about what he does know, the only problem is, he doesn't know anything other than his brilliant Klingon grammar AI. The other is a Slashdot fanboy who uses PostgreSQL instead of MySQL on his "personal home page" because he wants to feel 3leet! and couldn't tell the difference between a query and a stored procedure. He doesn't vacuum his room or his DB. Neither one will be able to help you out. Ironically, there was a PG expert who hated his day job administering Oracle and MySQL who could help you, if he hadn't given up on the rigors of work gone back to grad school to study database theory.

Re:Nice.

[tacocat]

But if everything is written on Rails then the difference between MySQL and PostgreSQL are buried beneath the layers that the developers care about. There are few, if any, changes required to go from one to the other.

Cross Database Joins??

[Foofoobar]

The one thing that has stopped me from picking up Postgresql yet is that I can't do cross database joins on the same server. Should a user have the same permissions on two separate databases on the same server, a properly constructed query should be able to join across multiple DB's but they still don't implement this yet that I am aware of.

Re:Cross Database Joins??

[geniusj]

You should consider using schemas as opposed to entirely separate databases.

Re:Cross Database Joins??

[Anonymous Coward]

I'm confused. Do you mean you should avoid cross-database joins during the design phase? If so, this isn't always possible. Often databases are created in a vacuum and only later does one need to utilize multiples in a query.

Re:Cross Database Joins??

[dfetter]

There are several ways to do cross-database JOINs in Postgres including dblink, and even to other DBMSs via dblink-tds and DBI-Link, but try schemas first, as another poster mentioned.

Re:Cross Database Joins??

[Foofoobar]

Agreed. And often times, the simple solution is often easier and faster than the complex solution. Why add several additional steps when a cross database join will do the trick without any security concerns? If you have you structures separated and your data separated properly in an MVC codebase, a simple cross database join can save alot of time and simplify a procedure that other procedures would turn into a 4 or 5 step process.

Re:Cross Database Joins??

[poet]

Look at plproxy or use schemas.

Re:Cross Database Joins??

[Anonymous Coward]

Postgres implements a oracle-style 1 database / many schemas rather than the mysql definition of database = schema.

doing a cross-database join in mysql is the same as doing a cross-schema join in oracle or postgres.

Re:Cross Database Joins??

[glwtta]

Often databases are created in a vacuum and only later does one need to utilize multiples in a query.

That feels wrong somehow; if your (logical) databases are so distinct that you can't plan to co-locate them in the same (Postgres) database, does it make sense to have such tight coupling on the query side? Now you have to synchronize the data between them, and you can't move them off the same machine, so what's the point of keeping those databases separate? It also seems like client code should never have to know where different databases are physically located.

I don't agree that this is the "simple solution", it's a horrible hack on the part of the database engine (I don't actually know if anyone apart from Oracle does this) with unpredictable performance results - looks more like the "lazy solution".

I don't know, just seems like such a thing breaks the database/application "contract".

Besides, shouldn't your ORM layer abstract such minutiae away pretty easily?

Re:Cross Database Joins??

[electroniceric]

Use dblink:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.17

Not as slick as doing it entirely in SQL, but it works.

Re:Cross Database Joins??

[naasking]

Sounds like a distributed application, whose distributed properties were then broken by forcing the data to be located on the same host. A shame.

Re:Cross Database Joins??

[prog-guru]

I don't know, seems to me a cross database join is neither simple nor proper structure. If you are stuck with it schemas would probably work, painless in Active Record, perl DBI requires 'schema.table' syntax.

Re:Cross Database Joins??

[Foofoobar]

Object Relational mapping is an entirely separate argument which could go on infinitely into the horizon. A cross database join is implemented is most major RDBMS... with the exception of POSTGRES. A common example is with a USR table. The USR_ID is shared across multiple databases but rather than building schemas for each instance that you would like to join on, you just join. Can you imagine the numbers of schemas you would have to maintain and manage in this instance if you had alot of databases that used that usr_id as a foreign key in their tables???

Re:Cross Database Joins??

[Foofoobar]

Oh one additional note, you can't version control the entries in a database. This would be another reason why you want the queries separate. When alot of your code (like I illustrated in my previous example) is based upon schemas, you are relying on your data remaining intact and your database not getting corrupted. At least with a versioning control systems you have your backups, your checkouts, your test environments, your tags and your production code.

Schemas will fail in this instance as well. Not a very good solution all in all.

Re:Cross Database Joins??

[Qzukk]

the simple solution is often easier and faster than the complex solution

When you want something that walks like a duck and talks like a duck, the simplest, fastest, and easiest solution is to get a duck. What you want is done by schemas in postgresql. If you're really doing the separate databases for "performance" reasons, then one presumes that at some point you're going to be putting the databases on separate servers, in which case you'll be wishing you had started with dblink in the first place.

What you're asking for may be "simple" for you, but what about the server side? Rewriting the entire database engine to have a monolithic superdatabase to store system catalogs and authorization information for the databases underneath is none of "simpler, faster or easier", while it does the exact same thing for you as having a "super" database with schemas underneath it using the search_path setting for applications that you don't want to rewrite with schema.table syntax for all of their queries.

Re:Cross Database Joins??

[Martin Foster]

Only if you plan to use multiple schemas at the same time. Otherwise you can use the defaults (public and the users name) or set the schema search path to whatever is desired. From that point forward using a table name will result in a search or the implied schema name if you only include the one.

I use this all the time in Perl to do a sort of virtual hosting capability. Multiple copies of the same database under a different schema to keep information contained.

Re:Cross Database Joins??

[nconway]

While it is true that dblink and dbi-link can be used to do cross-database joins, they are not even remotely close to being able to do them in an efficient manner.

Re:Cross Database Joins??

[dfetter]

I'm looking forward to the hooks you're working on that will make them more efficient :)

Re:Cross Database Joins??

[Anonymous Coward]

Yeah, if you're doing cross-DB joins, something is very wrong. You do joins across tables, and each DB represents some separate domain of information. That's like saying, "one of the annoying things about Unix is that one app can't access another app's memory space."

Re:Cross Database Joins??

[nullchar]

For our 'users' table, we do table-level replication from the users database to each database that needs to join with 'users'. This limits us by only being able to read from the replicated table -- we require a separate connection to write to the 'users' table. (Other tables like 'user_preferences' and such may be replicated as necessary.)

Once the table is replicated, we simply join on user_id like we would if 'users' was native to our DB. We heavily foreign key off of user_id. This means deletes cannot be cascaded. Thus users cannot easily be deleted so we have statuses and types and such to define user roles and authorization.

Re:Cross Database Joins??

[CaptainZapp]

I don't agree that this is the "simple solution", it's a horrible hack on the part of the database engine (I don't actually know if anyone apart from Oracle does this) with unpredictable performance results - looks more like the "lazy solution".

You can do it with Sybase and it's a terrible idea. It gets even worse when you have constraints on tables betwwen databases. Good luck when you have to restore the databases and good luck to dump them in a way so that their integrity is preserved. Especially on a busy db.

Re:Cross Database Joins??

[jadavis]

Are there DBMSs that can optimize joins across physically separate machines?

Re:Cross Database Joins??

[bytesex]

How do you make cross-database joins transactional ?

Re:Cross Database Joins??

[nconway]

Sure -- Greenplum DB is one such database. There's a lot of research on distributed databases from the 1980s onward that addresses this topic in detail.

Re:Cross Database Joins??

[aproposofwhat]

You can do it in MS SQL Server too - perhaps a throwback to the Sybase days.

So long as it's a simple 'select' query, I have no objection to joining between databases - it's just messy, obscure and bad practice.

Re:Cross Database Joins??

[poot_rootbeer]

You seem to be using a different definition of 'database schema' than I'm familiar with.

The SQL concept of schema is perhaps more similar to namespace than anything else. What does version control have to do with anything -- and under what circumstances would you NOT need to rely on your data remaining intact and your database not getting corrupted in order for the system to behave correctly?

Re:Cross Database Joins??

[Anonymous Coward]

Did cross database joins when there is a large common data set, such as when I did a few MLS websites. We had the MLS data in one database, then realtor specific data in their own database. Cross-database joins between MLS properties and the realtor's specific additions to the MLS data.
It wouldn't make sense to copy a 500MB database that updates daily to ~20 realtor's databases. It also didn't really make sense to add tables to a common database.
Used MySQL

Re:Cross Database Joins??

[einhverfr]

Besides, shouldn't your ORM layer abstract such minutiae away pretty easily? Although I would prefer simulating the old app's structure using schemas and views. ORMs face fundamental math problems which have no universally safe answers (i.e. OO and Relational models of data cannot be transformed to eachother losslessly because of the different definitions of data dependencies).

Re:Cross Database Joins??

[ShieldW0lf]

Oh one additional note, you can't version control the entries in a database. This would be another reason why you want the queries separate. When alot of your code (like I illustrated in my previous example) is based upon schemas, you are relying on your data remaining intact and your database not getting corrupted. At least with a versioning control systems you have your backups, your checkouts, your test environments, your tags and your production code.

That's the dumbest thing I've ever heard. The whole point of a database is that it uses transaction logging, which gives you a complete play by play of every single change made to the data since it was created when done properly, and allows you to roll backward and forward in time as you see fit.

It's also part of the reason you don't do joins or foreign keys across databases as a rule. They have separate transaction logs, indexes and locking per database, and when you try to violate those bounds, you're breaking the aspects of the database that made it useful in the first place and turning it into a fragile and corruptible mess.

Good version control systems, on the other hand, are made to be broken casually and merged in a resilient fashion. They're made to enter and leave inconsistent states on a regular basis.

You don't have pointy hair by any chance, do you?

frist psot

[Anonymous Coward]

muthafuckas 1st post

Re:frist psot

[Anonymous Coward]

u fail.

Re:frist psot

[Anonymous Coward]

Verily sir, when it cometh to failing it, thou art truly a success.

Will it be used?

[Anonymous Coward]

I'm a postgresql fan, I've considered it a superior database for years.

However, it seems every client I come into contact with (I am a freelance software designer) seems to believe mysql is the only open source database available and certainly the best one for all jobs.

Mysql is great (or at least, was great) for fast connection times and speed but for a stable, feature-rich excellent database, postgresql has always been ideal.

It's just a shame no one seems to be aware of it.

Re:Will it be used?

[cheater512]

People like myself who design software requiring a database usually prefer speed over features.
Thats why MySQL is usually chosen.

Re:Will it be used?

[ByteSlicer]

That, and people are more likely to remember 'My' than 'Postgre'.

Re:Will it be used?

[jadavis]

People like myself who design software requiring a database usually prefer speed over features.

Keep in mind that PostgreSQL may have more stable performance for a varied workload. That may mean fewer surprise slowdowns for you.

I don't know your specific situation, but you may want to re-evaluate postgresql for your needs, especially if you care about performance -- PostgreSQL made leaps and bounds in this area in 8.3. I'm not sure what the last version you tried was, but 8.2 was a good performance boost as well.

And if it still doesn't hold up to your expectations, please post your benchmarks to pgsql-performance, so that others can either help you meet the needs, or improve postgresql for the future.

I would think also, as a developer, you might like the data integrity available in PostgreSQL that can help catch tough bugs early. Also, MySQL has many configurable options that may make your application not work and your customers unhappy (including table type -- changing table types from MyISAM to InnoDB or vice-versa may break applications). PostgreSQL's options (for the most part) don't affect the application.

Re:Will it be used?

[Anonymous Coward]

People are ignorant and/or just go for the name they know -- that is usually why they choose MySQL. Embedded DBs run circles around it for simple tasks with low concurrency, while postgres kicks its butt on complex queries or higher loads.

It's also a piss poor platform for anyone who knows how to work a db (no, I don't use sprocs). I've toiled more than my fair share with mysql, and see no reason for going back.

Re:Will it be used?

[LurkerXXX]

You people who design software should read up on this thing called data integrity, and enforcement of foreign key constraints.

MySQL is pretty bad at those, but if you use an innodb table and try to use them, you find it's no faster than postgresql. And still missing many many features that postgresql gives you.

Re:Will it be used?

[cheater512]

Stability isnt critical for my applications.
Raw speed is however. A decrease in speed would be rather bad.

I'm talking about 600 queries per second averaged over the day.
I've never bothered figuring out what the peak is. I'm scared what it could be. :)

All I really use is basic SELECT, INSERT and UPDATE.
Nothing fancy. Just straight basic SQL.

MySQL fits these requirements perfectly.
PostgreSQL not so much.

Re:Will it be used?

[jocknerd]

If you want raw speed, you should be using SQLite instead of MySQL. Plus, are you using the InnoDB database engine? If you are, then you are not getting MySQL's raw speed. That only comes with their native database engine. You know, the one that doesn't support much of SQL at all.

Re:Will it be used?

[cheater512]

Last time I checked, SQLite didnt handle 100gig databases very well. :)
And I am using MyISAM.

Re:Will it be used?

[cheater512]

There are many instances where they are not needed.

Re:Will it be used?

[LurkerXXX]

And even more instances where they are needed, but not known/understood by a MySQL user.

Re:Will it be used?

[Plaid Phantom]

You have a 100 GB database and you're not concerned with stability??

Do you work for the government?

Re:Will it be used?

[cheater512]

No I dont work for the Government.

MySQL is very stable.
Guess what Google uses for some things?
Guess what many big websites use?

Re:Will it be used?

[ShieldW0lf]

Stability isnt critical for my applications. Raw speed is however. A decrease in speed would be rather bad.

Memcached?

Re:Will it be used?

[cheater512]

Yep I do use Memcached.

Re:Will it be used?

[Sancho]

Yeah, I used to be one of those idiot users. The database was nothing more than a datastore, and WHERE was nothing more than a handy way of filtering results. It's a really common thing, as learning SQL syntax is easier than writing your own data-handling routines.

But man, once you get the relational DB bug, it bites hard. I wouldn't touch MySQL with a 10 foot pole these days. Even with InnoDB, it's just not as good as Postgresql.

Re:Will it be used?

[gullevek]

The speed thing is slowly getting an urban legend. Postgres got really, REALLY faster since the 8.0 release and I am sure 8.3 will boost that more.

I always take postgres over mysql, I trust postgres more in a long term run with tons of data, than I trust mysql.

Re:Will it be used?

[Anonymous Coward]

MySQL is "fast" only when running simple queries against simple table structures.

Its lack of DRI and reasonable type checking give it that extra oomph making it just that much more appealing.

Re:Will it be used?

[jadavis]

Stability isnt critical for my applications. Raw speed is however. A decrease in speed would be rather bad.

I didn't say stability, I said stable performance. If you're afraid of fluctuations in performance (such as bad performance under high load or high concurrency), postgresql may be right for you.

I'm talking about 600 queries per second averaged over the day.

If they are simple queries, that's not very demanding. Have you even tried using anything other that MySQL?

MySQL fits these requirements perfectly.

Fair enough. I am a postgresql advocate, however, so I am still going to suggest that you take postgresql for a spin. If you are under-impressed, you can post to the lists and maybe it will suit your needs better in your next application.

Re:Will it be used?

[clambake]

People like myself who design software requiring a database usually prefer speed over features.
Thats why MySQL is usually chosen.

Except that postgres DESTROYS mysql in pretty much every benchmark you can think of, except the one where you use versions from 5 years ago...

Re:Will it be used?

[cheater512]

Would you like to back up your claim?

Your the first person in this thread to say that Postgre is faster than MySQL in raw speed.
Everything I have read confirms that MySQL is faster but has less features and is less 'correct'.

Re:Will it be used?

[zerocool^]

Keep in mind that PostgreSQL may have more stable performance for a varied workload. That may mean fewer surprise slowdowns for you.

Most of us who are worried about speed across a varied workload I'd imagine just split the workload up until it's homogeneous per server. It's what we do where I work - our backend stuff such as user DB's, policy rules, etc are all running mysql, and it might be faster if they were all on one server to do it with postgres, but as it stands, they're all independently functioning discrete servers, or IPVS/keepalived server clouds.

The decision was made before I got here, but as far as I can tell, the reason we picked mysql was speed first, and level / quality of code 2nd. There are probably other faster databases, but there aren't any faster databases that have both the functionality and the developer support of mysql, or the universal familiarity for that matter - new developers we hire can be dropped into a situation and be told "Ok, here's A, here's B, this is how we want the interprocess communication, and here's a mysql database. Go." and they'll know what's up.

~wx

Re:Will it be used?

[Per Wigren]

My guess is that he isn't using MySQL as a database, he's using it as a read-mostly dumb key-value datastore. Traditionally, MySQL is faster than PostgreSQL on swarms of very simple queries from a limited set of connections. In that edge case, I'm not surprised if MySQL is still faster. In most other cases, the newer versions of PostgreSQL are damn speedy and I recommend anybody who is looking for a professional RDBMS to evaluate it over MySQL and even Oracle, MS SQL and DB2. If you have very special needs, you might still settle with one of the others, but in most cases PostgreSQL will be perfect and compared to most other RDBMS it's a joy to administer.

Re:Will it be used?

[shutdown -p now]

SQLite can easily handle terabytes of data, remaining faster than MySQL. Its only drawback is that it sucks at concurrent access.

Re:Will it be used?

[shutdown -p now]

There is always a best tool for the job. Sometimes its TreeMap or HashMap, sometimes it's Berkeley DB, sometimes SQLite, sometimes MySQL, and sometimes a decent database such as Postgres or Oracle. The problem is that too many MySQL fans are trying to fit their tool in all of the above niches, because that's all they know.

Re:Will it be used?

[Anonymous Coward]

Your the first person in this thread to say that Postgre is faster than MySQL in raw speed.
Everything I have read confirms that MySQL is faster but has less features and is less 'correct'.

PostgreSQL is well known to be far, far, faster and scalable than MySQL save only for MySQL's sweet spot. MySQL's sweet spot is, simple, read-only, repeated queries where cached results can be applied, or where 95+% is all inserts with limited read/delete activity. Writes with concurrent reads kill MySQL because of locking. And even with that qualifier, MySQL still has significant problems scaling (that means lots of queries at the same point in time. In many benchmarks, MySQL is unable to even complete scalability tests, using crashing or locking up before completion). Simply put, by far and away, all but the most simple of applications fall outside of MySQL's sweet spot...and even then, those that do fall within tend to grow outside of it over time.

Let me give you an example using made up numbers so you can understand what I'm talking about. If you don't believe, feel free to find, or better yet run, your own benchmarks. One word of warning, MySQL has a very long history of lying and making up benchmark results, so please kindly ignore theirs. No one but MySQL seems to be able to reproduce their results...and on the rare occasion they can, the benchmark is otherwise tainted with biased hardware and/or configurations (apples to oranges comparison).

Based on this example, I belive you'll understand why so many MySQL people falsely believe MySQL is fast. Because bluntly, it is to the untrained eye; which is by in large, the bulk of MySQL users.
MySQL PostgreSQL
1 10 100 1000 1 10 100 1000 (concurrent, mixed, simple queries)
3ms 8ms 20ms crash 4ms 7ms 12ms 20ms

MySQL PostgreSQL
1 10 100 1000 1 10 100 1000 (concurrent, mixed, complex queries)
9ms 19ms 30ms crash 10ms 16ms 24ms 40ms

Notice for simple, single queries, which is what most MySQL developers do, MySQL appears to be faster. Well, not just appears to be, it is! But once you start adding additional load, MySQL typically falls far to the back of the pack of other RDBMS; which includes both alternate open source databases and commercial offerings. The problem is, by far, most MySQL developers are clueless as to what scalability means, what locking does to performance, why ACID is good, so on and so on.

Now then, add in the fact that you can write stored procedures in any of a handful of languages in PostgreSQL, and MySQL and you soon realize MySQL is the Tonka Toy of the RDDMS world. Frankly, almost any other DB will do better than MySQL, be it features or performance, save only for its sweet spot. And frankly, where MySQL excels, several other, much faster options are typically available, which does not require the overhead of a RDMS.

Long story short, you'll typically find those that willingly pick MySQL for their project are completely unqualified to be in a position to be asked to make such a pick. *Almost* every time, there exists a superior solution, be it PostgreSQL or some other data storage solution. Generally speaking, MySQL is picked because it's popular, not because it's good. And if you don't believe me, next I'm sure you'll be arguing VHS won because of superior quality of Beta. ;)

Re:Will it be used?

[tacocat]

If you are connecting over a network you are probably spending more time on the network connection than the database itself.

Re:Will it be used?

[cheater512]

That would be a good thing, not a bad thing because it means that its serving out requests pretty fast. :)

The DB server is connected by a private lan and persistent connections are on.
Over 2TB of traffic goes over that connection every month.

Re:Will it be used?

[jadavis]

MySQL is very stable.

This article by Monty implies that MyISAM is not crash safe:

http://monty-says.blogspot.com/2008/01/maria-engine-is-released.html

He developed the Maria engine to make "a crash-safe alternative to MyISAM". And this is from one of the most prominent MySQL developers.

Re:Will it be used?

[cheater512]

Maybe not but if MySQL doesnt crash (for me) then its a little redundant.

Re:Will it be used?

[jonnyj]

Don't patronise people who make technical decisions from a non-technical perspective - it might just be that they have an appropriately broad world view. MySQL is clearly good enough for many applications (if it weren't, the applications would fail or be unreliable), and MySQL's popularity is a very good reason for choosing it - skilled MySQL resource is ubiquitous and affordable. Add the quality of MySQL's support offering (a little googling will show you how highly MySQL rates for support) and you have a winning combination.

support + available resource > technical excellence

Re:Will it be used?

[mw]

Google is no real example. What are they doing with MySQL? Serving ADS. It does not matter for Google if a Database crashes, it can be easily taken offline and re-initialized (in fact, the use a lot of hosts for their adserver-stuff). If an add fails, who cares.

That does not mean MySQL is stable.

Re:Will it be used?

[MartinG]

Strange. I prefer speed over features, which is why I almost always choose PostgreSql over MySQL. The only time I find MySQL to be quicker is for simple applications that do far more reading than writing. (and I really mean _far_ more)

For general purpose, read/write applications containing tables with millions of records, I have found MySQL to be very slow, especially when doing joins across several tables.

Re:Will it be used?

[Enleth]

As far as I know, Google uses their custom, in-house backend, so this thing they use has as much in common with MySQL we all have as is probably the connection handler and some parts of the query parser.

Re:Will it be used?

[Anonymous Coward]

People like myself who design software requiring a database usually prefer speed over features.

"People like yourself" use what they consider to be the best programming tools to write thier code, but the they are content with mySQL, a toy database server. It's a fact that explains mySQL's continued popularity, but I cannot explain this fact in turn. Maybe they don't feel intimidated by things they don't understand. It's a mystery to me.

As for speed, have you actually looked at the figures of how they both perform under load? Or are you just repeating what you think you know.

Re:Will it be used?

[Anonymous Coward]

They would double their market share if they changed it to 'OpenSql'.

Re:Will it be used?

[angulion]

Hmm, you must have a lot of memory then since you just a few posts ago said you have a 100Gb DB?

Re:Will it be used?

[rat7307]

http://benchw.sourceforge.net/benchw_results_open3.html I've been looking at benchmarks to decide the fate of a website I develop for. We are on Postgres8.1 right now, but I'm a MySQL fan....but I'm not finding compelling reasons to switch........ YMMV

Re:Will it be used?

[Envy Life]

The fact is, if you were choosing a database for your enterprise and knew nothing of either database, MySQL would be eliminated on name alone. That's the irony of it all.

Re:Will it be used?

[Anonymous Coward]

Only 2TB?

That's a whopping 0.7mbps, sustained.

You're either a liar or an idiot. I'm not sure which is worse.

Re:Will it be used?

[Anonymous Coward]

Don't patronise people who make technical decisions from a non-technical perspective

If you wouldn't use MS Access then MySQL is unfit for your project. Sadly, MS Access is more feature rich than MySQL.

Re:Will it be used?

[ahodgson]

I tested them about 2 years ago. MySQL was about 25% faster for simple queries, hundreds of them per second (all read-only). But that's only with MyISAM; if you're that unconcerned about your data you might as well just use SQLite. And PostgreSQL has gotten even faster since then (I think that was 8.0); with prepared queries I would expect the margin is 10% - and your data is safe, and you can actually do updates on the database without locking the whole thing for the duration.

Re:Will it be used?

[jadavis]

Fair enough (although it does make me curious what would happen if there was a UPS failure).

I'm not telling you to rip out the database from a working application. I'm just trying to show you that postgresql is a powerful tool, and that I recommend that you look into it; if not for this application maybe one in the future. I'm a postgresql advocate (not trying to hide that), and this is a postgresql thread, so I don't think that I'm out of place.

If MySQL is working perfectly for you in every way, great. What I'd really like to know is if postgresql could also work for you, and if not, why not (so it can be fixed).

Re:Will it be used?

[einhverfr]

I have found that complex queries *really* benefit from PostgreSQL's bitmapped indexes. Some of my queries had their execution time cut in half when this was added in 8.2.

The thing is-- you have more tools at your disposal to improve performance on PostgreSQL--
* Functional indexes (i.e. CREATE INDEX foo ON bar(extract('YEAR' from entry_date)). Note that volitile functions cannot be used so you can't shoot yourself in the foot with something like CREATE INDEX foo ON bar random()...
* Partial indexes (CREATE INDEX foo ON bar(baz) where approved is true);
* Inheritance with intelligent table selection allowing for partitioning tables (star schemas and the like).
* A nice cost-based planner (warning: after doing a bulk load of data, run VACUUM ANALYZE to ensure that the planner has accurate statistics)

Re:Will it be used?

[cheater512]

Um you do realize that Google's main source of income is ads?
They stand to lose a *lot* of money if the db has problems.

Re:Will it be used?

[cheater512]

Everything I have seen indicates that MySQL with a standard web based workload is faster than PostgreSQL.
That plus the fact that I know MySQL means that there is little reason to switch.

I wouldnt mind switching at all. I'm not a MySQL fan boy.
However at the moment MySQL does the job well and nothing can consistently beat it in speed.

Re:Will it be used?

[chrysalis]

If you need raw speed, try MonetDB.

MonetDB/SQL is way faster than MySQL in almost every scenario.

Re:Will it be used?

[Anonymous Coward]

Don't patronise people who make technical decisions from a non-technical perspective

Don't patronize people for who make medical decisions from a biblical perspective. Sure, their idiots. Sure, they'll die. Sure, they have no idea what they are doing...after all, we all know God will cure them because they prayed and if they die, it's his will. Are we really not supposed to laugh at that position? Next you'll tell us accountants shouldn't use numbers to run banks. After all, that would make far too much sense. We need far more failed banks!

support + available resource > technical excellence

You're obviously new to the tech world. In your world, the level of support you get will cost your 2x-3x what it would have cost to have done it right the first time. This happens all the time. You by dirt cheap support for a sub-par, feature-poor product. You spend 30%-60% more time developing your application in an effort to work around the product's short comings, bugs, and/or deficiencies. Then, when you need support, the inferior DBA spends 1.5x-3x as much time to bandaid the problem. Net result is a late project which barely works, if it works at all, with serious cost overruns, which would easily have paid it self twice over if it had simply been done right the first time with another project. Bottom rung apps like VB, MySQL, and Access attract bottom run "talent."

As I said in the beginning, you're either new to the tech world or you just got your B.S. degree. Either way, you should help your doctor help you, help you, help your engineers help, you help you, and help your software help, you. And most of all, stop trying to rationalize idiotic decision making with your ego.

Do you have any idea how hard it is to find a good MySQL DBA? A good Windows programmer? Most MySQL DBAs can't even spell DBA, let alone understand how to support, design, and optimize a real database.

Good luck.

Re:Will it be used?

[aevans]

PostgreSQL can't compete with Oracle on anything except price. And where it's good enough, so is MySQL.

Re:Will it be used?

[Anonymous Coward]

o far more reading than writing. (and I really mean _far_ more)

From what I've been able to father, the point of return seems to be around 90-95%. Which is to say, if your application spends 90%-95% of its time strictly doing selects or inserts, with a low number of concurrent mixed operations, MySQL may be on part or slightly faster than PostgreSQL. As few real world applications function like this, MySQL is a very poor choice for the majority of real world applications.

The problem is, Johnny MySQL tends to test via a single user (single query with no lock/io contention) and assumes that if it's fast for him it's going to be fast for everyone, once released for production. That logic says more about the typical MySQL user than it does MySQL it self. Both of which are broken.

Re:Will it be used?

[mw]

You should re-read my post. Yes, google-ads is an important source of income, but suppose a cluster of thousands of google-ads machines. Who cares if it's not stable, another machine will take-over the job. That does still not mean mysql is stable.

Re:Will it be used?

[cheater512]

Can you see Google and thousands of other massive sites using a product which falls over more than a drunk guy?
I've used it for years on mission critical servers. Its never failed me.

Please stop spreading FUD about MySQL's stability.

Re:Will it be used?

[CryBaby]

People like myself who design software requiring a database usually prefer speed over features.

Then the choice is easy. PostgreSQL is faster than MySQL, especially for highly concurrent workloads. PostgreSQL 8.3 increases the performance gap considerably.

PostgreSQL SPEC benchmark
PostgreSQL 8.2 vs MySQL 4.1 and 5.0
PostgreSQL vs MySQL 5.0 pages per second
PostgreSQL 8.2 vs 8.3

I suggest you bone up on PostgreSQL tuning (here's a handy configuration guide) and perform your own comparison.

Anyone else wondering when this "MySQL is faster" myth is finally going to die?

Re:Will it be used?

[mw]

I did not say MySQL is falling like a drunken guy, but for sure it's not stable for mission critical stuff. If you want to use it for mission-critical data, feel free to do so, but be prepared to loose your data (keep backing up). And you should read http://drbrain.livejournal.com/61705.html and check your settings.

I'm not against using MySQL for several purposes, but dont use Google as an example for stability. It isn't for the reasons already mentioned. And I cannot call a database stable that allows incorrect data, especially MySQLs handling of NULL values is still broken in 5.x, even in strict mode. Stability is more than 99.99999% uptime.

And then...

[n3tcat]

someone will make a comment regarding how sad the story of Postgres's popularity is, and how they've seen German folk music with more of a following.

Re:And then...

[thrillseeker]

well, we will just have to polka holes in that discussion

Re:And then...

[rycamor]

PostgreSQL and Ron Paul...

The best are never the most popular. But who wants to be average?

Re:And then...

[nametaken]

Oh no you didn't...

Re:And then...

[zerocool^]

Polka?

Re:And then...

[aproposofwhat]

Waltz the F...?

Postgres has a load more oompha, that's all.

And don't get me started on the lederhosen.

:P

Re:And then...

[aevans]

You made the connection I hadn't yet. I just got through glancing through the Ron Paul thread, and felt a similarity. People use postgres because they believe the UFOs endorse it.

asynchronous committ

[stoolpigeon]

this was a new feature for Oracle with 10g R2 also - and as a DBA I can only shake my head and ask "why?" Why would you want to drop the durability part of ACID? Why would you risk losing data for speed? There are so many ways to tune things and speed things up without taking such drastic measures. I know I'd fight tooth & nail before I'd turn this on in anything I managed. I just hate to think that someone with less understanding is going to think of it as a 'go-faster' button and then blame postgres when they lose something important.

Re:asynchronous committ

[Wesley Felter]

Haven't you heard? In Web 2.0, data integrity doesn't matter.

Re:asynchronous committ

[nuzak]

> Why would you want to drop the durability part of ACID?

SQL already allows you to drop to READ_UNCOMMITTED if you really really want to -- though the DB actually under no obligation to drop to that level, you're just specifying that you don't care. That removes A, C, and I all at once. Why not make the D part optional too?

Not all databases are commerce. My company processes several billions of rows a day of data, and if we accidentally lose some data, it just degrades effectiveness a little bit and means our statistics just have to interpolate a smidge. In fact, we deliberately drop a lot of it anyway.

Re:asynchronous committ

[nuzak]

I'm smoking the discount crack today, what with the underscore in READ UNCOMMITTED and saying it removes Atomicity. Atomicity isn't lost, though running with autocommit on pretty much does the same thing (okay, not technically, but effectively). Seriously, a DB has to make ACID available, and sensibly speaking, the default. It doesn't mean that the user can't override it if they explicitly say that they don't care.

Re:asynchronous committ

[Simon (S2)]

Because sometimes you don't really care if the data will be there after the commit or not, you just need to do it fast. For example say you have a sensor that counts how many nails go in a package. You have to fill the package with 1000 nails, but it is not really important if there are 999 or 1001 nails in the package, the important thing is that the counter goes fast, say 1000 counts in 1/100 of a second.
It's not a feature you will use in your web or c/s app, but it has it's uses, and it's good to have it there.

Re:asynchronous committ

[Cramer]

Why would you risk losing data for speed?

Ask the millions of people using MySQL. Most of the time, it's unnecessary -- think of what most people use a database for... blogs, torrent sites, catalogs, wiki's, etc. The rest of the time, your hardware and software are generally stable enough that it's often an unnecessary performance drain. And in today's world, it takes very specialized setups to be 100% certain data has actually been commited to disk. LJ learned that lesson the hard way... Has the hard drive actually written the data it says it has?

Postgres has had the "no fsync" option for over a decade. This is no different.

Re:asynchronous committ

[stoolpigeon]

I have a limited frame of reference - my experience has primarily been in the support of mission critical business processes - where data loss is the end of one's job. And from the replies I guess I can see that circumstances exist where this might be desirable, though part of me wonders, if in such cases that a database is the right tool.
 
My other concern still stands - I hope the documentation makes the ramifications of choosing this option clear.

Re:asynchronous committ

[RelliK]

SQL already allows you to drop to READ_UNCOMMITTED if you really really want to -- though the DB actually under no obligation to drop to that level, you're just specifying that you don't care. That removes A, C, and I all at once. Why not make the D part optional too?

False. SQL standard explicitly specifies that writing to the database under READ UNCOMMITTED isolation is not allowed. You can only do read-only queries. Further, PostgreSQL doesn't even support READ UNCOMMITTED. There is no need for it. PostgreSQL implements MVCC such that each transaction gets a private snapshot of the database. With that you get READ COMMITTED for free.

I'm with the original poster here. Asynchronous transactions seem like a bad idea. But then it's not PostgreSQL's responsibility to enforce good software design. And maybe in some corner cases people can find use for them.

Re:asynchronous committ

[Simon (S2)]

Sure. Like so many times in software development "only you can decide whether data loss is acceptable to you. But there are many classes of applications, including high-speed data ingest programs whose only goal is to get the stream of data into the database, where commit-but-don't-wait is not only acceptable but very desirable performancewise." (Tom Kyte)

Re:asynchronous committ

[LWATCDR]

Databases can be a good tool for this because of ease of extracting the data with standard reporting tools. You are correct that many times databases are being used to replace flat files. While it is often not the optimum choice it often the right choice. Modern databases are fast enough and thanks to FOSS cheap enough that it is just easier to use a database you know then write your own file handling code. It also means that if you expand the application in the future that the data is already in a database.

Re:asynchronous committ

[dfetter]

It is a lot safer than turning fsync off. That's the point of the feature :)

Re:asynchronous committ

[mattcasters]

In the line of work I'm usually in, data warehousing, we have large data loading jobs that want to load say 100M rows of data.
For one thing, you usually can't do that in one transaction. To get over that problem you slam commits in between.
You basically don't care if the commits work immediately or not. What you care about is whether or not the 100M rows of data end up in the target table.

As such, the question then becomes: when am I going to be certain that the data is written to disk? Obviously not when I do the commit. Is everything synchronized when I close the connection?
Is there some "sync" command I can issue like in the "sync ; sync ; sync ; reboot" days on Linux?

I could imagine this being useful during one-off big-*ssed database loads, but other than that, I have to go with stoolpigeon : use with caution.

Matt

Re:asynchronous committ

[ivoras]

Are you sure this is such a disaster? As far as I can tell this only means that executing "COMMIT" doesn't block (wait) until the commit has actually happened but returns immediately, and the actual operation is performed "later". The data still goes through the journal (WAL), is still fsynced when needed, etc.

Re:asynchronous committ

[Ruzty]

Such a count does not require a DB write per transaction ( nail++ ). Such minor amounts of data are better left memory resident if they are wiped after a quantifier is reached. DB writes are for the purpose of keeping state. In your example the only reason to keep state is should the machine fail and the boxes be partially filled the remaining count necessary to complete the box needs to be known. That is better done with a physical weight measurement than a DB query.

Asynch writes are useful for keeping history on data that may be used to recreate or replay a period of time of data flow that is more dependant upon trending and rate than actual values. Say you have an automated trading system and you're working on various reaction algorithms based on market events. Recording a real-time stream of market data for playback during system testing is a perfect use for asynch commits. You want a replayable record of market data flow but the order it came in and even missing some records isn't as important as the data set as a whole.

Re:asynchronous committ

[hypersql]

You never _had_ durability. On most system. See http://hardware.slashdot.org/article.pl?sid=05/05/13/0529252. Durability is hard - mainly because of hard drives. See also http://www.h2database.com/html/advanced.html#durability_problems (I wrote that). It's not about 'losing data randomly', it's about losing transactions. The risk is: if there is a power failure or the process is killed, you may lose the transactions of the last x milliseconds. In most cases, you wouldn't know if the commit call returned before the failure, and for for those cases where it's important (distributed transactions), you anyway need the 2-phase-commit protocol. And again, this is not about corruption or losing records randomly, it's a about transactions.

Re:asynchronous committ

[Just Some Guy]

Why would you want to drop the durability part of ACID? Why would you risk losing data for speed?

We run an hour job to copy legacy FoxPro data to PostgreSQL. It's squirreled away in its own schema, and should that schema get totally destroyed, it only takes about 20 minutes to do a full rebuild.

I would happily trade integrity for speed on that schema, and anything that gives me that option is welcome.

Re:asynchronous committ

[Bill, Shooter of Bul]

I think if you go back and look your sources, your statement should read something more like " You never _had_ durability on your systems that you didn't properly setup to have durability". Which makes a lot more sense.

Re:asynchronous committ

[greg1104]

Why would you risk losing data for speed? There are so many ways to tune things and speed things up without taking such drastic measures.

The new async commit feature bypasses the requirement that records physically hit disk in order to complete a commit. If you must wait for a disk commit (typically enforced by fsync), the maximum number of true commits any one client can do is limited by the rotation speed of the hard drive; typically an average of around 100/second for a standard 7200RPM disk with PostgreSQL. There is no way whatsoever to "tune things and speed things up" here; that's how fast the disk spins, that's how fast you get a physical commit, period.

In order to accelerate this right now one needs to purchase a disk controller with a good battery-backed disk controller and pray it always works. If it doesn't, your database might be corrupted. With async commit, you can adjust the commit rate to something your disks can keep up with (say 50/second) just with this software feature while still allowing a write rate much higher than that, and at no point is database corruption possible (from this cause anyway). This makes people who want to use PostgreSQL in things like shared hosting environments have an option that allows heavy writes even for a single client while having a reasonable data integrity policy--only server crashes should ever lose you that brief period since your last true commit. That's a fair trade for some applications (think web message boards for example) and lets PostgreSQL be more competitive against MySQL based solutions in those areas.

Re:asynchronous committ

[jadavis]

As such, the question then becomes: when am I going to be certain that the data is written to disk?

When a well-defined delay expires, see wal_writer_delay. The maximum window of vulnerability is 3 times the WAL writer delay.

See more here:
http://www.postgresql.org/docs/8.3/static/wal-async-commit.html

Re:asynchronous committ

[plopez]

Why would you want to drop the durability part of ACID? Why would you risk losing data for speed?

SO you can write sloppy inefficient code and do sloppy DBA work and get away with it?

Just an idea.

Re:asynchronous committ

[Anonymous Coward]

Why would you want to drop the durability part of ACID?
maybe because hardware can take care?

Re:asynchronous committ

[Anonymous Coward]

The problem is most Web 2.0 programmers don't realise the trade-off they are making. Frankly, for the average website, you *do* need reliability for the data that powers it. (Perhaps not for recording http access logs where missing a hit doesn't really matter; but missing someone making a purchase matters in a *big* way.)

Programmers DO need to take this into account. And, frankly, they don't put enough emphasis on reliability. Livejournal discovered the HARD way that MySQL doesn't provide the guarentees. (In theory it is possible to have hard guarentees and performance, but it requires the HDDs to implement command tagging correctly and only say a tagged command is executed once it really has hit the magnetic media safely, the OS kernel to track things through from the disk responses to the database engine, and the engine has to be correctly written too. Any of these three can be the weak link!)

Re:asynchronous committ

[Anonymous Coward]

There is no way whatsoever to "tune things and speed things up" here; that's how fast the disk spins, that's how fast you get a physical commit, period. Isn't this what battery backed disk controllers are for? These instantly acknowledge a write and keep the data safe, until outages are dealt with and stuff can be written to disk.

Re:asynchronous committ

[Kent Recal]

Yea those work great until the day when the *controller* dies.

Re:asynchronous committ

[Anonymous Coward]

the maximum number of true commits any one client can do is limited by the rotation speed of the hard drive; This assumes your disks are configured for write-through instead of write-back. I'd wager there are quite a few folks who don't understand how the difference relates to database integrity (I was at an Oracle training session recently, and the instructor had never heard of such a thing). A lot of times, the db guys and the systems guys live in completely different silos, and it's very probably that the disk systems have been tuned for maximum performance, without regard for the effect on data integrity. In this scenario, the db guys/gals may be, and often are, completely deluded about the solidity of their database (but they are probably quite happy about the performance).

Disks configured for write back with a big cache can handle db writes very quickly. An fsync won't save you, it just asks the controller to verify the write took place. If the controller lies, well, too bad. From the fsync man page: "If the underlying hard disk has write caching enabled, then the data may not really be on permanent storage when fsync() / fdatasync() return."

All that said, I do think there is a place for async commits / write-back caching. Not all data going into a database is mission critical; it depends on the app. This will be a real boon for packet logging, for example. If you work in a bank, well, that's obviously a different story.

Re:asynchronous committ

[nconway]

SQL standard explicitly specifies that writing to the database under READ UNCOMMITTED isolation is not allowed.

Which part of the SQL standard says that? The SQL specification for isolation levels talking specifically in terms of what visibility behavior is disallowed at each isolation level; it makes no guarantees about what behavior will be provided (and it typically doesn't get into the nitty gritty details of whether database writes will be allowed). For example, it is perfectly legal per spec for a database to only implement SERIALIZABLE, and simply accept the other isolation levels as aliases with the same behavior (since those other isolation levels are defined in terms of which isolation defects are tolerable, not guaranteed).

Further, PostgreSQL doesn't even support READ UNCOMMITTED.

Per SQL spec it does, in the sense that it doesn't reject it, it is just implemented via a more strict isolation level (READ COMMITTED).

Asynchronous transactions seem like a bad idea

In real applications, there are often transactions whose durability requirements can be relaxed for better performance (session state in a web app is a typical example). The async commit feature is a nice way of speeding up those transactions, without harming the ACID guarantees provided for other transactions (it can be enabled on a per-transaction basis).

Re:asynchronous committ

[adamkennedy]

The point here is that you get database support in asyncronous programming environments, like Perl's POE or Python's Twisted.

These single-process environments co-operative multi-tasking environments are ideal for low-cpu but massively-parallel IO types of applications (for example, some of LiveJournal's Perl async stuff can support 30,000+ simultaneous connections).

The downside is that anything that blocks has to be spawned out into a separate process.

Currently to allow for database support in async environments they have to spawn a thread or fork just for the blocking action (read SQL request). Likewise,

Async commit doesn't mean fire and forget queries, it means that you can now run SQL queries natively and efficiently inside async environments, and go deal with 100 other things while the commit (and triggers and cascading deletes et al) happens.

Re:asynchronous committ

[hypersql]

> that you didn't properly setup to have durability Theoretically you are right, but it just doesn't work. You can change the BIOS settings, you can disable the write cache in the OS, and you can call fsync(). Most hard drives will ignore all that. You need to run tests to be sure.

Re:asynchronous committ

[clambake]

I can't comment on Oracle, but for porstgres this feature is MORE durable than the previous behavior (many people woul djust turn fsync OFF all together). Now you can leave fsync on and yet it will go as fast as when it's off, and you have zero chance of corrupting your database when the lights go off.

Re:asynchronous committ

[Vaginal_flatulence]

I think your reproduction is better done with a male asshole than a vagina. you know, so we don't get any more retards than you. so, let me get this straight - you think physical weight measurement is better than keeping the number of nails per box in a database. you would actually go to your client, and say something like:

"mr dude paying me money - it is a better design to keep my counter in memory on the local machine. please go out and purchase a bunch of scales for your factory line". you know what's cheaper? replacing you with someone who is good at their job rather than spending a cool mil on scales, their installation, and their maintenance.

I bet the guy you replied to makes more than you. I know I do, much like I also know the girls I fuck are cuter. now figure out why, you douchebag nerd.

Re:asynchronous committ

[aproposofwhat]

And you'd really use the database to do the count?

Don't get me wrong - it's years since I worked in manufacturing, but in the dark ages, we used to have PLCs to do the counting, and a SCADA system to grab and record the data as required.

Your example isn't the most realistic I've ever seen - I cannot ever forsee a situation where the data needs to be stored at all in which ACID is no longer important.

Re:asynchronous committ

[mosch]

Most of us with real databases run them on real hardware. Y'know, the kind with battery-backed write caches.

In those cases, it's a simple matter of correctly setting the sync options in the conf, and then having a beer for lunch.

Re:asynchronous committ

[poot_rootbeer]

SQL standard explicitly specifies that writing to the database under READ UNCOMMITTED isolation is not allowed.

Sure, but since when have RDBMS software developer-vendors ever cared about fastidious adherence to the SQL standards?

If an RDBMS wants to offer an optional non-standard-compliant mode, allowing the developer-integrator to sacrifice some integrity for performance, I'm all for it. Just keep the default configuration as close to the standards as possible.

Re:asynchronous committ

[ahodgson]

And in today's world, it takes very specialized setups to be 100% certain data has actually been commited to disk

A $500 RAID controller and Seagate drives will do the job nicely. No, your on-board fake-raid crap and Windoze won't do it, but it isn't exactly expensive or specialized to do so.

Re:asynchronous committ

[Cramer]

Tell it to LiveJournal, who had the expensive hardware SCSI RAID, and it STILL DIDN'T turn off the drive's write cache.

Re:asynchronous committ

[hypersql]

> battery-backed write caches I have a laptop as well ;-) I don't think you meant that. It's probably more cost efficient to use two cheap computers than one really expensive one. That means, if you can't afford to lose transactions, you should use clustering. With clustering, asynchronous commits are not a problem.

Re:asynchronous committ

[mosch]

An HP DL360 is hardly a really expensive machine.

$5k gets you 6 15k SAS drives with a 512MB BBWC.

Re:asynchronous committ

[hypersql]

I'm sure for $5000 you can buy two cheap servers. For example Google uses cheap machines. Just a lot of them. That's much cheaper and get you higher reliability at the end.

Re:asynchronous committ

[mosch]

No. it doesn't.

I'll put my $10k of actual hardware versus your pitiful budget solution any day.

MySQL-cluster is a fucking abortion. I've examined it and was incredibly disappointed to learn that it's a total piece of shit.

Google can work with commodity hardware, but you're not google. You're not that smart, and it's not that simple. You'll learn this in time.

Statice

[Anonymous Coward]

forever.

Re:Statice

[Fnordulicious]

You got modded -1 Offtopic because Statice is an object DB, not a relational DB. Sorry.

I do agree that Statice is awesome and wish we had it on vanilla platforms today.

Multi-master replication built in

[Bandraginus]

would have been nice. It certainly needs a better solution if they want to chase serious enterprise customers. A couple of years ago I was on a project with geographically dispersed sites and the client was determined to use Postgres. All I remember from that project was that the replication was a PIA. We had to rig up a complicated fail-over system in lieu of multi-master replication. Not at all elegant.

Postgres 9 maybe?

Re:Multi-master replication built in

[Acheron]

The 8.3 release notes list the Bucardo project http://bucardo.org/ for multi-master replication. I haven't used it... is there something that it is lacking that you think would be addressed by bringing it into the core code base?

Re:Multi-master replication built in

[ashridah]

Well, this does ship with Bucardo in tree. That does multi-master replication.

Admittedly, "in tree" isn't the same as "built in", and I have no idea what the performance is like, and I don't know if it requires any application logic modifications to utilize.

Re:Multi-master replication built in

[Bandraginus]

I saw the Bucardo project. Unfortunately it doesn't get replication onto the glossy for Postgres, which instantly rules it out of consideration for the enterprise customer's I've worked for.

Re:Multi-master replication built in

[Anonymous Coward]

I don't know if it requires any application logic modifications to utilize.

Its docs say no, apparently all of its logic resides in its own database, with external daemons handling the replication. Looks like it automatically creates triggers on whatever tables.

My problem with it is that it relies on a stable schema to work: it doesn't automatically replicate new tables and falls over if you ALTER TABLE. I'm wishing for something that just works, which probably would require it be built directly into the database engine.

Bonus points if it can do simultaneous multi-master synchronous replication between query-balancing servers at the same time one of the servers in the pool (which ever is least busy) performs asynchronous slave replication to an offsite emergency backup server over the interwebs where the connection might be slow/down/etc.

Re:Multi-master replication built in

[ashridah]

I'm curious, since I haven't poked at it yet.

What's missing from it, such that it misses out on the feature-list, in your opinion?

Re:Multi-master replication built in

[Bandraginus]

I've never played with it either. Although another commenter made a point about it not being dynamic. Table schema changes do not replicate; in fact it takes effort to fix the replication after such a change. This makes it difficult to maintain databases.

I guess my initial point was that management-types look only at the glossy for a product. When you look at Postgres' feature matrix you don't see replication anywhere on it. Compare that to Oracle's offerings (Industrial-strength replication built-in! Call us now!)... well, you get the gist.

long live postgres

[squoozer]

As far as databases go I don't think it gets much better than Postgres and I reckon over the years I've tried most popular databases. What I really don't understand though is why Postgres doesn't own more of the database market. Sure it was a bit slower than MySQL a few years ago but the benifits that you reaped for that slightly slower speed far outweighed the cost. The difference know is, I would say, much small and less significant.

I can only assume that MySQL keeps it's large market share because it has commercial backing and therefore good support. I'm sure there are plenty of products taht don't require that level of support though.

Re:long live postgres

[costing]

I can only assume that MySQL keeps it's large market share because it has commercial backing and therefore good support. No, it's because people are used to LAMP, and tons of easy-to-install apps only have MySQL support. But there is hope, I see more and more PHP apps allowing you to choose PostgreSQL instead. I think this is the turning point, once they reach the critical mass needed to turn the developers' heads it will become THE open source database. And for a good reason, it beats MySQL in every way you imagine, including the obvious features and not so obvious performance. Well, maybe for two queries in a 10 rows table MySQL will see an edge and enjoy, but let's face it, it never scaled up to more than that :)

I guess we will have to get used to saying LAPP from now on and not grin when we do. :D

Re:long live postgres

[bar-agent]

What I really don't understand though is why Postgres doesn't own more of the database market.

It's the market leader advantage combined with the network advantage. MySQL had it, and Postgres has been playing catch-up since. The obverse case of the "long tail" they talk about.

Re:long live postgres

[KillerCow]

Sure it was a bit slower than MySQL a few years ago but the benifits that you reaped for that slightly slower speed far outweighed the cost.

Pfft. Who needs ACID in a database... /sarcasm

Re:long live postgres

[Ed Avis]

What I really don't understand though is why Postgres doesn't own more of the database market.

Worse is better.

Re:long live postgres

[Foofoobar]

Nah. I dont use it because I still cant do a cross database join. That and features like SQL_CALC_FOUND_ROWS and query caching which make it EXTREMELY useful.

Re:long live postgres

[Lennie]

The problem with the whole LAMP-acronym is, we have a lot more to choose from. What do you think of: LLPP (Linux, lighttpd, Perl, PostgreSQL) or something like that.

Re:long live postgres

[Tablizer]

As far as databases go I don't think it gets much better than Postgres and I reckon over the years I've tried most popular databases. What I really don't understand though is why Postgres doesn't own more of the database market.

Some say its more buerocratic than MySql to configure. MySql is a bit more nimble in that regard.

Re:long live postgres

[Burnhard]

I took a look at it after using SQL Server but I wasn't impressed by the tools available. I think that with good (I mean comparable) integration with Visual Studio it would be a joy to use - because you know, it's free.

Re:long live postgres

[pathological liar]

Replication. None of the replication options for Postrgres are particularly pleasant, especially when compared to the support that's built into MySQL.

Re:long live postgres

[Anonymous Coward]

Slightly slower speed? What planet are you on? We have an web app with heavy management reporting that has to run on mysql and oracle, we also made it work on postgresql. Guess what, postgresql had to be scraped because it was useless for large up to date reporting queries, and that was after both postgresql and kernel adjustments to give it a good run. Unless your application runs in batch, a small web site and time isn't important, postgresql is a non-starter.

Re:long live postgres

[mortonda]

What I really don't understand though is why Postgres doesn't own more of the database market. This may sound silly, but for me the biggest hangup has been getting a database started - the default install iirc uses unix users to authenticate into their own databases, whereas mysql has its own internal user database - mysql I can set set up pretty quickly, but postgresql I always have to hunt for the right config file to set up a user that I can connect from the network with. I know, it would probably get easier if I used it more.

But that brings me to the second problem - While the documentation for postgresql is pretty good, it is written in a way that just doesn't march in step with my thinking - I can never find what I'm looking for. I don't know how to describe that better - if I did, I'd submit patches to the docs.

And lastly, when I ask questions about something is documented (but I can't find it), the responses often sound like they're putting my picture up on one of those "You're doing it wrong" pictures.

That said, I'll give the new version a spin sometime. Maybe it'll click.

Re:long live postgres

[specific_pacific]

I'm down with LPP !

Re:long live postgres

[David_W]

What do you think of: LLPP (Linux, lighttpd, Perl, PostgreSQL) or something like that.

My personal favorite is FAPP (FreeBSD, Apache, PostgreSQL, Perl).

...

What's so funny?

Re:long live postgres

[greg1104]

This is one of those things that's painful only until you've crossed a certain threshold of information, then it never bothers you again. It's better now but still harder to put the pieces together than it should be.

Start with the documentation on creating a cluster: http://www.postgresql.org/docs/current/static/creating-cluster.html In 8.3 the default of using auth you mentioned has been removed, for the reasons you described. So it now runs as unsecured for local users by default and you have to worry about this yourself, which since it reduces the frustration at getting started was deemed an improvement.

That page suggests some options you can pass to initdb to adjust the default security level. Now you want to look at the initdb docs: http://www.postgresql.org/docs/current/static/app-initdb.html See that if you use -W/--pwprompt (same thing) you can assign a password to the database superuser at cluster creation time. If you do that, you can now change the default authentication scheme to password-based (-A md5 passed to initdb will do that), you'll be secure, and you'll have one user you can login as (postgres) to create more.

To see what other authentication methods are available and to learn what your options are look at http://www.postgresql.org/docs/current/static/client-authentication.html The one you really need to dive into is pg_hba.conf which is the magic text file to edit here. A new one of those will be put in the base directory of your new database cluster. Open that file up, look at the documentation, and you'll need to add a line to add network support like one those in the examples. Probably something like

host postgres all 192.168.12.0/24 md5

(allow access to anybody on the 192.168.12 subnet access the database with a password)

That should get you past the ugly initial hurdles. The next document you may need is how to add more users: http://www.postgresql.org/docs/current/static/sql-createrole.html

again look at the examples first and then backtrack to the parameters, will make more sense that way. After that you'll want to create more databases with createdb: http://www.postgresql.org/docs/current/static/app-createdb.html

And then you should be able to find your away around from there using the psql command line tool.

Note that once you get past accepting connections over the network, you could use a tool like pgAdmin III to handle the rest of this work using a slicker interface. There's even a copy of it bundled with the Windows installer you can use on such a client to administer a remote server running a real OS. It's of course possible to install pgAdmin manually on other platforms as well, see http://www.pgadmin.org/ for other verions (note that binary packages for platforms like RPM don't show up in every release, you have to go back to v1.8.0 to get the last full set of packages).

Re:long live postgres

[gullevek]

erm, wrong. I use postgres since 7.4, and I used it on several very heavy duty webpages. One (with postgres 8.2) got millions of hits every day and postgres just worked without any problem).

Re:long live postgres

[gullevek]

Well, thats sort of not true. Postgres has internal users, you just can configure who and how users connect in an external file, which is extremly useful for emergency access and database transfer from one server to another.

I know, that this is confusing and difficult to understand. I really needed quite some time to grope this with my first postgres install, but once you get it, its much nicer than MySQL (IMVHO).

Postgres has the pg_hba.conf file where it defines which IP (or range) can connect with which method (unix user auth, internal auth with md5 or plain password, kerberos, etc) to which DB (or DBs).

I still create all my postgres users in postgres (with createuser), but I generally configure how to access it in the pg_hba.conf file. Which is very nice if you need to move the database from Server A to B, there is no way you can lock yourself out, because which IP can access which server is in a plain text file.

Re:long live postgres

[Anonymous Coward]

The problem is, your post is a turn off for me wanting to try postgres. You're trying to show how easy it is to setup, but the fact is that is far too much work just to get auth off the ground.

Re:long live postgres

[turbidostato]

"This is one of those things that's painful only until you've crossed a certain threshold of information, then it never bothers you again."

For the most part then it's too late.

There are *a lot* of people that started their days on DBs by their own or as another issue clogged to their back. Then they go opensource just because it's easier to start with (no provisions, not a dozen calls to a provider, just an "apt-get install" and you are on your way). Then you look for Postgres; it has a fame of a serious, reliable full-featured database, you start toying with it, having a light look at the manuals (remember: you are either a neophyte or burdened with other tasks; no time or ability to spend the time on a dense manual and start seeing the results next week)... and you go nowhere. Let's try the next one, you say, so you go with MySQL; you install it and within five minutes you get your first "show databases" and "select * from table"; you smile, you feel the strong feedback and there you go. Of course, after a year using MySQL you understand some basic concepts and you could give another try to Posgres, but who cares now? MySQL has the work done for the most part, you know its nuisances and you have invested a lot of time on it.

Re:long live postgres

[einhverfr]

I don't know what planet you are on or when you tried this (was that with Postgres95?) but PostgreSQL seems to handle large workloads pretty well. MySQL doesn't even have a cost-based planner or bitmap indexes yet (the latter has caused some of my queries to run in less than half the time they did before that feature was added).

In general, my guess is that your problems are probably ones which a competent Pg admin could solve. Want to contract with my business?

Yeah, I have a few queries (views based on views based on views, requiring multiple sequential scans on a table because the math is unamageable otherwise) which I would like to get to run faster, but when you are staring at a 85-line EXPLAIN output, you know you are doing something which could be done better ;-).

Re:long live postgres

[Anonymous Coward]

I'm curious: have you tried many object databases? How did you find they compared?

Re:long live postgres

[LizardKing]

My experience with MySQL replication is that when it works, it's OK. However, it is very fragile, with none of the robustness that Informix or Sybase have in this area (those two being the other RDBMS I've used replication with extensively). This fits in with the whole cheap and cheerful philosophy of MySQL, but along with the lack of features in MyISAM, the poor performance of InnoDB and the regular corruptions of data on any sizable (20GB plus) database it means I'd rather struggle with the configuration of Slony and use PostgreSQL instead.

Re:long live postgres

[poot_rootbeer]

Replication. None of the replication options for Postrgres are particularly pleasant, especially when compared to the support that's built into MySQL.

I worked at a company that came up with a rather elegant hack for Postgres replication, back in the 6.3 days. The source code was patched so that each query that was executed on the master server was logged to a file, and periodically those files were rotated, copied to the slave server(s), and executed as SQL scripts.

The state of the slaves was often several minutes behind the master, and large object datatypes were unsupported, but it worked well for our needs at the time.

Re:long live postgres

[Khelder]

I agree: it's the docs. I am not a DBA, but I am a really experienced programmer (and multiple CS degrees, yadda yadda). I set up both PostgreSQL and MySQL a few years ago for really simple databases, and found that MySQL was a lot easier to get going. The Postgres docs seemed focused on being a reference, whereas I found much better tutorial-type information about MySQL.

Technically, I have thought for a long time that at least for big systems (and maybe even for not-so-big, I dunno), Postgres was definitely the better choice. But I think MySQL is above the bar for a huge fraction of database users out there, and even if it isn't, its ease of installation and configuration has made it and will keep making it the favorite.

Re:long live postgres

[Anonymous Coward]

I think perl gives me hives. What about python?

Re:long live postgres

[Just6979]

I like and use FrAPPy (FreeBSD, Apache, PostgreSQL, Python) and FrAPPe (FreeBSD, Apache, PostgreSQL, Perl)

New HOT, faster Postgres

[byoung]

I suppose they've figured out how to only make it an order of magnitude slower than MySQL.

Re:New HOT, faster Postgres

[naasking]

Yeah, if only for those darn inconvenient facts demonstrating that PostgreSQL is faster than MySQL, particularly under load. Note that the benchmark was PostgreSQL 8.2. Now note that 8.3 is up to twice as fast as 8.2. I think the polarity on your order of magnitude performance difference should be reversed.

Of course, if you actually care about data integrity and database features, there's not contest at all. But the performance gap is now non-existent, if not completely reversed.

Re:New HOT, faster Postgres

[glwtta]

Oh give it a fucking rest. MySQL is 10-15% faster on simple queries, with few threads, on a single disk.

And that's only with MyISAM (in which case, why bother with a database server? SQLite is probably enough for your needs).

Re:New HOT, faster Postgres

[geniusj]

The only time PostgreSQL will be slower, generally, is in a situation where you have frequent connections/disconnections. Connection pooling greatly benefits pgsql. However, now there's pgBouncer which alleviates this.

In general, as far as query planning and such, it will outperform MySQL.

Re:New HOT, faster Postgres

[Anonymous Coward]

This benchmark you point to is completely useless. It doesn't even say if they used InnoDB or MyISAM tables with MySQL. InnoDB tables are well-known to scale better for the type of workload in this test.

You are way off-base in your assumptions about data integrity and features in MySQL as well. That test used MySQL 5, which has strict SQL compliance, stored procedures, etc.

If you want to compare databases in a meaningful way, that's great. If all you want to do is repeat ancient and obsolete complaints about MySQL, you're wasting everyone's time.

Re:New HOT, faster Postgres

[Anonymous Coward]

I can tell you my own experience with both. And, for our workloads, postgresql is way faster (2x) than any mysql configuration. This is true for many years.

In a few situations we know of, mysql is faster. But these are very, very simple DBs (not our product's DBs).

Also, considering reliability postgresql wins easily. We have more postgresql systems than mysql ones and, even with severe outages (PSU meltdown...) we never had data loss with postgresql. With mysql we had to do a few restores...

Anyway, even knowing our experience, some customers want mysql. Maybe because they already know mysql or because they read magazines (I'll refrain from making comments about the typical IT magazine...) or because they like the name... Who cares? When the customer chooses, the customer follows the hype.

Re:New HOT, faster Postgres

[Anonymous Coward]

Actually, what REALLY sucks is the incompatibilities on the client-to-server channel. If you're off by even one point release between the client and server machines, the psql utility loses major functionality in things like being able to display schema info, etc.

Considering that my desktop machines run desktop distros and my servers run server distros and the server distros are far more conservative when it comes to software releases supported, this translates into major pain - I have to allow user access to shell accounts on the servers because no one's client machine psql can do squat.

Thank goodness at least the JDBC drivers are more forgiving.

Re:New HOT, faster Postgres

[Anonymous Coward]

If you want to compare databases in a meaningful way, that's great. If all you want to do is repeat ancient and obsolete complaints about MySQL, you're wasting everyone's time. That's right. You can't compare Mysql with Postgresql. To do that you would need do make a light version of Postgresql and remove all the features from Postgresql that Mysql lacks!

'nough said.

Re:New HOT, faster Postgres

[gullevek]

Thats why I love to use debian for this, you can have serveral postgres releases installed parallel, so in a migration term (eg from 8.1 to 8.2) I had both client packages installed and I run the correct binary for each DB.

Still, I admit, it kinda sucks ...

Re:New HOT, faster Postgres

[byoung]

I guess nobody appreciates humor here. Well, not if it's against the fanboys.

I'll stand by it though-- switching to MySQL from Postgres made my life significantly simpler:

1) you can install MySQL easily
2) MySQL has great vendor support
3) my experience is that MySQL performs significantly better in the general case (i.e. I'm not spending my entire life tweaking performance)

Many an honest thing were said in jest, I suppose.

Time for a cross-DB comparison

[jd]

PostgreSQL 8.3 is nicely timed. I've been looking forward to trying it in a setting which wouldn't allow the use of betas. Now I've got the on-topic stuff out the way, onto my main point.

There are so many Open Source databases (MySQL, MaxDB the last GPL version, Firebird, Postgresql, Ingres Community Edition, hsqldb and H2) that it is hard to know which ones implement what, which ones are useful for what, or which ones are optimal for what. Simple benchmarks (a) rarely are, and (b) usually want to promote one specific product over another. There are standardized tests, for a small fortune and by a relatively closed group who probably don't have a fraction of the range of experiences of databases in the real world, so cannot possibly be trusted to authenticate a standard or measure that could be used to compare databases.

We could really do with some serious side-by-side evaluations of these database engines, or at least decide what such evaluations would need to be to be actually useful. (Hey, maybe CmdrTaco can add a comparison section, get it sponsored by Which? or some other consumer guide, and have some of us run evaluations. It'd have to be done well to not be flamebait, which I think might rule me out, but if it could be done, it would be hellishly useful.)

Re:Time for a cross-DB comparison

[naasking]

A couple such comparisons already exist. They may be a year or two out of date however.

Re:Time for a cross-DB comparison

[TheNarrator]

Here's a feature matrix of new features vs. old versions. It's easy to see that 8.3 is a huge upgrade.

http://www.postgresql.org/about/featurematrix

Re:Time for a cross-DB comparison

[0racle]

PostgreSQL vs. MySQL WIki

Seems to be features only, no performance.

Re:Time for a cross-DB comparison

[Anonymous Coward]

Here's a feature matrix of new features vs. old versions. It's easy to see that 8.3 is a huge upgrade.

You're right! There is clearly more 'green' now.

what do you mean by cross-database join?

[Reality Master 201]

Are you thinking like you'd do in SQL server (IIRC) or MySQL, where you have a db reference in the table list (i.e., SELECT * from db1.table1, db2.table2 WHERE [join clause])?

you'd probably just want to use a schema for that; the concept maps more or less the same way.

Re:what do you mean by cross-database join?

[Foofoobar]

A schema is an unnecessary step though. Nothing is added to make that a needed step in this case (except in large scale implementations where it may an easier level of maintenance). It justs adds an extra layer of complexity and slows down what could be a far speedier query process and development process.

Not that I'm doing a bunch of cross database joins but I like to separate out my databases for future scalability on the network and schemas answer 70% of the solutions but leave others in the lurch as there are somethings you can do with cross database joins that you can't do with schemas that I am aware of.

Re:asynchronous commit

[Anonymous Coward]

Asynchronous commit is very useful in applications where the thing that's important about the data is its statistical distribution, and not the individual data points per se.

why do they get a hot?

[Anonymous Coward]

This is bunk, man.. postgres servers were already overpowered, why make them that much harder to kill?

How quickly they turn on you ..

[jon3k]

So the ink barely dries on the press release for Sun's acquisition of MySQL and we're already posting minor (!) revision releases for PostgreSQL on the front page? Didn't waste much time abandoning ship, did ya?

Re:How quickly they turn on you ..

[swimmar132]

Going from 8.2 to 8.3 in postgresql is not a 'minor' release. It's quite a major step with a lot of new features.

Would it make a difference to you if they bumped up the version number to 9?

Re:How quickly they turn on you ..

[glwtta]

8.2 was released over a year ago - this is not a minor revision.

Re:How quickly they turn on you ..

[Just Some Guy]

we're already posting minor (!) revision releases for PostgreSQL on the front page?

Slashdot has always had a fetish for F/OSS that outperforms the competition. This isn't new.

Re:How quickly they turn on you ..

[k3ith]

I upgraded last night to 8.3 from 8.2 in Debian. It was really very easy. I simply ran pg_upgradecluster and it migrated all my settings, tables and data. It even switched the old database to an unused port and brought up 8.3 on the correct port. Now I gotta try out all the new stuff...

Re:How quickly they turn on you ..

[jon3k]

I can't believe that got modded troll. Learn to take a joke people, geez. > Going from 8.2 to 8.3 in postgresql is not a 'minor' release. It is, by definition, a minor release. http://en.wikipedia.org/wiki/Version_number > Would it make a difference to you if they bumped up the version number to 9? Yes. That would make it a MAJOR release.

whuh?

[Reality Master 201]

Ok, so first you say:

A schema is an unnecessary step though. Nothing is added to make that a needed step in this case (except in large scale implementations where it may an easier level of maintenance).

Which is fine, as far as it goes, I guess. If you don't need it, then it doesn't serve a purpose for you.

Not that I'm doing a bunch of cross database joins but I like to separate out my databases for future scalability on the network

See, but whereas before you were complaining about how schemas add an unnecessary layer of complexity, now you like to (entirely needlessly, from the description you give) add a whole lot of complexity by throwing in queries across database instances that you don't need yet?

So, do you not like unnecessary complexity, or do you just prefer adding really stupid forms of unnecessary complexity?

Re:whuh?

[Foofoobar]

Not planning for scaling is a common failure of a bad developer. A good developer plans for scaling. Schemas arn't always a catchall solution. For instance, say I have a user table in one database but 16 other database have tables that have keys that reference that table. If I were to build schemas for EVERY function, I would have a SHITLOAD of schemas to manage. If I were to use ORM, I would have a bunch of bloated queries. Instead I can just build the queries as cross database joins and just manage the queries as an object associated with the class. No muss no fuss.

Second problem, if I implement this with schemas, I'm relying on the database to backup my sql and not a versioning control system. Should the database corrupt, bye bye code. Have fun recreating those schemas. Separating them out from the database into queries that you inject, enables you to better manage them, back them up and control them from one central location.

Planning is the other good element behind a good developer.

Re:whuh?

[gerardolm]

16 databases, 16 schemas. SHITLOAD = 16?

Re:whuh?

[Foofoobar]

No. You don't build one schema that will meet all the needs of each function call for each cross database join for each table that has a foreign key connection in those 16 databases to that usr table in the other database. there could be 8 tables in each database and they themselves may need cross joined to each other (since they share the same foreign key).

I beleieve it would be something like 16^8. In other words, a shitload.

Re:whuh?

[Just Some Guy]

I think the disconnect is that PostgreSQL has a different definition of schema than you are using.

Re:whuh?

[Anonymous Coward]

Not planning for scaling is a common failure of a bad developer.

And you're planning on some architecture to have "cross-network joins" some time in the future for when you try to scale this across multiple machines?

if I implement this with schemas, I'm relying on the database to backup my sql and not a versioning control system

I don't understand how you were using a VCS to keep versions separate when you were using different databases before (btw, pgdiff: it works wonders).

Needless to say, if you've got databases like "oldclient" "clientnew" "clientnewest" "mostrecentclient" and "oldernewclient", I look forward to the day your application shows up as a WTF when your successor tries to figure out why there are 50 lists of clients, all different, and none of them have the same information.

Re:whuh?

[ahodgson]

A PostgreSQL schema is just a namespace qualifier; it functions just like MySQL's cross-database joins and is conceptually similar. It isn't a full copy of your database DDL.

Re:whuh?

[CryBaby]

I think you're missing a few key points.

First, you assume that your scaling strategy will work. If the plan is to distribute databases across multiple servers but you're performing a lot of cross-database joins, then you may very well decrease overall throughput due to the inter-server network traffic that was perviously handled in-process on a single server (plus, you may require distributed transactions, which will exacerbate performance problems and increase system-wide complexity and fragility). Remember that at one time a lot of developers thought they were "planning for scalability" by using distributed objects. We now know that the distribution of processes that need to communicate with each other in a fine-grained manner usually results in a performance bottleneck rather than in increased scalability.

Second, even though several other posters have already talked about this, I think PostgreSQL's definition of "database" and "schema" still seem incorrect or odd to you. From PostgreSQL's point of view, any two tables that are related to each other are, by definition, in the same database. This is not some weird idea from the minds of PG developers or a limitation in the sense of an overlooked feature-- it's a strict and useful interpretation of relational database theory. It forces you to clearly define your data domains and prevents coupling between them. Since PostgreSQL databases are truly separate entities with no interdependencies, you can safely and easily move them to different servers when and if the need arises. That's why all databases under the control of a single PostreSQL server instance are called a "database cluster" and not just a "database".

If you accept PostgreSQL's (and I would argue relational theory's) premise that any two tables with a relationship are part of the same logical database, then the question becomes "is your data model designed correctly?" If yes, then PostgreSQL's schema mechanism provides a clean and flexible solution for application-specific data models in separate namespaces that can cooperate in queries and transactions. However, it sounds like what you really have is a series of completely independent application-specific data models that need to share only a set of user accounts and authentication/authorization logic (i.e. single sign-on). If this is the case, IMO you would be better served by encapsulating that user data and logic into a single, shared application with a well-defined API (e.g. via lightweight web services or just a common library included in your other apps). This would provide all the normal benefits of encapsulation, reuse and decoupling (e.g. you could add/evolve user management features without affecting your other apps).

Don't get me wrong -- I'm not saying that you should design your applications or data models around whatever set of features happen to be available in a given RDBMS. Rather, I'm trying to demonstrate why this "limitation" in PostgreSQL exists and how it helps you to avoid a mishmash of interdependent data models.

A few words on database scalability in general: due to the need for atomic transactions and the disadvantages of distributed transactions and clustering (e.g. less performance / more complexity), the tried and true "vertical" approach to scaling database throughput is still often the best choice. Compared to separate but interdependent database servers, a single, powerful server can provide higher overall throughput (due to better resource utilization), is easier to maintain, often more cost effective and more fault-tolerant. And before someone cries "single point of failure" let me point out that spreading databases out on multiple servers only creates more single points of failure (with a higher combined failure rate than a single server) and that it's much easier to achieve database HA by adding a hot standby for your main DB server than it is to create a true multi-master HA cluster where any node can be removed at any time.

Second

Upgrade Procedure

[AltImage]

"A dump/restore using pg_dump is required for those wishing to migrate data from any previous release"

Postgres have a habit of making you do this to upgrade and it really sucks. I understand the reasons behind it, but that does not reduce the amount of suck, especially for a large database.

Re:Upgrade Procedure

[ashridah]

Most of the admins I know that deal with large PostgreSQL databases use slony-I to do their migrations.

Of course, if you're tight on hardware/disk resources, you're probably in trouble, no matter what, but slony can get you the migration/sync period down to a minimum of "shutdown, change setting, restart", if done correctly.
Don't forget that PostgreSQL can easily be run side-by-side with another version of PostgreSQL on the same box, so long as they aren't sharing a data tree (duh) or network ports. This migration doesn't necessarily require a new system, just enough disk space, and enough spare bandwidth to setup the migration. Slony shouldn't require a restart to get up and running (although it's delicate, you'll want to practice it in a set of VM's fairly rigourously if you can!)

ash

Re:Upgrade Procedure

[Jeffrey Baker]

Judging from history, it will be two years or more before slony is compatible with PostgreSQL 8.3. And the intrusive schema changes required of slony are unacceptable to most every DBA I've asked about it.

Re:Upgrade Procedure

[ashridah]

Intrusive? Eh?

Last time I set up slony-I myself, it required a _replication schema. That's hardly intrusive, it's a *separate schema*.

The main difficulty is that it then tends to attach itself via a number of REFERENCES. Of course, since we're not actually discussing long-term usage, but short-term usage for purposes of migration, this is hardly a major problem. You add in slony's replication details, trigger replication, wait until replication has succeeded, promote the slave and shut down the master, test carefully, potentially running the old master as a slave temporarily, and then once you're satisified, *turn off slony and remove it*

It's not that hard to do, I've done that part as well, you just need to understand FK's, constraints and triggers.

ash

Re:Upgrade Procedure

[greg1104]

Slony is already compatible with 8.3; the current maintainer (Chris Browne) is very on top of things. There are still some rumblings about problems with the dropping of some implicit casts to text in 8.3, but they have been squashing those as reported for months now already and they may already be completely gone. Since anybody with a database big enough that they need Slony to handle version upgrades is surely going to test heavily issues there should easily be caught during that.

Intrusive schema changes? Well, I guess most DBAs don't really understand programming magic like triggers so I wouldn't be surprised they don't like it. You've already gotten a good response here commenting on that topic.

Re:Upgrade Procedure

[Jeffrey Baker]

PostgreSQL 8.2 was released in December 2006. In August of 2007, patches were still being committed to Slony-I to fix problems stemming from changes of the semantics of certain SQL operations between 8.1 and 8.2. The list of semantically meaningful changes in 8.3 runs to 20 printed pages. You'll have to please excuse me if I think that not all the problems between Slony-I and PostgreSQL 8.3 have been ironed out yet.

Re:Upgrade Procedure

[greg1104]

That's fair, but in the context it was being brought up in (8.2->8.3 replication) you won't be using most of the semantic changes introduced by 8.3. I'm not saying Slony is ready to replicate very complicated stuff using, say, the new XML and UUID features in all circumstances today, but it shouldn't be too much headache to get it to copy an 8.2 instance into a new 8.3 one.

Re:Upgrade Procedure

[Dalroth]

Or you can use slony, create a cluster, and once the databases are synced up change your master node to the new database.

Re:Upgrade Procedure

[jadavis]

Maybe Slony is not intrusive per se, but it does require careful planning and execution. It's not a fire-and-forget process.

Don't get me wrong, having that option is great when you need it. But if you don't need it, it's probably better to schedule the time necessary to dump reload.

There's also a caveat: there are cache invalidation problems in 8.2 and before that can cause problems with Slony.

In 8.3 and above the problem is fixed, but that doesn't help when upgrading from a prior release.

Re:Upgrade Procedure

[gnuman99]

That's why 8.2 will be supported long term, as are other database versions they released so you can wait with database upgrade. For years if need be.

Postgres Books?

[crustymonkey]

Does anyone know of a good, semi-recently written book on PostgreSQL? Everything I find is from at least 3 years ago. Is it that PostgreSQL hasn't changed much, barring this release, in the past few years?

Re:Postgres Books?

[Sancho]

I don't know of any good books, but I do know that Postgresql has been changing quite a bit lately (or rather, adding useful and interesting features.) What are you looking for in a book? Is the online documentation not good enough?

Re:Postgres Books?

[earthpig]

"Is the online documentation not good enough?"

No not really.

For me the issue is also the depth of information out there.
The postgres online documentation usualy is the only resource. Need an in depth explination on how some part of postgres works, it is really hard to find.

With a book i can browse thru and pick up valuable information "at random", something which is so much harder with online documentation.

Then for me is also the preception.
There is a plethora of Oracle resources. it is high profile. i can find thru the vast ammount of literature something that meets my needs.

MySQL is also high profile. lots of resources.

With Postgres when i have been working with it i am struck immediately by the dearth of information. I know from personal experience that it is a powerful database, but the lack of information available, give me an impression on not being professional.

well, just some thoughts.

Re:Postgres Books?

[jadavis]

With Postgres when i have been working with it i am struck immediately by the dearth of information.

Can you give some examples of something that is not fully covered in the docs? The docs are very high quality in my experience, and much better than MySQL docs or books.

If you list some examples of things that you missed, there's a good chance they will get documented.

Re:Postgres Books?

[jadavis]

PostgreSQL has changed a lot, but there aren't a lot of great books, primarily because:
  * postgresql people are busy, and writing books takes a lot of time
  * the audience is smaller
  * the docs are very good
  * the mailing lists are great
  * IRC is very good

Cool! Good support for full text indexing/search

[MarkWatson]

Just recently, I discovered that Ferret had synchronization problems when I deployed my my http://cookingspace.com/ site using nginx and a mongrel cluster - a little nuisance to work around. I did some fast experimenting with indexing and search using MySQL and PostgreSQL, and I made a note to retry PostgeSQL when version 8.3 was released.

When a deployment platform has inherent weaknesses (like Rails!), it is important to be able to shove off as much processing as possible to more industrial strength tools like memcached and (choose a) relational database.

MySQL is FAST, PgSQL FARTs on MySQL's Performance

[einhverfr]

Fast
And
Sorta
Transactional

PostgreSQL is
Fast
And
Really
Transactional

And under heavy loads with normalized db's PostgreSQL's planner does *much* better than MySQL's.

Why PostgreSQL doesn't have more of the market

[einhverfr]

I have used PostgreSQL as my primary db since 2000 (version 6.5!) and I have watched it for a while.

PostgreSQL had a number of problems in the past which made it hard to work with including:
1) No ALTER TABLE DROP COLUMN support and other things needed for prototyping (fixed in 7.2 iirc)
2) Issues with dependency tracking in text SQL dumps (fixed in 8.0) meaning that some times one had to hack dumps to get them to restore properly.
3) maintenance tasks required exclusive locks on tables (corrected sometime in 7.x)
4) other things which generally made it somewhat user-unfriendly.

Note that all of the above issues have been corrected as of three years ago. However by that point a lot of the small less serious appliations were more or less wedded to MySQL. For large, shared databanks, PostgreSQL and Ingress II (and maybe Firebird) are the only serious choices. MySQL fails to be useful as soon as you end up requiring the sort of thing described in the title of Codd's original paper....

Re:Why PostgreSQL doesn't have more of the market

[McDutchie]

For large, shared databanks, PostgreSQL and Ingress II (and maybe Firebird) are the only serious choices.

How does Wikipedia manage to run on MySQL, then?

Re:Why PostgreSQL doesn't have more of the market

[turbidostato]

"How does Wikipedia manage to run on MySQL, then?"

Because it doesn't end up requiring the sort of thing described in the title of Codd's original paper?

Re:Why PostgreSQL doesn't have more of the market

[einhverfr]

What part of "large *shared* data banks" does Wikipedia need?

MySQL works quite well with large single-user apps with little normalization. When you need to build a large shared data bank, it breaks down.

Re:Why PostgreSQL doesn't have more of the market

[McDutchie]

What part of "large *shared* data banks" does Wikipedia need?

I'm clearly missing something that should be obvious (seriously, no sarcasm intended). I thought Wikis were shared by definition.

Re:Why PostgreSQL doesn't have more of the market

[Just Some Guy]

I thought Wikis were shared by definition.

Forget about the Wiki part. "Shared" here means that many clients are simultaneously reading from and writing to the same store. Sites like Slashdot and Wikipedia are darn near read-only in that sense, because a comment or article is typically viewed many thousands of times more often than it is written or edited.

Contrast with something like a point of sale system where each time a clerk scans in a product, a unit is removed from the inventory database and the accounting system gets updated with the new revenue. Multiplied by a few thousand terminals, that is a large, shared data bank.

Re:Why PostgreSQL doesn't have more of the market

[McDutchie]

Thanks, that clarifies. Appreciated.

Re:Why PostgreSQL doesn't have more of the market

[einhverfr]

Shared between applications, not users.

MySQL does work fine for light-weight content management, such as a wiki.

It does not work well for a major business information database which has many different applications running off the same data.

You are on to something.

[NotQuiteReal]

If PostgreSQL changed their name to OurSQL it would be easy to remember, and a sound a lot less selfish than MySQL.

Re:You are on to something.

[L7_]

PGSql isn't so bad.

Re:You are on to something.

[red_dragon]

PGSql isn't so bad.

"Piggy-SQL" definitely isn't kosher either.

Re:You are on to something.

[SolitaryMan]

If PostgreSQL changed their name to OurSQL it would be easy to remember, and a sound a lot less selfish than MySQL.

I think HerSQL has a good tick to it.

Why self-defeating names for FOSS?

[Futurepower(R)]

Agreed. It's more difficult to sell when it has a name that is hard to pronounce.

Why do open source software authors give their projects self-defeating names?

Another example: GIMP which means:
gimp -- noun -- disability of walking due to crippling of the legs or feet
Not too smart to give your project a name that means "cripple".

I agree with the other comment, change the name to OurSQL, EasySQL, or ProSQL. I like the last, ProSQL. (All those others are for amateurs.)

Re:Why self-defeating names for FOSS?

[kyofunikushimi]

ProSql might prove to be a difficult name change. There is already some OS X DB management software out there: http://www.scandariato.com/prosql/

Oh

[Anonymous Coward]

I got the message. I just haven't gotten around to reading it yet.

Cross-db joins are *more* powerful on PostgreSQL

[einhverfr]

than on MySQL.

On MySQL, a "database" is really what we call a "schema" on PostgreSQL (note that the "information schema" in MySQL is a separate db!).

So, you have that option built in within PostgreSQL. However, there is more.

Suppose I want to query data on another server? MySQL allows this through "FEDERATED" tables. PostgreSQL allows this through dblink.

However, there is a better tool out there. DBI-Link by David Fetter is a partial implementation of SQL/MED using Perl/DBI. So you can actually query data on a MySQL db from inside PostgreSQL.

You can't do cross-db joins because you haven't spent the time to lean and never understood that MySQL uses subtly different terminology.

Forget the "easy to set up for production"

[einhverfr]

The basic thing is-- PostgreSQL's default installation is either a starting point or a development setup. Setting it up for a production environment is completely different. You might want to have users authentiate via some central source, as system accounts, or something else. That is where the advanced documentation comes in.

Now, this has a good side: flexibility. One of the reason why we on the LedgerSMB project use the role system for application users is that it allows for external auth systems (LDAP, Kerberos, etc). 8.3 expands on these as well.

Now, it is not all good though. 8.3 by removing all the implicit casts breaks LedgerSMB so while we *will* fix problems reported to us, we will not guarantee that it will do much out of the box....

PostgreSQL is better in the sack

[inKubus]

From your link:

MySQL began development with a focus on speed while PostgreSQL began development with a focus on features and standards.

MySQL's MyISAM engine performs faster than PostgreSQL, but at the cost of transactions, data safety, and various constraints.

PostgreSQL, on the other hand, provides features, such as partial indexing, that can lead to faster performance on certain queries.

PostgreSQL is also better in the sack.

I didn't correct the wiki because I thought it was hilarious and probably true.

Why postgres fails

[Anonymous Coward]

Ok, so I've decided to give postgres a try. Took me a half hour just to figure out that I needed to create a cluster. So now I can actually connect to the postgres server. Ok, so I want to create my first table. What do you know, the postgres docs don't even give enough information on data types to be able to know what the different integer types are, and wtf are int2, int4, int8? You call 10 sentences on ints documentation?

You know why postgres isn't widely used? Because a newcomer can't even fcuking find out how to declare an unsigned int column, if they are even possible! The docs suck, and googling for tutorials doesn't turn up squat. No wonder I once installed mysql and postgres side-by-side, and had scrapped postgres within the first couple hours. Can't figure anything out.

Re:Why postgres fails

[jadavis]

Because a newcomer can't even fcuking find out how to declare an unsigned int column, if they are even possible

Unsigned ints aren't available in postgresql unless you create a custom type. Unsigned ints are not standard SQL as far as I know, and usually aren't a good idea. With all of the type coercion demanded by the SQL standard, it's very difficult to support unsigned ints with any kind of intuitive (and correct) coercion to other integer types.

Why do you need them anyway? Just use a check constraint. Declaring a domain is probably the best option.

How many databases support unsigned ints, anyway?

I don't think the docs are that bad. They could spell out that int2 is a two-byte int, I suppose. But how much is there to say about ints?

Re:Why postgres fails

[pairo]

Are you retarded, my dear sir? If you look at the link you posted, a bit above, you have table of what values you can fit in those datatypes. Not hard to figure out. Creating a cluster is not something obvious? No, I guess it isn't. Maybe you should go RTFM or use a distribution that does that for you.

As for unsigned integers and you not being able to figure it out... Please stick to MySQL, thank you.

Re:Why postgres fails

[Anonymous Coward]

Ah, I didn't see that data type table above (I was specifically looking for docs on ints, not data types in general). As for RingTFM, the steps shown in the "short version" of setup aren't even listed in the full version of installation instructions, which is what I read. Funny, who writes docs where the short version is more helpful than the long version?

The fact remains that the postgres docs are lacking.

Re:Why postgres fails

[mw]

>Can't figure anything out.

Maybe you missed your profession? Reading the docs, I immidiatly figured out how to create something you call unsigned int:
CREATE DOMAIN UINT numeric check (VALUE >= 0 and VALUE 4294967296);

ints?

[ryszard99]

seriously? you're reading slashdot and you cant understand what the differences are between the ints are even after reading the documentation?

Does it support multithreaded queries?

[G3ckoG33k]

Hi, I read that "MySQL does not uses several CPUs to execute single query - only multiple connections may benefit from several CPUs.". That was written January 6 2004 by Peter Zaitsev, then a full-time developer at MySQL AB, www.mysql.com. I found the quote at http://lists.mysql.com/benchmarks/45

Does anyone know if PostgreSQL supports a dual or quad CPU when it comes to executing a single query, or if MySQL now supports it?

The reason I ask is that I have a database with tens of millions of records and even 'simple' queries take a long time. Would it be beneficial to buy a 8 core machine, i.e. dual quad, over a single quad cpu?

Thanks for any tips or links!

Re:Does it support multithreaded queries?

[gazbo]

I doubt your problem is CPU bound; most DB issues tend to be I/O bound. If simple queries are taking too long, the chances are you need to rethink your indexing - and be sure to EXPLAIN the query to make sure it actually is using the indices, as there are various reasons (some unavoidable, some a little retarded) why it won't.

Re:Does it support multithreaded queries?

[Anonymous Coward]

A faster hard disk will help you a lot more than extra threads. The threads will spend most of their time waiting for I/O anyway.

Re:Does it support multithreaded queries?

[Anonymous Coward]

I can't speak for MySQL (I've studiously avoided using it for years). PostgreSQL won't break your query up in to chunks that are executed in parallel. If you have multiple queries going on at a time, it certainly can allow one query to be executed on one processor and another query on another processor, but those decisions are left up to the process scheduler in the operating system. Note that for many queries (I'd say "most", but that really depends on your workload) your bottleneck will be disk IO, not CPU load. If you really think you want something to parallelize your query, you might look at www.greenplum.com, which is based on PostgreSQL.

Re:Does it support multithreaded queries?

[Just Some Guy]

It would seem not to. Yeah, I wish it had that, too. The other posters who keep telling you to get faster IO miss the idea of having extra CPUs handling locking, cache management, etc. so that even single running queries are faster.

Re:Does it support multithreaded queries?

[gazbo]

We're in good company. I guess the only time you could benefit from a query being serviced by multiple processors is if your database is handling fewer concurrent requests than you have processors, and yet you need to shave milliseconds off the small number of queries that are running.

I might be missing something, but that sounds like a pretty specialised use case.

Re:Does it support multithreaded queries?

[Just Some Guy]

I guess the only time you could benefit from a query being serviced by multiple processors is if your database is handling fewer concurrent requests than you have processors,

That's often true for us. Our db sits idle a good percentage of the time, but when it runs, it needs to run quickly. In practice PostgreSQL in its current arrangement is good enough, but that doesn't mean it can't get better.

and yet you need to shave milliseconds off the small number of queries that are running.

You're joining 4 tables. Each is being filtered by an indexed field. Wouldn't it be nice if you could run each of those filters on its own CPU, then join the results at the end? That seems like a generally good thing even on smaller systems.

Re:Does it support multithreaded queries?

[fugaz]

add an index

Re:Does it support multithreaded queries?

[DragonWriter]

I guess the only time you could benefit from a query being serviced by multiple processors is if your database is handling fewer concurrent requests than you have processors, and yet you need to shave milliseconds off the small number of queries that are running.

I might be missing something, but that sounds like a pretty specialised use case.

Seems like it could be fairly common (and potentially shaving a lot more than "milliseconds" off individual queries) in an OLAP environment. Not as common or useful, I would presume, in most OLTP environments.

updatable views?

[davegaramond]

Yeah I know, it's planned in 8.4 and there are currently hacks to do this in Postgres. But it's ironic that "The world's most advanced open source database" still does not have this feature, which according to relational theory, is pretty crucial. While the toy database which everybody loves to flame, Access, has had this for what, centuries?

Just one issue

[bytesex]

I'm a great fan of postgres but I ran into an irritating limitation recently; I replicate a database over a large number of very small nodes using slony. I really don't care about the integrity of the slaves - they're read-only to their clients and should I suspect they're corrupt I just reboot (they live in memory and the OS lives on a 1 Gb read-only flash drive). But postgres insists on having a WAL directory (pg_xlog) with chunks of 16MB in it. And that's big if you live in 128MB of ramdisk, and you can't turn that off. I mean, from my reasoning - the WAL isn't really used unless you do recovery; the versions of the data are in the db itself (otherwise we wouldn't need vacuum, now would we ?) So why can't I just configure postgres to not use WAL ? And then if the db is corrupt we just die. No, say the guys on IRC, you just have to recompile it with its hard-defined value of 16MB down to something lower. Yeah right. I'm not interested in hacks - I want a versatile RDBMS.

updatable views?

[davegaramond]

Yeah I know it's planned to be in 8.4 and there are hacks to do it in the current version. But it's ironic that "the world's most advanced open source database" doesn't have this feature, which is supposedly pretty basic and crucial in a relational database system. While the "toy" database that everybody loves to hate, Access, has had this for what, centuries?

No, not a single query. Max one CPU per query.

[G3ckoG33k]

Thanks! From the post (dated 31 Oct 2007) in the link you provided:

"> Are queries spread across multiple processors?"
"No, not a single query. Max one CPU per query."

Thats is sad, even if not unexpected.

Now, even standard operations take a lot of time, and I have the only connection... Yes, it is a single user setup, without any website traffic.

-

Re:No, not a single query. Max one CPU per query.

[Just Some Guy]

Having said that, there's a lot of truth to what people are saying about IO tuning and proper indexing. I have some rather huge, many-multi-table queries that run in just a few milliseconds. You can squeeze a whole awful lot of performance out of it if you know how.

Re:No, not a single query. Max one CPU per query.

[einhverfr]

I have some queries which really would benefit from this sort of thing, but the basic truth is they are rare.

Usually in those cases, proper indexing does more than throwing hardware at the problem, however.....

Why cross database joins

[einhverfr]

Like it or not, this sort of thing is remarkably common. Pretty soon you have numerous applications designed with their own databases and someone needs information aggregating it all together. The SQL 2003 standard actually has an optional module dealing with this problem. It is called SQL/MED (Management of External Data) and it exists specifically to address management of data outside the database. One of the major limitations however is that the database cannot guarantee ACID compliance in the same way when it does not manage all aspects of the data.

In MySQL, this is done using Federated tables, but they are limited to those on MySQL servers. I.e. one cannot query Oracle from MySQL, etc.

In PostgreSQL, there is a partial implementation of SQL/MED written in PL/PerlU by David Fetter called DBI-Link. It allows you to access any data you can from Perl's DBI infrastructure as views in PostgreSQL. This means you can query data on other PostgreSQL servers, but also data on servers running MySQL, MS SQL, Oracle, DB2, LDAP, and all sorts of other things as well (note that LDAP isn't even relational in its model).

Apples and oranges

[einhverfr]

One of the issues with this discussion is that there is a disjunct in the terminology used by MySQL users and that used by the rest of us.

MySQL uses "database" and "schema" synomymously (note that their "information schema" is a separate "database"). In that sense, PostgreSQL has long had cross-schema joins in the same way MySQL does. It is just slightly harder to set things up so that you create tables in the right schemas. (hint: SET search_path='schema_name');

In this way, I do a *lot* of work using cross-schema queries. They work like a charm on PostgreSQL.

What PostgreSQL calls a "database" is analogous to the entire cluster of "databases" on MySQL. There is no analogy to "PostgreSQL cluster" on MySQL (i.e. a set of databases controlled by the same process, each of which contains multiple schemata). For that matter, last time I checked, there was no equivalent on Oracle either.

So "PostgreSQL doesn't have an equivalent to MySQL's cross-db queries" is an issue of terminology rather than substance.

At the same time, as nconway points out, PostgreSQL's MED solutions do not optimize well across distributed queries (i.e. where parts of the queries have to go through connections to other databases/servers). For MySQL users, these are like Federated tables, and I would expect similar (or worse) optimization problems in this area on MySQL. Hence when you are trying to use PostgreSQL as a distributed database, you are in for headaches. Fortunately this is not what the OP asked for :-)

So...

[jd]

Can we infer from this that PostgreSQL developers will show evidence of love bytes?

MySQL

[beyaz45]

i am useing mysql because i love mysql ! dizi izle