how we made a hub for DBMS maintenance
Working in a DBMS involves many diverse tasks: creating new products, scheduled updates, working with incidents. As the company digitalizes, the number of such tasks grows, and the queue of requests fills up. More and more time is spent not on decisions, but on preparation – data collection, analytics and other “windmills”. Graphical database management tools can help solve this problem.
My name is Ivan Pushkar, and together with my team I am developing one of these tools – Platform V Kintsugi. In this article I will tell you how the first platforms and orchestrators for working with DBMSs appeared, what is now available for managing PostgreSQL, and how we decided to create our own tool that would become the Ring of Omnipotence for many Sber databases.
Graphical database management tools: where it all began
Database management tools began to develop in the 1970s. The first systems worked through the command line and, despite their efficiency, were quite inconvenient for ordinary users, as they required knowledge of SQL.
1980s: dBase
With the development of computers and software, database management systems have moved towards visualization. One of the first graphical database management tools was dBase III, released in 1984. It allowed you to create and edit databases using a graphical interface without knowledge of SQL.
Although dBase's capabilities were limited and the program was not suitable for working with large databases, its simplicity and accessibility made the tool very popular. The graphical interface allowed users to easily create and edit tables, as well as perform various operations with data: sort, filter, etc.
Over time, dBase evolved: new features appeared, for example, reports and support for multi-user work. The most popular versions were dBase III and dBase IV. But due to the fact that the developer did not port them to Microsoft Windows for a long time, the program had strong competitors, such as FoxBase (FoxPro). DBase soon ceased development, the company was bought out by Borland and then lost in a series of acquisitions.
1990s: FoxPro (FoxBase)
Another graphical database management tool created by Fox Software in 1984. In 1992, the company was bought by Microsoft, which changed its name to FoxPro. The program was one of the first tools to offer a visual approach to creating and modifying databases.
The functionality of FoxBase was identical to dBase II, but, according to the developer, it worked faster. After the merger with Microsoft, the product began to actively develop and eventually turned into a powerful tool that could work with large and complex databases. FoxBase had advanced functionality: it supported multithreading, allowed you to work with different types of data and create complex reports. The disadvantages included difficulties with operation and high cost.
Despite its shortcomings, FoxPro remains an important milestone in the history of graphical database management tools. Its influence can be seen, for example, in Microsoft Access, which uses many of FoxPro's ideas and technologies. In addition, this tool closed the DBMS niche for Macintosh for some time, since MS Access was not supplied for this OS.
After the 2000s: Microsoft Access and others
A real breakthrough in the field of graphical database management tools occurred in 1992 with the release of Microsoft Access, the first program that allowed you to create databases through a graphical interface, without writing code. MS Access also provided a set of tools for data analysis and became very popular.
In the 2000s, graphical tools for DBMSs became even more powerful and convenient. They acquired specialization: now products were created for specific use cases. New tools have been added: for creating forms, generating reports, working with data in real time, maintenance and much more (more details broken down by category can be found Here).
Today, there are many graphical tools on the market for various DBMSs, such as MS SQL Server, Oracle Database, MySQL, etc. Each of them has its own advantages and disadvantages, so the choice of tool depends on the specific needs of the user and the type of DBMS he is using . For example, there are several established tools for Oracle. Among them, Oracle Enterprise Manager Cloud Control (EMCC) is a solution for managing cloud services and resources developed by Oracle. It provides tools to automate, monitor, secure, optimize, and manage cloud environments across the enterprise. With Oracle EMCC, you can control the deployment, upgrade, and scaling of your applications, as well as monitor their performance and analyze metrics. In conditions of complex infrastructure and a large number of loaded databases, the tool has practically no competitors. It comes to the point that engineers who have not caught the “console” era perform all actions through it, practically without going to the database console. Of course, Oracle also provides an IDE for writing code, for example, pl/sql developer, which is the de facto standard for developers in the CIS. Separately, it is worth mentioning the excellent Toad for Oracle tool, which, however, is traditionally more common in Western companies.
Beyond the scope of the article there remains a huge amount of less specific software for operating, diagnosing and monitoring DBMSs: Grafana, Zabbix, Prometheus, various software for working with logs, which, although they do not specialize in specific DBMSs (and often in DBMSs in general), but perform excellent their functions. And I want to focus on graphical tools for the most common DBMS in the Russian Federation PostgreSQL and, in particular, for the target DBMS Sber Platform V Pangolin.
Review of modern graphical tools for working with PostgreSQL DBMS
During the development of PostgreSQL, a huge number of tools for managing this DBMS have appeared, both from the community itself and from various software vendors. Listing all the products would take more than one article, so here I will give a list of the most popular and interesting solutions in the Russian Federation.
pgAdmin 4
A free and open source tool for managing PostgreSQL databases. Allows you to create tables, indexes, queries and other database elements, as well as visualize data and generate reports. It appeared in 1998 and has since become one of the most popular tools for managing PostgreSQL databases. PgAdmin has a simple and intuitive interface that makes it easy to use even for beginners. It can be deployed in a container or on the user’s computer as a server application.
There is a commercial version of pgAdmin developed by EnterpriseDB – Postgres Enterprise Manager. A solution with various interesting modifications, including:
Advanced diagnostics.
Assistant for working with logs.
Query profiling.
Ability to manage configuration, etc.
dBeaver
Free and open source database management software. A powerful tool that supports many different databases, including MySQL, PostgreSQL, SQLite and others. DBeaver is actively developing, releases with updates and improvements are regularly released.
There is a commercial version of the tool called dBeaver Pro with an expanded list of supported DBMSs, increased security and various interesting features such as performance diagnostics, an AI assistant for creating SQL code, etc.
Relatively recently, the dBeaver developer company began developing CloudBeaver, an online platform for developers and database administrators that allows you to work with several DBMSs in one interface. In CloudBeaver, you can manage various databases – MySQL, PostgreSQL, SQLite and others – from any device and from anywhere in the world, run SQL queries, create and edit tables, view database structures and much more. The platform also offers cloud storage and provides a high degree of security and reliability.
Tantor Labs
A platform that provides a single panel for managing all Tantor or PostgreSQL DBMS installations. The developer states that “through deep integration with the product stack, Tantor provides management, monitoring and automation support for PostgreSQL-based databases.”
The platform is a client-server application for servicing a DBMS. Main features:
multifunctional control console;
automatic adaptive configuration of the PostgreSQL cluster;
system overview;
warnings and monitoring;
automatic health checks;
scheme audit;
performing maintenance tasks via a graphical interface;
query profiling;
administration of several servers in one place;
easy installation within the client's perimeter.
More details: https://tantorlabs.ru/products/platform
PPEM
Integrated administrative control panel PostgreSQL Enterprise DBMS. Main features:
a single console with a simple monitoring and management interface;
the ability to perform basic administrative actions from a browser window;
centralization of access to all instances and databases;
navigation through a single menu taking into account the hierarchy of objects;
PPEM user access control role model;
calling the familiar psql utility directly from the console, which combines the simplicity of the graphical interface and the flexibility of the command line.
More details: https://postgrespro.ru/products/PPEM
ArenaData Cluster Manager
A general-purpose hybrid landscape orchestrator. Installation, configuration and updating of clusters in ADCM are performed by clicking a button in the graphical interface or by request in the API. In this case, all OS settings, services, networks and disk mounting occur automatically. As a result, users receive a monitoring stack that is ready for integration with corporate systems. ADCM is delivered in the form of containers and is an infrastructure service for maintaining a DBMS fleet.
ADCM main functions:
Automatic deployment of services and applications in a cluster.
Monitoring the status of the cluster and services.
Managing access and user rights.
Integration with other Arenadata products and third-party solutions.
Flexible configuration of policies and rules for working with the cluster.
Support for various technologies and standards (for example, Kubernetes, OpenShift, Apache Hadoop, Apache Spark, etc.
Platform V Kintsugi – our solution for centralized management of DBMS infrastructure
Platform V Kintsugi is a tool for solving a wide range of problems of operating and maintaining relational DBMSs and related infrastructure. The goal of the product is to fully automate work with the company's relational databases. Initially, Kintsugi was created to manage Platform V Pangolin, Sberbank's target DBMS. Today it is a single center for managing and interacting with the company’s relational data landscape, the point where all information about the DBMS and infrastructure flows. From this point, you can solve any problem facing the DBA, regardless of whether it is a production instance or current development servers.
Key product features:
centralized single window;
monitoring and analytics of database activity;
access to data via SQL interface;
listening to all events in the product using third-party tools;
easy horizontal scaling (from 1 to more than 30,000 instances, from 1 to more than 1000 simultaneous users of various access levels, without stopping the service or slowing down the work – and similarly in the opposite direction).
For whom and why are we developing Platform V Kintsugi
Initially, all work in the infrastructure and on the DBMS can be divided into two large blocks: run and change.
Change – tasks to create new products and improve the activities of existing ones. Run is the current operational activity, which for a DBMS is mainly divided into two areas:
Current planned work to improve, optimize the operation of the DBMS, planned updates or transitions to a new version of the database, assistance to development teams.
A variety of urgent tasks, from releasing hot functionality to working on incidents.
No matter how professional and powerful the support team is, there are always more incoming requests than there are people, and the queue of requests is replenished faster than a solution is issued. And, as usual, there are not enough specialists who know both application code and database code equally well.
Here is a typical example of DBMS maintenance during another incident:
Users have detected degradation of the service (at this moment we mentally start the timer).
After half an hour or an hour, the problem reached the team responsible for the service (an hour had passed since the incident).
Let's assume that the team is experienced and within half an hour finds a problem at the DBMS level (already an hour and a half).
Employees create a request to the DBA. It’s incredibly cool if the team has its own DBAs and they have already brought the initial analysis, but this does not always happen. Let's hope that this is not happening during rush hour, and after 15 minutes the task is up and running (an hour and forty-five minutes have passed).
For about half an hour, the specialist tries to understand what happened, especially if he sees this database for the first time (two hours and fifteen minutes from the beginning of the incident).
Now is the analytics stage, and it’s very cool if the problem is caused by an update or is clearly visible in the view of the DBMS itself and is solved by shooting toxic sessions. And if it “broke on its own,” then it may take several hours to find the problem (and then some four hours passed from the beginning of the incident until it was fixed).
The bottom line is that it can take days to resolve problems with services caused by the DBMS. But we shouldn’t forget about the transfer of tasks between department employees with different expertise and experience. The result is that instead of solving the problem, half of the working day is spent on searches and analytics.
We needed to shorten this stage as much as possible: so that when a user accesses the product, all the analytics for the database and primary recommendations are ready, subtle areas are highlighted, and support and development teams see one picture and make a decision as quickly as possible.
Main Features of Kintsugi
A general dashboard for instances (plans to develop it into a central hub for managing connected databases).
A general panel of a specific DBMS, where the user can quickly obtain the current state of the database.
Various dashboards.
Information about database performance, an analogue of the well-known Oracle ASH with flexible filtering and search capabilities.
An interface for executing database queries with an introspection interface, executing an explain, as well as basic capabilities for formatting and commenting code.
Previously, none of the existing tools for managing PostgreSQL and our DBMS made it possible to ensure that the maintenance process was not interrupted for a minute, and all work took place in one window.
Platform V Kintsugi can integrate with many services of a large customer: various authorization models, auditing and security and account storage services, logging and monitoring services. Our practice shows that after implementation the tool becomes a central hub for database maintenance and simplifies maintenance, diagnostics and maintenance of databases.
