 Good morning everyone, you are like a like my secondary school class So We founded this company like one and a half year ago in in the Bay area Both of us worked in database for a long time. We both came from University of Wisconsin medicine worked with DeWitt and Jeff Norton, you know those people So we worked in multiple database companies before and And you can see that I think the only missing experience here is Oracle Almost everything is here So I'm going to talk about data warehouse, right? So in the beginning You know if you if you run a website Pre-amazon days what you're going to have is a postgres database And then you're going to have app servers on top and some PHP code, right? But almost any startup start with this and then you discovered that you know business was good now you One single box is not enough. It's not fast enough. It's not It doesn't have enough IO bandwidth whatever so you start to partition your data, right? you start to say you either partition vertically by by business segment or you shot it, right and In some cases, it's not So in some cases, it's not possible or easy to shut your data So if you run a if you run if you are someone like Salesforce It may be easy to shut right because you can say this customer go to This PG that customer goes to that postgres, right? but if you run a Marketplace like artwork or Facebook you're going to join data between many people and You cannot put everyone who is connected in one box and everyone who is connected in another box, right? because there's always interconnects so so you add multiple postgres boxes and You you serve your data and then eventually You will need a data warehouse, right? Because there's going to be people asking about analytics How do I know how many people sign up today? How many people sign up? Last month versus this month. What's my year over year increase in in revenue? By by month by year, right? So you're going to need a data warehouse. So what what is a data warehouse, right? So In in in a very simple terms, these are the two different things here, right? Think about this this is my sequel and This is data warehouse Postgres is somewhere in here. I think now the technology makes it fast enough and how that it's going It can reach over on the other side But still this is the landscape, okay? So the difference is in OLTP when you're serving your web pages, you're going to retrieve single rows You're going to go, you know, this guy locks in I want to check his password. Okay, give me his name. I retrieve his His record via index and then I compare the the md5 hash or whatever of the passcode of the password, right? So you're going to run queries in in milliseconds. Okay, and each of these queries are single tupper retrievals you cannot afford to do multiple tuples or You know hundreds of IOPS just to serve one one web request will kill you essentially, right? Okay It should be it should be simple stuff for you guys and Your database is probably less than a terabyte It because you only store data that's relevant now So if I if I update my my address You're not going to store my old address. You're going to override the address field with my new address Right, so that's that's in a OLTP and then you know, you have you have databases and then you have multiple app servers That are connecting to the database, but you're going to get like hundreds of connections, right? And depending on how many apps servers you have we're going to get hundreds of thousands of connections And then you're going to provide hot data to to to the web users. Obviously, there may be some man cash D Instances in front, but essentially the data is is quite hot and you want to keep the man cash D Data up to date as well. Okay, but if you look at the data warehouse side We don't we usually do single topple retrieval because it's it's not interesting, right? We want to see We do big table scan because you know We retrieve many many right almost a whole chunk of records and if you have to go through indices The price you pay to go through a B3 and to retrieve The each topple it's it's it's way too much. I think you someone came up with racial You know if you have to do index retrieval you might as well retrieve 18,000 Tuples would be equal to Retrieving one index if you want to retrieve one roll through index You can do it in the same time as 18,000 tuples right in sequential scans And then the queries run for a long time, right? These are these are these are queries that tell you business Intelligence data, so you you let it run a few hours And then you get a report, right? It's not it's not interactive And then you have many years of data like I said just now if I change my address in the data warehouse you would store My previous address and my current address In in the database, so it would be it would it's what they call a dimension table slowly changing dimension table so that it also keeps a history of of the Everything that happened in those days Okay Can and and then you have a handful of connections So you have you know you can afford to buy Thousands of copies of tableau right you probably have 20 analysts and each one have a copy of tableau And then they just connect to the to the database Okay, and then this this data warehouse will provide a summary or report to users Questions so some of the vocabularies that we deal with between the two is you know on all TP you you have indices You have referential integrity. You have TPC see You know transactions per seconds You know if you run queries that are a few hours that that takes a few hours You don't care about transaction per second, right? You want to make it run small run faster and then some of the database, you know That that does tip or TP pretty well this that there But in in data warehouse, you have windows functions. You have roll up you have Q, right? And then you have you will partition your fact table. So a fact table is I think about If you think about a retail Walmart or or a target store so you're going to have The fact table basically store the transactions. So this person This ID by a b c d e f g right and how much how much how much so and each of those is a is a role and One one slip is an order And each item is a role Okay, and then you basically store those in in a in a partition by month or by day depending on how back how big the stories and you store like 12 months of it because you Well these days and maybe you can afford to store more but usually in the old days days they store 12 months of data so, you know in February I I store all all my transactions in the fact table in the February partition and then in March I throw away the last year's February and I create a new March partition and then I store my new record set okay, and Then you have ETL ELT, you know extract transform load and extract load and then transform MPP is a massively parallel database you you you basically It's like shutting but you can do joins in between Okay, and the data is partition and it's not It's it's a shed nothing architecture Okay, and then the the benchmarks are TPCH and the new one that came out TPC DS so this one has about 22 queries and this one has 99 queries and I don't know how you How you talk about 99 queries, right? And then some of the MPP databases are our vertical green plum exadata from Oracle and Terra data Okay introduction So what what is analytics right? So so you have your Postgres Serving your website nicely and your analyst was running their big queries here And when they run their big queries here, you you disrupt the the web the web traffic right because these big queries are going to To page in so so much data that it's going to kick out your hot data and then you And then you run into trouble serving your website and then the CEO says no The website is more important than analytics, right? So now you you go out and you buy a green thumb database or vertical or some of those MPP databases and then you do synchronization or Loading between the two right and then you let your analysts access green thumb DB using You know hi tools like Tableau Okay So what do we do here other than just copying the data, right? If you just copy the data, it's not very interesting. You have the whole copy of your transactional data Okay, but it's not very interesting So what you want to do is you want to use this processing power that you have now to tag your users So, you know think about American Express card, right? You have platinum cards. We have gold card You have silver card. So on your website, you probably have gold users platinum users and Normal everyday users so so using some business rules you can write some sequel on on the green thumb database or on the MPP database that you You Using some some rules that your analysts define you can you can say this user is gold and this user is silver Oh, and this user is is a fraud user and you know, I don't want him to log in ever again. Just just tag it right So tagging so you once you tag The data now you basically have intelligence, right? You know someone who is more important than the other and who you you really don't want to see anymore and who buy What more at which time of the year, right? So but then those those data sitting here doesn't help your website Okay, so what you want to do is? take data from Postgres move it into MPP run your aggregates or your your Your your rules to to to tag users and then you send it back over there So that your website can can your app server can you treat them? right easy so far huh, and then you know it it's now sexy to put things in AWS and So what you want to do is So, you know you start to put Postgres RDS on Amazon and you start to expand over there right and Then the data has to come and sink down back to to Grimfarm and push up all the way right so now it's Kind of complicated and and you may you may run into the bandwidth problem when you push data up or blow data down Yes Oh, yeah, there are a lot of tools that help you do that So you can either do you can either do it low tech You know you for each row that you modify you put a timestamp there and then you say you know I I delete everything starting from midnight of last last night and then reload everything that tag with Sometimes times that are bigger than midnight of last night, right? So That that's the low tech way and then the high tech way you buy some some Some software from Golden Gate or and then they would help you do all this It can be almost real-time now Yeah Yeah, there's something called lambda architecture and people kept talking about it And so there was a lot of pressure to do it real-time, especially when you're doing fraud detection You wanted real-time So so now you have this big investment in in green farm or terror data, but You're doing ETL on on on that machine, and it's owned by the IT group, right? It's owned by Basically data warehouse professionals So they define the I Talked about how you roll over the fact table the partitions and all those stuff, right? Those for those require people watching the process. So it's basically managed by a bunch of professionals that They really don't want people to touch it if you touch it now. I cannot do my ETL and everything gets blocked okay, right so so what does this mean you got the data there and Anyone else cannot touch it except the the bi people right except the the IT analyst who is doing the aggregates So it becomes essentially a data jail Data goes in there and just say it's there. Okay And then the marketing department comes and say hey, I really like this software You know looker or some some bi to set that that is different from what you're using But I prefer that I'm the CMO. I want to use that. Okay now. What do you do? So we tested something called a lot. It's a What we call a large optimized foreign table So you can synchronize the data over there. Okay, it's a it's a It's a column store that is remote and it's file based Okay, I'll talk about that later. And then you can each you can give each department a BTS DB Right and and then they can access that Okay, so essentially the data comes from here got washed and Analyze here and then got pushed to to a some remote Storage and then you know your departments can can start to use it without impacting This big investment here. Okay, so a little bit about loft. Okay, so so it's a what it's you can think about it as as a pocket server It saw it saw the data in in files so that you know You don't have to lock the table lock the file before you unlock the tables or records before you change stuff Essentially, it's just in unix file you you create a new file you rename it and it's atomically replaced, right? So the the files are partition aware So, you know, I just now when I say the fact table that is the fabric called the fabric partition gets written completely before it swaps out the old partition and Then we have something called simple pocket format. So we we did some work on pocket and the format is is we use the C library that they provide and and it's it's spending a lot of CPU in in in a Disassembling the columns so we wrote something that's a bit faster and probably open source there And once you have the files or partitions or data or columns in in loft How do you access them, right? So in Postgres we wrote foreign data wrappers So you can you can create a foreign data wrapper on Postgres on Vitesse DB Oh on Vitesse DB only that connect to the loft and then it you know, and then you just run queries and It would retrieve them. Um, so Vitesse DB Little bit is basically Postgres SQL for data warehouse We take we take Postgres we put in our execution engine. The execution engine is what you So you think about the query going into Postgres you get you put it put in the query in SQL It goes through the optimizer it goes through the parser and then the optimizer the optimizer gives you a query tree a plan tree Okay, and what we do is we take the plan tree and we try to compile it in using LLVM We do some data path optimization and if we are able to do that what we get is a Function that we can call and that function will just execute the whole query Directly right if we cannot do it then we pass it back to Postgres and Postgres will do it iteratively So if Postgres will say we'll go through the plan tree like one level at a time If you're familiar with how Postgres execution engine work, it's it's quite quite inefficient So we can do scan as fast as 180 Sorry, 18 gigabyte per second. Okay So how do we how do we do this in If you look at if you do a scan in in Postgres, you'll see that you can scan about 140 megabyte per second Okay, and that's because when you when you scan a table when you read the table You're going to get a tuple in serialized format, and then you are going to this I think you talked about it yesterday the day before You're going to go through a lot of processing to to deserialize deserialize the tuple Okay, in our case, we know exactly, you know, you do a select on This table and with only the second and the fourth column We know exactly which columns are required and we will generate the code to to just retrieve the second and the fourth column and then and then we skip the rest and There's no if statement in there. It's just go get the second get the fourth get the sky get a skip the first Get the second skip the third get the fourth, right? So that that's that's how we get to that speed and we can do tpch 100 In approximately three seconds Postgres will take eight eight point five minutes So this is tpch Q1 If you're interested so when I say about when I talk about fact table line item, that's that's the fact table in tpch in this case is there's about 66 million rows and I Don't know how many gigabytes, but it's going to scan this table and it's going to go and do you know many sums and many Averages and and accounts. Okay, so this is this query will touch millions of rows Not not the usual OLTP queries This is this is the timing for the test TV If you run Postgres and this is not exactly fair, right? Because we we do we do LLVM compilation and we also do multi-threading Okay, so on on this box. They are like 12 threads running And so Postgres single threaded you get about 163 megabyte per second on on on the test DB Vitesse DB is a hundred percent compatible with Postgres. You can so we also have the Postgres executable And if you like you can just copy this Postgres executable into your current Postgres instance, and it would just run As long as the version match So that's the timing we have for the query just now 93 93 we're going to go to 95 pretty soon. So now this this Picture is getting more a lot more crowded, right? So you have this guy in loft and then the and then you know companies directions saying, you know, we need to go to AWS and then you you hire a data scientist team and they are going to say, okay, I need more data I need more data and then there are multiple Basically, you know, if you if you work in the in the data IT part you're going to keep getting this request more people needs the data, right? So we have a loft to loft sync and because it's file base Basically, it's you think about it like our sync, but with some intelligence in there So it just copy the data over to AWS and then you can spawn multiple of these for each For each and and this or or data warehouse or data scientist team that you have It's pretty easy to to extend And each of them gets their own resources, you know, there's Queries running here doesn't impact queries running here But a little bit but because of the data coming over but here if you run here, you probably won't Interfere with queries running over there Okay, done any questions. Yes I mean distribution of data across nodes when you say that you're fully compatible with postgres How do you handle distribution? Postgres is single note. There's no distribution No, okay So this is so this is postgres. It's not big green Okay, so postgres is one single note and You know, if you the way we do threading is such that we don't If we have to hand data between the threads, it's going to store the processor right The first version that we did was twice lower and then you have to you have to make it Yes Yes Okay So that product we address that through our Grimplum we were talking about our Grimplum solution. So we put our engine into Grimplum. I think I have some slides here Okay, so if you're interested we talk about how how the technology works and then we can go into that Um So what why do we start with this data? It's because we thought, you know, the hardware has Obsoleted the software right in the old days everything is You are basically optimized for this speed, right? If you can if you can do less IO, you don't care How much CPU you're using you want to optimize for less IO, but these days How many of you have more memory than your data? So So if you go to Amazon, you'll see people running on RDS they have more memory available than their Their data set. So essentially everything is is in cash Okay, so if you now, you know optimizing for IO doesn't Buy you much anymore, right? So, you know, that's why when you see today Intel keeps giving you faster Faster more and more core CPU You don't see any improvement in your in your database, right? but because that's because the The software was designed 30 years ago for to solve a different problem. Okay, so, you know, you have it has to be changed So what we do is we have we do cheat compilation using our VM. You know, we do simd Optimization we have data path optimization. That's harder to get into essentially you have to flip the query tree around I think if I think some of the people starting to get it if you look at spark 2.0 They're starting to do some of these And then we have column store and What we do here at the test is we emphasize Compatibility, right? You don't have to change a line of code Everything is the same right to Postgres to all to Grim Club You just drop this thing in and it just runs much faster Okay, so so we we start after Grim Plum when open source we start to put our Engine into Grim Plum and we I think we're going to release release it this month We're seeing some aggregates because we haven't put in any threading. So it's single single It's still single thread, but each thread is going to run like five times faster than than Grim Plum for Q1 So this TPCH 10g that we did on between between deep green and and and Grim Plum and You know Q1 we are five times faster Q5 is a six-way join and and we're three times faster That's a no picture. Does that answer your question? We're not touching Postgres XL or X2. Yes You said that when Grim Plum went open source Is that mean your code that are your code is open source or does that work? No, we fork it It's the same as Yes, same thing as Postgres And you know many people have asked us why we don't open source and if you think about it Our code is in C++ and even if we open source it Postgres is not going to take it, right? There's I don't see any reason. I can do some demo if there's If it's interesting We have time. How do you do that? Let's see. I have to have to do this on the fly There you go. Oh shoot. I probably have to start a new Show right There's no I think there's I think I can do it. I can do it the other way. Okay. I can do it the other way It's called flipping the display. This is an instance running on on on Google Cloud So let's see. So we have select PG size. Okay, so this gives you an idea of How big is the line item table? It's about 8 gigabyte. Okay, and then we can do a I can show you what's Q1 So I we put it into a view and that is essentially q1 right there Okay, and I can do select star from so first time 2.3 seconds Everang again, it's about 788 millisecond Okay So if I put this this is in vites And I can disable all the vites optimization by setting a gap So now it now it's in postgres. It's going to take about a minute. Yeah. Yeah, they all they all use a show So we only replace the executor. We don't touch anything above or below it Um, so there are hooks in postgres where you can replace some part of the system So when you when the query tree finish, uh, when the query finish optimization You get a tree and then in the in postgres There is a hook that calls an executor So if you if you replace that with a if you put a hook in there, it will call yours And then you can either go back or No, we we used to have an extension We no longer do that we put it in a in the binary I mean in principle, how is that like you have postgres imperial and an extension bound together or yeah bound together Bound together. Yes, but you are not giving away a separate extension We used to do that, but there wasn't a lot of demand and and it's you know For just the two of us. It's very hard to maintain So many copies of stuff Yes, uh, so basically you'll not change it The plan is the same, but uh, you need one different executor Think about this as you know, you have two cars in your garage Yes, okay, you can either pick the postgres car or you can pick the BTS car The plan is the same like like the driver is the same, but you can go Take one or the other Yes There were some obviously converting the executor to To see Required you to duplicate a lot of the algorithms of the executor. Yes We years ago We're only able to emulate some types of plans Is that security? Yeah, we we we emulate we we do almost all the plans But obviously if you want to ship a product and you have to do all the plans It's going to take a long long time, right? So so as I said, we have a failsafe if you give me a query I try to compile it and I was not able to compile it. I throw it back to postgres So now you have a hundred percent It's a totally new executor Are you still using the buffer manager? We're still using the buffer manager So if you look at the pgconf talk that I gave last year, you'll see how we do the other The way I think it's it went into quite a bit of detail how we get into Get this this kind of speed So what's the single summarizing thing which makes it so fast and so fast as contractual? so if Okay, so So you want to think about this right you think about in the old days you have disk and this is the limiting factor Right, and then you have cpu. So these days your if you have everything in memory Now, where is your disk? Now your memory is your disk Your your cpu your l1 l2 cache is your buffer pool Okay, so with the old executor you are going to do you're going to run it like in the old days You run it without the buffer pool, right? You're going to disk all the time Okay, with this you're going to hit mostly the l1 l2 cache Okay, so how did that happen? I mentioned a little bit just now when I get a tuple when I deserialize it I know exactly which fill I need And when I deserialize it It's hot. It's right there, right and when I deserialize it it's in the the the the top the the Columns that I need are in registers Right, so in the scan right after you zero you deserialize what do you do you filter? Okay, so in our case when we filter we filter the registers That's 10 times at least right No slot business Exactly It's just physics like column store. It's physics Okay, and you've been at this for obviously four years? No Two just two years I remember I guess I remember hearing about what you were doing a while ago No, we we we started the company September of 14 2014 Okay Did you work on it before then or no? No But the the skill set is very different right you need someone who knows um assembly language You know database And you need to match them together So there is a some assemble assemble assembly Inline inline for punch not on this Yeah It's LLVM So you think about the think about how many lines in the executor and we Basically wrote we wrote all that in assembly Oh Just curious like you're developing something on top of and you know you have created your own executor Any reasons why you chose Postgres' skill to build your product on or uh Because we are familiar with post we we both came from Grimplum we worked on Postgres before Okay, more more questions Or otherwise we can take more questions Or otherwise