Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

PostgreSQL 8.3 Released

ScuttleMonkey posted more than 6 years ago | from the post-postgre-post dept.

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

cancel ×

286 comments

Sorry! There are no comments related to the filter you selected.

PostegreSQL 8.3? (5, Funny)

Anonymous Coward | more than 6 years ago | (#22297600)

Would that be POSTGR~1.SQL?

Re:PostegreSQL 8.3? (0)

Anonymous Coward | more than 6 years ago | (#22299532)

"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? (0)

Anonymous Coward | more than 6 years ago | (#22299926)

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

Yeah, it's HOT, but is it FAST! (-1, Flamebait)

Reality Master 201 (578873) | more than 6 years ago | (#22297614)

Like MySQL?

Fast is all that matters, you know.

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

0racle (667029) | more than 6 years ago | (#22297700)

Yes

MySQL is FAST, PgSQL FARTs on MySQL's Performance (2, Funny)

einhverfr (238914) | more than 6 years ago | (#22300152)

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.

Nice. (1)

LWATCDR (28044) | more than 6 years ago | (#22297632)

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. (0)

plague3106 (71849) | more than 6 years ago | (#22297758)

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

Re:Nice. (5, Funny)

Seumas (6865) | more than 6 years ago | (#22298096)

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. (1)

ianare (1132971) | more than 6 years ago | (#22299006)

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.

Cross Database Joins?? (4, Interesting)

Foofoobar (318279) | more than 6 years ago | (#22297636)

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?? (4, Interesting)

geniusj (140174) | more than 6 years ago | (#22297704)

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

Re:Cross Database Joins?? (1, Insightful)

Anonymous Coward | more than 6 years ago | (#22297940)

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?? (1)

Foofoobar (318279) | more than 6 years ago | (#22298052)

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?? (3, Informative)

prog-guru (129751) | more than 6 years ago | (#22299106)

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?? (1)

Foofoobar (318279) | more than 6 years ago | (#22299420)

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?? (1)

Foofoobar (318279) | more than 6 years ago | (#22299492)

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?? (2, Informative)

Qzukk (229616) | more than 6 years ago | (#22300252)

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?? (0)

Anonymous Coward | more than 6 years ago | (#22298202)

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?? (4, Insightful)

glwtta (532858) | more than 6 years ago | (#22298730)

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?? (1)

naasking (94116) | more than 6 years ago | (#22298864)

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?? (4, Insightful)

dfetter (2035) | more than 6 years ago | (#22298020)

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

Re:Cross Database Joins?? (1)

poet (8021) | more than 6 years ago | (#22298122)

Look at plproxy or use schemas.

what do you mean by cross-database join? (2, Informative)

Reality Master 201 (578873) | more than 6 years ago | (#22298188)

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? (1)

Foofoobar (318279) | more than 6 years ago | (#22299024)

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.

whuh? (1)

Reality Master 201 (578873) | more than 6 years ago | (#22299306)


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? (1)

Foofoobar (318279) | more than 6 years ago | (#22299656)

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? (1)

gerardolm (1137099) | more than 6 years ago | (#22299946)

16 databases, 16 schemas. SHITLOAD = 16?

frist psot (-1, Offtopic)

Anonymous Coward | more than 6 years ago | (#22297646)

muthafuckas 1st post

Re:frist psot (0)

Anonymous Coward | more than 6 years ago | (#22299572)

u fail.

Re:frist psot (1, Funny)

Anonymous Coward | more than 6 years ago | (#22299666)

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

Will it be used? (4, Informative)

Anonymous Coward | more than 6 years ago | (#22297666)

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? (2, Informative)

cheater512 (783349) | more than 6 years ago | (#22298908)

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? (5, Funny)

ByteSlicer (735276) | more than 6 years ago | (#22299052)

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

Re:Will it be used? (3, Interesting)

jadavis (473492) | more than 6 years ago | (#22299872)

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? (1)

cheater512 (783349) | more than 6 years ago | (#22300222)

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? (2, Informative)

jocknerd (29758) | more than 6 years ago | (#22300266)

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? (0)

Anonymous Coward | more than 6 years ago | (#22300142)

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? (2, Informative)

LurkerXXX (667952) | more than 6 years ago | (#22300204)

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.

And then... (3, Interesting)

n3tcat (664243) | more than 6 years ago | (#22297782)

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... (3, Funny)

thrillseeker (518224) | more than 6 years ago | (#22298050)

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

asynchronous committ (4, Insightful)

stoolpigeon (454276) | more than 6 years ago | (#22297942)

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 (4, Funny)

Wesley Felter (138342) | more than 6 years ago | (#22298028)

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

Re:asynchronous committ (4, Interesting)

nuzak (959558) | more than 6 years ago | (#22298186)

> 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 (1)

nuzak (959558) | more than 6 years ago | (#22298256)

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 (5, Interesting)

RelliK (4466) | more than 6 years ago | (#22298418)

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 [wikipedia.org] 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 (2, Insightful)

Simon (S2) (600188) | more than 6 years ago | (#22298326)

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 (2, Interesting)

stoolpigeon (454276) | more than 6 years ago | (#22298410)

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 (3, Informative)

Simon (S2) (600188) | more than 6 years ago | (#22298532)

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 (0)

Anonymous Coward | more than 6 years ago | (#22300108)

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 (1)

LWATCDR (28044) | more than 6 years ago | (#22298590)

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 (2, Insightful)

Ruzty (46204) | more than 6 years ago | (#22298802)

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 commit (2, Informative)

Anonymous Coward | more than 6 years ago | (#22298338)

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:asynchronous committ (1)

Cramer (69040) | more than 6 years ago | (#22298398)

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 (2, Insightful)

dfetter (2035) | more than 6 years ago | (#22298634)

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

Re:asynchronous committ (1)

mattcasters (67972) | more than 6 years ago | (#22298692)

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 (1)

jadavis (473492) | more than 6 years ago | (#22299618)

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 [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:asynchronous committ (2, Informative)

ivoras (455934) | more than 6 years ago | (#22298732)

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 (1)

hypersql (954649) | more than 6 years ago | (#22298882)

You never _had_ durability. On most system. See http://hardware.slashdot.org/article.pl?sid=05/05/13/0529252 [slashdot.org] . Durability is hard - mainly because of hard drives. See also http://www.h2database.com/html/advanced.html#durability_problems [h2database.com] (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 (1)

Bill, Shooter of Bul (629286) | more than 6 years ago | (#22299110)

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 (1)

Just Some Guy (3352) | more than 6 years ago | (#22298920)

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 (4, Informative)

greg1104 (461138) | more than 6 years ago | (#22299212)

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 (2, Funny)

plopez (54068) | more than 6 years ago | (#22299640)

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 (0)

Anonymous Coward | more than 6 years ago | (#22299726)

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

Statice (-1, Offtopic)

Anonymous Coward | more than 6 years ago | (#22298016)


forever.

Multi-master replication built in (1)

Bandraginus (901166) | more than 6 years ago | (#22298102)

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 (3, Informative)

Acheron (2182) | more than 6 years ago | (#22298312)

The 8.3 release notes list the Bucardo project http://bucardo.org/ [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 (1)

ashridah (72567) | more than 6 years ago | (#22298352)

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 (1)

Bandraginus (901166) | more than 6 years ago | (#22298820)

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 (1)

ashridah (72567) | more than 6 years ago | (#22299600)

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 (0)

Anonymous Coward | more than 6 years ago | (#22299042)

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.

long live postgres (3, Insightful)

squoozer (730327) | more than 6 years ago | (#22298132)

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 (3, Interesting)

costing (748101) | more than 6 years ago | (#22298740)

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 (2, Insightful)

Lennie (16154) | more than 6 years ago | (#22299886)

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 (1)

bar-agent (698856) | more than 6 years ago | (#22298874)

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 (1)

KillerCow (213458) | more than 6 years ago | (#22299394)

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 [wikipedia.org] in a database... /sarcasm

Re:long live postgres (1)

Ed Avis (5917) | more than 6 years ago | (#22299738)

What I really don't understand though is why Postgres doesn't own more of the database market.
Worse is better [jwz.org] .

Re:long live postgres (1)

Foofoobar (318279) | more than 6 years ago | (#22299768)

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 (1)

Tablizer (95088) | more than 6 years ago | (#22300030)

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.
 

New HOT, faster Postgres (-1, Flamebait)

byoung (2340) | more than 6 years ago | (#22298146)

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

Re:New HOT, faster Postgres (5, Insightful)

naasking (94116) | more than 6 years ago | (#22298662)

Yeah, if only for those darn inconvenient facts demonstrating that PostgreSQL is faster than MySQL, particularly under load [tweakers.net] . Note that the benchmark was PostgreSQL 8.2. Now note that 8.3 is up to twice as fast as 8.2 [kaltenbrunner.cc] . 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 (0)

Anonymous Coward | more than 6 years ago | (#22299396)

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 (4, Insightful)

glwtta (532858) | more than 6 years ago | (#22299012)

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 (1)

geniusj (140174) | more than 6 years ago | (#22299054)

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.

Time for a cross-DB comparison (4, Insightful)

jd (1658) | more than 6 years ago | (#22298180)

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 (2, Informative)

naasking (94116) | more than 6 years ago | (#22298766)

A couple [geocities.com] such comparisons [devx.com] already exist. They may be a year or two out of date however.

Re:Time for a cross-DB comparison (3, Informative)

TheNarrator (200498) | more than 6 years ago | (#22298992)

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 [postgresql.org]

Re:Time for a cross-DB comparison (2, Informative)

0racle (667029) | more than 6 years ago | (#22299596)

PostgreSQL vs. MySQL WIki [wikivs.com]

Seems to be features only, no performance.

why do they get a hot? (0)

Anonymous Coward | more than 6 years ago | (#22298742)

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

How quickly they turn on you .. (-1, Troll)

jon3k (691256) | more than 6 years ago | (#22298968)

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 .. (3, Insightful)

swimmar132 (302744) | more than 6 years ago | (#22299056)

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 .. (2, Informative)

glwtta (532858) | more than 6 years ago | (#22299238)

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

Re:How quickly they turn on you .. (1)

Just Some Guy (3352) | more than 6 years ago | (#22299616)

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.

Upgrade Procedure (2, Interesting)

AltImage (626465) | more than 6 years ago | (#22299326)

"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 (1)

ashridah (72567) | more than 6 years ago | (#22299794)

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 (1)

Jeffrey Baker (6191) | more than 6 years ago | (#22300104)

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 (1)

ashridah (72567) | more than 6 years ago | (#22300236)

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

Postgres Books? (2, Interesting)

crustymonkey (731497) | more than 6 years ago | (#22299754)

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?

Cool! Good support for full text indexing/search (1)

MarkWatson (189759) | more than 6 years ago | (#22299854)

Just recently, I discovered that Ferret had synchronization problems when I deployed my my http://cookingspace.com/ [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.
Load More Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>