Partitioning in PostgreSQL 10 and not only

Partitioning in PostgreSQL 10 and not only
 
Many of the achievements of the PostgreSQL 10 version have a registration in the Partitioning section. And this is true: it is obvious that in the transition from 9.6 to 1? a powerful technological leap occurred. In previous versions of the section, they built and managed them, with the means that they already had: the inheritance mechanism with all its limitations and inconveniences.
 
In version 1? we moved on to more specialized mechanisms and more familiar syntax (including for Oracle users, and this has to be taken into account). This jump in the transition from 10 to version 11 was to be supported ...
+ 0 -

What's new in PostgreSQL 11: INCLUDE-indexes

What's new in PostgreSQL 11: INCLUDE-indexes
 
 
The release of PostgreSQL 11 will not be held soon, only in October. But Fitchfreeze has already arrived, which means that we know what features were included in this release, and we can test them, gathering PostgreSQL from the branch master. Special attention deserves a feature called INCLUDE-indices . Patch Originally written Anastasia Lubennikova , and then dopylene Alexander Korotkov and Fedor Sigaev . Push it into ...
+ 0 -

Spring Boot. Background tasks and not only

Introduction


 
In this tutorial I would like to give an example of an application for sending emails to users, based on the date of their birth (for example, with congratulations) using the Scheduled annotation. I decided to bring this example, in my opinion it includes quite a few things, such as working with the database (in our case this is PostgreSQL), Spring Data JPA, a new java 8 time api, email service, creating background tasks and small business logic while remaining compact. Today the Internet is full of a huge number of tutorials that usually boil down to how to inherit from CrudRepository...[/h]
+ 0 -

What's new in DataGrip 2018.1

Hello! In this release, some improvements have appeared in minor updates. But, as we do not write about them on Habr, I'll tell in this post about everything new since the previous release.
 
 
What's new in DataGrip 2018.1
 
requested in the comments couple of releases back :)
 
 

 
 

Running the query


 

Read-only mode


 
We have the "Read-only" checkbox in the data source properties. It included this mode at the level of the jdbc-driver. But read-only is implemented in different drivers with the caveats:
 
- In SQLite It can not be switched for an already created connection.
 
- In MySQL it does not allow you to run queries that do not start with the "S" character.
 
- In Oracle, SQL Server and some other databases it does not work ...[/h][/h]
+ 0 -

How to speed up PostgreSQL 10

(The article uses examples and explanations from the book Nouveaulités de PostgreSQL 10. (c) Dalibo, the translation from the French Igor Levshin, the editor Yegor Rogov (3r3r3? original
) The examples are checked, sometimes changed for greater clarity)
 
Of course, we are already waiting for the appearance of the 11th version of PostgreSQL. But it is already clear that some fairly radical performance improvements have already appeared in version 10. Definitely, it makes sense to deal with them first.
 
The productivity of the "tens" improved immediately in several directions. In this article we ...
+ 0 -

Digest of news from the world of PostgreSQL. Issue number 4

Digest of news from the world of PostgreSQL. Issue number 4  
 
We continue to acquaint you with the most interesting news on PostgreSQL.
 
 

Releases


 
PostgreSQL 10.3 came out
 
 
In this case release closed security hole : an uncontrolled way to find objects in database schemas in pg_dump and other applications. Among the other fixes: now, logical replication will not attempt to commit changes if tables are not allowed for publication. Also updated versions of 9.x were released.
 
 
Version Postgres Pro Standard ??? came out the same day as PostgreSQL ...[/h]
+ 0 -

Why to put the most selective columns in the prefix of the composite index is not always good

tl; dr In this article, we will consider the case where the loop is moved the most selective attribute from the prefix of the composite index to the suffix.
 
And also consider what is pipeline and how with it select -see the data already sorted.
 

Why to put the most selective columns in the prefix of the composite index is not always good
 

Description of the subject area


 

There is an event log in the X system. You need to make an application to view log data from this system.
 
It is assumed that the system has its own error codes and the corresponding messages. Of these, about 10k are unique.
 
There are three types of messages:


 
  •  
  • notice ...
+ 0 -

Install Linux + server (Nginx + Apache) + PostgreSQL + PHP on VirtualBox (Ubuntu Server ??? LTS)

On the Internet, I have not found a single recipe for installing and configuring such a rather non-standard server. I decided to write my own recipe.
 
 
The working principle is as follows:
 
 
Install Linux + server (Nginx + Apache) + PostgreSQL + PHP on VirtualBox (Ubuntu Server ??? LTS)
 
Static data (files) are given by Nginx, and the dynamics is handled by Apache. VirtualBox
 
Ubuntu Server ??? LTS (LTS - Long Time Support)
 
PuTTY
 
 

Install VirtualBox


 
 
Run the VirtualBox installer.
 
 

 
Settings will ...[/h]
+ 0 -

Custom aggregate and window functions in PostgreSQL and Oracle

 
In this article, we'll see how to create custom aggregate and windowed (in Oracle terminology - analytical) functions in two systems. Despite the differences in the syntax and, in general, the approach to extensibility, the mechanism of these functions is very similar. But there are differences too.
 
 
It must be admitted that its own aggregate and window functions are quite rare. Window functions generally for some reason are traditionally classified as "advanced" SQL and are considered difficult to understand and master. Here we should deal with those functions that already exist in the DBMS!
 
 
Why, th...
+ 0 -

Configuring the bundle proftpd + {mysql /postgresql} with storing passwords in md5 + salt

For a long time to me did not give rest the fact that passwords of users at me in a DB are stored in an open kind. It was also very inconvenient to manually add /remove users and change their passwords.
 
 
As a result, I configured the bundle proftpd + mod_sql + mod_sql_passwd with the storage of passwords in the form md5 + salt, and also wrote three scripts to add, delete and change the users password.
 
 
In view of the fact that on different servers I use somewhere PostgreSQL, and somewhere MySQL, I spread the description of the settings for both DBMS.
 
 
The servers are running ALT Linux ...
+ 0 -