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...

Development of highly loaded WebSocket service

How to create a web service that will interact with users in real time, while maintaining several hundred thousand connections at the same time?
Hello everyone, my name is Andrey Klyuyev, I'm a developer. Recently I came across such a problem - to create an interactive service where the user can get quick bonuses for their actions. The matter was complicated by the fact that the project had rather high demands on the load, and the deadlines were extremely low.
In this article, I will describe how I chose the solution for implementing a websocket server for the complex requirements of the ...

How did we do the olympiad on SQL (ending)

How did we do the olympiad on SQL (ending) I continue the story about as we did the olympiad on SQL . This is a continuation of the previous article, in which everything simply did not fit.
A brief summary of the previous series: two absentee rounds of the Olympiad took place in December 2016 and March 201? respectively, where the candidates for the victory were rigorously selected both with theory and with the practice of applying SQL to Oracle databases. Further about the third round - the full-time finale of the Olympics in Sochi in early June 2017
Olympiad. on the official ...

Database and DBMS research using T-SQL


Greetings again, dear reader Habra!
When your realized ideas, experience, as well as all the information that does not give rest, you make out in the publication, sooner or later a logical point comes to the entire previously written information flow. This article will be different from all previously published by me my looseness and freer style of text presentation, and also she will finish the presentation of all my accumulated experience on MS SQL Server.
This article is in addition to article Let's explore the databases using T-SQL , and also briefly tells ...[/h]

Compatibility issues for Tibero and Oracle. Part 1. Conditional compilation PL /SQL

Compatibility issues for Tibero and Oracle. Part 1. Conditional compilation PL /SQL
Conditional compilation PL /SQL allows you to selectively compile the source code segments, depending on the condition, usually associated with the value of the packet constant. This is often used to ensure application compatibility with different versions of the DBMS.
In December 201? such an opportunity appeared in Tibero, which made it possible to provide another
The compatibility of applications between Tibero and Oracle.
Oracle2Tibero_Tools . The utility is a set of PL /SQL packages, two of which have already been discussed above. The easiest way to use them is if you install ...

How we did the Olympiad in SQL

How we did the Olympiad in SQL At the very beginning of autumn of 201? the management tasked me with preparing the technical part of the SQL Olympiad. Having discussed the situation with colleagues, including former ones, I was pinned (cliche?) In an article where in the declarative style on SQL the problem of building the shortest exit from the labyrinth was solved. Gathering a part of the request into one heap and running it on a real database, I whispered "magic! " and realized that there was an olympiad.
I think that the typical reader of Khabra at the Olympics has been at least once, but rather in the role of a participant, not an organizer...

Three aspects of optimization (DB and PO)


Quite often, users, developers, administrators, and so on of MS SQL Server DBMS encounter problems with DB or DBMS performance in general.
In this article, we will give general recommendations on how to optimize both the database and the entire database. Also, the main recommendations for the interaction of the .NET application and MS SQL Server will be given. Examples of solutions will be given for most of the recommendations below.
In this article, optimization of hardware resources, the OS itself and the use of various embedded features for the DBMS and OS as a whole will ...[/h]