vvizard writes "After almost a full year of development since PostgreSQL v7.1 was released, the PostgreSQL Global Development Group is proud to announce the availability of their latest development milestone ... PostgreSQL v7.2, another step forward for the project."
Re:Congrats to the PostgreSQL Development team!
by
thing12
·
· Score: 5, Interesting
Eliminating the locking vacuum...
I should probably clarify that - the full locking vacuum was separated out into two parts one which analyzes statistics and doesn't lock the tables and another which does what the old vacuum did by reordering data blocks to shrink the size on disk.... the bonus is that before you might run vacuum once a week or so because of the impact it has to a production system by doing a full lock on each table it vacuumed, now you can run it much more frequently as all it consumes is cpu time. Shrinking size on disk is nice, but it's the statistics that help the query planner turn SQL into faster queries.
I have only one feature request for PostgreSQL...
by
Trepalium
·
· Score: 3, Interesting
I really wish there was an embedable version of PostgreSQL... It's a very good database, but it's sometimes a real pain to write a program that ties together a SQL database with anything else, unless it's a local-use only program. I know MySQL added this feature in 4.x (but their transaction support is too new, IMO).
-- I used up all my sick days, so I'm calling in dead.
Re:some might disagree
by
dietz
·
· Score: 5, Interesting
That's a cheesy way to dispute his claims.
They're not use MySQL to store all their critical data. They're dumping all their data, presumably from some other more reliable database (Oracle, it sounds like), into mysql for quick web searches.
IOW, they're using mysql for what it does best: As a fast datastore for when data integrity isn't important (because they have all the data backed up in Oracle and could redump it to mysql at any time).
Admittedly, some of this post is conjecture, but you'd be crazy to suggest that the Census Bureau would trust all their critical data to mysql.
Re:Congrats to the PostgreSQL Development team!
by
nconway
·
· Score: 4, Interesting
I should probably clarify that - the full locking vacuum was separated out into two parts one which analyzes statistics and doesn't lock the tables and another which does what the old vacuum did by reordering data blocks to shrink the size on disk....
This is incorrect (I believe it describes the situation after 7.1: the VACUUM ANALYZE command only needed to lock the table exclusively when VACUUMing, only a read lock was needed for ANALYZE).
In 7.2, the ANALYZE command can now be used separately, as you say. However, there are other (more important) improvements: ANALYZE only takes a look at a statistical sampling of the rows in the table. This means that collecting statistics on even enormous tables is very fast. Furthermore, VACUUM has been made "lazy" by default: this means that it doesn't attempt to reclaim space as aggressively as before, but it no longer requires an exclusive lock on the database (instead, it cooperates with other DB clients). The old behavior is available as "VACUUM FULL", and it is suggested whenever you need to reclaim a lot of diskspace (e.g. you delete hundreds of thousands of rows of data and need the space).
you might run vacuum once a week or so
It was (and is) suggested that you run VACUUM once per day.
it's the statistics that help the query planner turn SQL into faster queries.
As far as I know, you only really need to update your planner stats when you change the statistical distribution of your data. Of course, running ANALYZE's reasonably often won't do any harm, and is a relatively cheap operation (performance-wise).
Re:So when should we expect...
by
Smoking
·
· Score: 3, Interesting
Postgres Ti:
Done...
I've got 7.1 running on my Titanium Powerbook...
there are really nice MacOS X packages of Postgres at Marc Liyanage's home page
I also take this occasion to thank him for the nice MacOS X packages he's put together...
Quentin
Thanks for the notice!
by
alexhmit01
·
· Score: 3, Interesting
We run PostgreSQL on a dual-processor Linux box to feed our OpenBSD web servers. We got a HUGE speed gain from the OpenBSD -> Linux change (even when we ran it on a slower machine while testing it), and any SMP gains will be helpful.
When we did OpenBSD we had to be VERY careful not to do more queries than necessary (including some complicated joins and then having PHP parse the results). With Linux as the database server, I feel that I can throw hardware at it (including moving to Solaris if need be) and optimize the queries a bit less to abstract the programming.
SMP improvement is important, as the next step up for us is a Quad-Xeon processor, then Sun Hardware. (PostgreSQL seemed to run best on Linux and Solaris from the old website)...
It's such a shame that they never figured out the PostgreSQL support model. I would have happily paid for some support, but it always seemed easier to get the OpenBSD port or the Redhat RPM than pay for their CDs. They never included much beyond installation support. I knew how to install it, having some support (not the mailling list) for some of my optimization questions would have saved days and been worth a support contract.
Slashdot Mirror
I should probably clarify that - the full locking vacuum was separated out into two parts one which analyzes statistics and doesn't lock the tables and another which does what the old vacuum did by reordering data blocks to shrink the size on disk.... the bonus is that before you might run vacuum once a week or so because of the impact it has to a production system by doing a full lock on each table it vacuumed, now you can run it much more frequently as all it consumes is cpu time. Shrinking size on disk is nice, but it's the statistics that help the query planner turn SQL into faster queries.
I really wish there was an embedable version of PostgreSQL... It's a very good database, but it's sometimes a real pain to write a program that ties together a SQL database with anything else, unless it's a local-use only program. I know MySQL added this feature in 4.x (but their transaction support is too new, IMO).
I used up all my sick days, so I'm calling in dead.
That's a cheesy way to dispute his claims.
They're not use MySQL to store all their critical data. They're dumping all their data, presumably from some other more reliable database (Oracle, it sounds like), into mysql for quick web searches.
IOW, they're using mysql for what it does best: As a fast datastore for when data integrity isn't important (because they have all the data backed up in Oracle and could redump it to mysql at any time).
Admittedly, some of this post is conjecture, but you'd be crazy to suggest that the Census Bureau would trust all their critical data to mysql.
This is incorrect (I believe it describes the situation after 7.1: the VACUUM ANALYZE command only needed to lock the table exclusively when VACUUMing, only a read lock was needed for ANALYZE).
In 7.2, the ANALYZE command can now be used separately, as you say. However, there are other (more important) improvements: ANALYZE only takes a look at a statistical sampling of the rows in the table. This means that collecting statistics on even enormous tables is very fast. Furthermore, VACUUM has been made "lazy" by default: this means that it doesn't attempt to reclaim space as aggressively as before, but it no longer requires an exclusive lock on the database (instead, it cooperates with other DB clients). The old behavior is available as "VACUUM FULL", and it is suggested whenever you need to reclaim a lot of diskspace (e.g. you delete hundreds of thousands of rows of data and need the space).
It was (and is) suggested that you run VACUUM once per day.
As far as I know, you only really need to update your planner stats when you change the statistical distribution of your data. Of course, running ANALYZE's reasonably often won't do any harm, and is a relatively cheap operation (performance-wise).
Postgres Ti:
Done...
I've got 7.1 running on my Titanium Powerbook...
there are really nice MacOS X packages of Postgres at Marc Liyanage's home page
I also take this occasion to thank him for the nice MacOS X packages he's put together...
Quentin
We run PostgreSQL on a dual-processor Linux box to feed our OpenBSD web servers. We got a HUGE speed gain from the OpenBSD -> Linux change (even when we ran it on a slower machine while testing it), and any SMP gains will be helpful.
When we did OpenBSD we had to be VERY careful not to do more queries than necessary (including some complicated joins and then having PHP parse the results). With Linux as the database server, I feel that I can throw hardware at it (including moving to Solaris if need be) and optimize the queries a bit less to abstract the programming.
SMP improvement is important, as the next step up for us is a Quad-Xeon processor, then Sun Hardware. (PostgreSQL seemed to run best on Linux and Solaris from the old website)...
It's such a shame that they never figured out the PostgreSQL support model. I would have happily paid for some support, but it always seemed easier to get the OpenBSD port or the Redhat RPM than pay for their CDs. They never included much beyond installation support. I knew how to install it, having some support (not the mailling list) for some of my optimization questions would have saved days and been worth a support contract.
Alex
Here's my Postgres wish list:
1. Point in time recovery
2. Reconstruct SQL from write ahead log
3. Function based indexes with SQL rewrite
4. Materialized views with SQL rewrite
5. Analogue to Oracle's v$sqlarea
6. Wait statistics
7. Tablespaces
8. Inline views (from clause subselects)
9. Parallel query capability
10. Partioned tables
11. Bitmap indexes
12. IO monitoring (read/write per object)
13. Dynamic sort and hash area allocation
14. Detailed SQL tracing (rows per plan step)
15. Multiplexed WAL writes
16. SQL optimizer hints