Developers Club geek daily blog

Design in PostgreSQL of the dokumento-oriented API: Full-text search and saving of many documents (Part 2)

1 year, 10 months ago
In the first part of this series of articles, I created good function of saving, as well as other function allowing to create the changeable dokumento-oriented tables on the fly. They work regularly and do that it is necessary, but we can make still a lot of things. Especially: I want the full-text search indexed on the fly and saving of many documents in transaction.

Let's make it.

Read more »

Access to tables of Xi of expansions for Postgres

1 year, 10 months ago

Hi everyone!

This time I will tell not about use Python or the next trick with CSS/HTML and, alas, not how I ported 5 years Vangera, and about one important aspect of writing of expansions for remarkable PostgresSQL DBMS.

Actually, there are already many articles about how to write expansion for Postgres on Xi (for example, this) including in Russian. But, as a rule, in them rather simple cases are described. In these articles and instructions authors implement functions which obtain data on an input, somehow process them, and then return one number, a line or the user type. In them there are no explanations what to do if from Xi of a code it is necessary to be run according to the normal table existing in base or an index.

It is possible to get access through well described but slow SPI (Server Programming Interface) to tables of Xi, there is also very difficult method, via buffers, and I will tell about compromise option. Under a cat I tried to give code samples with detailed explanations.

Read more »

Design in PostgreSQL dokumento-oriented API (Part 1)

1 year, 10 months ago
This article is transfer, original article is here, the author of Rob Conery.

Postgres as many know, supports JSON as data storage type, and with an output 9.4, Postgres supports storage of JSON in the form of jsonb now — a binary format.

These are fine news to those who want to step further simple "storages of JSON as the text". jsonb supports indexing with use of a GIN index now, and also the special operator of requests who allows to get advantages of a GIN index has.

Read more »

We invite to PGConf 2016 — the Russian PostgreSQL conference

1 year, 10 months ago
On February 3-5, 2016 in Moscow on a site of News hall (Pushkin Square, 5) there will take place the international Russian PgConf.Russia 2016 conference. Conference will be organized by the Russian community PostgreSQL with assistance of sponsors. The general partner of PGConf.RU 2016 — the Postgres Professional company, gold the partner became the Avito company.

This conference will be organized in Moscow already the second time. In February, 2015 PGConf.RU brought together 460 participants, having become the world's largest forum devoted to PostgreSQL.

Main subjects of conference:

  • Scalability, performance, safety of PostgreSQL.
  • Development of a kernel of PostgreSQL. Internal device. Current and future projects.
  • Live experience of practical use of PostgreSQL in Russia and abroad. Implementation, migration, applications programming. Reports "from fields".
  • Cluster. Fault-tolerant and scalable systems based on PostgreSQL
  • PostgreSQL in Russia. Russian community. Education. PostgreSQL in problems of import substitution and achievement of technology independence.

Read more »

PostgreSQL на многоядерных серверах Power 8

1 year, 11 months ago


При помощи московского представительства компании IBM мы провели тестирование производительности последних версий СУБД PostgreSQL на серверах Power8, изучили масштабируемость зависимость производительности от количества одновременных запросов, нашли узкие места ограничивающие производительность, предложили новые технические решения и добились рекордной производительности.


В ряде задач практически неограниченного масштабирования по объему обрабатываемых транзакций можно достичь, используя распределённые системы, в которых тем или иным способом поток транзакций распределяется на большое количество серверов. Такое масштабирование часто называют “горизонтальным”. Однако, универсального распределенного решения не существует, кроме того, распределённость имеет свою цену. Архитектура системы должна заранее проектироваться как распределённая. Распределенные системы менее гибки, чем монолитные, к тому же они сложнее в эксплуатации и требуют более высокой квалификации персонала. Одни задачи легче поддаются распараллеливанию, другие — сложнее. Поэтому спрос на высокопроизводительные монолитные системы существует, и достижение возможно лучших результатов по производительности в рамках одного сервера было и остается важной задачей. Это часто называют “вертикальным масштабированием”.

Сущность проблем, возникающих при параллельной обработке большого количества транзакций в монолитных и распределенных системах, одинакова — это конкуренция между транзакциями за доступ к одним и тем же ресурсам. Говоря просто, отдельные процессы работают параллельно и независимо до тех пор, пока не выстраиваются в очередь к какому-либо общему ресурсу (а это может быть как аппаратный ресурс, так и элемент информации, хранящийся в базе данных) и не начинают ожидать друг друга.

Для решения таких проблем существуют механизмы управления доступом к ресурсам — использование блокировок, а также пригодные в некоторых случаях неблокирующие (lock-free) подходы. Рост производительности этих механизмов, а также детализация блокировок дает возможность снизить издержки, связанные с одновременным (конкурентным) доступом.

При этом, если в распределённых системах узким местом оказывается, как правило, сеть, то в монолитных системах, близких к пиковой производительности, её рост ограничивается именно упомянутыми механизмами управления одновременным доступом.

Read more »

The report from a mitap #PostgreSQLRussia on November 3

1 year, 11 months ago

On November 3 in our Moscow office there took place the meeting of community #PostgreSQLRussia. This time participants discussed nuances of transition to PostgreSQL from other DBMS. In Russia already several years are observed a tendency to failure from proprietary DBMS. Some large companies already migrated or are in process of migration on PostgreSQL. Their experience is interesting not only from the point of view of the process of migration. Large projects which passed to Postgres can share new unique experience that, certainly, is useful also for those who use Postgres long ago. You watch our report from conference under a cat.

Read more »

Instruction of the evangelist of PostgreSQL: replicants against replication

1 year, 11 months ago

Throughout a series of the publications "Instruction of the Evangelist of PostgreSQL..." (1, 2) expensive edition contacts again, the promised overview of replicaiton engines in PostgreSQL and MySQL this time. The frequent criticism of replication of MySQL was the main cause for writing. As it often happens, the typical criticism is strong mix from the truth, a half-truth and evangelism. All this is repeatedly replicated by different people without special attempts to understand heard. And as it is quite extensive subject, I decided to take out analysis in the separate publication.

Read more »

PostgreSQL 9.5: any news? Part 3. GROUPING SETS, CUBE, ROLLUP

1 year, 11 months ago
We continue to get acquainted with new opportunities in PostgreSQL 9.5.
Today we will consider multiple groupings in one request. This opportunity was described in the SQL-99 standard. It is convenient to apply it if you need to make several request to the same table, differing only in a condition in GROUP BY. For this purpose GROUPING SETS, ROLLUP, CUBE modifiers are specified as a grouping element after a key word of GROUP BY.
Let's look closer as it works.

Read more »

PostrgreSQL: we accelerate through intarray

1 year, 11 months ago
Years so 6 back when the elephant was only in 8.0, and I densely sat on MySql, often heard appeals to replace DB. I remember as it was painful to begin. But after decided, was never sorry and on a muscle I will already hardly return. Much here it is plus, but a post not about it.

The task came: to write shop, big in the long term. A la Fotos, Hotlayn. Well and the standard task for such platforms is a filter.

Read more »

We invite on #PostgreSQLRussia on November 3

1 year, 12 months ago

On November 3 at the Moscow office of Mail.Ru Group the meeting #PostgreSQLRussia will take place. This time transition to PostgreSQL from other DBMS will be the main subject. At us in the country in recent years more and more companies pass with proprietary to open systems, in particular to PostgreSQL. This process has the nuances, and to each large project is what to tell about the experience of migration. This information would be useful not only to those who else are going to pass to PostgreSQL, but also to rather experienced users.

Read more »