TOP-5 database management systems

A database management system (abbreviated as DBMS) is software for creating and working with databases.

The main task of the DBMS is to manage information that is located both in external and in RAM. The DBMS supports database languages, and is also responsible for copying and restoring information after any failures.

MySQL

MySQL is the most widely used database management system. It is used by many large companies such as Facebook, Wikipedia, Twitter, LinkedIn, Alibaba and others. MySQL is a relational DBMS that is free software: it is distributed under the terms of the GNU Public License. This is a fairly fast and flexible DBMS, which is great for working with small and medium-sized projects.

Considering the advantages of MySQL, one can first of all single out a wide variety of tables, from the more well-known MyISAM and InnoDB, to non-standard HEAP and MERGE. In this regard, MySQL is constantly evolving and increasing the number of supported tables. Compared to other systems, MySQL is very fast. Another advantage would be the possibility of simultaneous access by an unlimited number of users. And the amount of data can reach up to 50 million. This option is well suited if you are considering working with a database in tabular form.

MySQL

MySQL

The not too extensive functionality of this DBMS, compared to others, makes it easier to work with it.

You can work with MySQL in two modes: text and graphics. The transition from one mode to another is possible thanks to the phpMyAdmin application. To work in this application, you do not need to know SQL commands. You can manage your database through a regular browser without additional programs.  

MySQL is perfect for those who need a fast, simple, and understandable DBMS for working with projects.

PostgreSQL

This type of DBMS is object-relational. PostgreSQL is freely available. Its work is based on the SQL language, however, unlike MySQL, PostgreSQL supports the SQL-2011 standard. The big advantage of this database management system is the unlimited number of entries in the table and the unlimited size of the database.

When choosing a DBMS, note that PostgreSQL provides transactional and replication reliability, inheritance, and easy extensibility. PostgreSQL supports various extensions and variants of programming languages ​​such as PL/Perl, PL/Python, and PL/Java, including C-compatible modules.

Compared to MySQL, the PostgreSQL DBMS boasts a thorough description of all the functions and features in the official documentation.

At the moment, PostgreSQL is considered one of the most advanced database management systems. In terms of its functionality, it is very extensive and is on a par with such a powerful DBMS as Oracle.

SQLite

If you are looking for a compact and embeddable DBMS, then pay attention to the relational SQLite.

SQLite is fast, which is achieved by storing all data in one file and the minimum amount of memory occupied. The structure of SQLite differs significantly from more powerful DBMSs such as MySQL and PostgreSQL. Because the SQLite engine and interface are in the same library, querying and parsing information is very fast. Other DBMS (MySQL, PostgreSQL, Oracle, etc.) use the "client-server" paradigm when the interaction takes place through a network protocol. An excellent option for working with information databases.

However, despite the advantages of this DBMS, it has its drawbacks: the absence of a user system and the impossibility of increasing productivity.

Oracle

This data management system is of the object-relational type. Its name goes back to the company that created this system - Oracle. Just like SQL, it uses a procedural extension called PL/SQL, as well as the Java language.

Oracle has established itself as a reliable and stable system that provides high protection for the database of organizations and guarantees security. It is preferred by large corporations, which primarily pay attention to the ability to quickly restore data after failures, a streamlined backup procedure, scalability, and other valuable features.

Oracle

But the price for using this DBMS is much higher than for others. Therefore, its users are predominantly large firms rather than small or medium-sized enterprises. Despite all its advantages and being ahead of competitors in terms of functionality and characteristics, the price of access and further maintenance of Oracle lowers it in the 2016 rating in Russia to 6th place.

MongoDB

This type of DBMS is primarily aimed at storing hierarchical data structures. It is also called document-oriented. In other words, MongoDB is an open-source document store without the use of tables or schemas.

Several factors contribute to the high speed of operation. First, is the use of an identifier, which allows you to perform quick operations on the object. Secondly, since MongoDB belongs to the class of non-relational databases, it means that it uses an object query language. Therefore, applications that look the same at first glance will differ in performance depending on the DBMS used.

Unlike more complex DBMS, MongoDB has many limitations in functionality. Therefore, this option should be selected when storing databases of simple projects.

Make clear requirements for choosing a database management system. Push away from what you need first. Try several database models and decide what is most convenient and profitable for you.

 Relational databases

This type of database is represented by interconnected tables, in which each row represents a record. The name "relational" comes from the relationship of objects in the database.

Let's say you have a student table and a course grade table that includes the course number, grade, and student ID. In this case, each row with grades will be correlated with the student's data.

Relational databases

Relational databases

SQL-like languages ​​are used to query information from relational databases. They have built-in support for the union operation. By themselves, relational databases can index columns to make things easier. An organized database building system allows you to define schemas before data is entered, which speeds up the database and access to information.

NoSQL databases

There is a non-relational database. Unlike relational ones, they do not have a common structured schema for records. Most NoSQL databases contain JSON records. Different records may contain different fields.

Such a group of databases is called NoSQL (Not only SQL - not only SQL). Although most NoSQL databases support SQL, this is not the preferred use case. Consider 4 types of NoSQL databases.

Documentary

The initial unit in this system is a document, each of which is defined as a kind of record - JSON. Depending on the document, its schema, number of fields, and so on may vary. Document databases allow some fields to be indexed to speed up query parsing.

With the help of such databases, it is very easy to process a large amount of information using parallel computing, since all elements in them have no structural or logical connection between them.

columnar

The initial unit of such databases is a table column. Storing new information uses a new column every time, so it's very easy to get the information you need from such a database. The columnar data saving option allows better compression of the content due to its uniformity.

If you need to access several blocks of information at once during the use of the database, then it is best to use columnar databases. Since when searching, they read a column, and not a whole line, as in other types of databases. This speeds up and facilitates the search process.

Key-value

Such databases operate solely on the basis of a key. In them, you can only request a specific key and then get its value.

NoSQL databases

NoSQL databases

In such databases, it is impossible to access several blocks of information at once and obtain data from several rows/columns. For example, you won't be able to select all records with the mention of the city of Moscow at once. Key-value databases have the advantage of having a Time-to-Live (TTL) field that can be set on a per-entry basis and state when they need to be removed from the program database.

These are very fast databases. This is achieved, firstly, due to unique identifiers, and secondly, by storing data in RAM, the path to which is much shorter than to cloud storage.

You need to define unique keys, good identifiers based on data you already know. They are often more expensive than other types of databases because they use RAM.

These databases are primarily used for caching, as they are fast and do not require complex queries. The time to live field helps you get rid of unnecessary files without any help. Such databases can be used for any data that requires fast queries and conforms to the key-value format.

Graph

First of all, such databases were developed to work with graph material, for example, social networks or knowledge networks. They contain nodes that represent database objects and edges that show the relationship between them.

 An example of using a database in web development

The use of a database in the field of web programming is necessary only in some cases. Ask questions:

What information will you store and why?

In what form and how do you plan to store this information?

How and in what way can the contents of the database be accessed?

Free online intensive

Your IT Journey Starts Here

More

Consider a website with a diary. Here it is necessary to provide at least one type of form to fill out with several fields: date, mood, description of the day, main thoughts, and so on.

The user will write down his thoughts every day in an online diary, which means he should be able to return to the first pages in a day or a year. In this case, developers must consider how and where all this data will be stored so that they can be accessed at any time.

It can be a brief overview of the day, or something more complex and inventive.

Unlike their paper counterparts, electronic diaries can have many features. For example, a simple analysis: you can see which day was the most fun, the saddest, which day had the most entries, and so on.

Big Data Processing: Basic Methods

In order not only to store data and have access to them, but also to analyze them, process and make certain calculations, databases were created.

Information storage system in databases

The structure of the database is represented by three levels from largest to smallest:

database;

table;

record.

Database

A database is a high-level concept that means the collection of a collection of data stored for the same purpose.

For most modern sites, separate databases are created, inside which all information will be stored. For our example of a personal online diary, we will also need a specific database.

Information storage system in databases

Information storage system in databases

table

A table is part of a database. This is one of its components. One database can store a huge number of tables.

If you imagine that a large closet is a database, then everything that lies inside, for example, a bunch of boxes, is tables.

Tables are designed to contain one type of information, such as a list of cities, site users, or a library catalog.

It can be presented as a regular Excel file, or as a simple set of rows and columns.

What is a website database.

What is a website database?

What is it for, how to manage it, and what does the site load speed have to do with it? And there are so-called revisions that add their own features when working. Let's look at all this from the outside, so that you can then skillfully handle the database of your site. At the same time, we will find out how long it takes for the request to go to the base and bring the information found back to the site.

Brief introductory paragraph

Any information on the site needs to be stored somewhere. This is an obvious fact. But the storage locations may be different. The first option is right inside the HTML or PHP file. This method is common. This is when you open a page in the admin panel to edit the information there, and inside the page is empty. At all. But when viewing a page on the site, there is text, pictures, and other data.

As an IT volunteer, I probably had about a dozen tasks when it was necessary to change information in this way. The thing is that in this case, the text and links to images are added directly to the PHP file of the site's theme. To change the page, you need to go to the hosting in the theme folder and edit the desired file.

The second option for storing data is more convenient and familiar. This is when you open a page in the admin panel, see all the data there and calmly change them. Refresh the page and you're done. With this scheme, the data is updated dynamically and is already taken from the database. Here we'll talk about her.

What is a database

Technically, this is a .sql file (for a WordPress site). Inside it has tables that have rows. And the data is already written in the lines. It is from here that this data is taken for display on the pages of the site. The main advantage of such a data storage scheme is that all information is in one file. This means it can be easily saved.

In addition to page information, the database contains a lot of service information. All in all, an important file. You can view the list of databases in your hosting account in the "Databases" section.

List of databases hosted by Timeweb

This is a list of bases. You can go inside each and see what is being done there using the full access link - circled in red in the screenshot above. phpMyAdmin is, in turn, a web application for managing databases. And the information inside it will be a little more unusual. Therefore, you can go there and look, but change anything there - only knowing exactly what you are doing. Well, or having a backup copy of the database in stock.

And here is the site loading speed

The connection here is the most direct. The smaller the database, the faster it will find information for further display on the site. And vice versa. In addition, in addition to the content of the pages, other service information is stored in the database.

And this is, first of all, the data of all plugins of the site. If you have a security plugin that records all user activity (failed login attempts, active sessions), then where does it store all this data? It's all there, in the database. Only in a separate table.

The database stores all comments on the site, data of all registered users, all links, and site settings. In short, everything. Without a database, your site simply will not open. Instead, a blank screen is displayed with the phrase "Error establishing a connection to the database." Therefore, the more content and plugins on the site, the larger the size of the database. This means that over time, the speed of the site may drop.

What are post and page revisions

In English, this is called revision. In this context, it can be translated into Russian as a copy (or edition) of the page. Every time you change a page on your site and save it, WordPress creates a copy of the page with your changes. If after 5 minutes you remember that you forgot to put a comma in the text and change and save the page again, another copy will be created.

On the one hand, this scheme is good. After all, you can, if necessary, roll back to the previous edition of the page. But, on the other hand, a lot of such copies can be created. And all of them are stored, as you understand, all in the same database. And all this is not the best way to affect the performance of the site.

Therefore, such copies need to be deleted from time to time. This can be done using the Optimize Database after Deleting Revisions plugin.

Screenshot of plugin page in WordPress directory

In addition to removing revisions, the plugin is good because it shows the size of each table in the database and its total size. And if, after analyzing the database, you see that there are too many rows in a table, then you need to look carefully at what is stored in this table and how it can be optimized.

I have the following plugin settings:

Screenshot of database optimization plugin settings

The first two checkboxes are responsible for deleting revisions from posts and pages. The plugin does its job, and nothing more is needed from it. This optimization often frees up a lot of space and makes the database lighter.

Speaking of plugins, the Teplitsa website has an article about plugins for various tasks for a WordPress site. Look, read.

In general, think of a database as a filing cabinet. Each box is filled with information on its section. If there is not enough space in the box, then you need to either clean it up or start a new box nearby. A lot of data - a lot of boxes. There is such a system in libraries where book cards are stored - on which shelf which book is located. This is the most illustrative example of how a database works.

What is the result

Yes, this entire database is a little more complicated than editing pages in the site's admin panel. But you can still figure it out. I highly recommend at least checking the size of your site's database. You can do this on hosting or through a plugin, whichever is more convenient for you. If you see a lot of rows in some tables there, it means that there is a reason to understand the reasons and clean up there. Do it yourself or create a task on an it-volunteer. I do not remember such tasks there, it will be interesting.