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:Great.
by
thesupraman
·
· Score: 2, Interesting
ok, so replace encryption with one way hashing. It's effectively the same thing here, it enhances the security of your passwords, a very very welcome step, and not something to be treated lightly.
One of the biggest problems I have with database servers are all the passwords that end up floating around semi-protected, this is a GREAT new feature!
Long time mysql user, postgresql newbie
by
gid
·
· Score: 2, Interesting
I've been using mysql for close to three years now, maybe around 6 months back I tried out postgresql, I switched a site or two over to using it. I did some lame inaccurtate benchmarking of a "typic page", pgsql was twice as slow at the time using v7.1, untweaked, but mysql was also untweaked.
Anyway's let me tell you, pgsql's user permissions still make my head swim, it's a nightmare. I mean, ok there's like how many different ways to authenticate a user, plain text password, crypted password, now md5, ident, local ident, kerberos, etc etc. Seriously, what's the "preferred" way to add a normal, non super user, only has select, insert, update, and delete access to a given database that can connect from the local machine, and remotely. Is this even possible? (yes I know you have to give it the -i option for network access)
I guess another kind of oddity about the pgsql is that out of the box, it only does ident type local socket authetication, no tcp/ip. You have to add all these weird rules to a config file to enable these mysterious, "accounts with passwords". Documentation inside the actual config files makes me dead btw.
I've looked forever, but I've yet to find a "mysql to postgresql" quick start guide. I know, rtfm, and figure it out, trust me I have, there's a boat load postgresql docs all split over 10 different manuals it seems.
Also, would it be darn nice to include a start/stop script that reads only config files and can be linked from/etc/rc2.d/ etc. pg_ctl doesn't exactly cut it. I wrote one myself that's basically a wrapper for pg_ctl, but it's a major hack, I can clean it up and post it if anyone's intersted.
Overall a good db, it definitely takes way longer than mysql to set up and understand, but the added features make it worthwhile. Even if you don't use the triggers, out the box transactions, and sub query support right away, you'll be glad when do finally want to use them to help you out with data integrity. Sorry, I'm rambling, I have no real point, this is more of a "this is my experience with this thing post". Maybe it will be of some use to someone, and hopefully I'll get an intelligent post or two setting me straight.:)
The O'Reilly book explains this excellently
by
Anonymous Coward
·
· Score: 1, Interesting
And you can even read it online. I recommend spending the money to buy it hardcopy. This book is *excellent*.
Like you, I couldn't figure out how to configure users. This book explains it so it makes sense. I was even able to make SSL authentication work! I can't recommend it highly enough.
Re:while that's true
by
Sivar
·
· Score: 2, Interesting
Fair enough.
However, Yahoo is not a typical business. As the Yahoo article says, "Finance managed its database needs through homegrown flat files and Berkeley DB databases. But those solutions proved to be inflexible and not scalable enough for their needs..."
Homebrew flat files will be wooped by any decent DB server, especially a blazing fast one like MySQL, any day of the week. Yahoo also tends to embrace open source and new technologies more readily. Most of their servers, for example, run FreeBSD and have since the beginning.
This is a slightly bad example considering that FreeBSD is unquestionably a product that you can trust your mission critical data to, but it illustrates to a degree the type of company that Yahoo is.
I, myself, would trust MySQL with anything that didn't matter to someone else. In the end--it's politics, and "nobody ever got fired for using an ACID compliant database.";-)
-- Computer Science is no more about computers than astronomy is about telescopes. --E. W. Dijkstra
Re:Online Backups/High Availability
by
aminorex
·
· Score: 2, Interesting
MySQL has master-slave replication.
In fact, you can make a trees of replication
(good for high transaction volumes with massive
redundancy) or even daisy-chain replication into
a ring (giving you master-....-master).
It's too bad that it takes about 2 years to
update the public perception when a product
transitions from toy to tool.
.
-- -I like my women like I like my tea: green-
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).
ok, so replace encryption with one way hashing. It's effectively the same thing here, it enhances the security of your passwords, a very very welcome step, and not something to be treated lightly.
One of the biggest problems I have with database servers are all the passwords that end up floating around semi-protected, this is a GREAT new feature!
I've been using mysql for close to three years now, maybe around 6 months back I tried out postgresql, I switched a site or two over to using it. I did some lame inaccurtate benchmarking of a "typic page", pgsql was twice as slow at the time using v7.1, untweaked, but mysql was also untweaked.
/etc/rc2.d/ etc. pg_ctl doesn't exactly cut it. I wrote one myself that's basically a wrapper for pg_ctl, but it's a major hack, I can clean it up and post it if anyone's intersted.
:)
Anyway's let me tell you, pgsql's user permissions still make my head swim, it's a nightmare. I mean, ok there's like how many different ways to authenticate a user, plain text password, crypted password, now md5, ident, local ident, kerberos, etc etc. Seriously, what's the "preferred" way to add a normal, non super user, only has select, insert, update, and delete access to a given database that can connect from the local machine, and remotely. Is this even possible? (yes I know you have to give it the -i option for network access)
I guess another kind of oddity about the pgsql is that out of the box, it only does ident type local socket authetication, no tcp/ip. You have to add all these weird rules to a config file to enable these mysterious, "accounts with passwords". Documentation inside the actual config files makes me dead btw.
I've looked forever, but I've yet to find a "mysql to postgresql" quick start guide. I know, rtfm, and figure it out, trust me I have, there's a boat load postgresql docs all split over 10 different manuals it seems.
Also, would it be darn nice to include a start/stop script that reads only config files and can be linked from
Overall a good db, it definitely takes way longer than mysql to set up and understand, but the added features make it worthwhile. Even if you don't use the triggers, out the box transactions, and sub query support right away, you'll be glad when do finally want to use them to help you out with data integrity. Sorry, I'm rambling, I have no real point, this is more of a "this is my experience with this thing post". Maybe it will be of some use to someone, and hopefully I'll get an intelligent post or two setting me straight.
And you can even read it online. I recommend spending the money to buy it hardcopy. This book is *excellent*.
Like you, I couldn't figure out how to configure users. This book explains it so it makes sense. I was even able to make SSL authentication work! I can't recommend it highly enough.
Here is a link to the online copy of Practical PostgreSQL. Enjoy!
Fair enough.
;-)
However, Yahoo is not a typical business. As the Yahoo article says, "Finance managed its database needs through homegrown flat files and Berkeley DB databases. But those solutions proved to be inflexible and not scalable enough for their needs..."
Homebrew flat files will be wooped by any decent DB server, especially a blazing fast one like MySQL, any day of the week. Yahoo also tends to embrace open source and new technologies more readily. Most of their servers, for example, run FreeBSD and have since the beginning.
This is a slightly bad example considering that FreeBSD is unquestionably a product that you can trust your mission critical data to, but it illustrates to a degree the type of company that Yahoo is.
I, myself, would trust MySQL with anything that didn't matter to someone else. In the end--it's politics, and "nobody ever got fired for using an ACID compliant database."
Computer Science is no more about computers than astronomy is about telescopes. --E. W. Dijkstra
MySQL has master-slave replication.
In fact, you can make a trees of replication
(good for high transaction volumes with massive
redundancy) or even daisy-chain replication into
a ring (giving you master-....-master).
It's too bad that it takes about 2 years to
update the public perception when a product
transitions from toy to tool.
.
-I like my women like I like my tea: green-
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