WEBVTT 00:00.000 --> 00:15.000 So we're about to start. It's almost 11 o'clock. Okay, 11 o'clock now. So it's my pleasure 00:15.000 --> 00:20.840 to introduce Karen from Karen Jeecks, from Crenci-Data, talking about the practical side 00:20.840 --> 00:25.760 of the former tuning. Our very first presentation this morning was more the history and 00:25.760 --> 00:31.440 the theory, but Karen is focusing on the hands-on stuff like the software stuff as I myself 00:31.440 --> 00:34.760 as a DBA need to know. So I was Karen, thank you very much. 00:34.760 --> 00:36.760 Thanks Dave. 00:36.760 --> 00:46.280 Hi, hello everybody. So yes, as Dave says, I'm going to be talking today about optimizing 00:46.280 --> 00:54.960 your database for analytics. So just to see your production database is probably optimized 00:54.960 --> 01:01.960 for your day-to-day OLTP application activity. I'll wait for that to do it. 01:25.960 --> 01:31.960 I'm not fighting that. 01:36.960 --> 01:43.960 Okay, I think we're all right. Okay, so your database is probably optimized for your day-to-day OLTP application 01:43.960 --> 01:49.960 activity. But what if you then need to run analytics queries against your application data? 01:49.960 --> 01:56.960 So how can you do that without compromising the performance of your application? 01:56.960 --> 02:01.960 This is me. I wear several hats today. I'm talking to you in my senior solutions architect 02:01.960 --> 02:08.960 at Crenci-Data hat. But I'm also on the PostgresQL, your board PGEU, and I'm leading 02:08.960 --> 02:13.960 the PGEU diversity task force. 02:13.960 --> 02:19.960 Okay, so I'm going to do a quick recap of different types of database activity. 02:19.960 --> 02:24.960 A lot of you will already know this. So you know, feel free to just have a little 02:24.960 --> 02:29.960 snooze until we get to the rest. But just make sure everyone's on the same page. 02:29.960 --> 02:33.960 So you've probably got at least one database. 02:33.960 --> 02:38.960 So at least one application that's got a database back end and you've got an application 02:38.960 --> 02:43.960 that that's being constantly queried inserted into and updated. 02:43.960 --> 02:50.960 So it might be something like a traditional payroll app. It might be an online store. 02:50.960 --> 02:53.960 It could be all sorts of different applications. 02:53.960 --> 02:59.960 And that type of workload is what we usually refer to as OLTP online transaction 02:59.960 --> 03:05.960 processing, or sometimes we'll call it operational database activity. 03:05.960 --> 03:10.960 And that's characterized by medical current users, short statements, 03:10.960 --> 03:16.960 and often multiple statements per transaction. 03:16.960 --> 03:24.960 Now most applications will gradually accumulate data through that OLTP activity. 03:24.960 --> 03:27.960 And at some point the business wants to glean insights from, 03:27.960 --> 03:30.960 read, make money out of that data. 03:30.960 --> 03:33.960 And for that you'll need to run analytics queries. 03:34.960 --> 03:39.960 And the analytics query activity can take many different forms and it's got different names. 03:39.960 --> 03:45.960 So you've got OLAP online analytical processing, reporting, decision support, 03:45.960 --> 03:48.960 or business intelligence. 03:48.960 --> 03:53.960 So it's basically any activity that's designed to answer real world business questions. 03:53.960 --> 03:58.960 So it could be something like what was the exact impact of this last marketing campaign that we ran 03:58.960 --> 04:04.960 or what would be the impact on our CO2 emissions if we perform this activity versus this activity. 04:04.960 --> 04:09.960 And OLAP workloads typically will involve complex queries. 04:09.960 --> 04:11.960 They'll work on large data sets. 04:11.960 --> 04:14.960 They'll be joining multiple tables. 04:14.960 --> 04:23.960 And therefore they're probably using high amounts of resource CPU memory and IO. 04:23.960 --> 04:28.960 Now once upon a time you had the OLTP databases, 04:28.960 --> 04:31.960 and then you had the analytics databases. 04:31.960 --> 04:33.960 And the two were very, very much separate. 04:33.960 --> 04:40.960 So data would somehow be thrown over the fence from one or more OLTP databases into an analytics database, 04:40.960 --> 04:45.960 a data warehouse data lake, et cetera. 04:45.960 --> 04:47.960 And that does sometimes still happen, 04:47.960 --> 04:51.960 and there are very good reasons for that still happening in certain circumstances. 04:51.960 --> 04:55.960 But often the business wants to get that data in real time. 04:55.960 --> 05:02.960 So it wants to get the data where it is without waiting for some overnight or weekly batch process. 05:02.960 --> 05:07.960 And that means they want to get that business intelligence directly from the OLTP system. 05:07.960 --> 05:13.960 So where you've got both OLTP and analytics activity going on in the same database, 05:13.960 --> 05:17.960 you'll often hear it described as a hybrid workload. 05:18.960 --> 05:21.960 And it's not a fancy new idea. 05:21.960 --> 05:27.960 Gardener came up with the HTAP hybrid transactional analytical processing back in around 2010. 05:27.960 --> 05:30.960 And it's been described in other places by different names, 05:30.960 --> 05:36.960 transliter call or HAAP hybrid operational and analytic processing. 05:36.960 --> 05:41.960 Okay, so far so good, but what's the problem with this? 05:42.960 --> 05:45.960 So well established in academic circles, 05:45.960 --> 05:49.960 that this type of workload presents performance challenges. 05:49.960 --> 05:52.960 There are papers from back in 2010 to 2011, 05:52.960 --> 05:57.960 where researchers identified that running mixed workloads or hybrid workloads 05:57.960 --> 05:59.960 causes specific performance issues, 05:59.960 --> 06:04.960 and there are ongoing discussions about the type of benchmarking that can be used 06:04.960 --> 06:07.960 to monitor this type of activity. 06:07.960 --> 06:10.960 But the majority of you I'm assuming will be pleased to know 06:10.960 --> 06:13.960 that I'm not planning an academic discussion today. 06:13.960 --> 06:18.960 Today I'm going to focus more on the practicalities of what you can do 06:18.960 --> 06:21.960 if you're in this situation with that mixed workload, 06:21.960 --> 06:26.960 so how you can run performance analytics whilst having minimal impact on your existing 06:26.960 --> 06:30.960 day-to-day database activity. 06:30.960 --> 06:37.960 So to recap, chances are your database is currently optimized for your day-to-day OLTP 06:37.960 --> 06:43.960 operational activity, so many concurrent users running lots of short statements. 06:43.960 --> 06:46.960 Adding analytics activity into the mix, 06:46.960 --> 06:51.960 so you've got complex long-running queries that consume lots of resources, 06:51.960 --> 06:54.960 means that you can end up with the worst of both worlds. 06:54.960 --> 06:57.960 You have analytics queries that perform properly, 06:57.960 --> 07:02.960 and it has a negative impact on your day-to-day activity. 07:02.960 --> 07:06.960 Okay, so now that we've identified the problem is that we're going to talk about 07:06.960 --> 07:11.960 what was that up, an environment to test some things out, 07:11.960 --> 07:16.960 some practical things that we can actually do to fix some of the issues. 07:16.960 --> 07:21.960 Coffee and cake feature in many of my demos, presentations and examples, 07:21.960 --> 07:23.960 because there's some of my favorite things. 07:23.960 --> 07:31.960 So I used the coffee shop sales data set that I found on Kaggle to populate a coffee shop sales table, 07:31.960 --> 07:37.960 and I expanded the data till I had about 26 million rows in my table. 07:37.960 --> 07:41.960 So just in case anybody wants to do this for themselves, 07:41.960 --> 07:46.960 I've included the details in the slides of exactly how I got the data into my database 07:46.960 --> 07:53.960 and then expanded it so that I'd got enough data to run some simple analytics queries 07:53.960 --> 07:57.960 but where we could actually see some kind of impact. 07:57.960 --> 08:03.960 Okay, so I took the Excel file converted it to CSV, removed the header row. 08:03.960 --> 08:08.960 I created a temp file with all text fields mainly because I couldn't be bothered dealing 08:08.960 --> 08:14.960 with any errors at the load stage, loaded the data from the CSV file into my temporary table. 08:14.960 --> 08:16.960 So far so good. 08:16.960 --> 08:21.960 Then I created my real coffee shop sales table with the data types I actually wanted, 08:21.960 --> 08:24.960 and I inserted the rows from my temp table, 08:24.960 --> 08:30.960 making a couple of changes along the way just so that I'd got some nice easy data to work with. 08:30.960 --> 08:34.960 Then I mocked up some data for different time periods, 08:34.960 --> 08:42.960 and I kept adding data and reinserting until I had that 26 million rows that we talked about. 08:42.960 --> 08:47.960 Then I created an analytics query to answer a slightly convoluted question. 08:47.960 --> 08:52.960 I asked which store had the highest total sales for each month of the previous calendar year, 08:52.960 --> 09:00.960 taking into account only transactions with a value greater than 20. 09:00.960 --> 09:04.960 This is the full query, the details in this case really aren't important, 09:04.960 --> 09:08.960 and I'd actually rather any SQL experts in the room didn't look too closely at it, 09:08.960 --> 09:11.960 because I'm sure it is not the most elegant solution, 09:11.960 --> 09:14.960 or the most performance solution that I could have come up with. 09:14.960 --> 09:18.960 The idea is just to have a query that we can use for demo purposes, 09:18.960 --> 09:21.960 so we can see the impact of different actions on that. 09:22.960 --> 09:27.960 Okay, so the important things for us to note are that it can catenate some data, 09:27.960 --> 09:29.960 it filters out some data, 09:29.960 --> 09:34.960 and it aggregates data from that coffee shop sales table that we created. 09:37.960 --> 09:39.960 And this is what the output looks like. 09:39.960 --> 09:44.960 It just gives us a row per month showing month store and the total monthly sales. 09:44.960 --> 09:50.960 Okay, so then we need to look at the execution plan for the query, 09:50.960 --> 09:53.960 so we can see what it's doing behind the scenes. 09:53.960 --> 09:57.960 And to do that, I'm using Explain, which I imagine a lot of you already familiar with. 09:57.960 --> 10:02.960 You simply type in, or in this case I used Explain Analyze before the query. 10:02.960 --> 10:05.960 So the Analyze keyword here is optional, 10:05.960 --> 10:08.960 but I used it because I wanted Postgres to actually run the query 10:08.960 --> 10:12.960 to give me the actual results, the actual timings, numbers of rows, et cetera, 10:12.960 --> 10:16.960 rather than just an estimate of what it was planning to do. 10:18.960 --> 10:22.960 I just want to anticipate the question, why don't you include the buffers keyword, 10:22.960 --> 10:25.960 but somebody is bound to ask that one, 10:25.960 --> 10:27.960 I don't need it for the purposes of this talk, 10:27.960 --> 10:32.960 and I just wanted to keep the amount of data on the slides slightly cleaner 10:32.960 --> 10:33.960 than it otherwise would have been, 10:33.960 --> 10:37.960 but it is a useful parameter to add, 10:37.960 --> 10:41.960 and it's definitely worth looking at the Explain documentation, 10:41.960 --> 10:45.960 so you can see all of the different options that are available to you. 10:46.960 --> 10:50.960 Okay, this is the execution plan that I got back from that, 10:50.960 --> 10:53.960 and you absolutely don't need to read this. 10:53.960 --> 10:57.960 I've just pulled out some of the main details on the next slide, 10:57.960 --> 11:00.960 the things that we're going to be looking at as we go through. 11:00.960 --> 11:04.960 Amongst other things, we see that we've got a full table scan 11:04.960 --> 11:07.960 on our coffee shop tells sales table, 11:07.960 --> 11:10.960 so that's the seek scan on coffee shop sales. 11:10.960 --> 11:12.960 It's doing some sorts on disk. 11:12.960 --> 11:16.960 I can see sort method external merge disk, 11:16.960 --> 11:19.960 and it's taking around three seconds to execute. 11:21.960 --> 11:27.960 Now I haven't included the details of this as I go through the demos in this talk, 11:27.960 --> 11:31.960 but now if you want to, you can simulate some OLTP activity 11:31.960 --> 11:35.960 against your database whilst you run this analytics query 11:35.960 --> 11:38.960 to see what kind of impact it has on that. 11:38.960 --> 11:41.960 And to do that, you could use the PG Bench utility, 11:41.960 --> 11:44.960 so that's available by default with Postgres. 11:44.960 --> 11:47.960 When you initialize it, it will create you four tables, 11:47.960 --> 11:51.960 so you can see the PG Bench branches, 11:51.960 --> 11:53.960 tellers, accounts, and history tables. 11:53.960 --> 11:57.960 The number of rows shown here are if you initialize it with 11:57.960 --> 11:59.960 the default scale factor one. 11:59.960 --> 12:02.960 So for example, if you set scale factor to 100, 12:02.960 --> 12:06.960 during the initialization, you'll get 10 million rows 12:06.960 --> 12:09.960 in your PG Bench accounts table. 12:09.960 --> 12:12.960 The history table, you know, it shows zero. 12:12.960 --> 12:14.960 It starts at zero at the start of every run, 12:14.960 --> 12:18.960 and it will generate data as it goes through. 12:18.960 --> 12:22.960 Once it's initialized, you can run PG Bench telling it 12:22.960 --> 12:25.960 things like how many concurrent sessions you want connected 12:25.960 --> 12:27.960 and how long it should run for. 12:27.960 --> 12:30.960 By default, it repeatedly runs this transaction. 12:30.960 --> 12:34.960 It's loosely based on the TPCB benchmark, 12:34.960 --> 12:36.960 and each transaction, as you can see, 12:36.960 --> 12:38.960 is made up of several SQL statements. 12:38.960 --> 12:42.960 You've got three updates, a select and an insert, 12:42.960 --> 12:44.960 and it takes random values of AID, 12:44.960 --> 12:47.960 TID, BID, and Delta. 12:47.960 --> 12:51.960 And that for the purposes of this talk is all I'm going to cover 12:51.960 --> 12:55.960 on PG Bench, but feel free to go away and have a look at it, 12:55.960 --> 12:59.960 because it is a very useful, very simple little tool. 12:59.960 --> 13:02.960 So now we've got the environment in place. 13:02.960 --> 13:05.960 Let's have a look at some of the different things that you can use 13:05.960 --> 13:08.960 to optimize that analytics query, 13:08.960 --> 13:11.960 hopefully, without slowing down your application. 13:11.960 --> 13:14.960 It's definitely not going to be an exhaustive list. 13:14.960 --> 13:17.960 The idea really is to give you enough ideas to get started 13:17.960 --> 13:20.960 so that you can go out and try other things. 13:22.960 --> 13:24.960 First configuration parameters. 13:24.960 --> 13:26.960 Your configuration parameters, 13:26.960 --> 13:28.960 have probably, hopefully, at least, been set 13:28.960 --> 13:31.960 to optimize your day-to-day database activity 13:31.960 --> 13:34.960 if they haven't, I highly recommend having a look at them, 13:34.960 --> 13:37.960 and that gives me the opportunity for a shameless plug 13:37.960 --> 13:38.960 for another talk that I've done, 13:38.960 --> 13:41.960 which talks about setting those parameters. 13:44.960 --> 13:48.960 Okay, some parameters have to be set across the entire instance. 13:48.960 --> 13:50.960 Some can be set for specific users. 13:50.960 --> 13:54.960 Some can be set for individual statements. 13:54.960 --> 13:56.960 So for those that can be set per user, 13:56.960 --> 13:57.960 that's great. 13:57.960 --> 14:00.960 You could create, for example, one or more analytics users. 14:00.960 --> 14:04.960 Rolls that have the appropriate values for those parameters. 14:04.960 --> 14:07.960 For parameters that have to be set across the whole postgres, 14:07.960 --> 14:10.960 instance, you're going to have to find some kind of compromise 14:10.960 --> 14:15.960 that works well or well enough for both the OLTP activity 14:15.960 --> 14:17.960 and the analytics activity. 14:17.960 --> 14:19.960 And that can be a bit of a challenge, 14:19.960 --> 14:21.960 and it will require repeated testing 14:21.960 --> 14:24.960 and knowing what is the priority in your situation. 14:24.960 --> 14:32.960 Okay, so let's have a look just at some of the most relevant parameters. 14:32.960 --> 14:36.960 Database connections, so the max connections parameter, 14:36.960 --> 14:40.960 tells postgres the maximum number of concurrent client connections 14:40.960 --> 14:43.960 that are allowed for the entire instance. 14:43.960 --> 14:46.960 So it's 100 by default, and on most systems, 14:46.960 --> 14:49.960 you don't want to increase it too much higher than that. 14:49.960 --> 14:53.960 The chances are you won't actually have that many analytics connections, 14:53.960 --> 14:57.960 but as we already discussed, they're likely to be using a lot of resources, 14:57.960 --> 15:01.960 so you want to make sure that you limit the number of them. 15:01.960 --> 15:04.960 To do that, you might want to look for example at connection pooling, 15:04.960 --> 15:11.960 so that you create a separate smaller pool for your analytics connections. 15:11.960 --> 15:15.960 Workmen is the maximum amount of memory that can be used 15:15.960 --> 15:21.960 by a query operation before it will spill to disk and create a temporary file. 15:22.960 --> 15:25.960 By default, that's four megabytes. 15:25.960 --> 15:28.960 Complex queries, performing large sort or hash operations, 15:28.960 --> 15:30.960 might benefit from a larger value. 15:30.960 --> 15:34.960 Now that describes the analytics type queries that we've been talking about. 15:34.960 --> 15:37.960 You can check if workmen needs to be increased by 15:37.960 --> 15:39.960 watching out for the creation of temp files, 15:39.960 --> 15:44.960 so if you use log temp files and set that to zero, 15:44.960 --> 15:49.960 then it will log in your postgres logs every time a temp files created, 15:49.960 --> 15:54.960 and that could imply that you need to increase workmen. 15:54.960 --> 16:02.960 But just be careful, because complex queries could use multiple blocks of workmen. 16:02.960 --> 16:05.960 And you might have lots of that going on at the same time. 16:05.960 --> 16:09.960 So you don't want to set this to a high value across the board. 16:09.960 --> 16:14.960 You likely want this to be one of those settings that is set high 16:14.960 --> 16:18.960 just for the individual roles or statements that need it. 16:19.960 --> 16:25.960 You've got the option if you want to set this parameter for a specific transaction. 16:25.960 --> 16:31.960 So here I set it to 20 meg just for one statement, 16:31.960 --> 16:37.960 and then after that transaction can see it's back to its default. 16:37.960 --> 16:42.960 If you're concerned that certain queries might run for too long 16:42.960 --> 16:46.960 and use up too much resource, you can set statement time out. 16:46.960 --> 16:50.960 So that will abort any statements that run for longer than the specified amount of time. 16:50.960 --> 16:54.960 By default it's set to zero, so that means it's disabled. 16:54.960 --> 16:59.960 You can set statement time out for the whole instance, 16:59.960 --> 17:03.960 or just for specific sessions. 17:03.960 --> 17:08.960 It probably makes or definitely makes sense to set it to different values for your 17:08.960 --> 17:14.960 OLTP and your analytics workload, because analytics queries might be okay 17:14.960 --> 17:21.960 depending for many minutes or even longer, whereas certain of the queries in your OLTP activity 17:21.960 --> 17:27.960 might have to run in milliseconds. 17:27.960 --> 17:32.960 So if you want to make sure that any statement that is timed out in this way 17:32.960 --> 17:36.960 is written into your postgres log so you can see that it's happening, 17:36.960 --> 17:41.960 make sure to set log min error statement to error or lower. 17:41.960 --> 17:45.960 If it's set to error, then any statement is causing errors, logs, 17:45.960 --> 17:49.960 but fatal errors or panics will be logged. 17:49.960 --> 17:54.960 So here you can see from the least problematic to the most problematic 17:54.960 --> 17:58.960 the different values there. 17:58.960 --> 18:04.960 Okay, so that's all of the parameters that we'll look out for now. 18:04.960 --> 18:08.960 Next indexing strategy. 18:08.960 --> 18:15.960 You will probably need to create specific indexes for some or all of your analytics queries. 18:15.960 --> 18:20.960 You just need to be aware that it's not just the space that's taken up by indexes 18:20.960 --> 18:22.960 that see a issue, they need to be maintained. 18:22.960 --> 18:26.960 So if you create too many indexes on your application tables, 18:26.960 --> 18:29.960 then you'll start slowing down your application activities. 18:29.960 --> 18:36.960 So it's a balance that you need to be very aware of. 18:36.960 --> 18:40.960 As well as creating indexes on one or more table columns, 18:40.960 --> 18:44.960 you can also create indexes on functions or scalar expressions. 18:44.960 --> 18:48.960 So expressions that are computed from your table columns. 18:48.960 --> 18:54.960 So here, for example, I created an index on the total sales amount expression 18:54.960 --> 18:57.960 that I used in the query. 18:57.960 --> 19:02.960 And then when I rerun the query, I could see that I now have a bitmap index scan 19:02.960 --> 19:05.960 on the CSS total sale amount. 19:05.960 --> 19:08.960 So that's showing me that the index is being used. 19:08.960 --> 19:11.960 And it actually took the execution time down to just over one second. 19:11.960 --> 19:14.960 So if you remember, it was three seconds to start with. 19:14.960 --> 19:16.960 So that had a reasonable impact. 19:16.960 --> 19:21.960 And obviously that will depend on your use case and on your data. 19:21.960 --> 19:27.960 Most analytics queries aggregate sort and calculate large quantities of data. 19:27.960 --> 19:33.960 You've got totals, averages, comparisons, groupings, all sorts of things like that. 19:33.960 --> 19:36.960 And these are resource intensive operations. 19:36.960 --> 19:42.960 So if you can, anything you can do to pre-calculate, pre-aggregate and pre-thought data 19:42.960 --> 19:49.960 can make the analytics queries much less expensive to run at query time. 19:49.960 --> 19:54.960 So one way you can do that is using stored generated columns. 19:54.960 --> 19:59.960 So you can create a generated column where the values calculated automatically 19:59.960 --> 20:02.960 to match an expression that you use in your queries. 20:02.960 --> 20:08.960 So here, for example, I've used the same thing I've used the total sale amount. 20:08.960 --> 20:14.960 And I've created a column with the default value of that expression. 20:14.960 --> 20:19.960 So I've used the generated always as keywords to do that. 20:19.960 --> 20:29.960 And the stored keyword is the one that says pre-calculate that and actually store it physically in my table. 20:29.960 --> 20:34.960 You can also create indexes on these generated columns to make it even faster. 20:34.960 --> 20:41.960 So this obviously can improve your query time, but you need to be aware that again, this needs to be maintained. 20:41.960 --> 20:47.960 So when you are inserting data or updating data, this needs to be calculated. 20:47.960 --> 20:53.960 So you just need to be aware of the balance needed. 20:53.960 --> 21:01.960 So now I can use that calculated column in my query instead of the expression that I had in there before. 21:01.960 --> 21:09.960 And I don't think I actually made a note of how long it took, but that took the execution time down again. 21:09.960 --> 21:14.960 Please believe me. 21:15.960 --> 21:20.960 Okay, materialized views, another really good way of pre-calculating sorting and aggregating data. 21:20.960 --> 21:25.960 So a materialized view is a physical copy of the results of a query. 21:25.960 --> 21:30.960 But it acts as a normal table that you can query. 21:30.960 --> 21:35.960 So they're very useful where you've got things. 21:35.960 --> 21:40.960 Excuse me, I've just got some notifications I need to get rid of. 21:40.960 --> 21:47.960 So they're really useful tool for pre-aggregating data and particularly useful way you don't need exactly up to date data. 21:47.960 --> 21:53.960 So sometimes for analytics queries, it might be good enough to use data that's an hour old, 21:53.960 --> 22:00.960 or even a day old or a week old, depending on what level you're reporting out. 22:00.960 --> 22:05.960 And again, you can create indexes on materialized views. 22:05.960 --> 22:11.960 So here, for example, we can create a materialized view for the ranked sales part of our original query. 22:11.960 --> 22:21.960 And it's as simple as just using the create materialized view keywords before the query that you want to materialize. 22:21.960 --> 22:26.960 And now wherever that block would have been used in as part of bigger query. 22:26.960 --> 22:33.960 So here, for example, in our original analytics query, you can just select from the materialized view instead. 22:33.960 --> 22:36.960 So the data's already been queried. 22:36.960 --> 22:40.960 It's already been grouped, sorted, aggregated, all of the different things you want to do. 22:40.960 --> 22:44.960 And in this case, the analytics query completed in a couple of milliseconds, 22:44.960 --> 22:51.960 instead of the several seconds when it was selecting from the base table. 22:51.960 --> 22:58.960 Note, of course, that you will have to schedule periodic refreshes of that materialized view, 22:58.960 --> 23:02.960 assuming that it's not going to be just a one-off thing. 23:02.960 --> 23:09.960 You'll want to have maybe that hourly, weekly, daily refresh of that data. 23:09.960 --> 23:13.960 Frequency, obviously, depends on what you need for your queries. 23:13.960 --> 23:17.960 It won't necessarily be the same for all of the materialized views. 23:17.960 --> 23:24.960 Hopefully, you'll be able to schedule that during quieter times when there's not so much application activity happening. 23:24.960 --> 23:29.960 And if you're using PGCron, you can set up a period refresh actually within postgres itself. 23:29.960 --> 23:40.960 So I've just given an example here that schedules an hourly refresh of our materialized view. 23:40.960 --> 23:45.960 Of course, as we all know, the best way to optimize something is by not doing it at all. 23:45.960 --> 23:53.960 So if you can avoid running these analytics queries on your application database, then that's fantastic. 23:53.960 --> 24:02.960 Even if you don't have a completely separate analytics environment, there are probably some things that you can do. 24:02.960 --> 24:09.960 For example, most database environments already have a higher availability architecture in place. 24:09.960 --> 24:15.960 So you've got your primary database that's replicating to one or more replica databases. 24:15.960 --> 24:23.960 Those replica databases are usually available for read-only activity. 24:23.960 --> 24:31.960 So instead of querying your primary database, you could send some of your analytics queries to one of those replicas. 24:31.960 --> 24:35.960 So that will take some of the load of your primary database. 24:36.960 --> 24:46.960 There are some things to do with primary, sorry, physical replication that's being used in this case gives you a complete exact copy of your primary database. 24:46.960 --> 25:00.960 So whatever you're doing on your analytics, sorry, on your replica, you can't create indexes there, you can't create separate users there, you can't create materialized views. 25:00.960 --> 25:04.960 There are many changes that you would want for your application activity. 25:04.960 --> 25:13.960 So the things that we already looked at in the previous bits of the presentation, you would have to do on your primary database and let those be replicated over. 25:13.960 --> 25:18.960 So that might not be an issue, but you'll still have some of those trade-offs. 25:18.960 --> 25:22.960 You still have to find a time when you can refresh your materialized views. 25:22.960 --> 25:27.960 For example, you'll still take the hit on your inserts if you've got pre-calculated columns etc. 25:31.960 --> 25:36.960 So another option that you've got available to you is to use logical replication. 25:36.960 --> 25:49.960 So you can use that to replicate just certain objects from your primary database into, or we'll call it just a replica database again here. 25:49.960 --> 25:55.960 So this, you go from a publisher to a subscriber, so your primary database will be your publisher. 25:55.960 --> 26:02.960 You choose which objects from that database you want to make available to be logically replicated. 26:02.960 --> 26:06.960 Your replica database is the one you're going to use to analytics. 26:06.960 --> 26:15.960 We'll be your subscriber, and you choose which publication, which publisher you want to take that information from. 26:15.960 --> 26:24.960 So this is much more complicated to set up and to maintain than physical replication, but it is a lot more flexible. 26:24.960 --> 26:28.960 So for example, you can replicate just a selection of objects. 26:28.960 --> 26:41.960 You can replicate two and from multiple targets, so if you've got many application databases that you want to consolidate into one analytics database, you can pull data from different sources. 26:42.960 --> 26:49.960 Your subscriber database will be available for read rights, so that means you can make changes on your analytics database. 26:49.960 --> 27:02.960 You can create those indexes, materialised views, etc. You can change the settings just for that database. 27:03.960 --> 27:09.960 Okay, looks like I've spoken much more quickly than I needed to today, so in summary. 27:09.960 --> 27:18.960 So we've looked at kind of what the problem is, what the issues are that we're trying to solve, where we've got this hybrid workload going on in the database. 27:18.960 --> 27:27.960 And then looks at the actual practical things, some of the practical things that you can do to make sure that your database is optimized for that analytics activity. 27:27.960 --> 27:33.960 But with as little impact as possible on your existing day-to-day OLTP activity. 27:33.960 --> 27:48.960 So things like tuning your configuration parameters to be relevant for that hybrid workload, taking into account, of course, where you can do that for specific analytics users, where you have to find a compromise across the whole database. 27:48.960 --> 28:03.960 Consider your indexing strategies, make sure that you create the indexes you need for your analytics queries, but just be careful not to create so many that you will have too much of an impact on your inserts updates, etc. for your day-to-day activity. 28:03.960 --> 28:11.960 Create generated columns, materialised views, etc. so that you can be doing that pre-aggregation and presorting of your data. 28:12.960 --> 28:19.960 And don't forget that you combine all of the different techniques so that you can create a materialised view and add index to it. 28:19.960 --> 28:29.960 You can create a logical replica and then add indexes and change the configuration parameters, etc. 28:29.960 --> 28:43.960 So the important thing is to know what your requirements are and to test so that you know what the different impacts are in your environment so that you can put exactly what you need in place. 28:43.960 --> 28:48.960 Thank you very much, I will very, very happily take questions if you have any. 28:48.960 --> 28:55.960 Thank you very much, Karen. Thank you, thank you. 28:55.960 --> 29:00.960 So it's a very large and full room. We have people also watching for online. 29:00.960 --> 29:07.960 So if you've got a question at the back, please shout it out and I'll ask Karen to summarize and just the question. 29:07.960 --> 29:10.960 So the people online know what we're talking about. 29:10.960 --> 29:16.960 Any hands up for questions? I'm sure there's lots. So at the front here. 29:16.960 --> 29:27.960 Do you talk about the junior age problems and your age values and if you sometimes probably affect one or two different. 29:27.960 --> 29:35.960 Okay, so the question is talking about generated columns and how that impacts the OLTP performance. 29:35.960 --> 29:41.960 So where you've got a generated column in a table, a generated column itself isn't an issue. 29:41.960 --> 29:52.960 It's the stored generated column that's the issue because we're actually creating, we're actually calculating that and storing it in the table. 29:52.960 --> 30:07.960 So the issue is that when you then insert a row into your table or when you update things that impact that generated column, that's got to be calculated and stored as you do your OLTP activity. 30:07.960 --> 30:10.960 So that's where the hit comes. 30:11.960 --> 30:17.960 Thank you, chat with the glasses just in front of me and I'll be fair to that. 30:17.960 --> 30:21.960 So if we have make a bell to see the replicas and sublogical replicas, 30:21.960 --> 30:27.960 yeah, and one of the nautical replicas goes up some heavier than in the analytics query. 30:27.960 --> 30:31.960 The application on the call to the left speaker or stand by. 30:31.960 --> 30:38.960 So the question is if you've got multiple physical replicas and a logical replica. 30:39.960 --> 30:58.960 Yeah, so the physical replication doesn't care about what's going on with the logical replication. 30:58.960 --> 31:04.960 So even if you've got a lag on your logical replica, it doesn't matter. 31:04.960 --> 31:16.960 And we're assuming here that when you're running your analytics query, you don't care about that lag either because it doesn't matter if your data is slightly out of date because you're, you're getting generalized data. 31:16.960 --> 31:19.960 So no, it's fine. 31:19.960 --> 31:20.960 Okay. 31:20.960 --> 31:21.960 Hi there, we have a request. 31:21.960 --> 31:24.960 If you're leaving the room early, please. 31:24.960 --> 31:25.960 Opposite side. 31:25.960 --> 31:29.960 The site with Devran in with the burgundy t-shirt. 31:29.960 --> 31:33.960 So okay, we've got a question over here and past the market. 31:34.960 --> 31:35.960 Hello, that was great talk. 31:35.960 --> 31:45.960 Just one question you talk about the indexes there and where they can start to have a negative effect on the work, the speed on certain use cases. 31:45.960 --> 31:56.960 How did you manage that or do you just just some automated way of kind of monitoring that that your indexes are having negative effect on your performance? 31:56.960 --> 32:01.960 Okay, you can just be really careful with the chairs because they do make a lot of noise. 32:01.960 --> 32:16.960 So if I've understood the question is talking about the fact that indexes can have a negative impact on your OLTP activity and how can you monitor that and. 32:16.960 --> 32:19.960 Okay, I know when it's happened. 32:19.960 --> 32:29.960 So mostly for me, that would be a case of testing before putting the indexes in place to to already know what kind of impacts are going to happen. 32:29.960 --> 32:40.960 I would hope that you would then have kind of general monitoring in place to to see how long things are taking, you know, whether they're taking longer than you expect them to, etc. 32:40.960 --> 32:49.960 But for me, a lot of that work would be testing the running the OLTP activity and the analytics activity alongside each other. 32:49.960 --> 32:58.960 With and without your indexes and with different combinations of indexes to see which gives you the best the best compromise. 32:58.960 --> 33:26.960 Okay, so the question is what about table partitioning, especially for very large tables? I mean, that is a huge topic in itself. So is there a particular aspect of it that you're thinking? 33:26.960 --> 33:38.960 Yeah, so there are particular strategies about table partitioning and moving one of the analytics queries. 33:38.960 --> 33:53.960 Potentially yes, but it's one of those things where it's it's a big kind of design question about how your OLTP activities happening, which parts of the data being query. 33:53.960 --> 34:05.960 So potentially yes, there are things that you might want to think about and do is very hard to give any kind of precise answer because it is so dependent on the data, the use case, the application, the analytics. 34:05.960 --> 34:19.960 But it might be that you can, for example, have partitions where your updating data and others where things are being query, it's possible. 34:19.960 --> 34:26.960 But for me, it wouldn't be the first place that I'd look for this kind of thing. Thank you in the black. 34:26.960 --> 34:34.960 You're so short thing for the dog, my question if you're running microservices, so you have multiple positives instances. 34:34.960 --> 34:40.960 If you have a microservices environment, you've got lots of small postcards. 34:40.960 --> 34:55.960 If you have a microservices environment, you've got lots of small postcards databases that you want to run analytics on. 34:55.960 --> 35:16.960 Would I recommend consolidating those biological replication into one large analytics database and then optimizing that? 35:16.960 --> 35:20.960 Or would I recommend something with Kafka, Spark, etc. 35:20.960 --> 35:27.960 Can I just answer it by it depends? 35:27.960 --> 35:41.960 It really, without knowing what kind of analytics queries you're doing, what rate of data change you've got, there are so many different things. 35:41.960 --> 35:55.960 I genuinely can't know which would or wouldn't be the better approach, but there are lots of situations I can see where consolidating those biological replication into a single analytics database could be the right way to go. 35:55.960 --> 36:01.960 Okay, we have one question here, then I'm going to do some questions on the other side. 36:01.960 --> 36:08.960 Hi, I have a question what materialized views, you'll mention to be pressing them as frequently as needed. 36:08.960 --> 36:17.960 And then you mentioned that when the load is not high, but on the current job we have hardly, and this is a common case. 36:17.960 --> 36:28.960 Is there any recommendation about to be pressing materialized view or is there any performance impact that should be aware of when we schedule it frequently? 36:28.960 --> 36:46.960 Okay, so a question about refreshing materialized views and I think the gist is basically getting that balance between refreshing as often as necessary and trying to mitigate the performance hit on because of the refresh. 36:46.960 --> 36:59.960 So that's it's the same as trying to balance out actually running the analytics queries themselves whilst having your OLTP activity happening. 36:59.960 --> 37:06.960 So it's going to depend on which is the most important to you. 37:06.960 --> 37:16.960 Is it more important to you to have the up-to-date analytics data or is it more important to you to not have the impact on your production environment? 37:16.960 --> 37:29.960 And it's something you can only know by knowing what your particular requirements are and by testing to see exactly what kind of impact it's going to have and then making decisions based on that. 37:29.960 --> 37:32.960 Thank you, there was a question. 37:33.960 --> 37:39.960 Thank you, actually it's very close to to the questions just asked about materialized views. 37:39.960 --> 37:47.960 How can we avoid the full crunch of the data when we update a materialized view? 37:47.960 --> 37:52.960 We want to update to only crunch the deep since the last update. 37:52.960 --> 37:58.960 How can we control or know that? Because it's quite an opaque process. 37:58.960 --> 38:13.960 Okay, so the question there is again about refreshing materialized views and this time about how to avoid having to reprocess all of the data that's used to make up your materialized view. 38:14.960 --> 38:26.960 There are tools around that will help with that external to postgres, so it might be that you would need to look at something like that. 38:26.960 --> 38:39.960 At the moment, it very much is an all or nothing. You refresh the entire materialized view, but I mean if somebody wants to submit a patch that does that, then that would be fantastic. 38:39.960 --> 38:44.960 By the way, it's just as painful in other data platforms with materialized views. 38:44.960 --> 38:55.960 Question on the cloud, are you aware of any of the platforms of service offerings that can't do this kind of mixed load configuration that you've been talking about? 38:55.960 --> 39:03.960 So the question was am I aware of cloud providers that can't do the mixed load? 39:03.960 --> 39:18.960 Do you know I haven't even looked into that and I've deliberately not talked specifically about offerings from my company because I've tried to keep it generic, so there are various cloud providers out there, I encourage you to check them all out. 39:18.960 --> 39:27.960 I genuinely don't know, but I'd be interested to hear what other people have found out. 39:27.960 --> 39:40.960 If it on mind, there is an accession called P. John the score IBM, which is the main incremental view maintenance, which helps to confuse the change of data for refresh material as you think may help. 39:40.960 --> 39:41.960 Thanks Deborah. 39:41.960 --> 39:43.960 No, that's perfect. Thank you. 39:43.960 --> 39:54.960 So in the interest in, Karen is our diverse, the task force leader, so I've tried to cover most of the room. Is there anyone who's got any more questions? I think I've covered both sides. 39:54.960 --> 39:56.960 I think just one in the middle here. 39:56.960 --> 40:01.960 Just one in the middle. Okay, if you can shout out the question out to Karen and back to. 40:02.960 --> 40:29.960 I'm trying to think how that would work. I think the table would have to be the same, so I don't think you could create a generated column just on the logical replica. 40:29.960 --> 40:36.960 But I go on, Deborah, I was just saying, I'm 90% sure to be sure. 40:36.960 --> 40:41.960 Okay, thank you. So good. At least I was right, and I do. 40:41.960 --> 40:50.960 But that's good news that that will be available in 18, because I wanted to say that that would be a good thing in my presentation, but yeah, perfect. 40:50.960 --> 40:52.960 Thank you for the question. 40:52.960 --> 40:55.960 The lady in with the white shirts, I think it is. 40:55.960 --> 40:57.960 You can shout out to Karen. 40:57.960 --> 41:00.960 Yeah. How does that scale for a person? 41:00.960 --> 41:17.960 I don't personally got hands-on experience of using time scale, so I don't know the specifics. Is there anything in particular that you're thinking? 41:17.960 --> 41:32.960 Yeah, and it shouldn't have negative impacts on running the analytical queries, but I just don't note the details of how it might enter out with that. 41:32.960 --> 41:43.960 Sorry, I don't think I repeated the question there. It was about what the impact might be of time scale with analytics queries. 41:44.960 --> 41:46.960 So what was the point? 41:46.960 --> 41:58.960 So the comment there is that time scale, of course, is the external tool that does help to do the incremental materialized view refresh based on certain criteria. Thank you. 41:58.960 --> 41:59.960 Thank you. 41:59.960 --> 42:02.960 Any more questions? We've had a really good round of questions. 42:02.960 --> 42:07.960 I should learn to speak more slowly, so I don't have to answer as many questions. 42:07.960 --> 42:12.960 Sorry, I take just this. Any more questions? If not, we'll wrap up a couple of minutes there. 42:12.960 --> 42:18.960 They can give you a couple of minutes of break. This is my third session in the room, so a really great start to the day. 42:18.960 --> 42:21.960 Thank you very much, Karen, if you can give a warm round of applause.