Developers Club geek daily blog

It is a little about query tuning

2 years ago
Want on a simple example to tell about how sometimes it is possible to optimize requests quite simple at first sight strongly. Let's take such code, for an example on PostgreSQL 9.3, but the principle approaches all DBMS at which there is hash join.

Task simple — to sdzhoynit two tables — one very big, another small — but dzhoin not simple, but gold with OR. (As a real case — dzhoin tables of postings on accounts to accounts, considering that in wiring two fields with the account — for the debit and the credit.)

Read more »


Instruction of the evangelist of PostgreSQL: we criticize MySQL even more competently

2 years ago


Since the previous publication expensive edition has received large number of responses. The majority of them were positive that undoubtedly strengthens belief of expensive edition in mankind. Also some serious additions in the form of critical remarks about MySQL about which I or have forgotten have arrived, or never heard. As has led to creation of the second part which actually is addition to the first and initially did not include in my plans.

So, we continue analysis of typical delusions about MySQL within cultural exchange and autumn aggravation. To start some critical reviews of the first part.

Read more »


Recursive requests in PostgreSQL (WITH RECURSIVE)

2 years ago

Strangely enough, to understand a recursion, in PostgreSQL it is not necessary to understand a recursion. Because WITH RECURSIVE which is present at a posgresa (and in other serious bases) is rather calculation something iterations before some condition is satisfied.
Nevertheless it is very useful functionality of base which can be used, for example, to display all subcategories of the set category if the table is set in a type (id, parent_id...)

Read more »


Instruction of the evangelist of PostgreSQL: we criticize MySQL competently

2 years, 1 month ago
image

Hi, Habr! This publication — attempt to dispel some popular myths and legends of MySQL. I was not mistaken with hub as the publication of Possibility of PostgreSQL which are not present in MySQL and vice versa from here was the cause for writing. The publication regarding criticism of MySQL though is imperfect, but is quite correct, and here comments to it direct at sad reflections.

Generally speaking, I was going to write the publication about opportunities of MySQL which are not implemented or implemented in PostgreSQL worse. But not to stir many subjects in one publication, and considering quite hard work on comparison of that I know very well (MySQL) that I know very badly (PostgreSQL), I have decided to postpone such publication for later and for a start to answer immediately many comments from the publication varanio.

Read more »


How to count everything on light as one SQL query. Window PostgreSQL functions

2 years, 1 month ago

I with surprise have found out that many developers, even long ago the using postgresql, do not understand window functions, considering as their some special magic for favorites. Well or at best "kopipastit" from expression StackOverflow like "row_number () OVER ()", without going into detail. And after all window functions — the most useful functionality of PostgreSQL.
I will try to explain simply how it is possible to use them.

Read more »


New GUI for Postgresql

2 years, 1 month ago
I want to share new product with all users of the remarkable Postgresql database. Meet — SQL Tabs — the graphic SQL console for Postgresql. It is new GUI the client in whom it is possible to view database objects, to write SQL requests and to build diagrams.

Today it is possible to download versions for OSX and Ubuntu. Users of other linux-distribution kits can start application from source codes. Unfortunately, the version for Windows is not present so far. There is a wish to hope that application will be pleasant to community, and there will be developers capable to help with release of packets under different platforms: rpm, debian, windows.

It is a little information for developers: SQL Tabs is written on the stack javascript, electron, react 
 and uses native libpq the driver for work with base. Is issued under the licenses GPL3.

Now about the main features of SQL Tabs, differences from other similar products and the main motivation which stands behind development. 



Read more »


Adminer — the web interface for databases of one .php in size the file

2 years, 1 month ago


In the light of recent post about comparison of PostgreSQL and MySQL, in comments there was problem of choice of the convenient interface for work with the postgresy. I have faced such problem, having decided to look for alternatives to all to known phpMyAdmin/php*Admin which is considered the standard at webmasters.

Read more »


Greenplum DB

2 years, 1 month ago
We continue cycle of articles about the technologies which are used in work of the data storage (Data Warehouse, DWH) our bank. In this article I will try to tell briefly and a little superficially about Greenplum — to the DBMS based on postgreSQL, and which is kernel of our DWH. Will not be provided in article installation log, configs and other — and without it the note has turned out rather volume. Instead I will tell about the general architecture of the DBMS, ways of storage and filling of data, backups, and also I will list some problems which we had faced during operation.



It is a little about our installations:

  • the project lives at us slightly more than two years;
  • 4 circuits from 10 to 26 machines;
  • DB size about 30 Tb;
  • in DB about 10000 tables;
  • to 700 queries per second.

How it works, I ask under kat!

Read more »


Possibilities of PostgreSQL which are not present in MySQL and vice versa

2 years, 1 month ago


Many are afraid to pass with "мускуля" on "посгрес" because only vaguely understand that it will give. Some are stopped by thought that likely Postgres — is too difficult base and demands training. And also that perhaps something should lose in connection with transition. I will try to clear situation a little.

Read more »


PostgreSQL and tasks connected with it on HighLoad ++

2 years, 1 month ago


To watch development of different databases — fascinating occupation, especially — if you understand undercurrents. One of the strongest communities round the DBMS in Russia — this PostgreSQL-community. Two thematic conferences in year, the consulting company and even the company developer of modules to PostgreSQL.

The head and the ideologist of the international community, Bruce Momzhan, here comes what year to us on HighLoad ++. This year not exception, Bruce will tell about "Upcoming PostgreSQL Features" — to whom to tell about the future of this DBMS, how not to Bruce?

Why, despite such activity, it is database it is still far not so widespread, as, for example "bazulka" of MySQL. In what dirty trick? We actively discussed this subject at the PGDay conference' 15 which was organized by one of speakers of HighLoad ++ Ilya Kosmodemyansky.

For a start small research:
  1. The largest paid CMS in Russia (Bitriks, Netcat, UMI) do not support PostgreSQL;
  2. The most popular free CMS (Wordpress, Drupal, Joomla) too (either support hardly or support recently);
  3. Only every third hosting the provider offers support of PostgreSQL.

Read more »