Bruce Momjian, Marco Green, Fedor Sigaev and Maxim Boguk answered questions about PostgreSQL
June 30, we held an online session of "Questions to the world's leading experts PostgreSQL". Within three hours, anyone could ask a question to one of the invited guests and get a comprehensive answer. The result is more than 40 questions, none of which went unheeded.
Participants: Bruce Momjian (Bruce Momjian) (PGDG, EnterpriseDB), Maxim Boguk (Rambler, Masterhost), Fedor Sigaev (PGDG), Marco Creen (Marko Kreen) (Skype).
Here are some selected questions and answers:
the Question # 3
the kaikai:
One of the unfortunate pieces in the work is the absence of design "INSERT OR UPDATE". In MySQL you can do this: "INSERT INTO users (username, email) VALUES ('Jo', 'jo@email.com') ON DUPLICATE KEY UPDATE email = 'jo@email.com'". Will there be something similar in PostgreSQL?
the Bruce Momjian, the leader of community PostgreSQL, the expert of the company of EnterpriseDB:
Standard (ANSI) method for such things is a MERGE or UPSERT. Both are in our TODO list and, as far as I know, someone is working on this functionality for PostgreSQL 8.4, though I personally have not seen this patch. We, no doubt, need such syntax.
the Question # 5
the Alexander:
Questions to the developers of Skype:
1) I Want to assess the scale of Skype, at least approximately. How many users are registered now? How many users are online at the same time? How many transactions per second performs Your PostgreSQL? How many PostgreSQL servers?
2) How to measure the number of transactions per second in PostgreSQL?
the Marco Kreen, the engineer of company Skype:
1) the Total number of users we now have in the region of 300 million. I don't know about active user, but my Skype client shows that now online there are 10 million users, and this is not peak load. I have no exact figures on the number of transactions per second (TPS), but I think our load — somewhere around 10-20 thousand TPS. In total we have more than 100 servers PostgreSQL, but not all of them work at the same time.
2) Use the log analyzer or pgBouncer set before the database, it gives such statistics.
the Question No. 7
the Sergey Konoplev
We have tasks to event-based operations: add/modify/delete. This, of course, can be done in triggers, but at the level of WAL files IMHO it would be more effective, not to mention the fact that processing could be delayed and submitted. Question — is there a well-documented API for working with WAL files and, if so, how much it will change from version to version? If so, where can I find information about it?
the Feodor Sigaev, leading developer of PostgreSQL:
The external API is missing because it was never intended for external access to the WAL files. Moreover, their format is compatible only for minor versions, guarantees compatibility between major versions of never even expected. In Postgres't even have a Central decryptor WAL records, each record in a sense, a container that has a sign of the decryptor (table, btree index, GiST index, etc.) to Build their treatment on WAL-logs, not very convenient also for the following reason: the file can be reused, and Postgres did not notify the outside world about it.
the Bruce Momjian, the leader of community PostgreSQL, the expert of the company of EnterpriseDB:
So You are looking for a way to read WAL files directly? There is no API for this, because their internal format is quite complex. I now realized that the ability to read logs could be useful, perhaps we should consider the inclusion of this suggestion in the TODO list. We have actually pg_filedump (http://sources.redhat.com/rhdb/utilities.html)but I'm not sure that he dampit WAL files.
the Marco Kreen, the engineer of company Skype:
Maybe it is more efficient, but less reliable (for example, WAL-rotirovatsya file without your knowledge). Please try PgQ from Skytools package and study the scripts which use this mechanism for Queuing. This is a very effective queue within a database, easy to use and reliable. Also there is a built-in framework to guarantee transactions across multiple databases in the processing queue. Still there are triggers that automatically detect the table structure and format data in an easily parsed format (urlencode).
the Question # 8
the sakalr:
A question about books. Today I got the book from the British Amazon, had to wait a few weeks. In Russia from the current versions found only the book for 3900 rubles. In General, though books are even in English, but I want to come in "Biblio-Globus" or the "Young Guard" and to see this book on the shelf, but not for 3900 rubles (on Amazon but this book was 2 times cheaper). There is a need for developing on PostgreSQL, but for me as a novice in PgSQL, it's complicated. Constantly having to chat on freenode in broken English, to learn the basics, spending a lot of time, when it should just be mechanical digestion. That is why the question arises about books. Books on postgresql (eng./eng.) really need.
the Feodor Sigaev, leading developer of PostgreSQL:
If You are already familiar with any dialect of SQL, Postgres should not cause You great inconvenience. Documentation Postgres very good and it is usually enough (with careful reading, of course). If You are not familiar with SQL, it is better to take any textbook on SQL.
the Bruce Momjian, the leader of community PostgreSQL, the expert of the company of EnterpriseDB:
I completely agree that we need more English and Russian books. Ideally, we need to have translated into Russian language documentation for PostgreSQL.
the Question 18
the kaikai:
Here's another question about pgAdmin — do you plan to regularly build packages under a common version of Linux? For example, in Ubuntu 8.04 now pgAdmin 1.8.2 (buggy) and on the website pgadmin.org is already pgAdmin 1.8.4.
the Bruce Momjian, the leader of community PostgreSQL, the expert of the company of EnterpriseDB:
I was told that these problems have been fixed in Ubuntu (Ubuntu), which, by the way, I use. I think the only problem here is to force the developers of the Ubuntu update servers for updates. Also works great bundle of software packages with Postgres for Ubuntu and You might want to try it. There is the PostgreSQL server, pgAdmin and many other software packages prepared by Dave page (Dave Page), the author of pgAdmin, not the developers of Ubuntu.
the Question 26
the in Skype you are migrating between major PostgreSQL (major) version?
the Marco Kreen, the engineer of company Skype:
Using Londiste (replication component in Skytools).
the Issue # 36
the Dmitry:
Do you plan the introduction of integrated multi-master replication? If so, when can we expect beta version?
the Bruce Momjian, the leader of community PostgreSQL, the expert of the company of EnterpriseDB:
We could try to implement multi-master replication, but blocking often makes it unproductive. Most developers said the master/slave replication or protezirovanie data across servers the best alternative. PgPool, by the way, can run in multi-master mode. PgCluster implements a multi-master, but its performance leaves much to be desired. Slony-II was trying to implement multi-master, but again, the same productivity was low.
the Feodor Sigaev, leading developer of PostgreSQL:
There are plans for a built-in replication, Master-Multiple Slaves, I hope that it will be in 8.4. About Multi-Master — look at Postgres-R source code open for the past month.
the Marco Kreen, the engineer of company Skype:
If You want to distribute the load on the record for several cars, view the PL/Proxy is multi-master in the conventional sense of the word, but he has a very good performance, which is lacking in conventional multi-master systems.
A full history of the online session can be read our site.
Комментарии
Отправить комментарий