11 questions for PostgreSQL database administrators, part 2
Most recently, we published the first part of an interview with leading experts from the companies RT LABS, Git in Sky, Postgres Professional, Avito and EnterpriseDB. If now you decide whether to link your life with the profession of DBA, then you will have to use the second part of the tips from the PG Day'16 speakers. And if there are still questions, you can ask the speakers of this year from July 5 to July 7 at PG Day'17 Russia .
Alexander Chistyakov : Read the query plans. Think faster than people who exploit what you wrote. Exercise, sleep well at night.
Anton Bushmelev : You need to know your subject area . If you work with ORM, as it usually happens with us: I’m “naked * in-hand”, it doesn’t matter to me how it will work with the base! Do not be so abstracted. If you do not know something, you can come up, ask. Spent five minutes of time will greatly save time in the future. Do not be shy, I also do not know a lot of things. If there are experts with whom you can consult, you need to communicate .
')
Dmitry Vasilyev : Do not use ORM . In fact, hiding the simplicity and communication with the base through the ORM, you further postpone the technical debt, which you still have to repay. You still need to know SQL, and how the databases work inside . These are the basics, which are not so many, but they need to be studied.
Mikhail Tyurin : Everything is very serious here, no jokes, application developers need to understand that working with data is one of the main tasks that they have to solve . It is necessary to connect the DBA in the early stages.
We are now all “infected” with modern management models (agile and other things). Both DBA and devops must be connected to the final decision-making process as early as possible , because it can be very expensive to err in the data.
Of course, good DBA can be anything, but sometimes it is also expensive, simply because DBA can not withstand such problems for a long time and give up. Then the developer will be completely alone.
Bruce Momzhan : I can answer regarding Postgres: you need to do research and find out about its many-sided possibilities . Many, coming from other DBMS, use it out of habit: "I always did it like this, I will do as I used to." There is nothing wrong with that, but Postgres is much more than a data warehouse. It is not necessary to use Postgres as you used the database before, study modern non-standard functions related to data consistency, data modeling, they will make your application easier and cleaner.
Alexander Chistyakov : DBA is a person who is on both sides of the bridge - development and operation . A person who is looking to ensure that something unforeseen does not occur. He must concentrate in himself the knowledge of how the database works, and pass them on to others.
Anton Bushmelev : At the start, when designing . But it often happens like this: here’s the system, we had to put it yesterday, let's quickly lift it up and start operating. And then everything is sad. That is, at the stage of starting somehow, but it works. But with growth, “bottlenecks” emerge, although this could have been avoided at the design stage. More time needs to be given to this.
Dmitry Vasiliev : As I have already said, the database is the heart and circulatory system, therefore the discussion and architectural structure should occur with the participation of the DBA . This is one of the key development points, not just how devops solve application problems.
Mikhail Tyurin : One of the central places .
Bruce Momzhan : DBAs are not just people who stand between the application and the OS, they are concerned with performance, data storage reliability, network response delays . That is much more tied to the hardware and its performance. Database queries can be both very simple and very complex - it is difficult to predict. DBA are involved in the choice of hardware, such as storage and see the big picture, because performance requirements may vary.
Alexander Chistyakov : This is a subjective thing. At the Postgres conference, I think PostgreSQL is an advanced one, and if there was a MySQL conference here - sorry guys ...
Anton Bushmelev : Community and training . Postgraduate training suffers. In Oracle, everything is in parts. There are separate courses for those who are engaged in back-ups, those who are engaged in productivity, and, in fact, “jobstart”.
Community and training - without this in any way. The base may be very advanced, but no one works with it, and no one can help if any problems arise.
Dmitry Vasilyev : Usability, availability of documentation, availability of code . The first is monitoring , the second is the availability of information in the database . The third is an opportunity to influence the processes taking place , incl. opportunity to see something in the code, correct or redo it.
Mikhail Tyurin : Advancement is a rather subjective concept. If you are good at doing your work, then your technology is advanced. I manage to do my job well on Postgres, so I consider it advanced.
But there are also objective aspects, such as the openness of the community . In an open community, those principles, concepts and ideas that can be effective are laid, cultivated and reproduced. Although Stonebraker swears now and rubs out everything he once did, it was all well done. Stonebraker is a man who has just received a Turing Award, he once laid the architectural foundations including Postgres.
Bruce Momzhan : Four factors: opportunities, price, durability and accessibility in training . With the fourth, Postgres often has problems, the question arises whether employees have all the necessary skills, whether they are sufficiently qualified. This is a complex database with many possibilities , but, as I said, many do not want to learn new things.
Alexander Chistyakov : Read the classics, look around you, go to conferences.
Anton Bushmelev : Monitor the monitoring . Monitoring is our everything. Hang the main triggers on the monitoring, then to sleep peacefully. Instead of doing health checks every half hour, you can submit all this to monitoring and self-development yourself.
Dmitry Vasiliev : Study, do not be afraid to try, be sure to experiment . I am not talking about experiments on “production”, it turns into a change in salary. Look for something new and unknown!
Mikhail Tyurin : Do not be afraid to read, to understand difficult problems. You need to understand what a transaction is , why it exists at all, why it is needed.
Take the main problems and start digging from either side. And, on the sly, the whole picture will open slightly: there are still unsolved difficulties, hidden skeletons in the closet. All global companies are actually experiencing all the same problems that small startups are experiencing.
They all lose transactions, they are all difficult to recover, they all spend a lot of money on crutches to make it all work. There is still room for optimization here, for bringing to mind the approaches to working with data at different levels of the stack. There is something to introduce.
And in general, DBAs are appreciated . From the experience of interaction with large companies, I know that a specialist who understands how and where the data lies, how and where to correctly redirect them, will always be in demand.
Bruce Momzhan : Be open to everything new . When I was engaged in consulting, there were people who specialized in the most popular software at that time. They knew everything about MS DOS and Windows, it was the most current software, and they traveled on it. After 2-3 years, priorities have changed, other software has become relevant, and after him another. Many people look at this change of priorities, and think: “Everything comes and goes, I’ll just do what I’m doing”. But if you take a closer look, you can understand which technologies will remain viable for a long time , and which ones will be just cute, passing ideas.
Alexander Chistyakov : The emergence of NoSQL databases .
Anton Bushmelev : Nobody has yet repeated the Oracle cluster , this is a specific thing, but a good one. Especially in version 12. The breakthrough technologies that are currently used are in-memory . This is the absence of indexes, fewer locks, respectively. The absence of indexes is already a big plus.
Dmitry Vasilyev : Probably the biggest change that has occurred in databases over the past ten years is the column-oriented base .
Mikhail Tyurin : Databases are what the notion of a transaction brings to the programmer’s tools: what we have saved can be obtained by us in the future. This is one of the key features of a transaction. And as soon as we start talking about it, we start talking about the recovery problem. Because if an accident happens, and they happen often, the nodes in the distributed system fail. And just the ability to handle these accidents is what modern databases decide. You paid for something and do not want your money to be gone - the database helps to solve this problem very effectively.
Bruce Momzhan : If we talk about hardware, the most important change was the emergence of SSD . Until then, everything was stored on magnetic media that were rotating, and access to this data was very slow. Using SSD significantly improved performance .
Another crucial idea was the proposal to store unstructured data . Relational databases love structure. But lately there have been many requests from people who do not need this structure. In my opinion, Postgres was best able to adapt relational databases for using unstructured data.
Alexander Chistyakov : In open source, in principle, there are no flaws , this is the only living workflow that can exist. Even before the word open source appeared, people exchanged source codes, it was always in the scientific community. Atomic bomb passed to us. This was done not because of money, but because of beliefs.
Regarding PostgreSQL, the flaws are quite surmountable. This is about the lack of a proper number of workers . The only way to deal with this is to increase visibility, to convey your thoughts to people who want to participate. I think open source is ultimately the only way to go. All that is a closed source, just does not keep up with open source.
Anton Bushmelev : A huge number of crutches . From the very beginning, these are only crutches; there is no single solution — everyone is sawing under them. A bunch of wrappers, all this is difficult to maintain. With PostgreSQL, the problem for me is the backup of large databases . It is impossible to determine what exactly the session hangs on, there is no debug. Debag is, but it is on the entire base. Sense to read dvukhgigovuyu “footwoman” logs, when I need to figure out something for a particular session, I do not see.
Dmitry Vasilyev : When you make a product for yourself, you can include things in it that you don’t need to prove to anyone. When you use an open source solution, you cannot support such a large project as Postgres yourself, but you need changes in it. This disadvantage can be a plus in the sense that the community has to prove the need for features in an open source product.
Mikhail Tyurin : If we compare such communities by the number of participants, it may seem that the post-community community is less represented.
Does Postgres have flaws as a product? No I do not think so. Perhaps, it is possible to make work on integration with other communities. Postgres has its own niche, many companies use it effectively.
Bruce Momzhan : The main drawback is that it is difficult to find an experienced specialist . Most simply adapts Postgres to the company. Every three months we organize training seminars for people who come to Postgres from other DBMS. We can’t hope that there will be a group of people who have mastered Postgres in some miraculous way. Therefore, should teach them to grow quickly and consistently.
Another thing that scares people in open source software is a choice . In commercial software, there is usually one specific way to solve a particular problem. In Postgres, there are a lot of applications and additions, so there can be many solutions to a particular task.
Alexander Chistyakov : I have two personal top - there is what I love, and there is what I do. I love Postgres , I love MySQL less. Even less I love Firebird, because I have never worked with it, but I love the people who make it. And the rest I do not know. In terms of business, I do a lot of MySQL . New projects that at least have something to do with the processing of money, I did on PostgreSQL, but nothing more. There is nothing more than PostgreSQL and MySQL.
Anton Bushmelev : Oracle , since I have been working with it for 7 years. Postgres - because of great potential. MS SQL - because in Russia it is 1C. 1C works very well with MS SQL, although they try to prove that Postgres is somehow moving. But just that week I saw an installation of 7 terabytes, and on MS SQL it all works.
Dmitry Vasiliev : Oracle, PostgreSQL, MS SQL, DB2.
Mikhail Tyurin : Oracle, Postgres , Microsoft and IBM solutions - these are all developed systems. They have their pros and cons, application history. MySQL , of course.
Of the modern systems, I would single out Redis, MongoDB . Of the specialized solutions - the system Vertica, Cassandra . There are other similar things, such as Kafka . There are specialized solutions for distributed storage, such as Zoo Keeper .
Bruce Momzhan : Naturally, Postgres will be in the first place for me, because I work with him. It is an excellent general purpose DBMS, it is also suitable for working with non-sequential workloads and it becomes better with each version in many ways.
Oracle continues to lead with very large volumes. Postgres at the moment can cover 95-98% of the workload, but this 2% remains.
Plus Microsoft SQL is that it is integrated with the Windows environment. DB2, in my opinion, underestimate. This is a very good system, but due to the tight connection with IBM hardware, it seems very inflexible, and this repels many.
About NoSQL. A few years ago, it was considered “noble” not to use SQL and not to have the need for a database administrator. But about a year and a half ago, everything changed, because, despite the ease of deploying NoSQL, there was a shortage of many functions. Now they often use a mixture of Postgres and NoSQL and achieve amazing results. So NoSQL is not the last test for relational databases, but they have existed for 30 years, they are developing, adapting, and I think this will continue in the future.
MySQL was left behind about five years ago , when Postgres started doing the same thing as MySQL, only better. But it took time for the industry to realize this. Many people continue to use MySQL out of habit, but new companies no longer choose it. This is normal. Sometime, Postgres will go into oblivion, but I hope there will be no more then.
Three tips for application developers to stop tormenting and start enjoying databases?
Alexander Chistyakov : Read the query plans. Think faster than people who exploit what you wrote. Exercise, sleep well at night.
Anton Bushmelev : You need to know your subject area . If you work with ORM, as it usually happens with us: I’m “naked * in-hand”, it doesn’t matter to me how it will work with the base! Do not be so abstracted. If you do not know something, you can come up, ask. Spent five minutes of time will greatly save time in the future. Do not be shy, I also do not know a lot of things. If there are experts with whom you can consult, you need to communicate .
')
Dmitry Vasilyev : Do not use ORM . In fact, hiding the simplicity and communication with the base through the ORM, you further postpone the technical debt, which you still have to repay. You still need to know SQL, and how the databases work inside . These are the basics, which are not so many, but they need to be studied.
Mikhail Tyurin : Everything is very serious here, no jokes, application developers need to understand that working with data is one of the main tasks that they have to solve . It is necessary to connect the DBA in the early stages.
We are now all “infected” with modern management models (agile and other things). Both DBA and devops must be connected to the final decision-making process as early as possible , because it can be very expensive to err in the data.
Of course, good DBA can be anything, but sometimes it is also expensive, simply because DBA can not withstand such problems for a long time and give up. Then the developer will be completely alone.
Bruce Momzhan : I can answer regarding Postgres: you need to do research and find out about its many-sided possibilities . Many, coming from other DBMS, use it out of habit: "I always did it like this, I will do as I used to." There is nothing wrong with that, but Postgres is much more than a data warehouse. It is not necessary to use Postgres as you used the database before, study modern non-standard functions related to data consistency, data modeling, they will make your application easier and cleaner.
Place DBA in the development and operation of IT-systems?
Alexander Chistyakov : DBA is a person who is on both sides of the bridge - development and operation . A person who is looking to ensure that something unforeseen does not occur. He must concentrate in himself the knowledge of how the database works, and pass them on to others.
Anton Bushmelev : At the start, when designing . But it often happens like this: here’s the system, we had to put it yesterday, let's quickly lift it up and start operating. And then everything is sad. That is, at the stage of starting somehow, but it works. But with growth, “bottlenecks” emerge, although this could have been avoided at the design stage. More time needs to be given to this.
Dmitry Vasiliev : As I have already said, the database is the heart and circulatory system, therefore the discussion and architectural structure should occur with the participation of the DBA . This is one of the key development points, not just how devops solve application problems.
Mikhail Tyurin : One of the central places .
Bruce Momzhan : DBAs are not just people who stand between the application and the OS, they are concerned with performance, data storage reliability, network response delays . That is much more tied to the hardware and its performance. Database queries can be both very simple and very complex - it is difficult to predict. DBA are involved in the choice of hardware, such as storage and see the big picture, because performance requirements may vary.
What determines the advancement of some DBMS relative to others?
Alexander Chistyakov : This is a subjective thing. At the Postgres conference, I think PostgreSQL is an advanced one, and if there was a MySQL conference here - sorry guys ...
Anton Bushmelev : Community and training . Postgraduate training suffers. In Oracle, everything is in parts. There are separate courses for those who are engaged in back-ups, those who are engaged in productivity, and, in fact, “jobstart”.
Community and training - without this in any way. The base may be very advanced, but no one works with it, and no one can help if any problems arise.
Dmitry Vasilyev : Usability, availability of documentation, availability of code . The first is monitoring , the second is the availability of information in the database . The third is an opportunity to influence the processes taking place , incl. opportunity to see something in the code, correct or redo it.
Mikhail Tyurin : Advancement is a rather subjective concept. If you are good at doing your work, then your technology is advanced. I manage to do my job well on Postgres, so I consider it advanced.
But there are also objective aspects, such as the openness of the community . In an open community, those principles, concepts and ideas that can be effective are laid, cultivated and reproduced. Although Stonebraker swears now and rubs out everything he once did, it was all well done. Stonebraker is a man who has just received a Turing Award, he once laid the architectural foundations including Postgres.
Bruce Momzhan : Four factors: opportunities, price, durability and accessibility in training . With the fourth, Postgres often has problems, the question arises whether employees have all the necessary skills, whether they are sufficiently qualified. This is a complex database with many possibilities , but, as I said, many do not want to learn new things.
What advice would you give to young professionals who want to deal with databases?
Alexander Chistyakov : Read the classics, look around you, go to conferences.
Anton Bushmelev : Monitor the monitoring . Monitoring is our everything. Hang the main triggers on the monitoring, then to sleep peacefully. Instead of doing health checks every half hour, you can submit all this to monitoring and self-development yourself.
Dmitry Vasiliev : Study, do not be afraid to try, be sure to experiment . I am not talking about experiments on “production”, it turns into a change in salary. Look for something new and unknown!
Mikhail Tyurin : Do not be afraid to read, to understand difficult problems. You need to understand what a transaction is , why it exists at all, why it is needed.
Take the main problems and start digging from either side. And, on the sly, the whole picture will open slightly: there are still unsolved difficulties, hidden skeletons in the closet. All global companies are actually experiencing all the same problems that small startups are experiencing.
They all lose transactions, they are all difficult to recover, they all spend a lot of money on crutches to make it all work. There is still room for optimization here, for bringing to mind the approaches to working with data at different levels of the stack. There is something to introduce.
And in general, DBAs are appreciated . From the experience of interaction with large companies, I know that a specialist who understands how and where the data lies, how and where to correctly redirect them, will always be in demand.
Bruce Momzhan : Be open to everything new . When I was engaged in consulting, there were people who specialized in the most popular software at that time. They knew everything about MS DOS and Windows, it was the most current software, and they traveled on it. After 2-3 years, priorities have changed, other software has become relevant, and after him another. Many people look at this change of priorities, and think: “Everything comes and goes, I’ll just do what I’m doing”. But if you take a closer look, you can understand which technologies will remain viable for a long time , and which ones will be just cute, passing ideas.
What technological achievement / invention in the database world do you consider the most breakthrough and why?
Alexander Chistyakov : The emergence of NoSQL databases .
Anton Bushmelev : Nobody has yet repeated the Oracle cluster , this is a specific thing, but a good one. Especially in version 12. The breakthrough technologies that are currently used are in-memory . This is the absence of indexes, fewer locks, respectively. The absence of indexes is already a big plus.
Dmitry Vasilyev : Probably the biggest change that has occurred in databases over the past ten years is the column-oriented base .
Mikhail Tyurin : Databases are what the notion of a transaction brings to the programmer’s tools: what we have saved can be obtained by us in the future. This is one of the key features of a transaction. And as soon as we start talking about it, we start talking about the recovery problem. Because if an accident happens, and they happen often, the nodes in the distributed system fail. And just the ability to handle these accidents is what modern databases decide. You paid for something and do not want your money to be gone - the database helps to solve this problem very effectively.
Bruce Momzhan : If we talk about hardware, the most important change was the emergence of SSD . Until then, everything was stored on magnetic media that were rotating, and access to this data was very slow. Using SSD significantly improved performance .
Another crucial idea was the proposal to store unstructured data . Relational databases love structure. But lately there have been many requests from people who do not need this structure. In my opinion, Postgres was best able to adapt relational databases for using unstructured data.
What are the disadvantages of PostgreSQL and Open Source in general?
Alexander Chistyakov : In open source, in principle, there are no flaws , this is the only living workflow that can exist. Even before the word open source appeared, people exchanged source codes, it was always in the scientific community. Atomic bomb passed to us. This was done not because of money, but because of beliefs.
Regarding PostgreSQL, the flaws are quite surmountable. This is about the lack of a proper number of workers . The only way to deal with this is to increase visibility, to convey your thoughts to people who want to participate. I think open source is ultimately the only way to go. All that is a closed source, just does not keep up with open source.
Anton Bushmelev : A huge number of crutches . From the very beginning, these are only crutches; there is no single solution — everyone is sawing under them. A bunch of wrappers, all this is difficult to maintain. With PostgreSQL, the problem for me is the backup of large databases . It is impossible to determine what exactly the session hangs on, there is no debug. Debag is, but it is on the entire base. Sense to read dvukhgigovuyu “footwoman” logs, when I need to figure out something for a particular session, I do not see.
Dmitry Vasilyev : When you make a product for yourself, you can include things in it that you don’t need to prove to anyone. When you use an open source solution, you cannot support such a large project as Postgres yourself, but you need changes in it. This disadvantage can be a plus in the sense that the community has to prove the need for features in an open source product.
Mikhail Tyurin : If we compare such communities by the number of participants, it may seem that the post-community community is less represented.
Does Postgres have flaws as a product? No I do not think so. Perhaps, it is possible to make work on integration with other communities. Postgres has its own niche, many companies use it effectively.
Bruce Momzhan : The main drawback is that it is difficult to find an experienced specialist . Most simply adapts Postgres to the company. Every three months we organize training seminars for people who come to Postgres from other DBMS. We can’t hope that there will be a group of people who have mastered Postgres in some miraculous way. Therefore, should teach them to grow quickly and consistently.
Another thing that scares people in open source software is a choice . In commercial software, there is usually one specific way to solve a particular problem. In Postgres, there are a lot of applications and additions, so there can be many solutions to a particular task.
What would you highlight the top of modern DBMS and why is it like that?
Alexander Chistyakov : I have two personal top - there is what I love, and there is what I do. I love Postgres , I love MySQL less. Even less I love Firebird, because I have never worked with it, but I love the people who make it. And the rest I do not know. In terms of business, I do a lot of MySQL . New projects that at least have something to do with the processing of money, I did on PostgreSQL, but nothing more. There is nothing more than PostgreSQL and MySQL.
Anton Bushmelev : Oracle , since I have been working with it for 7 years. Postgres - because of great potential. MS SQL - because in Russia it is 1C. 1C works very well with MS SQL, although they try to prove that Postgres is somehow moving. But just that week I saw an installation of 7 terabytes, and on MS SQL it all works.
Dmitry Vasiliev : Oracle, PostgreSQL, MS SQL, DB2.
Mikhail Tyurin : Oracle, Postgres , Microsoft and IBM solutions - these are all developed systems. They have their pros and cons, application history. MySQL , of course.
Of the modern systems, I would single out Redis, MongoDB . Of the specialized solutions - the system Vertica, Cassandra . There are other similar things, such as Kafka . There are specialized solutions for distributed storage, such as Zoo Keeper .
Bruce Momzhan : Naturally, Postgres will be in the first place for me, because I work with him. It is an excellent general purpose DBMS, it is also suitable for working with non-sequential workloads and it becomes better with each version in many ways.
Oracle continues to lead with very large volumes. Postgres at the moment can cover 95-98% of the workload, but this 2% remains.
Plus Microsoft SQL is that it is integrated with the Windows environment. DB2, in my opinion, underestimate. This is a very good system, but due to the tight connection with IBM hardware, it seems very inflexible, and this repels many.
About NoSQL. A few years ago, it was considered “noble” not to use SQL and not to have the need for a database administrator. But about a year and a half ago, everything changed, because, despite the ease of deploying NoSQL, there was a shortage of many functions. Now they often use a mixture of Postgres and NoSQL and achieve amazing results. So NoSQL is not the last test for relational databases, but they have existed for 30 years, they are developing, adapting, and I think this will continue in the future.
MySQL was left behind about five years ago , when Postgres started doing the same thing as MySQL, only better. But it took time for the industry to realize this. Many people continue to use MySQL out of habit, but new companies no longer choose it. This is normal. Sometime, Postgres will go into oblivion, but I hope there will be no more then.
Source: https://habr.com/ru/post/331148/
All Articles