Skip to content

Development

SQL Joins Visualization

SQL Joins Visualization

Interactive tool for sql joins visualization

Every pure database developer could confirm that SQL language can be compared if not to the mother tongue but at least to any other foreign language you know. I.e. it's not a programming language. It's a more verbal, human-like tongue.

Jokes aside, this is just an expression of respect to the simple but very powerful language that allows you to "speak" to the different databases. Being so widely used it's assumed by default today that every developer who ever wrote any piece of backend code must know SQL language.

Containerized Postgres version upgrade

Postgres upgrade in container

How to upgrade postgres version which runs in docker container

Introduction

Main idea of this post is to demonstrate postgres major version upgrade process for the case when postgres runs within docker container.

Demonstrated approach uses pg_dumpall utility. In any case it does not pretend to be the "best" approach. There are other options availabe such as pg_upgrade or setting up a replication server. Moreover, for big databases pg_dumpall might be non optimal or even not feasible solution.

But as said at the beginning, idea is to demonstrate possible upgrade process for containerized postgres installation.

MSSQL Graph DB

MSSQL GraphDB

Table is nothing, relation does matter

Disclaimer

Post language: Ukrainian

Трохи теорії

Компанія Microsoft зробила ще один крок для об’єднання різних технологій і методологій роботи з даними під одним дахом. Додавання обробки JSON дало можливість працювати з даними в стилі NOSQL Document DB. Добавання роботи з графовою моделлю даних дасть можливість працювати в стилі NOSQL Graph DB. Звичайно, що під капотом залишився той самий движок реляційних баз даних, на який навішали новий функціонал.

SQLite PK implementation

SQLite PK

SQLite table's primary key explained

SQLite documentation maze

Being a long term fan of SQLite database I have always been a little bit disoriented by its documentation structure. For me it seems enough cluttered and not easy to follow.

Apart of documentation structure there is another thing which is out of line to what we expect from Primary Key constraint definition in other RDBMS. If you ever were wondering what is the difference between INT PRIMARY KEY and INTEGER PRIMARY KEY and why AUTOINCREMENT is not needed for default primary key generation in SQLite then this post might be interested for you. It makes an attempt to describe these things in hopefully more easier format.

Note: I don't say official documentation is bad. It describes all these things. I just want to filter out Primary Key related info and provide it in more consistent manner accompained by some examples.

Oracle, Postgres and AWS

Oracle 2 Postgres migration

Oracle 2 Postgres migration into AWS cloud

As a contractor of Epam company I'm currently working on Oracle 2 Postgres project migration into AWS cloud. Being on tech team lead position I've been asked to present our work on official Epam's webinar as a speaker. I'm thankful to the Epam for the given opportunity to share our experience with a wide auditory.

Parallel jobs in Postgres

Parallel jobs

Solution to implement parallel jobs in Postgres

Problem

Common approach for running time consuming or maintenance tasks in databases is using job processes. Two classic extensions that allow to launch scheduled jobs in Postgres are pgAgent and pg_cron. Problem is that if you have database hosted in a cloud they might be unavailable. This happens for Postgres RDS in AWS cloud. One of the unofficial recommendation from AWS team on support forum was to launch EC2 instance and trigger via crontab on that server psql which will execute some script or call an API on database side.

Such approach requires from you to keep (and pay for) separate EC2 instance. It has to be continiously up and running. Another advise was even more complex setup with Lambda functions.

Persistent temp tables in Postgres

Persistent temp tables

Solution to implement persistent temp tables in Postgres

Problem

One of the problems that has to be resolved during Oracle 2 Postgres code migration is handling of temporary tables. Biggest difference is that in Oracle temporary tables are persistent schema level objects. You define the table structure once and it exists until you will explicitly drop it. The only difference comparing to ordinary table is that data in temporary table are stored either during current transaction only or until end of current session.

In contrast in Postgres temp tables are pure temporary objects and got automatically dropped after current session end. They also support data lifetime duration on the transaction or session level. But no temp table is available upon start of a new session.

In PL/SQL code you might have any SELECT or DML statement issued against temp table. They will work because it will be present as a schema object.

In migrated plpgsql code such temp tables references will fail with relation "table_name" does not exist error. We have to somehow create the table before executing any statement against it.

Oracle 2 Postgres cheatsheet

Oracle 2 Postgres cheatsheet

Comparative "How To" between Oracle and Postgres

Following text describes Oracle as it was before its pluggable era which started from 12th major version. While majority (if not all) of these examples will work as described there might be new features that will make some cases from not possible to already exists in Oracle. One of such examples is added OFFSET clause in SELECT which was absent in latest 11.2 version.

Postgres version described is mainly latest 11th range version which is 11.7.

In general, provided examples are not version specific and should work on both latest versions of Oracle and Postgres.