Transcription
[music] We have with us today on stage Alexander, who will tell us about optimizing PostgreSQL queries. Listen, I immediately have a question: why PostgreSQL, why not other databases, why not MySQL? Well, we need to analyze it with a concrete example. There will be no general phrases. Purely everything you need to know in practice. That is, essentially, some meat in a specific technology, specific tools. And why PostgreSQL? I think everyone is familiar with PostgreSQL and works with it. Raise your hands, who works with PostgreSQL. For some reason, this story is always present at Python conferences. If there's PostgreSQL, there's Python. If there's Python, there's PostgreSQL. And so on. Well, I think our talk will be long, fun, and spirited. I won't take up your time, let's get started right away. Yes, thank you. So, hello everyone. It's great that there are so many of you. Yes, we will have an island without SQL here today. In general, there will be no SQL, I'll say that right away. Well, in general, optimizing PostgreSQL queries, or everything you need to know in practice. But first, let's talk about me. I am a Staff Engineer at Cian. I've been at Cian for 5 years and have managed to work with all our core components. This includes search, product cards, and the ad submission form. In general, now I'm more of a freelance artist, moving between teams, trying not to interfere with them, and sometimes even helping. I also lead the Python guild, ensuring developers don't get bored, and improving processes. What will we talk about today? Of course, about PostgreSQL, about query optimization. In general, only what is needed in practice. First, we will understand the problem itself, why we need to optimize something, then the basics, a minimal set, the basics of storage, the basics of query execution. And, of course, approaches to optimization from the PostgreSQL side, from the architecture side. Let's begin. And we'll start with the main question you need to ask yourself before your manager comes to you and asks it. In general, do we need to optimize anything at all? That's a good question. Let's look at it from a few perspectives. Firstly, many of us live in the cloud, and most likely, if you live in the cloud, even if your database is not managed, even if your services are in Python, there are many instances, in general, the database costs an order of magnitude more. Here's an example of one of our real services. Moreover, this picture is similar across all services, this difference in cost. In general, why is this important to us as engineers? We are, well, we are engineers or want to become them. And, of course, we need to invest effort where there is the greatest value for the business, to show ourselves, so that some of this benefit accrues to us in some form. In general, we optimize PostgreSQL because it is expensive. And, of course, next is the issue of speed. Many of you, I think, are building web applications, and page load speed is very important because slower responses lead to worse page indexing. Clearly, users don't reach our site, and if we take a typical response from our site, PostgreSQL will definitely be there, it will be at the top. And again, we invest effort in what takes the most time, because that's where the maximum results will be. And, of course, the issue of change. I think many of you have thought about migrating from PostgreSQL to Cassandra or MongoDB. or somewhere else. All of these are expensive projects. They might seem cool, but they are unclear to the business. In general, their value is unclear. And sometimes it's really better to stay with PostgreSQL and optimize something, because changing the storage format is expensive and very risky. Well, let's start with the basics. In general, we won't go into any theoretical depth. Really, what we need in practice. And we'll start, of course, with storage. We have a relational DBMS, so we have tables and rows, or as they are also called, academic tuples. We won't talk about tuples, we'll talk about rows in this presentation so that we understand each other. Rows, in turn, are stored in pages. They are also called blocks of 8 KB, so they are just some files that lie on disk. Well, and pages, in fact, lie in the heap. The heap is called so complexly, but it's just some amount of memory. All of this is on disk. In general, nothing complicated yet. And immediately, what you need to know in practice, we have a limit of 8 KB. Of course, there are rows that exceed 8 KB, or exceed the specified limit. And PostgreSQL will send such rows to TOAST storage. That is, they will be stored separately. It sounds unclear for now, but this is what you need to know in practice. TOAST adds overhead to reading, writing, and cleaning. In general, if you have TOAST, your queries will become slower. If we talk about a concrete example, if we have JSON, well, I think we all like to store JSON in the database, in PostgreSQL. Most likely, it's already in TOAST, it's not fatal, you just need to consider it. And how to live with it, we'll see later. The next word, or rather, the concept of Multiversion Concurrency Control, in general, sounds maximally unclear, but in reality, it's needed simply so that our transactions live harmoniously. It's an optimistic locking engine because locking every modifiable row from reading is expensive. We want to read rows quickly, regardless of whether they are locked or not, whether someone is updating them or not. MVCC is simple. Each transaction has some identifier, and each row is assigned an identifier of which transaction this row is visible to. XMIN and XMAX, which one is not. Again, what does this mean for us in practice? In practice, it means that a copy of the row is created with each change. That is, even if we change just one column, a completely new copy appears. And this is not very cool, because these copies will live with us for some time. They are reused only after a periodic VACUUM operation, and are completely cleaned up only during VACUUM FULL. In general, it sounds not great. And what practical advice can we derive here? And in general, if we have some gigantic wide row, for example, I don't know, 100 columns, you store some ad and put a view counter next to it, in general, it's clear that it will work very poorly, because for every increment of the counter, we will create a completely new ad, a new row. In general, it's scary to imagine what will happen. And a good approach is to split wide rows by update frequency. That is, we move frequently updated parts elsewhere, and leave rarely updated parts. This way we save space. Well, another interesting side effect is that READ COMMITTED in PostgreSQL. No, perhaps this is news to you, but you can enable it, but it will have no effect, in general. But it's unclear if you would want that. I think not. In general, let's move on. The next important concept is Shared Memory Buffers. That is, we have our pages on disk. It's clear that reading from disk is expensive. And this optimization appears. To read less from disk, we store some rows in RAM. Moreover, this cache lives separately from the operating system cache. Such a clever optimization. There will be many optimizations, in fact, in this talk, and most of them are from the creators of PostgreSQL. But you need to know about them. And in general, all pages pass through this cache. And if the page is there, we won't go to disk, and this is an order of magnitude acceleration. This is cool. What conclusions can we draw? Fewer updates are good, because there are fewer row copies, fewer extra rows to read. And smaller rows are also good, because more rows will fit into our cache. Since more rows fit on a page, we will put more rows into shared buffers. And slower database growth, because smaller rows, copies will take up less space. Well, storage is good, but we also retrieve data from it somehow, so let's talk about the query plan. This is our foundation. Let's first understand what types of queries we have at all. In reality, there are short and long queries, nothing complicated, but the classification is useful. For example, a short query is fetching a user by ID, because we will read few disk pages, or dumping the configuration at application startup, because the configuration is probably not very large, and few pages. In general, this is a short query. Long queries are all aggregations, counting the total number of records, finding the maximum value. In general, by and large, very often in our applications, we want to find all long queries and turn them into short ones, especially if you have a real-time application. In general, long queries are definitely not your friend. How is a query plan built? At the input, there is some SQL, a declarative query. What does declarative mean? We don't explicitly tell PostgreSQL what to do, in what order. PostgreSQL decides everything for us, or almost everything. That is, it decides in what order to scan tables, how to join them. That is, even the order of joins that we specify, it's actually not that interesting to it. It will still do each output itself. And in general, we have some set of operations, like scanning tables, joining them, and each operation has several ways to perform it. Like sequential scan, index scan, we'll look at them later. And there are several ways to join them. And, accordingly, the order also changes. This results in a huge number of different trees. And the planner has to evaluate each of these trees. It evaluates them based on its heuristics, based on table statistics. That is, for each table, it roughly knows the distribution of values in it, what are the most frequent values, what are the histograms. In general, why is this needed? To see your query, to see your filter, and understand how much you are filtering the tables. And it likes it when you filter tables heavily, because few rows remain, they are easy to process, in general, it relies on statistics. And, of course, it evaluates each node and ultimately chooses one tree. In general, nothing complicated, as everywhere in PostgreSQL. And how to manage it? Well, you don't need to, you can answer immediately. In general, PostgreSQL makes decisions independently based on its statistics. If something goes wrong, check the accuracy of the statistics. There is the PG_STATS view, it is public. You can just go in and see all statistics for all tables, for all columns. But if you still want to experiment, there is a cool extension called PG Hint Plan. In general, using comments, you can explicitly tell PostgreSQL what it needs to do. This is, of course, only for experiments, I do not recommend using it in production. There can be unexpected side effects. And how to get it? Actually, the plan. Well, there's nothing new here, I think, for you. EXPLAIN will build a plan, ANALYZE will execute the query and show some comparison with the plan. I also advise you to add the BUFFERS keyword, because it will show you statistics on the blocks read, on the pages, that is, how much we read from disk, how much we read from shared buffers. In general, this is important. And by and large, the speed of the query depends precisely on this. That is, the less we read from disk, the better. But we'll get back to this. And how to read what we got? In general, here is some incomprehensible tree. Well, we start reading it from the inside upwards. Just some set of operations. They are combined into larger operations. And each node has its own set of parameters. For example, cost is the cost in the plan, rows is how many rows the node returned, the width of the rows in bytes. This can also be important for PostgreSQL. There are also loops. Loops are the number of repetitions of a node. That is, if, for example, you join one table with another, then for each row of the left table, for example, you look for a match on the right. And then you will have many loops. And, of course, actual time, which appears during ANALYZE, is actually the time, and it is indicated for one cycle. And what you definitely need to know is that we don't compare cost with actual time, they are completely different quantities. Cost is some internal PostgreSQL metric. In general, time is actual time. Well, let's analyze what is truly important to us, we won't go into depth. In general, these are scan types, how we scan tables. There is, of course, sequential scan. I think many of you are familiar with it and it seems like our enemy. Well. Actually, no. It just reads all pages sequentially. It's not very fast, but if you have a small table, it's your friend. Next is index scan, when we first search for the row's location in some additional data structure, that is, we immediately search for the correct page, and then we go to the heap and retrieve it. Sounds cool. So we are already accelerating. Index only scan is when we read only the index, that is, we don't go to the heap, the index already contains all the data, so the query works even faster. But, of course, there are nuances in PostgreSQL, we will talk about them a little later. And, of course, bitmap index scan is when we want to read many rows from an index or combine several indexes. PostgreSQL first builds a bitmap of all pages, and then goes through the heap and simply reads them. And it can also combine several indexes. That is, for each index, it builds a separate bitmap, then combines them using logical operations, and it will be great. That is, several indexes can be used in one query. PostgreSQL helps us here. But, of course, all this is uninteresting, because you can read the query plan conveniently. This is explain.tensor. In general, the most useful tool in the world, probably. In general, just throw your query plan there and get a beautiful tree with all the metrics, and even with advice, what to change, what to modify. For example, you have a sparse index or you are missing an index somewhere. In general, it will give you advice. The main thing is to check the box not to publish in the archive, so that your queries do not become public domain. Don't forget about this. In general, we could have finished the talk here, because there is a ready-made tool, use it, but no. The first thing we will analyze is how not to build plans manually. For this, there is the autoexplain module. You can set, for example, the minimum query duration, you can sample, you can specify to perform analyze, and some tool will spit out ready-made plans, in logs, and you will analyze them yourself later. Post factum. In general, it's cool, you can use it. And the question may arise, what is the overhead of all this? I was also curious, but I found a ready-made article where everything was measured for me. In general, the overhead is 1-2%, I think you can manage it with sampling. And decide for your load how critical this is for you. Well, well, finally, we've reached, in general, the optimization approaches. And let's begin. The first question, or rather, well, yes, the first question we'll ask ourselves is, what is the main goal? In general, I've already mentioned this. Well, to read fewer disk pages, because the fewer disk pages we read, the faster we work. This is a fact. Here's a cool table, I really like it, it compares reading from memory, from disk, with one CPU cycle. If we take the CPU as one second, then reading from disk takes 6 minutes, and even from SSD it takes 2 to 6 days. In general, it speaks for itself. Read from disk as little as possible, work faster. This is our main advice. And our first friend in this matter is, of course, indexes. I think you've heard of them. This is the first thing you remember when your query is slow. Just some additional data structure. Well, let's go over some basics in PostgreSQL. That is, in PostgreSQL, by default, a B-tree is created. Perhaps you thought it was a binary tree, but no, it's not binary, it's balanced and optimized for sequential reading. Such a tongue twister. Well, and all this is, of course, specifically designed for HDD disks. What other interesting points are there? An index on columns A, B, C will be used not only for a query on A, B, C, but also for a query on A and A and B. Such an interesting point. That is, you don't need to create many different indexes, you can create one wide one that covers several of your queries at once. And, of course, MVCC. It has caught up with us here. It will catch up with us throughout the presentation. In general, in the index, there is a pointer to each version of the row. And remember one of the previous tips: fewer updates are good, because each update is not only an update of data, but also an update of indexes. If there are many indexes, each of them needs to be updated. In general, it sounds not great. And often you might wonder why an index is not used. That is, you added it, but for some reason it's not. Well, firstly, the order of columns in the query is incorrect. That is, it's possibly an index on A and B, and in the query, the condition is on B and A. I don't know why PostgreSQL doesn't resolve this automatically, but in such a case, it will not use the index. The next case is that the index might have been created concurrently to avoid blocking the table entirely. Well, and something went wrong. The process terminated with an error somewhere in the background. Using the query in the slide, you can find out and check. Well, if nothing else helps, you can, for your session, of course, not entirely, although perhaps you'd like to, disable sequential scan and see what happens. The planner will avoid sequential scans as much as possible and then look at the results, what you got. Perhaps it's really faster for you, it's some small table, and going to the index is some overhead, in general, unnecessary reading, because the index also needs to be read from disk. Well, or you simply don't have enough statistics. That is, ANALYZE for some reason, or rather, the PostgreSQL planner makes the wrong choice. And here, firstly, run ANALYZE on your table and look at the DEFAULT_STATISTICS_TARGET setting. That is, it determines the accuracy of the statistics, that is, how large your histograms will be, how many of the most frequent values PostgreSQL will record. All of this is interesting, it all helps PostgreSQL. But, of course, by increasing accuracy, you slow down ANALYZE. That is, here again, it's a trade-off, you need to choose something for yourself. And, of course, we want our indexes to be as small as possible. And here partial indexes will help us. That is, we can exclude some records from the index that are not of interest to us. For example, if we have an ad, we can exclude deleted ads, because we don't want to search by them. Why is this important? Because, firstly, it reduces the index size, we read from it faster, and it reduces the cost of updating it. That is, when we write to the heap, we don't need to update the index, which is great. And here we have reached index-only scans. As I said, there are nuances in PostgreSQL. In general, this is our dream, to read less disk. With index-only scans, this is possible, but with a nuance. We can store some additional data in the index that is not indexed. But, of course, there's a catch. Firstly, it increases the index size, which slows down reading. And some rows may not be visible to the current transaction due to MVCC. That is, MVCC has caught up with us here too. And therefore, in many cases, we need to recheck the heap, because there are some auxiliary parameters on the page itself. Of course, we want to optimize this somehow, and the creators of PostgreSQL thought the same and came up with such an additional data structure, the Visibility Map. It simply shows for all pages whether the rows on this page are visible to all transactions. It sounds complicated, but of course, it may not be accurate. Its accuracy depends on the number of updates you have, on the number of ANALYZE runs. But if you have few updates, then most likely index-only scans will work for you. And again, remember our advice that fewer updates are good, because there will be more index-only scans. So, let's talk about partitioning now. I think many of you have heard of it. But in reality, there's nothing complicated here. We just take some huge table and break it down into smaller ones. Sounds cool. Why not do that? For all tables, it might seem. And we can partition by date or hash, and we will search in each partition separately. In general, it sounds cool, but as always, there are nuances. Reading from multiple partitions is much worse than from a single large table. And there can be an imbalance in size between partitions. That is, for example, one partition for a month might become overloaded because 10 times more orders were created. And the benefit is lost because of this. Also, you cannot move data between partitions during updates. This also needs to be considered in your data scheme. And, of course, by default, they need to be created manually. That is, it sounds awful, you can forget. And if we forget, then, of course, there is a default partition, which is a partition for values that do not fall into any of the existing ones. That's how it turns out. And this will help if you forgot to create partitions in advance, but you need to use it with caution. That is, if you use such a partition and some rows fall into it, you cannot create new partitions as long as there are rows in the default partition that are suitable for new partitions. Such a tongue twister. But I think you understood me. And how to generally reduce risks when working with partitions? Of course, determine your read pattern in advance. That is, if you partition by months, will the exact range always be known or not? Because if it's unknown, you have to scan all partitions, which, as we've already understood, is awful. And, of course, monitor partition sizes and row distribution. For this, there are some system tables. There are also, I think, some ready-made monitoring tools. And the simplest thing you can do is just check the use of the partition key in the query during review. That is, without it, there will be no error in the query, but everything will work much slower, and then it can be difficult to understand why it works so slowly. And to automate partition creation, use PG_CRON. In general, PG_CRON is for schedules, PG_Partman is a more convenient interface for creating partitions. In general, it will make your life easier. Another important point to consider when working with partitions is dynamic exclusion. It appeared, possibly in one of the recent PostgreSQL versions, but PostgreSQL has become smart enough to filter partitions dynamically at query execution time, not during planning. That is, even if you don't know the partition key, you can join cleverly, filter, and in the end, PostgreSQL will give you "never executed" in the execution plan. In general, if you see "never executed," you can rejoice. PostgreSQL helped us, we didn't have to read that partition. In general, an interesting technique, it should also be considered. Well, and a few tips on how to help the planner. So, wait a moment, let me help myself first. So, let's go. Well, in general, there will be no complex techniques, just two rules. How to help the planner, what to remember. Redundant conditions. That is, as we've already understood, PostgreSQL knows more about our tables than we do. And there are cases when we, for example, write some condition and don't add some redundant conditions, because they are redundant for us and seem unnecessary. But it's not like that with PostgreSQL. All conditions are important to it, so they can help it choose a different query plan. For example, you will filter more. Such an interesting technique. Another technique is early filtering. Here you need to look at the query tree, what you got, and monitor rows and loops, that is, monitor the case when you initially have a huge number of rows that go through all the nodes upwards and are only filtered at the end. If you see this, it's immediately bad. You will likely want to avoid this, because time is spent on transferring, processing these rows, some extra actions. And what do we do here? We just filter more and use EXISTS and ANTI JOIN. Perhaps you haven't heard of them before, but in reality, it's just the EXISTS keyword. And PostgreSQL likes such joins because they never increase the final result set. That is, the final result set either remains unchanged or decreases. And this is cool, fewer rows. Well, of course, we are at a Python conference. What should we do from the application side? We can say not to use ORMs, but we won't do that. Maybe some other time. Firstly, of course, we use connection pooling. We just prepare some connections in advance and reuse them. Why do we need to do this? We have overhead in connection creation time for each connection. That is, we
We need to spend extra time. We don't like to spend time. Also, from PostgreSQL's side, memory is allocated for each connection. And we'd rather spend memory on share buffers, on our cache, rather than on a connection. Why? We can use some ready-made implementations locally, for example, in Async PG or SQL Alchemy Pool. Well, and, of course, don't forget about global connection pools. They are also cool, for example, PgBouncer or the more advanced Odyssey. In them, for example, you can even do cool things like having multiple applications perform transactions sequentially within a single connection. That's a complex scheme. Well, meaning, reuse is maximized here. Ah, ah, well, and, of course, we control long-running transactions from the application side. Why is this important? Well, here MVC has caught up with us again. In general, vacuum cannot reuse rows that are visible to the oldest transaction. And it's quite possible that the old transaction is you. Also, these transactions hold locks on rows and tables, which is awful. And even if you are just doing some long select or reading something in a cursor, there might be such an unusual nuance that if an ALTER TABLE comes in, it cannot be executed because of the log queue mechanism due to you and blocks all subsequent selects. That's an interesting nuance. That is, selects, it would seem, are not to blame, but they have to wait for ALTER TABLE, which is waiting for you. So what to do here? Well, of course, we look. PG Stat Activity is our main friend to understand what queries are currently executing. There will be an exec_start field – this is the start time of the last transaction. That is, you can set up some monitoring. Or simply from the application side, set `statement_timeout` or `idle_in_transaction_session_timeout` to stop hung transactions. For example, within a transaction, you decided to multiply matrices in Python or went to some other services. In general, in such cases, PostgreSQL will help you, it will stop your actions for you. Ah, well, and, of course, a little bit of tuning. Where would we be without it? PostgreSQL's default settings are a bit strange, perhaps. Let's look at the most interesting cases. Well, of course, the first is `shared_buffers`, that is, the size of our cache. By default, it's 128 MB, but in the modern world, this is, of course, so-so. And even the documentation recommends increasing it to 25% of available memory immediately, because the more memory, the higher the hit rate of pages during reads. That is, all queries start to fly. Maybe your tables will even fit entirely into RAM, and then it's just a dream. The next setting is perhaps not very obvious, it's `random_page_cost`. This is the cost of randomly reading disk pages. It occurs in cases when you first read from an index, then go to disk. And for PostgreSQL, this is a random read, because you are reading non-sequentially, but somewhat randomly across the disk. And since PostgreSQL likes HDDs, it tracks such cases and slightly penalizes them. And if you do a lot of such random reads in a row, it might switch to a sequential scan at some point, that is, stop using the index and read the entire table sequentially. Of course, this was relevant in the era of HDDs, but if your database is on an SSD, set it to one and one. This won't speed up all your queries, but it might speed up some of the heaviest ones. And the most complex setting, in general, let's figure out what it is. It's `fillfactor`. It is defined for a table. And in fact, it shows how many new rows can fill a page. By default, it's 100%. And the remaining space is left only for updates. It would seem, why? Some inefficient use of memory. But in fact, remember that when updating a table, we also need to update the indexes. This is some additional overhead. In general, not very cool. And here again, some optimization from PostgreSQL's side. Heap-only tuple updates. That is, we won't go into details, just know that if there is free space on a page and under certain other conditions you can update a row without updating indexes, which is cool. Therefore, if you have highly updatable rows and not very frequent insertions, in general, this setting will help you reduce the overhead of index updates. Oops. Well, let's talk about architectural approaches now. In general, the most interesting, probably. Ah, well, counters, I think, are the main stumbling block between backend and product, because counters are, by default, long queries, and we, as we remember, long queries are our enemy. At least in web applications, we like short queries. So what to do? Well, the first question you can ask yourself and the product is: "Do we need counters at all?" The product, of course, will be against you switching to such a bubble-like view, that is, simply answering the question of whether there are unread items or not, rather than how many. Hmm, well, of course, this won't speed up the case when there are no notifications and no index. You still need to scan the entire page, or rather, the entire table. In general, a controversial option, most likely rejected, but you can try. The next iteration is: do we need exact counters? Because such a compromise for the product, for example, we can switch to a view of 10 unread messages, that is, we will still read fewer pages. Cool. For example, I have 19,000 unread emails in my work inbox. And in general, knowing that it has become one more is no longer so important. It's all the same somehow. This is regarding the product. And as for internal graphs, here we can rely on PostgreSQL statistics. It knows the approximate number of rows in your tables. You can find this out using system tables, but the accuracy, of course, depends on your case. That is, how often you run `ANALYZE`, what is your number of updates. In general, the same story again. The less you update, the more accurate this statistic will be. It's unlikely to be worth bringing into the product, of course, because it's inaccurate, but for some simple monitoring, I think it's fine. Ah, well, and here's the final iteration. In general, if accuracy is still needed, then we go for external or exact counters. That is, these are counters that will become accurate at some point in the future. We don't know when, but they will be accurate at some point. Here, of course, we use the transactional outbox pattern, that is, in one transaction, we change an object and write some event to a neighboring table. And then in a separate process, we trigger the counters, in general, we collect all events. What needs to be considered here? Well, of course, don't forget to monitor processing delays. That is, if a user sees a plus one after an hour, it's probably not very good. Maybe they'll be upset, maybe not. Ah, well, and since we're talking about counters, let's talk about denormalization in general. Such a word. In universities, we were always taught that we should normalize. Well. But in life, of course, everything is different. So what is denormalization? It's duplicating data in several tables or even storage. That is, we reduce the time for joining tables, aggregate as much as possible, and simplify tables for fast searching. We move something to other, more efficient storage. And in general, we can divide it into local denormalization. This is when, for example, we take a large row and move individual fields to separate tables and search only by them. Why is this cool? Because our rows become smaller. More rows will fit into `shared_buffers`. In general, we will read much faster. For example, we can move the description of an ad to a separate table. I think it will turn out well if we want to search by it using text search in PostgreSQL. Well, and of course, this will help you if read queries are much more frequent than write queries, because writing also fills up `shared_buffers`. In general, if you read a lot, this is a method, I think, for you. Ah, well, and of course, global denormalization. We can move not only to separate tables, but also directly to separate storage, for example, Elasticsearch for indexing and full-text search. Yes, here we again have eventual consistency, that is, everything will be consistent, but at some point in the future, maybe this is your option, maybe not. But what needs to be remembered is that all changes are still made through PostgreSQL. That is, we make changes in transactions, throw events through the transactional outbox, and that's it. We live happily, because in this way, we will definitely not lose any events. And this can help you with migrating entirely to some other storage. In general, you can sleep soundly. I will separately highlight read replicas here. Well, because it's like some kind of denormalization case. Replicas are already available in many databases. Why are they idle for us? We pay for them, and they stand idle. In general, it's not very good. We can use them for reading. But, of course, this is only for the case with one master and two replicas, because if you overload your only replica, it won't be very good. So, let's monitor this and use it cautiously. And what are the specifics here? Firstly, we have asynchronous replication. This is when there is a delay in data freshness on the replica, but problems with the replica do not affect the master, as in synchronous replication. And if you choose any of these methods and read from a replica, you might encounter some incomprehensible errors at some point, `canceling statement`, in general, some conflicts, although it would seem we are just selecting, why are we being canceled? In general, we haven't done anything wrong. In fact, this happens because vacuum wants to clean up some rows that are needed on the replica, which are being read there. And PostgreSQL on the replica will wait for some time, of course. And this time is set using `max_standby_streaming_delay`, but after that, of course, you will be canceled, and that's it, the query is canceled. If you don't like this, you can either increase the timeout or enable `hot_standby_feedback` mode. Then the replica will start reporting to the master about which queries are executing on it, and vacuum will become less aggressive. That is, it will selectively clean up rows. Well. Of course, there is a nuance here, that vacuum becomes less effective because of this, but the number of read cancellations becomes significantly less. In general, as always, there is a trade-off here, and you need to consider what suits your case. What conclusions can we draw here? Don't be afraid of denormalization. If you do it through transactional outbox, everything will be okay. And, of course, use the capabilities of read replicas, because why let them idle? You've paid money. In general, use them. What general conclusions can we draw from the entire report? In general, minimize disk reads by all possible means. Increase `shared_buffers`, add indexes, and, of course, as a last resort, use partitioning and denormalization, because these are extreme measures, risky. Something might go wrong. In general, postpone this to the very end. Ah, and slow down database growth, because the longer the database grows, the fewer unnecessary rows we have. And, of course, move large parts to separate tables so that copies are not duplicated. In general, these are the conclusions. If you ask me what else to read, of course, "Designing Data-Intensive Applications" by Martin Kleppmann is the most popular book on backend, I think, right now. There is also a book with a fish, "Distributed Data," by Alex Petrov. In general, also a good book. It's like Kleppmann, only more low-level, more difficult. And, of course, "PostgreSQL Query Optimization." The presentation was inspired by this book. I think you'll find something interesting there too. And this is Shopper, I took a photo of it with Kleppmann. In general, if anyone wants to buy it, please, they didn't pay me for advertising. Well. Thank you for your attention. I'll be happy to answer your questions. [applause] It's good that I spoke little at the beginning. We've left most of the time for you. The report turned out to be really big and substantial. Ah, so. Well, what do we have? Let's raise hands higher so I can see everyone at a glance. So. 3, 4, 5. That's fine. We have about 11 minutes. Maybe even a little less. I'll go from here. Please forgive me, it's just more convenient. You remember that you need to choose the best question. I'll be writing them down. Last time I didn't write them down and forgot everything. Can I? Yes, you can. Ah, well, first, a small comment. I think it's not just `shared_buffers` that needs attention, but generally optimizing settings, because there are various things. One of the main ones is the number of workers that can process and the number of workers that can process one query in parallel. My question is the following: how often in practice do you encounter situations where the query plan differs slightly from what is actually happening on the production database? For example, there, on a test database, one population, or we use a query with hardcoded parameters in `PREPARE`, which are usually generated. For it, it was different. Or what other problems can there be with plans being slightly different from what we expect? We don't have such a problem at Cian. We simply don't have a test database. We test on production. That's my favorite. If something breaks, the user will write about it. But it exists, but it's empty. In general, well, I think you often need to look at your indexes, possibly at some table statistics, because statistics are still collected individually. So, looking at that, well, everything comes down to statistics, I think. My question was a bit different. What you're doing, like `EXPLAIN`, I think you're already going into holy wars. Come on, come on, look, we'll have a discussion zone, and you'll discuss it there perfectly, both about testing and about the lack of testing on users. Ah, yes, and I'll say right away, don't forget to rate the report, of course, definitely. Hello, hello, thank you for the great report. My question is about how to accept and deploy such changes to production. That is, when we roll out backends, it's clear, you start the second version, with a load balancer, you split traffic, look, and you've done the acceptance. But with databases, it seems much more complicated to do such a thing. What approaches, life hacks, experience, and, I don't know, funny stories from life are there? Well, I think, well, a read replica is a good way, that is, it costs, well, of course, maybe some other statistics, but you can run queries on it, or set up a test environment with some artificial data, perhaps. Well, there are nuances, because artificial data is not real data. It has its own distribution of values. In general, PostgreSQL statistics will be different. So, there's probably no simple way. You either have to go to a replica, or, by the way, a good way is to somehow limit the query time. That is, if you've rewritten a query and it's very bad, I don't remember the setting name, you can set it for a session. And, in general, PostgreSQL will automatically cancel your queries. That is, you test, if something goes wrong, the query is canceled, everything is okay. Let's move on. Did everyone get seated? Let's have the next question. Thank you very much for the report. My question is about `EXPLAIN` and plans. So, suppose we are good developers, maybe not, but suppose we really collect plans on the production database, we really look at them and read them. But, as a rule, we still look at the number of rows that we read, where we read from, what scans. And in practice, we usually look at the cost and say: "Well, some cost," because it's some incomprehensible number that PostgreSQL calculated, based on what it always fluctuates. The question is, maybe we're reading it wrong, and in your experience, has the cost really helped you, can you see and analyze something specific from it? I, I, no, but I think it's interesting, I, by the way, didn't mention this. There's this `rows`. And, I think, PostgreSQL provides it for both the plan and the actual. So, you should orient yourself by that. That is, it depends on that. For example, according to the plan, it said there would be zero rows, but in reality, 10,000. And if that's the case, then something is definitely wrong. So, some statistics are missing. In general, I probably wouldn't look at the cost, but at `rows`, yes, you can see the difference with the plan. Thank you for the question. So, can I ask a question? Great report. Thank you. The question is: have you encountered such a problem? Well, we know that the PostgreSQL planner plans everything cool, but have you encountered this problem where it chooses the join strategy incorrectly, well, we know there are three, that it chooses it incorrectly and only does it in one percent of cases? And how can such a case be tracked at all? Because, well, we have such a pain, like, you have a kilometer-long database, 99% of queries work fast, but at some point it says: "No, I'm not choosing merge, I'm choosing hash, everything." Or, for example, and you say: "Whoa, your query takes 5 minutes." And you say: "A user runs up: 'What's happening?'" And you say: "What to do?" So, that's the question. Well, I think this is exactly the story with this `random_page_cost`, because at certain moments, when there are very many values, PostgreSQL can indeed collapse into sequential scans, into some suboptimal things. But I haven't encountered problems with joins. Interesting, why could that be? In general, I think it's all statistics. Wait, look, there were four questions. Which one did you remember best? I'm writing them down, and then I'll choose. Cunning. Ah, thank you for the report. I have not a question, but just a comment. On slide thirty-six, it was about updating partitions, that it's not supported. I think in PostgreSQL twelve, it was fixed. Yes, I thought not. Well, okay, no, if you mean during an update. During an update. Well, then it's magic, you can do cool things. Well. But double-check before using it. We still have some time for questions. If you have hands, raise them higher. If you have questions, you can also raise your hands. So. Aha. Excellent. Ah, hello. Thank you for the report. Look, a question about `fillfactor`. We've mainly talked about query execution time, right? Also, as far as I understand, if a table is frequently updated, these dead tuples remain in some often large quantities that you don't expect. And `fillfactor`, as I understand it, should also help with this, right? So, the first question is, is this really a good way to deal with it through `fillfactor`? And secondly, the choice of `fillfactor`, can it be chosen deterministically, or is it more through experiments? Well, I think, well, 10% is definitely not worth it. 80 is probably worth it. I think it's purely empirical based on the load. And regarding dead tuples, I don't think it helps at all. It might even hinder if they move away. Well, maybe it doesn't hinder. In general, it doesn't affect them in any way, if I remember correctly. So, they will still appear. They can also move to other pages. So, you need to look at that. Thank you. I remind you again that I cannot see your cameras scanning the QR code to rate the report. Yes, please rate it, preferably with a comment. Thank you very much for the report. I have a somewhat strange question, perhaps. We have PostgreSQL, and you talked about partitions. How does the experience of working with partitions in PostgreSQL generally work when working with other databases? I think the experience is roughly the same, but still, partitions are cool, but you need to approach them consciously, that is, you still need a partitioning key, you also need to consider it. Well, perhaps somewhere you can move between partitions through updates. Well, maybe in PostgreSQL, as we found out. I think the general principles are roughly the same, both in PostgreSQL and not in PostgreSQL with partitions. Thank you. Yes, thank you for the report. My question is this. Could you tell me if you have any alerts for suboptimal queries? And if so, what do you use for it? We have alerts for suboptimal indexes, I think. They arrive somehow, that is, if an index is not used. But I don't know what's behind it. We used to have PGCrawler. PGCrawler could scan. Well, in general, it processed queries somehow, but then PGCrawler was removed. In general, there's nothing like that now. But, probably, we would like it. Well, and again, there are tools to automatically sample long queries, not just plans, but in general, I can't remember the name, but something like that definitely exists. I think you can find it. So, they add one thing, remove another, and still test on users. Ah, I have another question. You've mentioned a lot of cool cases that help optimize. Are there any things that are rumored to work great, but don't actually work? Like, any top-two things that everyone advises to do, and you're like: "No, this is complete nonsense, it never helps." Well, that's an interesting question. I probably can't even recall. Well, indexes can be used unconsciously when you don't need them, and then they work to your detriment. I've very often seen cases where an index weighs more than the database, but that, of course, I think everyone has encountered this. If you have five such indexes, you should probably think about combining them somehow. I think this is the main antipattern: adding indexes for every case and not thinking about how they are used. Although, by the way, PostgreSQL has statistics on index usage. You can also extract everything and look, that is, when there were traversals through the index, and when not. You should look at that. This is probably the main antipattern. How do you work with? I have a question, thank you for the report. How do you work with slow and fast storage? For example, for warm tables, for storing historical data? In PostgreSQL, there isn't such a thing. Ah. And with separate storage, I don't know, but in PostgreSQL, there's definitely nothing like that. But in theory, perhaps, I think the same partitions could be used. That is, you move old data to one partition, and the last 5 years to another partition. Something like that, for example, a change table or something like that. Ah, that is, changes to data specifically from a specific table. I didn't understand again. Well, for example, a change occurs in a table, and you write this change somewhere in PostgreSQL, somehow it's implemented. You use it for this. Ah, I understand. The idea, like writing to PostgreSQL, and then writing somewhere else, right? For example. Yes. Ah. No, we don't have such an automated system now. We just send some events to analysts, that is, what they need. We don't export everything, because it's scary. There's personal data, it's probably better not to share it. Well, did you write down many questions? Well, yes. And now let's choose. So, in the sense, choose, eat. You choose, eat. I'm like this, I'm not the sufferer in this case. So, well, I liked the question about testing. It really makes you think. Who asked the question about testing? That's it. Yes. Yes. Come out on stage. Here's such a book. Unfortunately, it's not about PostgreSQL. No, well, we're at a Python conference. Well, how to talk about Python. Well, let's have applause. Thank you for the question. And let's. Okay. Well, a little bit, a little bit of applause for the speaker. Let's add some too. So be it.