VeniceDB
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Wei, Min
VeniceDB, a Peta-byte scale real time analytics service running Postgres on Azure VeniceDB is a large scale OLAP service with a custom build of Postgres/Citus on Azure. Since the public talk at PostgresSV2018, the cluster has grown to 1PB to support Microsoft CoreOS executive decision dashboard that hosts all measures that cover from edge devices to cloud OS. VeniceDB has been in production for about a year, and went through a few major revisions while keeping the service running. During each revision, we keep tuning the data model and indexing to improve the data ingestion and query performance. We also migrated from Postgres10 to Postgres11, which helps to reduce the cluster cost by 30%. This talk covers why we chose Postgres and Citus as the foundation, and how we build a unified storage to serve various measure data needs. During our journey, we not only replaced traditional MapReduce based cubing jobs but also replaced a columnar storage cluster. We will share our perspectives on row storage vs column storage, and how we compare with other OLAP solutions like Druid, etc. and our wish list for future Postgres improvements.
 Okay, I will get started. Okay. Welcome to my talk. I'm going to share my experience about how I build a large-scale analytics service with Postgres on Azure. In my talk, I would have two parts. The first part is going to cover the use case and how I use the site as a Postgres to be for the service. In the second part of the talk, I'm going to talk about why I'm using site as a Postgres, because in particular these days, if you come from West Coast, from Silicon Valley, there's many other choices to build a large-scale analytics and services. I will conclude the talk with some discussions on how Postgres can be improved to make it better for the building analytics and services. Now it's on my use case. I'm a database engineer working in the Microsoft Core OS group. So Windows, of course, now we have Azure, like Linux, and we have Windows, but Windows is still dominant for Microsoft. It's a huge business. So Windows has been deployed on over 800 million devices. And Windows itself is also a very sophisticated system. It's been heavily instrumented. So just on a rough count, it has had over 100K type of events being fired at a different stage of the Windows operation. So the data being generated from all the devices that are being put onto the Azure Cloud is measured in a few petabytes per day. In order to understand the data, we need to stitch those events together to make sense of it, to understand what the user experience is going to look like and what the reliability of the system is. Say, I mean, like, as I said, Windows has been heavily instrumented, so every logon, Wi-Fi connections, like Bluetooth connections, all being instrumented. So an example of the measured data is, say, if you click on the startup menu, so the launch time of the startup menu is from two events. From you click on the start menu and to the start menu, actually launch. So this latency turned out is quite important for the, I mean, for most of the user experience and also being affected by the hardware quite a lot. Being the new hardware, I mean, with high-end SSDs, so it's come up very quickly. In some older, say, hard drive-based hardware, I mean, there's still quite a lot of people running Windows on the old generation of machines. It's become, say, slower. So we're looking for ways to make the overall Windows experience a lot better. So the measured data is created from the event data. So it's much smaller. There's about, like, five terabytes per day. And so it's, like, a month late can, say, like, give or take about 150 to 200 terabytes, like, per month. So all those measured data is being collected to serve, we call it the executive decision dashboard. Because of the size of the Windows, I mean, the business itself, so this is the most important dashboard for Microsoft. I mean, this dashboard is used to decide if a Windows update is actually ready for the customers. So as such, there are thousands of monthly internal users for this. I mean, a lot of people, the whole job is trying to improve the metric being shown on the dashboard. And there's hundreds of, like, dashboard pages. And as a result, there are about six million queries being generated per day from those, like, dashboard pages. A few months ago, there was a public disclosure of this Azure system. And I just have this blog link. So for some other details, you can feel free to read. Like I said, currently, most of the measures coming from the telemetry event, and I mean, there are more and more measures coming, has been generated from the machine learning algorithms. But because this is such an important system for Microsoft like business, and for the past six years, they have multiple solutions being built. So the generation one was built about five years ago. It's actually using the Shared SQL Roadstore. Once it's shared, because there is no distributed query involved. So it's a bunch of SQL servers, and so the application had to write a proxy to do the query fan out. So it merged them together, kind of like the Postgres, like a data wrapper. And this system in operation for about, say, a year and a half, and then it's running out of steam. So the second generation system was built. It's also, I mean, so it changed from Roadstore into the column store. I think five to six months after the system got built, and because Microsoft have a lot of people passionate working on database and tech, there is, I gotta say, mature, like distributed column store tech being built. And it's complicated. I mean, I'm gonna talk about the same a little bit, but I'm surface to say that the second generation, I mean, it worked for about, say, another year and a half. So then I was actually brought in to look at it, to build it in the third generation, because the second generation was about to run in the out of steam. Why I first, to join the effort, I analyzed why the first two generation failed. So the number one reason, well, because it's an under-service, the cost of the dimensionality is actually real. And one of the issue in the first two, like in generation, is it requires a separate, like a large scale, map reduce processing to producing the cubes, right? Because I mean, the use case is to light up, say, the dashboard, right? So I mean, because of the dimensionality, and they take longer and longer time to prepare those cubes. And also the data is quite highly skewed. So those are batch jobs, quite often, which is randomly failed, just somewhere, because of the memory and other type of resources. And each fail would have causing the job to be re-run, which is impacting the SLA, right? Because when people make a decision next day, they want as much data as possible. So the second issue is more related to the shared SQL type of solution, because there is no true, like, distributed SQL support. Because the data complexity and the calculation complexity is an increase over the years. In one case I've seen is a single query is using a dynamic SQL. It's about 500 kb, just kind of, it's mind-boggling how to debug those kind of scripts, right? I mean, it's taking longer and longer to introducing new calculation types because of, like, this edge data complexity. And then the third reason, so it's like a, say, the distributed column store, like, does not have the dimensionality problem. And it's also relatively concise to actually express, like, different calculation types. So one problem with that is cannot drive decisions like a dashboard for the concurrent queries. I'm going to cover some of the reasons in a later slides. So because of these three main reasons, so I have come up with a totally new design. Yes. In this set of new design, so there's like three main pieces. One is I called measure data lake. I mean, you can think about is kind of like I said Kafka queue, right? And in fact, when I started it is a Kafka queue because I'm on the Azure cloud, I have put a Kafka queue on the Azure Blob storage. As the data actually grows and then the total cost actually doesn't make sense anymore, right? And the Kafka just getting more and more expensive. And then I realized, well, I really need is actually the storage under the cover. I do not need a Kafka lifestyle or the API. So basically we just, I just took out the Kafka layer and just using the Azure like a Blob storage like natively. So this is as integration queue to receive the measure data generating from the upstream because we still need a set of processing layer to stitch those events together and enrich those events. And since it's a reporting system mostly, then I do have a fairly sophisticated like a data access service and I would not cover much in this talk. And now, I mean, the elephant in a diagram is excited as postgres cluster. So as part of the design, I should fold this like a data like preparation, I mean, kind of large scale like a map reducing job into the site as a postgres cluster itself. So that's why I drew, picked a photo of this, I guess, here at the iceberg, right? Because there's so site has helped to actually extract a lot of complexity. So the one added advantage with this approach is also unified the real time and historical query part, right? Because there is no more, so I just learned architecture, which is for whatever reason getting very popular for analytics like services. So here's some of the details about the database cluster itself. I'm on the site as enterprise and the postgres 11.3. As you see, I tried, I'm fairly up to date, right? So the thing is, I actually need every improvement from postgres, even from minor releases to gain the performance edge because, I mean, the performance means like cost reduction. So I started this project about two years ago, I think. So the site is around time was shipping like 7.0. And also the postgres 10.0 just as released. Or I should put it in another way, if there's no site as 7.0 or postgres 10, because to have those as partition, like declarative partition table support, I would have used a different solution and then like I say the postgres space. So some minor details about the production cluster itself. That has over like 2800 cores, have 18 terabytes of memory. And the content is about one petabytes of the disk space. And this is keep on growing. So I have to do some pulling to suppress people's data to make sure they really need. I think probably by the end of the year, this space will be doubled. And so like data-wise, this cluster is hosting about 20,000 measures. So by each measure, it's like a multi-month time series like data. So the new data coming is about like five terabytes of data per day. And thanks to the GDPR requirement, it also need to delete five terabytes, I mean, first high bytes like data per day because of, I mean, this is actually creating some additional like challenges for some other type of like system. I'm going to talk about I have two main type of query. And one is a little bit cheaper. The other is actually more expensive. The cheaper one, the P75 query latency is about, it's actually less than like, say like 90 millisecond. And for the more expensive one, it's about 200 milliseconds. Of course, like for such a large-scale system, we often time need to support, like I say, some other data science workloads and stuff like that. We, from the front door query, we do allow, like I say, the four minutes up to four minutes of queries. And so as people can do fairly less, like sophisticated analysis with the data. And if you need something longer than that, so like we have a backdoor, right? So we can make the job to running up to two hours. And this is how I set the locked time out because like we do not want, let's say, job running for tens of hours. And this is the way, I mean, so this also forces people think about how to like, so partition and slice and dice data, right? So into more like manageable chunks. And in fact, this is approach we use internally for our own like, maybe reducing job. Just like, it's a measure that kind of like the fish from the sea is come in all shape and sizes, right? This is a, let's see the photo of the Tokyo fish market. So I mean, since there are, I mean, the country, there are about 10 types of schema and different types of keep on coming in. So in order to make the processing job same, right, to a tag manage complexity. So this is a perfect case to using like JSON. So as you can see in the slides, I have a JSON beacon column. And one advantage of using JSON is also to allow, have per measure like a schemas, right? So because they have come, come special dimension in these days because like, and I mean, it's a quite a goofy to a, to a large extent. And this schema has two timestamp. So the streaming time, the stream timestamp is actually say the processing time. So I mean, it's the current hour. So it'll be the current time. So the windows measure data has a one feature is like I said, the data could be always edge to delay, right? So if you have a laptop, you use it before the telemetry get uploaded. So people just close their laptop and say on vacation or I mean, simply it's a long weekend. So the data could be delayed for a couple of days, sometimes to couple of weeks. I think just as a business logic, so we actually allow the data up to say, say like 15 days like delay. And this table also has a hash partition key. Like, I mean, it's used by Citus to do the hash like partitioning and it's can partition the data, say the relatively evenly, so over the cluster. And then also using the native postgres partition to do the data processing on the event time. So this will allow us to do this like table dropping, right? Because otherwise you have to, I mean, just delete the rows is not going to cut it. So the data is coming mostly on the hourly like basis. And we have some newer data now coming as a minute, like a time grain now. Let's also want to have one word about the ingestive program. Originally I thought I need a small cluster to read in terabytes of data per day and like say, say, shred it and then sort of side us and copy into those like postgres like workers. And then I just said, hey, what if I just write a simple like a goal program, multi-thread goal program to do the same thing? Well, it turned out, we actually shipped it in production. It's been used for almost like two years. And it's actually fairly impressive to see that the heap can go to say, so at least 100 like gigabytes and it can run for months, I mean, without stopping. So it's actually kudos to this goal of EMT. Okay, so after the data getting ingested, so I'm going to have like say a lot of fish, I'll get a metaphor here. As a typical, say the sushi chef, before they serve the sushi, they need to prepare the fish into some like more manageable, say cut it into like manageable chunks, right? Well, in my case, my knife is like such a scientist, it's a distributed absurd statement, right? I mean, it's just a vanilla postgres absurd statement. And I just said, it's actually enhanced it. So it worked in a, you know, like a distributed fashion. There's two ways to cut the data, right? So one is, say the reporting table is shared the hash partition key as say original income in the measure table. So it's like co-located. So when you do this kind of cutting, there is no internal traffic. So it's actually fairly efficient. So and everything was done in parallel on each of the worker nodes. And of course, this is not always the case. I mean, we do have fairly rich report and then we need to pick a different partition key. In this case, the data will go through this side-to-side coordinator and get reshuffled to the worker node again. Because on Azure, we have like 40 gigabit network, so the whole thing was, I mean, very efficient. So the algorithm-wise is you can think so ecologically, and it's about one billion rows, right? So every hour we have about one billion rows. And we partition it into like 15-day buckets, right? And then we just parallel insert into the 15 days, right? So because we have different kinds of, say, reporting tables, say, I just picked a sum of my random number here, like 10, I think we are at over 10 kinds of reports now. So you can think about it as almost on this cluster, every hour I have 150 jobs to do this massive updates, right? And it's a serving query at the same time. So I mean, it's actually fairly impressive. So the postgres along with, let's see, like, I just can do this. And so each of the daily table, I mean, say, the reporting table has been heavily, like, indexed, right? So some of the daily table has over, like, 50, like, indexes. And I do use partial covering index a lot. And this would allow me to the index only scan. So this is the case. I'm trading disk space for the performance, right? So in a cloud environment, even though the CPU performance is not improving much year over year anymore, there's still lots of room for the disk space and networking. So with all that work, we're actually ready to serve, like I said, the queries. So on this side, I show there's two types of query. The first one, just a vanilla group by query, right? So this particular example I show is how we calculate the averages. And this query, we're getting more complicated. When we need to calculate, seems like I say, like a percentile. Because in this case, instead of a simple sum and count, I'm getting an array of sum and count, right? So basically, I have to write the code to leverage the feature from Hsidus to do those types of distributed computations. And then, fairly recently, we also had a histogram, like, data types coming in. And also the dimension columns is getting more complicated. So originally we started with scalar columns, like integer strings. And now we have things like arrays, like a list of the experimentation IDs and stuff like that. And then, so the first type of query, even the first type of query, getting fairly complicated. So, think about the first type of query, do the average calculation, right? Because statistically it has one problem. It's actually subject to the offlier. I mean, it's actually a bias. So metric value will be biased to the outlier. So in some cases, because it's, like, we don't have such a huge population, a lot of the devices are coming in various test labs, right? So some test lab in Southern Taiwan could be rebooting the machine 10,000 times a day, right? So if you want to count in machine, see, reliability, this one at the outlier would totally see, like, skew, you know, actually a metric and would create an alarm, I mean, on the dashboard to say, well, there's something wrong because the machines seem to be constantly, like, say, rebooting. There are a couple of different ways to tackle this kind of, say, like statistical anomalies. One is, of course, you can use machine learning. Well, the problem with using machine learning is you have to know the, like, distribution, right? And we have 20,000 measures that you have to know, like, say, like, distribution for each other measure. And also the user behavior changes all the time. So you have to keep up with understanding, okay, what the distribution are. Well, clearly, this is not very, like, say, like, scalable. So the, so this is the second type of query coming in. Second type of query, I mean, from the statistical point of view, is actually, like, a population, like, distribution agnostic. So the way to achieve that is, is introducing an inner query, right? So the first one is, grew by of the, say, take an average, for example, you grew by the device ID. You calculate per device level average. Then you calculate the average across all, like, devices. So to some extent, like, this query, in turn, we call it one device at one volt, right? Because this query itself does not have any of the data assumption. So it can be applied to any kind of, say, like, a measure. So I want to, like, talk, pause the scene for a second. So the device ID, as I mentioned, is 800 million, right? So this inner query, the factory, like, grew by 800, potentially, 800 million, like, device IDs. And I think when I sign up, I mean, with the team to do this, this is actually the query I look at. So I do not know, so I do not know post-grace. I was just looking for ways how to, I just speed up this query, right? So this is also the query killed the first two generation of the solutions. So I hear some of my findings and some of them done by my colleagues because Microsoft is a pretty big company. There's a lot of people in similar, like, space. I think because of the data warehouse and analytic service, it's not really a new problem, right? Especially for the past four or five years, there's many, many, like, solutions being, like, down, right? So even Apache has quite a few, like, projects, right? So the key link is from eBay. I mean, it's doing mostly doing aesthetic, cubing. And recently, they have a way to do introducing the Lambda architecture to do in-memory and aesthetic, cubing, like, together. P-Node is a project from LinkedIn. Now it's also part of Microsoft. In fact, I worked with P-Node engineer for about two months to see kind of whether they can handle that particular query type. And then we have things like a Druid. And quite recently, because the data break in Spark has become a first class, like, citizen on Azure. So there are some teams want to try those kind of queries on the Spark Azure cluster. So I've been, like, looking through and evaluating these Azure solutions. So here's a sublist of the issues I've seen. One is, like, even though everyone calls themselves, like, SQL engines and stuff like that, I mean, they're not really true kind of mature SQL engine compared with the engines, like, I mean, from, like, say, like, DBMS. Right? So say, take P-Node, for example, it cannot even do that, say, the Inner, like, subquery. So I have to let along to the hard cardinality one. So the hard cardinality would have an issue that you won't have enough, like, so, like, memory, then you have to spill things on the disk. And I mean, the data could be actually skewed, and then you have to handle that, too. So it's actually, so, I mean, that query looks very innocent, but it turned out to be a really hard inquiry to take. And then, of course, like, I mean, with new dimension types, like, arrays, that stuff like that. So I think most of the solutions, I mean, existing like open source like solutions from Apache, like, just, I mean, cannot even touch it. Also I noticed, like, most of the, say, recent, like, analytic service, all using column store of some sort. Right? I would say it's almost like, I mean, they're depending too much on the column store. I could say using scanning, because JSON data type, I mean, have per row kind of, say, schemas getting quite like a popular, I mean, kind of whether we, as a data guy, we like it or not. Right? So for Windows major data, those are, JSON typically embedded something called, like, so those are good types, which is just horrible for any column store as a compression. Right? I say that because I saw many column store, I evaluated, maybe, say, column stores. And since then, since they cannot compress much, and then they have to do a lot of scanning, and then when you have a few concurrent query coming, well, one thing we realize, even four or five concurrent query just suck away the system memory bandwidth of a physical machine or a VM. Right? So once the system memory bandwidth gets suck away, the machine is dead. Once a few machines are dead, the whole cluster just, I mean, becomes, say, very unresponsive. And kind of the other thing just from the, say, the production experience is quite often I realized we do need updates. Right? So for the finite set production experience now for almost once or twice every two to three months, I really need update because they're from upstream, people have a bug, and then people give me the wrong data. Right? And so causing the production issue, give me the billions of billions of rows or wrong data because, I mean, okay, so either we kind of fix, say, the service, right? So you have to patch up and figure which time range is special patch, say, the service with the business logic. I mean, if we bring down that, that means first of all, you have to code it. Second is you have to have a downtime, right? Because you have to, like, swap in, say, the new service. And of course, you have no idea why you need to swap out. So the cheaper solution is actually update data. Right? So, I mean, with Sinus and PostgreSQL, you really take, I mean, for the few cases, I encounter, take about one hour to update two to three billion rows. And so the outage is kind of more or less, so I last for about one to two hours, which is okay. And I think because of the modern, the new analytics service being built, they always have some, again, I call it, say, accidental complexities, right? So you need a high-end Spark and cluster to prepare the data. You also need to, I mean, even a cloud environment because it's tied into HBase. I mean, in this case, like I say, like a key link, and you have to swap on your own, like, HDFS. It's kind of why, right? Because these things are designed for bare metal system. It doesn't make much sense to run in a cloud environment, right? And quite often for the database, people that, I mean, then we need to run like a zookeeper. So I, in my previous role, when I'm working for the being infrastructure team, I do have a team kind of working for me to maintain, to keep the zookeeper up. It turned out, it's actually not that easy. So basically this time around, I say, okay, I'm just going to go into full cloud native and just use the fabric features and end result, like I say. So using like zookeeper or the ETCD, those kinds of things. Okay, now, actually, it's close to the end of my talk, right? So if the Postgres and Citus and Solution sounds so great, and why a lot of people, I mean, if it's especially start from new, I mean, Postgres probably is not going to be your first choice to build analytics, large-scale analytics service, right? So it would probably most likely pick Druid. Like I say, I think even NBNB is actually picking something like Druid. I think there's, I've been thinking about this for some time. I would say like the Postgres SQL and the SQL in general is marketed towards like DBAs, right? It's not really marketed towards actual programmers. So if you're a real programmer, you write procedural code in all languages called Java and Scala, right? So you suck the data out, you do bunch of processing, and you write, say, the data back in. So the SQL itself, I mean, it's being particular, so Postgres SQL is actually very powerful. But the declarative programming is kind of unnatural for a lot of people. So it takes time to practice. So this is some of the slides, I mean, some of the points I have summarized over the past two years. So to compare with a normal procedure like program, right? So the first thing you need to come up with is like data structure. So in SQL, that is like a table, right? But of course designing a table is not easy because sometimes you had to, quite often you had to think about the indexing, right? And also because the table could be huge, right? It could be, say, billions, hundreds of billions of rows. And then you had to think about how do you actually partition this data like properly, right? And the other thing is very often, even when we use internally, right, you have to, quite often you have to scan the entire range, all the data in that range, right? Postgres doesn't really have a particular efficient way for you to do the paging. And then it's basically you have to write your own custom paging. So it works out fairly well, but if you know what you're actually like doing, right? If you do not know what you're doing, then you're spending like the job cost around just for hours. I mean, I should say very slow like performance, right? And the second thing is about this algorithmic design. So SQL let you think the problem like differently, right? So in particular with the Citus, you have to think about also how the data get to move, I mean, so like among the nodes, right? Because you want to improve the IO efficiencies. So this kind of like programming sometimes is not easy, right? So say for example, because of using Citus as a distributed SQL, so all the, I mean, even the percentile, the building percentile function doesn't work anymore, right? So basically you have to think about ways to implement it. So for the past two years, I personally implement like three different edge versions and try to fit into the three different types of data characteristics that are actually different. Right? So sometimes you have to make certain like trade-offs. And also compare with the normal program execution, there isn't no profilers. I mean, yes, sure, in love and learn, I say, explain a lot, right? So, but sometimes it doesn't have enough of the details. So I didn't realize why it started. I just said, hey, I just need to grab all the IO, low-level OS level IO counters, memory counters, the connection counter into a SQL table. And then I graphed it with another open source project called Grafana. And it can show me almost, I mean, once I start refreshing every five seconds, I can almost have this live view about the system. And what turned out, I build myself a visual debugger, right? So for the past two years, whenever I run queries or any indexes, I go to my, like see the Grafana pages to kind of see what's happening, right? I mean, this kind of helped me to tune the algorithm to understand the data actually better. I would say the other one is actually watch out for the author of ACUM. I didn't really pay attention to this about, I mean, until one and a half go, and I realized like there's small disk kind of leakage, right? So there's always using, I mean, I do a lot of calculation stuff a lot. So I might want to say like a numbers guy. The number just doesn't really add up. So why I'm using model disk space, and then it should. And then turn out, well, I need to dive the auto vacuum, because if the thing cannot keep up, if the auto vacuum worker cannot keep up, and then so the disk space will just add to grow. Since I have like a daily partition table, it will add to grow for a few hours a day and then stopped, right? Because it's switched to the new table. So I think this is kind of one of those as a gorgeous, I mean, not to say the natural, I mean, if coming from the all procedural programming in the background. So I will just talk about some of the potential suggestion to the Postgres itself. I think the Postgres 11 does have a runtime partitioning pruning. What I found is the performance has more to be actually desired. So I had a table, I mean, it's a six month table, so 180 days. And I realize, even though it's due to the runtime partition pruning, the performance is not great, right? So it's because I want to see how can we improve on that. Since I, that profile said queries, and then I noticed, I would say over 90% of query is hitting this, I say monthly, right? I mean, it's actually, then I just created a 30-day table, right? I just, I have this space to spare. So I just created a 30-day table to route a query to the 30-day, most of query to the 30-day table. And it turned out it's improved the performance by three times. Well, I could take that. So the second issue I encounter with a sort of a Postgres is, because I have to use a PG Bouncer, right? There is no building connection pool like managers. So there's two use cases from my point of view. One is to the connection like throttling. So the second use case is, since I need to drop those partitions in a very deterministic fashion. If I need to drop those partitions, I want to drop it within a minute or two, right? So in this particular case, I have to use a PG Bouncer to turn off the set queries coming into those tables. And then I just drop them, because otherwise, you can always wait in for the lock, right? So the problem is, if the query is not stopping coming with different pattern, then this kind of like, say, maintenance stuff is becoming non-deterministic, right? So I think if having some building connection pool like manager with some more fine grain of a lock control, it will help improve those maintenance jobs. And I think now I'm just entering more, say, speculative suggestions. So on the shared buffer, right? So the performance of the Vanessa is heavily depending on what pages get cut into the shared buffer. So there's no fine grain control over the shared buffers, which indexes can get loaded into the page. In order to improve the performance and also to have an external view control about that, so where to write the customized one-map code to a periodically just rerun a few queries we can see, and then those indexes will get loaded, right? And the other thing is, I'm thinking, is as a machine, getting bigger and bigger, right? So I mean, on a full terabytes, like a memory machine, probably you can dedicate one terabytes for the shared buffer. So the idea is, like, you would, I mean, you want certain pages kind of like, I say, never get a page load. So since there is more CPU to spare, so why not take some code index page, just compress them and store them in a DRAM. And then upon access, just as you decompress it. So I think the PostgreSQL up until now is still using this volcano-style query processing as one vector at a time. And for analysis queries, it would be benefiting greatly from a vectorized query processing. So I talked to a few PostgreSQL committers. This thing is not really high on the list yet. I think the other one is because my table right now has, let's say, lifetime for about like six months, right? So I mean, I stored all on the premium storage. So as I need to extend, say, into one year and two years in the past, right? So currently I have kind of pretty many ways. So I just dump all the data on disk, reload it into another table in, say, another table space, which is on a cheaper storage, right? So ideally there will be kind of a little bit more automated or a better, more efficient way to do this if this features a larger building into this, I could say, PostgreSQL to allow the partition table to switch to table spaces. So the last point I would like to make is because the distributed query execution, I would say almost, I would say many steps function in PostgreSQL, it just doesn't work. So currently, I mean, just you have to write your own. So I was hoping that there will be some, say, standardize way of, I mean, say, like, doing this, okay, because I can see a lot of PostgreSQL coming with its own distributed query execution and none of those steps functions are, say, portable anymore, right? So I think this, well, it could be a big problem down the road for analytics workload. So I hope I'm on time. So questions? Oh, yes, please. Okay. Okay, well, this is a very good question. I'm not sure if I need to repeat the questions. Probably I would just say, do not. I mean, how do I, say, go protect myself against, say, like data corruption? I see initially I was fairly confident, right, because I only need to maintain, because once I have device ID, I only need to maintain a 30-day table. So I can just drop the table. So I only have one month's factory worth of, like, say, like, life data. So the way to protect against that is because I have this, like, say, like, measure like data lake, right? So I can always, if data corrupted, I can always reinjust it. Now my cluster lifetime for, I mean, non-device ID based data extended to six months. So this do get me quite concerned now, right? So the thing is there's a couple of things to, like, how much of the sale I do in that. One is, I mean, we start to do the, say, the daily backups. I mean, I can into the, say, the blob storage. Well, it's, well, I mean, sort of incrementally, right, right? So, well, because, say, the blob storage is that cheap, right? So I think probably it's probably known that it's about $10,000 per month per petabytes, right? So, I mean, there's, I mean, I can just say to blow a lot of money actually on that one. So I'm not, assume, okay. So you get to build for all this, start or end user, you don't know much. Oh, all right. So this is the graph for the internal Microsoft. I'm, internal Microsoft team, in my, say, in my group, what you pay higher price than the enterprise customer, than a typical enterprise customer. So I had to be much more cost-conscious than a typical enterprise customer. And I'm also, because internal customer, we only limited to a few data center. So not all, like, data center are available to us. So do you answer the question? Yes, I have worked harder. Yes? Sorry, again, I did get a hundred questions. Kind of a weird question, but so something like obviously the Microsoft product has to be on the server, like it's just not well suited to tackling the data that you have in this model, remember? Well. So the SQL server itself, I mean, is a standard alone like SQL server as, so as Osgon called out, it's not really like distributed. But Microsoft do have, because we're a big company and a lot of people, smart people working there, so we do have distributed column store too, right? So I think the thing I realized, because I was a column store, I fined it, say, ten years ago. I think right now, because the data is so rich now, and I mean, it's kind of one trick pony with column store and just a very narrow column compression is not going to cut it anymore. So, I mean, people have more wider, like, data structures, like, say, like JSON or even arrays, right? So, I mean, think about even for, say, array, like, data structure. So if I have one billion rows, it would flatten the thing out. So if the array, say, on average has ten IDs in there, I mean, just a very scary thought to kind of having row explosion like that. So I think because of this richer data types is actually making the traditional, like, sort of, sort of, row the database, I say, more attractive. And also, I think one thing did not come out, I should say, like, very explicitly, is the event time coming, the timestamp is in milliseconds, right? So sometimes even, say, like, nanoseconds. However, human cannot really understand, I mean, say, like, data at that kind of time like resolution. So the reporting is usually on the weekly and seven days. So it actually requires, say, the delivery. So the one of the things I did is during those aggregation, I truncated time at, say, like, daily grain. So this would actually allow me to massively also compress the data, right? I call it semantic compression. So even having higher compression rate than if I just use a row store, right? I mean, sorry, say, a column store because that only has the append-only strategy. And then you still end up with lots and lots of rows. And for monthly queries, there are just tens of billions of rows to be, like, scanned, like, proquery. Yeah. Yeah. Well, I mean, all the cooking are considered as updates. Well, I say updates and inserts, right? If a new row you inserted and then if you have, say, old rows already with existing key, they have to update them, right? And I mean, the other kind of updates, as I said, like, typically, quite often, I mean, when you see that around data and where do you need to fix this data retroactively? Okay. Okay. All right. Let me have a look at a little bit of this. Okay. Okay. Okay. Let me see. I'll take a look at this one. This one, okay. Okay. Okay. The second one. Okay. Okay. Okay. All right. Okay. Okay. All right. Okay. Okay. Okay.
 Okay, I will get started.  Okay.  Welcome to my talk.  I'm going to share my experience about how I build a large-scale analytics service with  Postgres on Azure.  In my talk, I would have two parts.  The first part is going to cover the use case and how I use the site as a Postgres to be  for the service.  In the second part of the talk, I'm going to talk about why I'm using site as a Postgres,  because in particular these days, if you come from West Coast, from Silicon Valley, there's  many other choices to build a large-scale analytics and services.  I will conclude the talk with some discussions on how Postgres can be improved to make it  better for the building analytics and services.  Now it's on my use case.  I'm a database engineer working in the Microsoft Core OS group.  So Windows, of course, now we have Azure, like Linux, and we have Windows, but Windows  is still dominant for Microsoft.  It's a huge business.  So Windows has been deployed on over 800 million devices.  And Windows itself is also a very sophisticated system.  It's been heavily instrumented.  So just on a rough count, it has had over 100K type of events being fired at a different  stage of the Windows operation.  So the data being generated from all the devices that are being put onto the Azure Cloud is  measured in a few petabytes per day.  In order to understand the data, we need to stitch those events together to make sense  of it, to understand what the user experience is going to look like and what the reliability  of the system is.  Say, I mean, like, as I said, Windows has been heavily instrumented, so every logon,  Wi-Fi connections, like Bluetooth connections, all being instrumented.  So an example of the measured data is, say, if you click on the startup menu, so the launch  time of the startup menu is from two events.  From you click on the start menu and to the start menu, actually launch.  So this latency turned out is quite important for the, I mean, for most of the user experience  and also being affected by the hardware quite a lot.  Being the new hardware, I mean, with high-end SSDs, so it's come up very quickly.  In some older, say, hard drive-based hardware, I mean, there's still quite a lot of people  running Windows on the old generation of machines.  It's become, say, slower.  So we're looking for ways to make the overall Windows experience a lot better.  So the measured data is created from the event data.  So it's much smaller.  There's about, like, five terabytes per day.  And so it's, like, a month late can, say, like, give or take about 150 to 200 terabytes,  like, per month.  So all those measured data is being collected to serve, we call it the executive decision  dashboard.  Because of the size of the Windows, I mean, the business itself, so this is the most important  dashboard for Microsoft.  I mean, this dashboard is used to decide if a Windows update is actually ready for the  customers.  So as such, there are thousands of monthly internal users for this.  I mean, a lot of people, the whole job is trying to improve the metric being shown on  the dashboard.  And there's hundreds of, like, dashboard pages.  And as a result, there are about six million queries being generated per day from those,  like, dashboard pages.  A few months ago, there was a public disclosure of this Azure system.  And I just have this blog link.  So for some other details, you can feel free to read.  Like I said, currently, most of the measures coming from the telemetry event, and I mean,  there are more and more measures coming, has been generated from the machine learning  algorithms.  But because this is such an important system for Microsoft like business, and for the past  six years, they have multiple solutions being built.  So the generation one was built about five years ago.  It's actually using the Shared SQL Roadstore.  Once it's shared, because there is no distributed query involved.  So it's a bunch of SQL servers, and so the application had to write a proxy to do the  query fan out.  So it merged them together, kind of like the Postgres, like a data wrapper.  And this system in operation for about, say, a year and a half, and then it's running out  of steam.  So the second generation system was built.  It's also, I mean, so it changed from Roadstore into the column store.  I think five to six months after the system got built, and because Microsoft have a lot  of people passionate working on database and tech, there is, I gotta say, mature, like  distributed column store tech being built.  And it's complicated.  I mean, I'm gonna talk about the same a little bit, but I'm surface to say that the second  generation, I mean, it worked for about, say, another year and a half.  So then I was actually brought in to look at it, to build it in the third generation,  because the second generation was about to run in the out of steam.  Why I first, to join the effort, I analyzed why the first two generation failed.  So the number one reason, well, because it's an under-service, the cost of the dimensionality  is actually real.  And one of the issue in the first two, like in generation, is it requires a separate,  like a large scale, map reduce processing to producing the cubes, right?  Because I mean, the use case is to light up, say, the dashboard, right?  So I mean, because of the dimensionality, and they take longer and longer time to prepare  those cubes.  And also the data is quite highly skewed.  So those are batch jobs, quite often, which is randomly failed, just somewhere, because  of the memory and other type of resources.  And each fail would have causing the job to be re-run, which is impacting the SLA, right?  Because when people make a decision next day, they want as much data as possible.  So the second issue is more related to the shared SQL type of solution, because there  is no true, like, distributed SQL support.  Because the data complexity and the calculation complexity is an increase over the years.  In one case I've seen is a single query is using a dynamic SQL.  It's about 500 kb, just kind of, it's mind-boggling how to debug those kind of scripts, right?  I mean, it's taking longer and longer to introducing new calculation types because of, like, this  edge data complexity.  And then the third reason, so it's like a, say, the distributed column store, like, does  not have the dimensionality problem.  And it's also relatively concise to actually express, like, different calculation types.  So one problem with that is cannot drive decisions like a dashboard for the concurrent queries.  I'm going to cover some of the reasons in a later slides.  So because of these three main reasons, so I have come up with a totally new design.  Yes.  In this set of new design, so there's like three main pieces.  One is I called measure data lake.  I mean, you can think about is kind of like I said Kafka queue, right?  And in fact, when I started it is a Kafka queue because I'm on the Azure cloud, I have  put a Kafka queue on the Azure Blob storage.  As the data actually grows and then the total cost actually doesn't make sense anymore,  right?  And the Kafka just getting more and more expensive.  And then I realized, well, I really need is actually the storage under the cover.  I do not need a Kafka lifestyle or the API.  So basically we just, I just took out the Kafka layer and just using the Azure like  a Blob storage like natively.  So this is as integration queue to receive the measure data generating from the upstream  because we still need a set of processing layer to stitch those events together and enrich  those events.  And since it's a reporting system mostly, then I do have a fairly sophisticated like  a data access service and I would not cover much in this talk.  And now, I mean, the elephant in a diagram is excited as postgres cluster.  So as part of the design, I should fold this like a data like preparation, I mean, kind  of large scale like a map reducing job into the site as a postgres cluster itself.  So that's why I drew, picked a photo of this, I guess, here at the iceberg, right?  Because there's so site has helped to actually extract a lot of complexity.  So the one added advantage with this approach is also unified the real time and historical  query part, right?  Because there is no more, so I just learned architecture, which is for whatever reason  getting very popular for analytics like services.  So here's some of the details about the database cluster itself.  I'm on the site as enterprise and the postgres 11.3.  As you see, I tried, I'm fairly up to date, right?  So the thing is, I actually need every improvement from postgres, even from minor releases to  gain the performance edge because, I mean, the performance means like cost reduction.  So I started this project about two years ago, I think.  So the site is around time was shipping like 7.0.  And also the postgres 10.0 just as released.  Or I should put it in another way, if there's no site as 7.0 or postgres 10, because to  have those as partition, like declarative partition table support, I would have used  a different solution and then like I say the postgres space.  So some minor details about the production cluster itself.  That has over like 2800 cores, have 18 terabytes of memory.  And the content is about one petabytes of the disk space.  And this is keep on growing.  So I have to do some pulling to suppress people's data to make sure they really need.  I think probably by the end of the year, this space will be doubled.  And so like data-wise, this cluster is hosting about 20,000 measures.  So by each measure, it's like a multi-month time series like data.  So the new data coming is about like five terabytes of data per day.  And thanks to the GDPR requirement, it also need to delete five terabytes, I mean, first  high bytes like data per day because of, I mean, this is actually creating some additional  like challenges for some other type of like system.  I'm going to talk about I have two main type of query.  And one is a little bit cheaper.  The other is actually more expensive.  The cheaper one, the P75 query latency is about, it's actually less than like, say like  90 millisecond.  And for the more expensive one, it's about 200 milliseconds.  Of course, like for such a large-scale system, we often time need to support, like I say,  some other data science workloads and stuff like that.  We, from the front door query, we do allow, like I say, the four minutes up to four minutes  of queries.  And so as people can do fairly less, like sophisticated analysis with the data.  And if you need something longer than that, so like we have a backdoor, right?  So we can make the job to running up to two hours.  And this is how I set the locked time out because like we do not want, let's say, job  running for tens of hours.  And this is the way, I mean, so this also forces people think about how to like, so  partition and slice and dice data, right?  So into more like manageable chunks.  And in fact, this is approach we use internally for our own like, maybe reducing job.  Just like, it's a measure that kind of like the fish from the sea is come in all shape  and sizes, right?  This is a, let's see the photo of the Tokyo fish market.  So I mean, since there are, I mean, the country, there are about 10 types of schema and different  types of keep on coming in.  So in order to make the processing job same, right, to a tag manage complexity.  So this is a perfect case to using like JSON.  So as you can see in the slides, I have a JSON beacon column.  And one advantage of using JSON is also to allow, have per measure like a schemas, right?  So because they have come, come special dimension in these days because like, and I mean, it's  a quite a goofy to a, to a large extent.  And this schema has two timestamp.  So the streaming time, the stream timestamp is actually say the processing time.  So I mean, it's the current hour.  So it'll be the current time.  So the windows measure data has a one feature is like I said, the data could be always edge  to delay, right?  So if you have a laptop, you use it before the telemetry get uploaded.  So people just close their laptop and say on vacation or I mean, simply it's a long  weekend.  So the data could be delayed for a couple of days, sometimes to couple of weeks.  I think just as a business logic, so we actually allow the data up to say, say like 15 days  like delay.  And this table also has a hash partition key.  Like, I mean, it's used by Citus to do the hash like partitioning and it's can partition  the data, say the relatively evenly, so over the cluster.  And then also using the native postgres partition to do the data processing on the event time.  So this will allow us to do this like table dropping, right?  Because otherwise you have to, I mean, just delete the rows is not going to cut it.  So the data is coming mostly on the hourly like basis.  And we have some newer data now coming as a minute, like a time grain now.  Let's also want to have one word about the ingestive program.  Originally I thought I need a small cluster to read in terabytes of data per day and like  say, say, shred it and then sort of side us and copy into those like postgres like workers.  And then I just said, hey, what if I just write a simple like a goal program, multi-thread  goal program to do the same thing?  Well, it turned out, we actually shipped it in production.  It's been used for almost like two years.  And it's actually fairly impressive to see that the heap can go to say, so at least 100  like gigabytes and it can run for months, I mean, without stopping.  So it's actually kudos to this goal of EMT.  Okay, so after the data getting ingested, so I'm going to have like say a lot of fish,  I'll get a metaphor here.  As a typical, say the sushi chef, before they serve the sushi, they need to prepare  the fish into some like more manageable, say cut it into like manageable chunks, right?  Well, in my case, my knife is like such a scientist, it's a distributed absurd statement,  right?  I mean, it's just a vanilla postgres absurd statement.  And I just said, it's actually enhanced it.  So it worked in a, you know, like a distributed fashion.  There's two ways to cut the data, right?  So one is, say the reporting table is shared the hash partition key as say original income  in the measure table.  So it's like co-located.  So when you do this kind of cutting, there is no internal traffic.  So it's actually fairly efficient.  So and everything was done in parallel on each of the worker nodes.  And of course, this is not always the case.  I mean, we do have fairly rich report and then we need to pick a different partition  key.  In this case, the data will go through this side-to-side coordinator and get reshuffled  to the worker node again.  Because on Azure, we have like 40 gigabit network, so the whole thing was, I mean, very  efficient.  So the algorithm-wise is you can think so ecologically, and it's about one billion rows, right?  So every hour we have about one billion rows.  And we partition it into like 15-day buckets, right?  And then we just parallel insert into the 15 days, right?  So because we have different kinds of, say, reporting tables, say, I just picked a sum  of my random number here, like 10, I think we are at over 10 kinds of reports now.  So you can think about it as almost on this cluster, every hour I have 150 jobs to do  this massive updates, right?  And it's a serving query at the same time.  So I mean, it's actually fairly impressive.  So the postgres along with, let's see, like, I just can do this.  And so each of the daily table, I mean, say, the reporting table has been heavily, like,  indexed, right?  So some of the daily table has over, like, 50, like, indexes.  And I do use partial covering index a lot.  And this would allow me to the index only scan.  So this is the case.  I'm trading disk space for the performance, right?  So in a cloud environment, even though the CPU performance is not improving much year  over year anymore, there's still lots of room for the disk space and networking.  So with all that work, we're actually ready to serve, like I said, the queries.  So on this side, I show there's two types of query.  The first one, just a vanilla group by query, right?  So this particular example I show is how we calculate the averages.  And this query, we're getting more complicated.  When we need to calculate, seems like I say, like a percentile.  Because in this case, instead of a simple sum and count, I'm getting an array of sum  and count, right?  So basically, I have to write the code to leverage the feature from Hsidus to do those  types of distributed computations.  And then, fairly recently, we also had a histogram, like, data types coming in.  And also the dimension columns is getting more complicated.  So originally we started with scalar columns, like integer strings.  And now we have things like arrays, like a list of the experimentation IDs and stuff  like that.  And then, so the first type of query, even the first type of query, getting fairly complicated.  So, think about the first type of query, do the average calculation, right?  Because statistically it has one problem.  It's actually subject to the offlier.  I mean, it's actually a bias.  So metric value will be biased to the outlier.  So in some cases, because it's, like, we don't have such a huge population, a lot of the  devices are coming in various test labs, right?  So some test lab in Southern Taiwan could be rebooting the machine 10,000 times a day,  right?  So if you want to count in machine, see, reliability, this one at the outlier would totally see,  like, skew, you know, actually a metric and would create an alarm, I mean, on the dashboard  to say, well, there's something wrong because the machines seem to be constantly, like,  say, rebooting.  There are a couple of different ways to tackle this kind of, say, like statistical anomalies.  One is, of course, you can use machine learning.  Well, the problem with using machine learning is you have to know the, like, distribution,  right?  And we have 20,000 measures that you have to know, like, say, like, distribution for  each other measure.  And also the user behavior changes all the time.  So you have to keep up with understanding, okay, what the distribution are.  Well, clearly, this is not very, like, say, like, scalable.  So the, so this is the second type of query coming in.  Second type of query, I mean, from the statistical point of view, is actually, like, a population,  like, distribution agnostic.  So the way to achieve that is, is introducing an inner query, right?  So the first one is, grew by of the, say, take an average, for example, you grew by the device  ID.  You calculate per device level average.  Then you calculate the average across all, like, devices.  So to some extent, like, this query, in turn, we call it one device at one volt, right?  Because this query itself does not have any of the data assumption.  So it can be applied to any kind of, say, like, a measure.  So I want to, like, talk, pause the scene for a second.  So the device ID, as I mentioned, is 800 million, right?  So this inner query, the factory, like, grew by 800, potentially, 800 million, like, device  IDs.  And I think when I sign up, I mean, with the team to do this, this is actually the query  I look at.  So I do not know, so I do not know post-grace.  I was just looking for ways how to, I just speed up this query, right?  So this is also the query killed the first two generation of the solutions.  So I hear some of my findings and some of them done by my colleagues because Microsoft  is a pretty big company.  There's a lot of people in similar, like, space.  I think because of the data warehouse and analytic service, it's not really a new problem,  right?  Especially for the past four or five years, there's many, many, like, solutions being,  like, down, right?  So even Apache has quite a few, like, projects, right?  So the key link is from eBay.  I mean, it's doing mostly doing aesthetic, cubing.  And recently, they have a way to do introducing the Lambda architecture to do in-memory and  aesthetic, cubing, like, together.  P-Node is a project from LinkedIn.  Now it's also part of Microsoft.  In fact, I worked with P-Node engineer for about two months to see kind of whether they  can handle that particular query type.  And then we have things like a Druid.  And quite recently, because the data break in Spark has become a first class, like, citizen  on Azure.  So there are some teams want to try those kind of queries on the Spark Azure cluster.  So I've been, like, looking through and evaluating these Azure solutions.  So here's a sublist of the issues I've seen.  One is, like, even though everyone calls themselves, like, SQL engines and stuff like that, I mean,  they're not really true kind of mature SQL engine compared with the engines, like, I  mean, from, like, say, like, DBMS.  Right?  So say, take P-Node, for example, it cannot even do that, say, the Inner, like, subquery.  So I have to let along to the hard cardinality one.  So the hard cardinality would have an issue that you won't have enough, like, so, like,  memory, then you have to spill things on the disk.  And I mean, the data could be actually skewed, and then you have to handle that, too.  So it's actually, so, I mean, that query looks very innocent, but it turned out to be a really  hard inquiry to take.  And then, of course, like, I mean, with new dimension types, like, arrays, that stuff like  that.  So I think most of the solutions, I mean, existing like open source like solutions from Apache,  like, just, I mean, cannot even touch it.  Also I noticed, like, most of the, say, recent, like, analytic service, all using column store  of some sort.  Right?  I would say it's almost like, I mean, they're depending too much on the column store.  I could say using scanning, because JSON data type, I mean, have per row kind of, say, schemas  getting quite like a popular, I mean, kind of whether we, as a data guy, we like it or  not.  Right?  So for Windows major data, those are, JSON typically embedded something called, like,  so those are good types, which is just horrible for any column store as a compression.  Right?  I say that because I saw many column store, I evaluated, maybe, say, column stores.  And since then, since they cannot compress much, and then they have to do a lot of scanning,  and then when you have a few concurrent query coming, well, one thing we realize, even four  or five concurrent query just suck away the system memory bandwidth of a physical machine  or a VM.  Right?  So once the system memory bandwidth gets suck away, the machine is dead.  Once a few machines are dead, the whole cluster just, I mean, becomes, say, very unresponsive.  And kind of the other thing just from the, say, the production experience is quite often  I realized we do need updates.  Right?  So for the finite set production experience now for almost once or twice every two to  three months, I really need update because they're from upstream, people have a bug,  and then people give me the wrong data.  Right?  And so causing the production issue, give me the billions of billions of rows or wrong  data because, I mean, okay, so either we kind of fix, say, the service, right?  So you have to patch up and figure which time range is special patch, say, the service  with the business logic.  I mean, if we bring down that, that means first of all, you have to code it.  Second is you have to have a downtime, right?  Because you have to, like, swap in, say, the new service.  And of course, you have no idea why you need to swap out.  So the cheaper solution is actually update data.  Right?  So, I mean, with Sinus and PostgreSQL, you really take, I mean, for the few cases, I  encounter, take about one hour to update two to three billion rows.  And so the outage is kind of more or less, so I last for about one to two hours, which  is okay.  And I think because of the modern, the new analytics service being built, they always  have some, again, I call it, say, accidental complexities, right?  So you need a high-end Spark and cluster to prepare the data.  You also need to, I mean, even a cloud environment because it's tied into HBase.  I mean, in this case, like I say, like a key link, and you have to swap on your own, like,  HDFS.  It's kind of why, right?  Because these things are designed for bare metal system.  It doesn't make much sense to run in a cloud environment, right?  And quite often for the database, people that, I mean, then we need to run like a zookeeper.  So I, in my previous role, when I'm working for the being infrastructure team, I do have  a team kind of working for me to maintain, to keep the zookeeper up.  It turned out, it's actually not that easy.  So basically this time around, I say, okay, I'm just going to go into full cloud native  and just use the fabric features and end result, like I say.  So using like zookeeper or the ETCD, those kinds of things.  Okay, now, actually, it's close to the end of my talk, right?  So if the Postgres and Citus and Solution sounds so great, and why a lot of people, I mean,  if it's especially start from new, I mean, Postgres probably is not going to be your  first choice to build analytics, large-scale analytics service, right?  So it would probably most likely pick Druid.  Like I say, I think even NBNB is actually picking something like Druid.  I think there's, I've been thinking about this for some time.  I would say like the Postgres SQL and the SQL in general is marketed towards like DBAs,  right?  It's not really marketed towards actual programmers.  So if you're a real programmer, you write procedural code in all languages called Java  and Scala, right?  So you suck the data out, you do bunch of processing, and you write, say, the data back in.  So the SQL itself, I mean, it's being particular, so Postgres SQL is actually very powerful.  But the declarative programming is kind of unnatural for a lot of people.  So it takes time to practice.  So this is some of the slides, I mean, some of the points I have summarized over the past  two years.  So to compare with a normal procedure like program, right?  So the first thing you need to come up with is like data structure.  So in SQL, that is like a table, right?  But of course designing a table is not easy because sometimes you had to, quite often  you had to think about the indexing, right?  And also because the table could be huge, right?  It could be, say, billions, hundreds of billions of rows.  And then you had to think about how do you actually partition this data like properly,  right?  And the other thing is very often, even when we use internally, right, you have to, quite  often you have to scan the entire range, all the data in that range, right?  Postgres doesn't really have a particular efficient way for you to do the paging.  And then it's basically you have to write your own custom paging.  So it works out fairly well, but if you know what you're actually like doing, right?  If you do not know what you're doing, then you're spending like the job cost around  just for hours.  I mean, I should say very slow like performance, right?  And the second thing is about this algorithmic design.  So SQL let you think the problem like differently, right?  So in particular with the Citus, you have to think about also how the data get to move,  I mean, so like among the nodes, right?  Because you want to improve the IO efficiencies.  So this kind of like programming sometimes is not easy, right?  So say for example, because of using Citus as a distributed SQL, so all the, I mean,  even the percentile, the building percentile function doesn't work anymore, right?  So basically you have to think about ways to implement it.  So for the past two years, I personally implement like three different edge versions and try  to fit into the three different types of data characteristics that are actually different.  Right?  So sometimes you have to make certain like trade-offs.  And also compare with the normal program execution, there isn't no profilers.  I mean, yes, sure, in love and learn, I say, explain a lot, right?  So, but sometimes it doesn't have enough of the details.  So I didn't realize why it started.  I just said, hey, I just need to grab all the IO, low-level OS level IO counters, memory  counters, the connection counter into a SQL table.  And then I graphed it with another open source project called Grafana.  And it can show me almost, I mean, once I start refreshing every five seconds, I can  almost have this live view about the system.  And what turned out, I build myself a visual debugger, right?  So for the past two years, whenever I run queries or any indexes, I go to my, like  see the Grafana pages to kind of see what's happening, right?  I mean, this kind of helped me to tune the algorithm to understand the data actually  better.  I would say the other one is actually watch out for the author of ACUM.  I didn't really pay attention to this about, I mean, until one and a half go, and I realized  like there's small disk kind of leakage, right?  So there's always using, I mean, I do a lot of calculation stuff a lot.  So I might want to say like a numbers guy.  The number just doesn't really add up.  So why I'm using model disk space, and then it should.  And then turn out, well, I need to dive the auto vacuum, because if the thing cannot keep  up, if the auto vacuum worker cannot keep up, and then so the disk space will just add  to grow.  Since I have like a daily partition table, it will add to grow for a few hours a day  and then stopped, right?  Because it's switched to the new table.  So I think this is kind of one of those as a gorgeous, I mean, not to say the natural,  I mean, if coming from the all procedural programming in the background.  So I will just talk about some of the potential suggestion to the Postgres itself.  I think the Postgres 11 does have a runtime partitioning pruning.  What I found is the performance has more to be actually desired.  So I had a table, I mean, it's a six month table, so 180 days.  And I realize, even though it's due to the runtime partition pruning, the performance  is not great, right?  So it's because I want to see how can we improve on that.  Since I, that profile said queries, and then I noticed, I would say over 90% of query is  hitting this, I say monthly, right?  I mean, it's actually, then I just created a 30-day table, right?  I just, I have this space to spare.  So I just created a 30-day table to route a query to the 30-day, most of query to the  30-day table.  And it turned out it's improved the performance by three times.  Well, I could take that.  So the second issue I encounter with a sort of a Postgres is, because I have to use a  PG Bouncer, right?  There is no building connection pool like managers.  So there's two use cases from my point of view.  One is to the connection like throttling.  So the second use case is, since I need to drop those partitions in a very deterministic  fashion.  If I need to drop those partitions, I want to drop it within a minute or two, right?  So in this particular case, I have to use a PG Bouncer to turn off the set queries coming  into those tables.  And then I just drop them, because otherwise, you can always wait in for the lock, right?  So the problem is, if the query is not stopping coming with different pattern, then this kind  of like, say, maintenance stuff is becoming non-deterministic, right?  So I think if having some building connection pool like manager with some more fine grain  of a lock control, it will help improve those maintenance jobs.  And I think now I'm just entering more, say, speculative suggestions.  So on the shared buffer, right?  So the performance of the Vanessa is heavily depending on what pages get cut into the shared  buffer.  So there's no fine grain control over the shared buffers, which indexes can get loaded  into the page.  In order to improve the performance and also to have an external view control about that,  so where to write the customized one-map code to a periodically just rerun a few queries  we can see, and then those indexes will get loaded, right?  And the other thing is, I'm thinking, is as a machine, getting bigger and bigger, right?  So I mean, on a full terabytes, like a memory machine, probably you can dedicate one terabytes  for the shared buffer.  So the idea is, like, you would, I mean, you want certain pages kind of like, I say, never  get a page load.  So since there is more CPU to spare, so why not take some code index page, just compress  them and store them in a DRAM.  And then upon access, just as you decompress it.  So I think the PostgreSQL up until now is still using this volcano-style query processing  as one vector at a time.  And for analysis queries, it would be benefiting greatly from a vectorized query processing.  So I talked to a few PostgreSQL committers.  This thing is not really high on the list yet.  I think the other one is because my table right now has, let's say, lifetime for about  like six months, right?  So I mean, I stored all on the premium storage.  So as I need to extend, say, into one year and two years in the past, right?  So currently I have kind of pretty many ways.  So I just dump all the data on disk, reload it into another table in, say, another table  space, which is on a cheaper storage, right?  So ideally there will be kind of a little bit more automated or a better, more efficient  way to do this if this features a larger building into this, I could say, PostgreSQL to allow  the partition table to switch to table spaces.  So the last point I would like to make is because the distributed query execution, I  would say almost, I would say many steps function in PostgreSQL, it just doesn't work.  So currently, I mean, just you have to write your own.  So I was hoping that there will be some, say, standardize way of, I mean, say, like, doing  this, okay, because I can see a lot of PostgreSQL coming with its own distributed query execution  and none of those steps functions are, say, portable anymore, right?  So I think this, well, it could be a big problem down the road for analytics workload.  So I hope I'm on time.  So questions?  Oh, yes, please.  Okay.  Okay, well, this is a very good question.  I'm not sure if I need to repeat the questions.  Probably I would just say, do not.  I mean, how do I, say, go protect myself against, say, like data corruption?  I see initially I was fairly confident, right, because I only need to maintain, because once  I have device ID, I only need to maintain a 30-day table.  So I can just drop the table.  So I only have one month's factory worth of, like, say, like, life data.  So the way to protect against that is because I have this, like, say, like, measure like  data lake, right?  So I can always, if data corrupted, I can always reinjust it.  Now my cluster lifetime for, I mean, non-device ID based data extended to six months.  So this do get me quite concerned now, right?  So the thing is there's a couple of things to, like, how much of the sale I do in that.  One is, I mean, we start to do the, say, the daily backups.  I mean, I can into the, say, the blob storage.  Well, it's, well, I mean, sort of incrementally, right, right?  So, well, because, say, the blob storage is that cheap, right?  So I think probably it's probably known that it's about $10,000 per month per petabytes,  right?  So, I mean, there's, I mean, I can just say to blow a lot of money actually on that one.  So I'm not, assume, okay.  So you get to build for all this, start or end user, you don't know much.  Oh, all right.  So this is the graph for the internal Microsoft.  I'm, internal Microsoft team, in my, say, in my group, what you pay higher price than  the enterprise customer, than a typical enterprise customer.  So I had to be much more cost-conscious than a typical enterprise customer.  And I'm also, because internal customer, we only limited to a few data center.  So not all, like, data center are available to us.  So do you answer the question?  Yes, I have worked harder.  Yes?  Sorry, again, I did get a hundred questions.  Kind of a weird question, but so something like obviously the Microsoft product has to  be on the server, like it's just not well suited to tackling the data that you have  in this model, remember?  Well.  So the SQL server itself, I mean, is a standard alone like SQL server as, so as Osgon called  out, it's not really like distributed.  But Microsoft do have, because we're a big company and a lot of people, smart people  working there, so we do have distributed column store too, right?  So I think the thing I realized, because I was a column store, I fined it, say, ten years  ago.  I think right now, because the data is so rich now, and I mean, it's kind of one trick  pony with column store and just a very narrow column compression is not going to cut it  anymore.  So, I mean, people have more wider, like, data structures, like, say, like JSON or even  arrays, right?  So, I mean, think about even for, say, array, like, data structure.  So if I have one billion rows, it would flatten the thing out.  So if the array, say, on average has ten IDs in there, I mean, just a very scary thought  to kind of having row explosion like that.  So I think because of this richer data types is actually making the traditional, like,  sort of, sort of, row the database, I say, more attractive.  And also, I think one thing did not come out, I should say, like, very explicitly, is the  event time coming, the timestamp is in milliseconds, right?  So sometimes even, say, like, nanoseconds.  However, human cannot really understand, I mean, say, like, data at that kind of time  like resolution.  So the reporting is usually on the weekly and seven days.  So it actually requires, say, the delivery.  So the one of the things I did is during those aggregation, I truncated time at, say, like,  daily grain.  So this would actually allow me to massively also compress the data, right?  I call it semantic compression.  So even having higher compression rate than if I just use a row store, right?  I mean, sorry, say, a column store because that only has the append-only strategy.  And then you still end up with lots and lots of rows.  And for monthly queries, there are just tens of billions of rows to be, like, scanned,  like, proquery.  Yeah.  Yeah.  Well, I mean, all the cooking are considered as updates.  Well, I say updates and inserts, right?  If a new row you inserted and then if you have, say, old rows already with existing  key, they have to update them, right?  And I mean, the other kind of updates, as I said, like, typically, quite often, I mean,  when you see that around data and where do you need to fix this data retroactively?  Okay.  Okay.  All right.  Let me have a look at a little bit of this.  Okay.  Okay.  Okay.  Let me see.  I'll take a look at this one.  This one, okay.  Okay.  Okay.  The second one.  Okay.  Okay.  Okay.  All right.  Okay.  Okay.  All right.  Okay.  Okay.  Okay.
VeniceDB_-_Min_Wei_0_47.png VeniceDB_-_Min_Wei_48_143.png VeniceDB_-_Min_Wei_144_275.png VeniceDB_-_Min_Wei_276_383.png VeniceDB_-_Min_Wei_384_599.png VeniceDB_-_Min_Wei_600_671.png VeniceDB_-_Min_Wei_672_1739.png VeniceDB_-_Min_Wei_1740_8543.png VeniceDB_-_Min_Wei_8544_8639.png VeniceDB_-_Min_Wei_8640_14135.png VeniceDB_-_Min_Wei_14136_14339.png VeniceDB_-_Min_Wei_14340_14495.png VeniceDB_-_Min_Wei_14496_18095.png VeniceDB_-_Min_Wei_18096_18155.png VeniceDB_-_Min_Wei_18156_19247.png VeniceDB_-_Min_Wei_19248_23627.png VeniceDB_-_Min_Wei_23628_29039.png VeniceDB_-_Min_Wei_29040_34043.png VeniceDB_-_Min_Wei_34044_41363.png VeniceDB_-_Min_Wei_41364_46055.png VeniceDB_-_Min_Wei_46056_50999.png VeniceDB_-_Min_Wei_51000_51047.png VeniceDB_-_Min_Wei_51048_51095.png VeniceDB_-_Min_Wei_51096_51143.png VeniceDB_-_Min_Wei_51144_51395.png VeniceDB_-_Min_Wei_51396_51527.png VeniceDB_-_Min_Wei_51528_51803.png VeniceDB_-_Min_Wei_51804_51947.png VeniceDB_-_Min_Wei_51948_52103.png VeniceDB_-_Min_Wei_52104_52595.png VeniceDB_-_Min_Wei_52596_52727.png VeniceDB_-_Min_Wei_52728_53123.png VeniceDB_-_Min_Wei_53124_53255.png VeniceDB_-_Min_Wei_53256_53663.png VeniceDB_-_Min_Wei_53664_53747.png VeniceDB_-_Min_Wei_53748_53927.png VeniceDB_-_Min_Wei_53928_54059.png VeniceDB_-_Min_Wei_54060_54179.png VeniceDB_-_Min_Wei_54180_54443.png VeniceDB_-_Min_Wei_54444_54851.png VeniceDB_-_Min_Wei_54852_55043.png VeniceDB_-_Min_Wei_55044_55103.png VeniceDB_-_Min_Wei_55104_55151.png VeniceDB_-_Min_Wei_55152_55259.png VeniceDB_-_Min_Wei_55260_55475.png VeniceDB_-_Min_Wei_55476_55523.png VeniceDB_-_Min_Wei_55524_55643.png VeniceDB_-_Min_Wei_55644_55799.png VeniceDB_-_Min_Wei_55800_56063.png VeniceDB_-_Min_Wei_56064_56459.png VeniceDB_-_Min_Wei_56460_56591.png VeniceDB_-_Min_Wei_56592_56999.png VeniceDB_-_Min_Wei_57000_57119.png VeniceDB_-_Min_Wei_57120_57527.png VeniceDB_-_Min_Wei_57528_57659.png VeniceDB_-_Min_Wei_57660_57935.png VeniceDB_-_Min_Wei_57936_58871.png VeniceDB_-_Min_Wei_58872_59003.png VeniceDB_-_Min_Wei_59004_59135.png VeniceDB_-_Min_Wei_59136_59675.png VeniceDB_-_Min_Wei_59676_59771.png VeniceDB_-_Min_Wei_59772_78949.png