PostgreSQL 8.3 Released 286
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."
PostegreSQL 8.3? (Score:5, Funny)
Re: (Score:3, Insightful)
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.
Nice. (Score:2)
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. (Score:5, Funny)
Now, please excuse me while Postgres 8.3 and I go take a little alone-time in a dark closet.
Re: (Score:2)
Re: (Score:3, Insightful)
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 suppor
Re: (Score:3, Interesting)
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 o
Cross Database Joins?? (Score:4, Interesting)
Re:Cross Database Joins?? (Score:5, Interesting)
Re: (Score:2)
http://www.postgresql.org/docs/faqs.FAQ.html#item4.17 [postgresql.org]
Not as slick as doing it entirely in SQL, but it works.
Re: (Score:2)
Re:Cross Database Joins?? (Score:4, Informative)
Re: (Score:2)
Re: (Score:2)
Schemas will fail in this instance as well. Not a
Re: (Score:2)
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: (Score:3, Informative)
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 yo
Re:Cross Database Joins?? (Score:5, Insightful)
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: (Score:2)
Re:Cross Database Joins?? (Score:5, Insightful)
Re: (Score:3, Funny)
Apples and oranges (Score:3, Informative)
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 cr
Re: (Score:2)
what do you mean by cross-database join? (Score:3, Informative)
you'd probably just want to use a schema for that; the concept maps more or less the same way.
Re: (Score:2)
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
whuh? (Score:2)
Ok, so first you say:
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.
See, but whereas before you were complaining about how schemas add an unnece
Re: (Score:2)
Re: (Score:3, Informative)
Re: (Score:2)
I beleieve it would be something like 16^8. In other words, a shitload.
Re: (Score:3, Insightful)
I think the disconnect is that PostgreSQL has a different definition of schema [postgresql.org] than you are using.
Will it be used? (Score:4, Informative)
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: (Score:3, Informative)
Thats why MySQL is usually chosen.
Re:Will it be used? (Score:5, Funny)
You are on to something. (Score:5, Funny)
Re:Will it be used? (Score:4, Interesting)
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: (Score:2)
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: (Score:3, Informative)
Re: (Score:2)
And I am using MyISAM.
Re:Will it be used? (Score:5, Funny)
You have a 100 GB database and you're not concerned with stability??
Do you work for the government?
Re: (Score:3, Informative)
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: (Score:2)
Re: (Score:3, Insightful)
And then... (Score:3, Interesting)
Re:And then... (Score:4, Funny)
asynchronous committ (Score:5, Insightful)
Re:asynchronous committ (Score:5, Funny)
Re:asynchronous committ (Score:5, Interesting)
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: (Score:2)
Re:asynchronous committ (Score:5, Interesting)
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: (Score:3, Insightful)
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
Re: (Score:3, Interesting)
My other concern still stands - I hope the documentation makes the ramifications of choosing this option clear.
Re:asynchronous committ (Score:4, Informative)
Re: (Score:2)
Re: (Score:3, Insightful)
Asynch writes are useful for keeping
Re:asynchronous commit (Score:2, Informative)
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.
Re: (Score:2)
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 whe
Re: (Score:2)
When a well-defined delay expires, see wal_writer_delay [postgresql.org]. 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 [postgresql.org]
Re: (Score:3, Informative)
Re: (Score:2)
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 [honeypot.net]. 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 (Score:5, Informative)
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: (Score:3, Funny)
SO you can write sloppy inefficient code and do sloppy DBA work and get away with it?
Just an idea.
Re: (Score:3, Insightful)
Re: (Score:2)
long live postgres (Score:4, Insightful)
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: (Score:3, Interesting)
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
Re: (Score:3, Insightful)
Re: (Score:3, Funny)
My personal favorite is FAPP (FreeBSD, Apache, PostgreSQL, Perl).
...
What's so funny?
Re: (Score:2)
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: (Score:2)
Re: (Score:2)
Why PostgreSQL doesn't have more of the market (Score:3, Informative)
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
Re: (Score:3, Insightful)
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 n
Re: (Score:2, Insightful)
Re: (Score:2)
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 fo
Re:long live postgres (Score:4, Informative)
Start with the documentation on creating a cluster: http://www.postgresql.org/docs/current/static/creating-cluster.html [postgresql.org] 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 [postgresql.org] 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 [postgresql.org] 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 [postgresql.org]
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 [postgresql.org]
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/ [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: (Score:3, Interesting)
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,
Time for a cross-DB comparison (Score:5, Insightful)
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: (Score:3, Informative)
Re:Time for a cross-DB comparison (Score:4, Informative)
http://www.postgresql.org/about/featurematrix [postgresql.org]
Re: (Score:3, Informative)
Seems to be features only, no performance.
Upgrade Procedure (Score:3, Interesting)
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: (Score:2)
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 mi
Re: (Score:2)
Re: (Score:2)
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
Re: (Score:2)
Intrusive schema changes
Postgres Books? (Score:2, Interesting)
Cool! Good support for full text indexing/search (Score:2)
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
Does it support multithreaded queries? (Score:3, Interesting)
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: (Score:3, Informative)
It would seem not to [postgresql.org]. 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.
Just one issue (Score:3, Interesting)
Re: (Score:3, Informative)
Re: (Score:2)
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: (Score:2)
What's missing from it, such that it misses out on the feature-list, in your opinion?
Re:New HOT, faster Postgres (Score:5, Insightful)
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 (Score:5, Insightful)
And that's only with MyISAM (in which case, why bother with a database server? SQLite is probably enough for your needs).
Re: (Score:2)
In general, as far as query planning and such, it will outperform MySQL.
Re: (Score:3, Informative)
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.
Re:How quickly they turn on you .. (Score:4, Insightful)
Would it make a difference to you if they bumped up the version number to 9?
Re: (Score:3, Informative)
Re: (Score:2)
Slashdot has always had a fetish for F/OSS that outperforms the competition. This isn't new.
MySQL is FAST, PgSQL FARTs on MySQL's Performance (Score:3, Funny)
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.