Scaling a Cloud Based Analytics Engine
Billington, Samantha
Scaling a 100% Cloud Native Analytics Engine Your mission, should you chose to accept it, create a data storage system that can handle 200 Gigs of data per day on cloud servers with heavy analytics. GO! The architecture plan of a real time logging system built to handle 200g/day of data and hand it off from mid-term OLTP storage into and OLAP postgres data warehouse. It was built with heavy reliance on inheritance, dblink, and streaming replication.
 Hello. This presentation is about scaling a cloud-based analytics engine. It was originally titled Mission Impossible. That was a marketing person that gave it that name. It's not my favorite, so I went back to basics. You probably don't know me. I've been around the community for quite a while. I've been a Postgres DBA for about 15 years now, but this is my first talk at a conference. My name is Samantha Billington. Currently, I'm the database manager for a company called Iparadise. This project that I'm speaking about here was actually for a company called Message Bus, which was a startup that sadly didn't quite make it. We did some really cool things while we were around. The kind of things that startups do when you can start from nothing, build things the right way, and spend lots of cash. Not all companies have that benefit. A little bit about the platform. It was 100% cloud native. It was giant and rack space, not Amazon. The entirety of the company was cloud. It was not just the database architecture. It wasn't just the log. Everything that we did was cloud-based, so there was no actual physical hardware. What we found out, well, what I found out after leaving that type of environment, was it didn't really matter. What the cloud meant for us was that we needed small to mid-size instances because we needed many of them. You don't want large instances for many different reasons, but with your many instances, you get lack of control over your physical resources. You don't know what else is running on your hardware. You don't know what else your resource can tending with. Even though that's typically not supposed to be a problem, everybody knows it actually is. There were many times that four o'clock, the entire cloud becomes slow because everybody just got home from school and they're playing a game on the other VM. It wasn't pleasant. There's often no delay in expanding, so when you find your under load, you spin up new VMs, which is fantastic. That's the whole benefit of the cloud, besides not having to pay for your own hardware. It's that you just get more. It's there. You expand. You contract. But at the same time, you don't have the options over your storage, so it's the same thing over lack of control of physical resources. You don't necessarily know how your storage is attached to your database. These are the benefits trade-offs of clouds. Redundancy in cloud extremely easy, but it's absolutely necessary because it's not just a prediction that you might lose your VM. It happens. It happens a lot. It's not just a possibility that you can lose your entire cloud because if you're on Amazon, you've probably done that. These are the benefits trade-offs. These are the things you have to architect around. Unlike having physical hardware where you know your limitations, it adds an extra layer of vulnerability. Just to reiterate on that one more time, those same principles that you build around cloud, even though your necessity is building around them for cloud, it still works for hardware too. You don't want to overcommit your resources. You want the small backups. You don't want large backups. You don't want things taking over 24 hours and then skipping. These are the resource limitations that even though you're making them because you're in the cloud, there's still good rule of thumb when you're on physical hardware. What does your data look like? In our case, we were using classic transactional log data. We were an email service provider of sorts. The analytics that came with sending email were what we were dealing with. How many messages per hour? What channel were they? Were they marketing? Were they direct transactional type? How do you store that volume of data, not necessarily in flat files because we wanted the ability to comb over it? How do you get it into a database where you have full SQL to go over that? By the nature of that type of platform, logs from distributed sources, they're already distributed. We're not dealing with, say, a web application that has a single entry point for writes. We have the ability to take every single entry point where these logs are created and get them into a local warehouse. That's fantastic. That's absolutely fantastic because one of the major issues that comes with an application is dealing with a single master. But in this case, when you're dealing with distributed data, that problem simply goes away. We needed both long and midterm storage. Short-term, midterm storage was the ability to go through everything and analyze it quickly and then archive that off. For legal reasons, for whatever, this is a common practice among data is the ability to use the data that's hot and to store that data long-term. By long term here, we were meeting three months, not necessarily forever. That's another rabbit hole. Eventual versus immediate consistency. The approach that we took here worked because we didn't need immediate consistency. Again, this is log data. Logs can get held up. They can be absent for a while. They take time to write. Nothing about this type of system necessitated immediate consistency. In that type of instance, this wouldn't necessarily have worked, but again, it was a nice thing that solved a lot of problems. The important thing that we needed overall of this was the speed of retrieval because the endpoint of this data is API, customer statistics. That's a very common high demand thing for most companies is the ability to tell customers what you've done, how fast you've done it, and give them proof that what they're paying for is valid. Most marketing will just love. That's the one thing consistently throughout my career that people have wanted is marketing wants data. This is the eventual output of this is being able to tell your customer you have value to what you've done. I put the slide in here because I realized that not everyone is going to be high level here and for developers and for long term DBAs. The two hot things that we're dealing with in this are OLTP. Online transaction processing, this is the log data that I'm just going over. Things that are one time actions that you need to get to disk heavy on writes versus the analytical processing OLAP which is combing over all that data and deriving your statistics from it. The requirements in this case, we're absolutely no data loss. That goes back to redundancy. At no point in this could we have a single point of failure. It also meant that we needed the ability to change masters, to pause replication, to pause writes, to promote to any type of critical maintenance. This is a point where historically if you're not designing for this from the start, it will be a major issue in the future. That safety net, that building redundancy into your system where if the database goes away, your system doesn't crash is a huge point to build around. How many of you have come across a point where you have a web application and your database goes down and your web application completely stops? Yeah, don't you wish you had a cue or a write cache? This was from the start an immediate need. Secondly, we could not be the bottleneck. Again, this was the output of the service. In no way can your analytics stop the core of your actual service from running. If at any point the write slowed down or the cue wouldn't take answers, there was no way that we could let that stop service. Again, this is the idea that the data itself is a service to the rest of the company. The other thing that comes with this type of transactional data is out of sequence events. If a VM becomes unresponsive and comes back later, there's valuable data there. You don't want to just throw that away. It's also possible things just got paused or you had network hiccups. The ability to take that data and fold it back in at the proper point in time was a requirement. Along with that is you're going to have bugs. They happen. I've never seen a piece of software. Has anyone seen an absolutely perfect piece of software with any degree of complexity that was free of bugs? Yeah, I haven't. To that degree, what we needed was the ability to go back in history and restate analytics. Even over the course of this, there were query errors, there were issues, and Alphas always have that. That was a fundamentally awesome thing to have. This really sounds like Splunk. I mean, really, the ability to go over logs, to perform ad hot queries, to show data. Why not? Splunk is really expensive. The more data you have, the more expensive it gets. That was not an issue here. However, this was a customer facing API. That's not exactly something you want to throw in front of Splunk. It's a rest interface in front of Splunk and hand that back to your customers. It was also defined analytics. These were preset things that we wanted to specifically search for, analyze, condense, and have out immediately to a customer. The difference being your internal debugging and log combing versus what you're going to show as a service and as a business class deliverable. The tools that we chose to use, not all of these were the best choices, but some of them were absolutely fantastic. Chef for server automation, which in cloud, even not in cloud, it's such a wonderful thing to have. Does everybody here familiar with Chef? Not as many people as I would assume. Chef allowed us to bring up servers in minutes and they were identical and they were fantastic. You could say the same for Puppet or for CF Engine to lesser degrees, but some type of automation tool, especially when you're working on several different clusters, especially across different locations, incredibly useful. Liquid base. Liquid base is the schema tool of choice for myself for schema management. I assume that's fairly known here. Yes, liquid base. No. What do you guys use for schema management? SVN. Liquid base is similar to, I would suppose, Git, although we use it inside of Git, but it's specifically to roll out merges, changes, updates, rollbacks, and keep consistency in your schema. It allowed for Chef, which set up the original database and the users and permissions. Liquid base would run on top of and set up your full schema. Automated rollout with tracking. It made the ability to do single line schema updates over however many merges that you might have incredibly easy. I'm actually surprised that it's not a more known tool. The queue that sat in front of this was Hornet queue. It could have been anything that simply because we were using a Scala implementation. It could have been a Redis queue. It could have been a Rabbit M queue. It could have been Rescue. It was simply a queue. Rep manager. Looking back, that caused more problems than it solved. Rep manager was in place for promote and for redundancy with master slave failovers. I don't actually think that's necessary. Has anyone used Rep manager? What did you think of it? That's exactly what we did. I no longer use it in the stack that I'm using just because the return value wasn't high enough. Finally, Postgres, the star of the show, which I assume you all use. This is the knowing that you all not of yes, you do. You don't actually need it. I've had several conversations about that. Everything that Rep manager did can either be done in bash or with just straight Pgctl promote. Unless you really need automated promote of master, in the case of failover, you really don't need it. Even if you do, bash script it. Honestly, I would rather wake up at 2 a.m. or 4 a.m. and do a manual promote than have it be automated for me. I'm sure some of you feel differently about that, but if my database goes down the middle of the night, I want to know it. I want to see what's going on. It's more critical to me to be able to do hands-on, get up and get to a computer, than failover and possibly have more severe side effects. Writing the raw data. Distributed OLTP. I got into this a little bit earlier. By nature, all of that log file, transactional data, created distributed architecture that could be per cluster and per web host. Very elegant, very easy to create redundancy within your cluster and cross cluster. Because it's log data, it's not relational. Actually, nothing about this project was relational. That was a rather big shock for me, being a relational database person. This is the first project of any scale I had worked on that wasn't specifically tied into foreign keys and that type of data. But it was rather elegant. Your disparate event types can be written to separate instances. If you have a log sending events and a log of errors and a log of updates, all these things can go to different databases in different instances because there's no need to consolidate them back. That's done at a later level. You don't need synchronous multi-masters. Again, because you're not dealing with data that has to be relational. You're not querying a master expecting the same result from every other place. You're holding results from your single instance or for your single type. The next piece, batch process writes with copy because it was absolutely essential. That was one of those things that you think, oh, inserts are slow. We'll add a copy. It'll be an optimization for when we need it. That was absolutely not a premature optimization from the start at a very low volume when we encountered issues with copy who has not encountered an issue with copy. Yeah, that's the second point. We found that the system absolutely so do a crawl because the safety net for this was individual inserts. Individual inserts even at a very, very small scale were drastically bad. This is a must have from the start when dealing with any type of volume, but you must have a safety net. When copy breaks because copy does. We reverted to individual inserts, but that had bugs too. Developers going through this would go through the copy, list of copies, find the line that broke because copies all are nothing. As soon as they got to the logic was find the line that broke, throw it out, go back to copy. Yeah, you get immediately why that's bad, right? Everything below that line got thrown out. So beat it with a hammer. Test and test and test again because developers don't always make the right choices. That goes without saying. But in this case, it's one of those logic things that as a DBA or as someone familiar with how this works, it makes perfect sense. But if you're a developer, finding the error and moving on makes better sense. In this case, it's completely illogical. This is the thing I love most about Postgres. And that's a big statement. That's a big statement. Over 15 years in the same software, an inheritance is what I love most. It really is. So back to the disparate event types. As you're writing your types, parent per type, so my sends have a single parent. And if those sends are such a volume that you need to shard, and I'm assuming you do, shard by time was the approach that we took. I'm sure it can be done in multiple different ways depending on the data. But this was right dependent. So the volume that we chose to start with was one child per day. And that could have easily been up to a child per hour or a child per half hour or whatever your volume demanded. But by keeping those children small, you create the ability to do individual backups. Your queries are more efficient. And it creates sanity. So those individual children could be archived off one at a time, pgdump-shtable. The caveat to that is that say in three months, you go back to a single child that was dumped. And you try and back that up to go over that data again because you found a bug. But your schema has changed and back up fails. So with this, one of the ways you can fix for that is back up into its own database, run liquid base, get the table itself up to the current version and then back up. But you have to make sure that you plan for these things. Because the last thing you want when you have a critical bug, and again you're up at 4 a.m., is your back up to fail. So you know that, you plan for that. You know that you're not backing up the entire database and it's not going to back up perfectly unless you're in the same state. So the other thing about inheritance are your constraints. Like indexes, your constraints are type specific. So if you hand a date, if you hand a timestamp constraint, a timestamp where clause to a constraint on date, inheritance will ignore that and you will end up going through your entire data set. So everyone familiar with constraints on parent child and why they work so well? Right. Make sure you use the same type. Otherwise, it's going to take a very, very long time. Our event ID was the other, the serial primary key which allowed us to close a shard and always explain analyze. The queries that are going to be going over this data, compressing this data need to work and they need to be fast because this is your primary right master. Even if you're doing it on slaves, you don't want that bogged down by something going over your entire data set. So this might seem redundant and it might seem like elementary, but explain analyze and make absolutely sure you're not going through that full data set. This is a thing that doesn't show up in tests and it won't show up in black box unless you're under scale. Because small scale testing where you build up your test database, run through it, small data set, you're never going to find that. So specifically look for explain analyze or do load testing. It's the only two places to find this. So when using inheritance like this, you need to make sure that you have a place to write which means that in advance of the time when you're going to be using that shard, it has to exist. We used Quartz for cron just because we were in a scholar environment. However, you can use basic cron. It really doesn't matter. Code to go through, create the actual shard that you're creating, including all of your index, your check constraints, and complete. I did not optimize any of these and they're actually kind of simplistic for the sake of just getting them on a slide. So there's no pre-declared variables or other extreme optimizations. The other important part is closing the shard. And by that I mean while you're actively using a child, you're running from a base event ID, primary key up until the time that you stop. And if your query is using an ID to constrain itself, if you never determine your final ID for that time period, again, you're going to go through your entire data set. So when you're done with this shard, nightly, hourly, when you have passed the time that it is a viable table to write information to, you must close the shard. So this was the function created to go through. Select all of the tables, parse out the names, and then loop through everything that is not closed yet and determine your minimum and your maximum event ID so that you can replace the current constraint which just has a start with a start date and an end date. Is that clear? The reasoning, the logic behind that? This is actually a more advanced crowd than I thought so. So star and snowflake analytics. This was again because this was a client facing analytics engine. The API had to be fast. It had to be sub-second fast. So star and snowflake was basically the only choice for that, which added also the bonuses of being able to partition it, use table spaces if you'd like. Very basic star, snowflake data warehousing techniques. So again, we were using Quartz. And it was a three-part way to get data from our transaction warehouse into our analytics warehouse where we pull all the aggregates in. So reach out to all of our transactional data. Update and add into the base fact, just the very base that you want, and then cascade. So in our analytics database, our children, as we discussed, were pulling by sequential ID. So position, pull, update, look up, pull and update in a single transaction. And saving the state so that you can go through and do analytics on how much you've done and how your performance is. That was a pretty awesome thing for us so that we could determine what our resources were and what we needed to throw out of cluster. So this is the code for an aggregate on pull. This was done in PG9.1. Now we would use foreign data wrappers. This is also the side where I recall you. Currently, foreign data wrappers with 9.3 and the ability to write back into your primary source would have been far superior. And the reasoning for that is to keep track of your ID. That was a point of contention, where to keep track of your pull. So whatever ID your last pull stopped on is the point where your next one will start. Keeping track of that or keeping a work log in your transactional database, if your analytics database goes down and another one takes over for it, say in a multi-cloud situation where you have one cloud picking up for another, if you cannot access your analytics engine, keeping that on the transactional database is absolutely essential. But if you keep it in your analytics, it's easier to get to and it's just easier to process. So that was a back and forth where we ended up actually just keeping it in both places. So this I touched on before. When you aggregate data, so DB link, FDW from your transactional data, only go into your finest facts. So if your star snowflake has five dimensions deep, you don't want to aggregate into every single one. You want to go to your finest one and then you want to continually condense down. So this is where the query or the code gets a little yucky. Updating into the base fact. So your event source, I should have cleaned this up. I really should have. However, the idea is that when you aggregate on that pool, you create not a temporary table, but you actually create a table in your analytics database that holds all the aggregate data. And at that point, you select and you condense from that table into your fact table. It's not a temporary table because temporary tables are not item potent. This is the case that we wanted several workers to be able to come along and aggregate a table back into the main fact. We wanted things to be if this was a interrupted transaction. We don't want to have to re-aggregate that because that's the expensive part of this, is going to your transaction processing, grabbing all that data, sending it back over the wire, and then pulling it into your fact. So that's the point where everything has to be set on disk and solid. That's the item potency of this. And it gets worse. This is where you take the base fact and in two phases, update everything down the line and insert all the new data down the line. And that includes all of your data that might have come in delayed, needs to get aggregated back into the proper time period of which it came from, or the proper whatever it came from. In our case, we had multiple different ways of aggregating based on channels, sessions, and date. But it's the idea that historical data needs to get filed properly, historically. So what we found was that aside from being a service that we provided to customers and an analytics and marketing information piece, everything showed up here. Every little problem that we found higher up, you see the end here because it's not just for marketing data. This is performance data. This was something went wrong here and I could see that because we accidentally sent 55,000 emails to that one guy in five minutes. No, we didn't, but we did send text messages. That's what happens when you have your text routed through email. So don't always trust your data. If something looks funny, don't assume it's your problem. Don't assume that you have a bug. You might, but don't make, don't immediately think the worst. Maybe it's somebody else's problem up the chain because this is the funnel where you will find everything. And it's also really kind of nice when you can say, nope, not my problem. So putting it all together, what it looks like, inputs from our transaction processing. In this case, it was the queue to our analytics, sorry, to our transactional processing databases, different types, different copies of types into your first warehouse and out through your query head. So this was a base system that can have one or more OLTP databases and just very simple, very basic cluster configuration. The next scalability point is when your log files or when your transactions get to be more than you originally wanted. Each of them can scale independently and still go back into the same analytics warehouse. And the further scaling of this is to go into separate warehouses. So multiple transactions and multiple analytics and all out through one or multiple query heads. And in our case, everything was based on Dune. So we started with Harvester. Harvester gathered all of our data and the data had the flow, so it was our spice. And because our analytics saw new everything, we had Alia and out through the mentat. I can't take credit for that. I just named a couple of them. So questions? Yes? That was one of the requirements. We were using streaming replication and at the time, some of the bugs weren't known. But we were reading off of the master as well as writing to the master. The slaves were for hot swap redundancy, not necessarily for reads, writes. And because they were small instances, we were able to scale that way. Async. This was before sync. Again, the slaves were there for hot swap redundancy. What we were doing with no data loss was primarily having the queue layer. So everything wrote to the queue and the queues were redundant. So Hornet writes through to disk. It's not an in memory only cache. So we had both Hornet writing to disk across and it had slaves as well. And then from that, the copy batch into Postgres. If there were any issues with there, the Hornet queue was not cleaned, so it would retry. And then the retry at the copy level as well. So if the entire copy failed, it would revert back to inserts. And then from the Postgres level, once you have it there, we were reading, writing off of the master and keeping the master small enough and light enough to load that we didn't necessarily have to go to the slave. But if we did, we had instantaneous or not instantaneous, but very, very quick promote. And we could go back over the master if needed. How long ago was this? I was interested in rep manager part because I'm looking back. How long ago was that? This was a year ago. And at the time, one of the major things I disliked about rep manager was that it has a 5000 file limit to keep on disk. It assumes that you have a very long time between when you start your rsync to do a slave to stop, which in our case was absolutely not the case. And you can set that lower, but it was a pain. Yes? It was a manual basis. So this system was tested up to 200 gigs a day. And it was put under load for several days during load test and then for production. Sadly, the company went under before it got to see that level of load. So I don't know what the final numbers were, but they were not bad. But the scale out was by hand. And usually you can see it projecting enough, at least in our case. But that would be an easy thing to automate out because once you have the chef tools in place to spin up a new cluster, it's really easy to set limits on write numbers and whatnot and kick off a new cluster. Yes? Being a start-up, this was a company of 30 people and I wrote all the SQL for it. There were two other developers, like I said, that helped in the REST API and writing the end points and whatnot, which is how we ran into the copy bug. But all the SQL itself, I was lucky enough to touch directly. This is a perfect case for a DAL. And this was in itself a data access layer. It wasn't something that had we grown to the point where developers wanted to add more statistics, it would have remained sequestered off into its own access layer. It had the REST end points. Everything that would have been needed to be pulled outside of it would have come off of that REST endpoint. Yes? No, well, it was an invalid row where the logic on that was the copy failed and copy is all or nothing. That was one of the requirements I set out in the writing of the inserts. So the fallback to that was to do individual row inserts. The developer who went through that went through insert by insert, starting from the top, until he found the error. Once he found the error. Yeah, it was not an actual bug with copy, it was a logical bug with not continuing on from the point of error. If you went to the tutorial yesterday, the secondary tutorial on Inet, did anyone go to that? There was a cute way of handling copy bug where everything was parsed into a sequential ID column and a secondary column that had all data and just a straight copy into as one nice large blob. And the secondary piece of that was to split to array based on some item in your data. So from that, it's a two part copy into that alleviates a lot of the problems. I thought that was a nifty little trick. It would have been a lot slower in this case, so I wouldn't have coded around it, but I might have used that for my safety net. So my safety net being my copy into broke, copy is an entire column, split that column and then try and copy those into something else. What else? Colin? All right. Thank you. Thank you.
 Hello. This presentation is about scaling a cloud-based analytics engine. It was originally  titled Mission Impossible. That was a marketing person that gave it that name. It's not my  favorite, so I went back to basics. You probably don't know me. I've been around the community  for quite a while. I've been a Postgres DBA for about 15 years now, but this is my first  talk at a conference. My name is Samantha Billington. Currently, I'm the database manager for a  company called Iparadise. This project that I'm speaking about here was actually for a  company called Message Bus, which was a startup that sadly didn't quite make it. We did some  really cool things while we were around. The kind of things that startups do when you can  start from nothing, build things the right way, and spend lots of cash. Not all companies  have that benefit. A little bit about the platform. It was 100% cloud native. It was  giant and rack space, not Amazon. The entirety of the company was cloud. It was not just  the database architecture. It wasn't just the log. Everything that we did was cloud-based,  so there was no actual physical hardware. What we found out, well, what I found out  after leaving that type of environment, was it didn't really matter. What the cloud meant  for us was that we needed small to mid-size instances because we needed many of them.  You don't want large instances for many different reasons, but with your many instances, you  get lack of control over your physical resources. You don't know what else is running on your  hardware. You don't know what else your resource can tending with. Even though that's typically  not supposed to be a problem, everybody knows it actually is. There were many times that  four o'clock, the entire cloud becomes slow because everybody just got home from school  and they're playing a game on the other VM. It wasn't pleasant. There's often no delay  in expanding, so when you find your under load, you spin up new VMs, which is fantastic.  That's the whole benefit of the cloud, besides not having to pay for your own hardware. It's  that you just get more. It's there. You expand. You contract. But at the same time, you don't  have the options over your storage, so it's the same thing over lack of control of physical  resources. You don't necessarily know how your storage is attached to your database.  These are the benefits trade-offs of clouds. Redundancy in cloud extremely easy, but it's  absolutely necessary because it's not just a prediction that you might lose your VM.  It happens. It happens a lot. It's not just a possibility that you can lose your entire  cloud because if you're on Amazon, you've probably done that. These are the benefits  trade-offs. These are the things you have to architect around. Unlike having physical  hardware where you know your limitations, it adds an extra layer of vulnerability. Just  to reiterate on that one more time, those same principles that you build around cloud,  even though your necessity is building around them for cloud, it still works for hardware  too. You don't want to overcommit your resources. You want the small backups. You don't want  large backups. You don't want things taking over 24 hours and then skipping. These are  the resource limitations that even though you're making them because you're in the cloud,  there's still good rule of thumb when you're on physical hardware.  What does your data look like? In our case, we were using classic transactional log data.  We were an email service provider of sorts. The analytics that came with sending email  were what we were dealing with. How many messages per hour? What channel were they? Were they  marketing? Were they direct transactional type? How do you store that volume of data, not  necessarily in flat files because we wanted the ability to comb over it? How do you get  it into a database where you have full SQL to go over that? By the nature of that type  of platform, logs from distributed sources, they're already distributed. We're not dealing  with, say, a web application that has a single entry point for writes. We have the ability  to take every single entry point where these logs are created and get them into a local  warehouse. That's fantastic. That's absolutely fantastic because one of the major issues  that comes with an application is dealing with a single master. But in this case, when  you're dealing with distributed data, that problem simply goes away. We needed both long  and midterm storage. Short-term, midterm storage was the ability to go through everything and  analyze it quickly and then archive that off. For legal reasons, for whatever, this is a  common practice among data is the ability to use the data that's hot and to store that  data long-term. By long term here, we were meeting three months, not necessarily forever.  That's another rabbit hole. Eventual versus immediate consistency. The approach that we  took here worked because we didn't need immediate consistency. Again, this is log data. Logs  can get held up. They can be absent for a while. They take time to write. Nothing about  this type of system necessitated immediate consistency. In that type of instance, this  wouldn't necessarily have worked, but again, it was a nice thing that solved a lot of problems.  The important thing that we needed overall of this was the speed of retrieval because  the endpoint of this data is API, customer statistics. That's a very common high demand  thing for most companies is the ability to tell customers what you've done, how fast  you've done it, and give them proof that what they're paying for is valid. Most marketing  will just love. That's the one thing consistently throughout my career that people have wanted  is marketing wants data. This is the eventual output of this is being able to tell your  customer you have value to what you've done.  I put the slide in here because I realized that not everyone is going to be high level  here and for developers and for long term DBAs. The two hot things that we're dealing  with in this are OLTP. Online transaction processing, this is the log data that I'm  just going over. Things that are one time actions that you need to get to disk heavy  on writes versus the analytical processing OLAP which is combing over all that data and  deriving your statistics from it. The requirements in this case, we're absolutely no data loss.  That goes back to redundancy. At no point in this could we have a single point of failure.  It also meant that we needed the ability to change masters, to pause replication, to pause  writes, to promote to any type of critical maintenance. This is a point where historically  if you're not designing for this from the start, it will be a major issue in the future.  That safety net, that building redundancy into your system where if the database goes  away, your system doesn't crash is a huge point to build around. How many of you have  come across a point where you have a web application and your database goes down and your web application  completely stops? Yeah, don't you wish you had a cue or a write cache? This was from  the start an immediate need. Secondly, we could not be the bottleneck. Again, this was  the output of the service. In no way can your analytics stop the core of your actual service  from running. If at any point the write slowed down or the cue wouldn't take answers, there  was no way that we could let that stop service. Again, this is the idea that the data itself  is a service to the rest of the company. The other thing that comes with this type of transactional  data is out of sequence events. If a VM becomes unresponsive and comes back later, there's  valuable data there. You don't want to just throw that away. It's also possible things  just got paused or you had network hiccups. The ability to take that data and fold it  back in at the proper point in time was a requirement. Along with that is you're going  to have bugs. They happen. I've never seen a piece of software. Has anyone seen an absolutely  perfect piece of software with any degree of complexity that was free of bugs? Yeah,  I haven't. To that degree, what we needed was the ability to go back in history and  restate analytics. Even over the course of this, there were query errors, there were  issues, and Alphas always have that. That was a fundamentally awesome thing to have.  This really sounds like Splunk. I mean, really, the ability to go over logs, to perform ad  hot queries, to show data. Why not? Splunk is really expensive. The more data you have,  the more expensive it gets. That was not an issue here. However, this was a customer  facing API. That's not exactly something you want to throw in front of Splunk. It's a  rest interface in front of Splunk and hand that back to your customers. It was also defined  analytics. These were preset things that we wanted to specifically search for, analyze,  condense, and have out immediately to a customer. The difference being your internal debugging  and log combing versus what you're going to show as a service and as a business class  deliverable. The tools that we chose to use, not all of these were the best choices, but  some of them were absolutely fantastic. Chef for server automation, which in cloud, even  not in cloud, it's such a wonderful thing to have. Does everybody here familiar with  Chef? Not as many people as I would assume. Chef allowed us to bring up servers in minutes  and they were identical and they were fantastic. You could say the same for Puppet or for CF  Engine to lesser degrees, but some type of automation tool, especially when you're working  on several different clusters, especially across different locations, incredibly useful.  Liquid base. Liquid base is the schema tool of choice for myself for schema management.  I assume that's fairly known here. Yes, liquid base. No. What do you guys use for schema management?  SVN. Liquid base is similar to, I would suppose, Git, although we use it inside of Git, but  it's specifically to roll out merges, changes, updates, rollbacks, and keep consistency in  your schema. It allowed for Chef, which set up the original database and the users and  permissions. Liquid base would run on top of and set up your full schema. Automated rollout  with tracking. It made the ability to do single line schema updates over however many merges  that you might have incredibly easy. I'm actually surprised that it's not a more known tool.  The queue that sat in front of this was Hornet queue. It could have been anything that simply  because we were using a Scala implementation. It could have been a Redis queue. It could  have been a Rabbit M queue. It could have been Rescue. It was simply a queue. Rep manager.  Looking back, that caused more problems than it solved. Rep manager was in place for promote  and for redundancy with master slave failovers. I don't actually think that's necessary. Has  anyone used Rep manager? What did you think of it? That's exactly what we did. I no longer  use it in the stack that I'm using just because the return value wasn't high enough. Finally,  Postgres, the star of the show, which I assume you all use. This is the knowing that you  all not of yes, you do. You don't actually need it. I've had several conversations about  that. Everything that Rep manager did can either be done in bash or with just straight  Pgctl promote. Unless you really need automated promote of master, in the case of failover,  you really don't need it. Even if you do, bash script it. Honestly, I would rather wake  up at 2 a.m. or 4 a.m. and do a manual promote than have it be automated for me. I'm sure  some of you feel differently about that, but if my database goes down the middle of the  night, I want to know it. I want to see what's going on. It's more critical to me to be able  to do hands-on, get up and get to a computer, than failover and possibly have more severe  side effects. Writing the raw data. Distributed OLTP. I got into this a little bit earlier.  By nature, all of that log file, transactional data, created distributed architecture that  could be per cluster and per web host. Very elegant, very easy to create redundancy within  your cluster and cross cluster. Because it's log data, it's not relational. Actually, nothing  about this project was relational. That was a rather big shock for me, being a relational  database person. This is the first project of any scale I had worked on that wasn't specifically  tied into foreign keys and that type of data. But it was rather elegant. Your disparate event  types can be written to separate instances. If you have a log sending events and a log  of errors and a log of updates, all these things can go to different databases in different  instances because there's no need to consolidate them back. That's done at a later level. You  don't need synchronous multi-masters. Again, because you're not dealing with data that  has to be relational. You're not querying a master expecting the same result from every  other place. You're holding results from your single instance or for your single type.  The next piece, batch process writes with copy because it was absolutely essential. That  was one of those things that you think, oh, inserts are slow. We'll add a copy. It'll  be an optimization for when we need it. That was absolutely not a premature optimization  from the start at a very low volume when we encountered issues with copy who has not encountered  an issue with copy. Yeah, that's the second point. We found that the system absolutely  so do a crawl because the safety net for this was individual inserts. Individual inserts  even at a very, very small scale were drastically bad. This is a must have from the start when  dealing with any type of volume, but you must have a safety net. When copy breaks because  copy does. We reverted to individual inserts, but that had bugs too. Developers going through  this would go through the copy, list of copies, find the line that broke because copies all  are nothing. As soon as they got to the logic was find the line that broke, throw it out,  go back to copy. Yeah, you get immediately why that's bad, right? Everything below that  line got thrown out. So beat it with a hammer. Test and test and test again because developers  don't always make the right choices. That goes without saying. But in this case, it's  one of those logic things that as a DBA or as someone familiar with how this works, it  makes perfect sense. But if you're a developer, finding the error and moving on makes better  sense. In this case, it's completely illogical. This is the thing I love most about Postgres.  And that's a big statement. That's a big statement. Over 15 years in the same software,  an inheritance is what I love most. It really is. So back to the disparate event types.  As you're writing your types, parent per type, so my sends have a single parent. And if those  sends are such a volume that you need to shard, and I'm assuming you do, shard by time was  the approach that we took. I'm sure it can be done in multiple different ways depending  on the data. But this was right dependent. So the volume that we chose to start with  was one child per day. And that could have easily been up to a child per hour or a child  per half hour or whatever your volume demanded. But by keeping those children small, you create  the ability to do individual backups. Your queries are more efficient. And it creates  sanity. So those individual children could be archived off one at a time, pgdump-shtable.  The caveat to that is that say in three months, you go back to a single child that was dumped.  And you try and back that up to go over that data again because you found a bug. But your  schema has changed and back up fails. So with this, one of the ways you can fix for that  is back up into its own database, run liquid base, get the table itself up to the current  version and then back up. But you have to make sure that you plan for these things. Because  the last thing you want when you have a critical bug, and again you're up at 4 a.m., is your  back up to fail. So you know that, you plan for that. You know that you're not backing  up the entire database and it's not going to back up perfectly unless you're in the  same state. So the other thing about inheritance are your constraints. Like indexes, your constraints  are type specific. So if you hand a date, if you hand a timestamp constraint, a timestamp  where clause to a constraint on date, inheritance will ignore that and you will end up going  through your entire data set. So everyone familiar with constraints on parent child  and why they work so well? Right. Make sure you use the same type. Otherwise, it's going  to take a very, very long time. Our event ID was the other, the serial primary key which  allowed us to close a shard and always explain analyze. The queries that are going to be  going over this data, compressing this data need to work and they need to be fast because  this is your primary right master. Even if you're doing it on slaves, you don't want  that bogged down by something going over your entire data set. So this might seem redundant  and it might seem like elementary, but explain analyze and make absolutely sure you're not  going through that full data set. This is a thing that doesn't show up in tests and it  won't show up in black box unless you're under scale. Because small scale testing where you  build up your test database, run through it, small data set, you're never going to find  that. So specifically look for explain analyze or do load testing. It's the only two places  to find this. So when using inheritance like this, you need to make sure that you have  a place to write which means that in advance of the time when you're going to be using  that shard, it has to exist. We used Quartz for cron just because we were in a scholar  environment. However, you can use basic cron. It really doesn't matter. Code to go through,  create the actual shard that you're creating, including all of your index, your check constraints,  and complete. I did not optimize any of these and they're actually kind of simplistic for  the sake of just getting them on a slide. So there's no pre-declared variables or other  extreme optimizations. The other important part is closing the shard. And by that I  mean while you're actively using a child, you're running from a base event ID, primary  key up until the time that you stop. And if your query is using an ID to constrain itself,  if you never determine your final ID for that time period, again, you're going to go through  your entire data set. So when you're done with this shard, nightly, hourly, when you  have passed the time that it is a viable table to write information to, you must close the  shard. So this was the function created to go through. Select all of the tables, parse  out the names, and then loop through everything that is not closed yet and determine your  minimum and your maximum event ID so that you can replace the current constraint which  just has a start with a start date and an end date. Is that clear? The reasoning, the  logic behind that? This is actually a more advanced crowd than I thought so. So star  and snowflake analytics. This was again because this was a client facing analytics engine.  The API had to be fast. It had to be sub-second fast. So star and snowflake was basically  the only choice for that, which added also the bonuses of being able to partition it,  use table spaces if you'd like. Very basic star, snowflake data warehousing techniques.  So again, we were using Quartz. And it was a three-part way to get data from our transaction  warehouse into our analytics warehouse where we pull all the aggregates in. So reach out  to all of our transactional data. Update and add into the base fact, just the very base  that you want, and then cascade. So in our analytics database, our children, as we discussed,  were pulling by sequential ID. So position, pull, update, look up, pull and update in  a single transaction. And saving the state so that you can go through and do analytics  on how much you've done and how your performance is. That was a pretty awesome thing for us  so that we could determine what our resources were and what we needed to throw out of cluster.  So this is the code for an aggregate on pull. This was done in PG9.1. Now we would use foreign  data wrappers. This is also the side where I recall you. Currently, foreign data wrappers  with 9.3 and the ability to write back into your primary source would have been far superior.  And the reasoning for that is to keep track of your ID. That was a point of contention,  where to keep track of your pull. So whatever ID your last pull stopped on is the point  where your next one will start. Keeping track of that or keeping a work log in your transactional  database, if your analytics database goes down and another one takes over for it, say  in a multi-cloud situation where you have one cloud picking up for another, if you cannot  access your analytics engine, keeping that on the transactional database is absolutely  essential. But if you keep it in your analytics, it's easier to get to and it's just easier  to process. So that was a back and forth where we ended up actually just keeping it in both  places. So this I touched on before. When you aggregate data, so DB link, FDW from your  transactional data, only go into your finest facts. So if your star snowflake has five  dimensions deep, you don't want to aggregate into every single one. You want to go to your  finest one and then you want to continually condense down. So this is where the query  or the code gets a little yucky. Updating into the base fact. So your event source, I should  have cleaned this up. I really should have. However, the idea is that when you aggregate  on that pool, you create not a temporary table, but you actually create a table in your analytics  database that holds all the aggregate data. And at that point, you select and you condense  from that table into your fact table. It's not a temporary table because temporary tables  are not item potent. This is the case that we wanted several workers to be able to come  along and aggregate a table back into the main fact. We wanted things to be if this  was a interrupted transaction. We don't want to have to re-aggregate that because that's  the expensive part of this, is going to your transaction processing, grabbing all that  data, sending it back over the wire, and then pulling it into your fact. So that's the point  where everything has to be set on disk and solid. That's the item potency of this. And  it gets worse. This is where you take the base fact and in two phases, update everything  down the line and insert all the new data down the line. And that includes all of your  data that might have come in delayed, needs to get aggregated back into the proper time  period of which it came from, or the proper whatever it came from. In our case, we had  multiple different ways of aggregating based on channels, sessions, and date. But it's  the idea that historical data needs to get filed properly, historically. So what we found  was that aside from being a service that we provided to customers and an analytics and  marketing information piece, everything showed up here. Every little problem that we found  higher up, you see the end here because it's not just for marketing data. This is performance  data. This was something went wrong here and I could see that because we accidentally  sent 55,000 emails to that one guy in five minutes. No, we didn't, but we did send text  messages. That's what happens when you have your text routed through email. So don't  always trust your data. If something looks funny, don't assume it's your problem. Don't  assume that you have a bug. You might, but don't make, don't immediately think the worst.  Maybe it's somebody else's problem up the chain because this is the funnel where you  will find everything. And it's also really kind of nice when you can say, nope, not my  problem. So putting it all together, what it looks like, inputs from our transaction  processing. In this case, it was the queue to our analytics, sorry, to our transactional  processing databases, different types, different copies of types into your first warehouse  and out through your query head. So this was a base system that can have one or more OLTP  databases and just very simple, very basic cluster configuration. The next scalability  point is when your log files or when your transactions get to be more than you originally  wanted. Each of them can scale independently and still go back into the same analytics  warehouse. And the further scaling of this is to go into separate warehouses. So multiple  transactions and multiple analytics and all out through one or multiple query heads. And  in our case, everything was based on Dune. So we started with Harvester. Harvester gathered  all of our data and the data had the flow, so it was our spice. And because our analytics  saw new everything, we had Alia and out through the mentat. I can't take credit for that.  I just named a couple of them. So questions? Yes?  That was one of the requirements. We were using streaming replication and at the time,  some of the bugs weren't known. But we were reading off of the master as well as writing  to the master. The slaves were for hot swap redundancy, not necessarily for reads, writes.  And because they were small instances, we were able to scale that way.  Async. This was before sync. Again, the slaves were there for hot swap redundancy. What we  were doing with no data loss was primarily having the queue layer. So everything wrote  to the queue and the queues were redundant. So Hornet writes through to disk. It's not  an in memory only cache. So we had both Hornet writing to disk across and it had slaves as  well. And then from that, the copy batch into Postgres. If there were any issues with there,  the Hornet queue was not cleaned, so it would retry. And then the retry at the copy level  as well. So if the entire copy failed, it would revert back to inserts. And then from  the Postgres level, once you have it there, we were reading, writing off of the master  and keeping the master small enough and light enough to load that we didn't necessarily  have to go to the slave. But if we did, we had instantaneous or not instantaneous, but  very, very quick promote. And we could go back over the master if needed.  How long ago was this? I was interested in rep manager part because I'm looking back.  How long ago was that? This was a year ago. And at the time, one of the major things I  disliked about rep manager was that it has a 5000 file limit to keep on disk. It assumes  that you have a very long time between when you start your rsync to do a slave to stop,  which in our case was absolutely not the case. And you can set that lower, but it was a pain.  Yes?  It was a manual basis. So this system was tested up to 200 gigs a day. And it was put  under load for several days during load test and then for production. Sadly, the company  went under before it got to see that level of load. So I don't know what the final numbers  were, but they were not bad. But the scale out was by hand. And usually you can see  it projecting enough, at least in our case. But that would be an easy thing to automate  out because once you have the chef tools in place to spin up a new cluster, it's really  easy to set limits on write numbers and whatnot and kick off a new cluster. Yes?  Being a start-up, this was a company of 30 people and I wrote all the SQL for it. There  were two other developers, like I said, that helped in the REST API and writing the end  points and whatnot, which is how we ran into the copy bug. But all the SQL itself, I was  lucky enough to touch directly. This is a perfect case for a DAL. And this was in itself  a data access layer. It wasn't something that had we grown to the point where developers  wanted to add more statistics, it would have remained sequestered off into its own access  layer. It had the REST end points. Everything that would have been needed to be pulled outside  of it would have come off of that REST endpoint. Yes?  No, well, it was an invalid row where the logic on that was the copy failed and copy  is all or nothing. That was one of the requirements I set out in the writing of the inserts.  So the fallback to that was to do individual row inserts. The developer who went through  that went through insert by insert, starting from the top, until he found the error. Once  he found the error. Yeah, it was not an actual bug with copy, it was a logical bug with not  continuing on from the point of error. If you went to the tutorial yesterday, the secondary  tutorial on Inet, did anyone go to that? There was a cute way of handling copy bug where  everything was parsed into a sequential ID column and a secondary column that had all  data and just a straight copy into as one nice large blob. And the secondary piece of  that was to split to array based on some item in your data. So from that, it's a two part  copy into that alleviates a lot of the problems. I thought that was a nifty little trick. It  would have been a lot slower in this case, so I wouldn't have coded around it, but I  might have used that for my safety net. So my safety net being my copy into broke, copy  is an entire column, split that column and then try and copy those into something else.  What else? Colin? All right. Thank you.  Thank you.
Mission_Impossible_2_0_467.png Mission_Impossible_2_468_1511.png Mission_Impossible_2_1512_2303.png Mission_Impossible_2_2304_5063.png Mission_Impossible_2_5064_10559.png Mission_Impossible_2_10560_11651.png Mission_Impossible_2_11652_15467.png Mission_Impossible_2_15468_17063.png Mission_Impossible_2_17064_17507.png Mission_Impossible_2_17508_17759.png Mission_Impossible_2_17760_17879.png Mission_Impossible_2_17880_18011.png Mission_Impossible_2_18012_18131.png Mission_Impossible_2_18132_18251.png Mission_Impossible_2_18252_18383.png Mission_Impossible_2_18384_18503.png Mission_Impossible_2_18504_18635.png Mission_Impossible_2_18636_18887.png Mission_Impossible_2_18888_19007.png Mission_Impossible_2_19008_19259.png Mission_Impossible_2_19260_19403.png Mission_Impossible_2_19404_19655.png Mission_Impossible_2_19656_19751.png Mission_Impossible_2_19752_20027.png Mission_Impossible_2_20028_21155.png Mission_Impossible_2_21156_21251.png Mission_Impossible_2_21252_21503.png Mission_Impossible_2_21504_21635.png Mission_Impossible_2_21636_21755.png Mission_Impossible_2_21756_21887.png Mission_Impossible_2_21888_21971.png Mission_Impossible_2_21972_23987.png Mission_Impossible_2_23988_25895.png Mission_Impossible_2_25896_28919.png Mission_Impossible_2_28920_32243.png Mission_Impossible_2_32244_34727.png Mission_Impossible_2_34728_36131.png Mission_Impossible_2_36132_38003.png Mission_Impossible_2_38004_38915.png Mission_Impossible_2_38916_39743.png Mission_Impossible_2_39744_40547.png Mission_Impossible_2_40548_42755.png Mission_Impossible_2_42756_43463.png Mission_Impossible_2_43464_45527.png Mission_Impossible_2_45528_46643.png Mission_Impossible_2_46644_48359.png Mission_Impossible_2_48360_48503.png Mission_Impossible_2_48504_48551.png Mission_Impossible_2_48552_48851.png Mission_Impossible_2_48852_49403.png Mission_Impossible_2_49404_49811.png Mission_Impossible_2_49812_50987.png Mission_Impossible_2_50988_60478.png
Hello. This presentation is about scaling a cloud-based analytics engine. It was originally titled Mission Impossible. That was a marketing person that gave it that name. It's not my favorite, so I went back to basics. You probably don't know me. I've been around the community for quite a while. I've been a Postgres DBA for about 15 years now, but this is my first talk at a conference. My name is Samantha Billington. Currently, I'm the database manager for a company called Iparadise. This project that I'm speaking about here was actually for a company called Message Bus, which was a startup that sadly didn't quite make it. We did some really cool things while we were around. The kind of things that startups do when you can start from nothing, build things the right way, and spend lots of cash. Not all companies have that benefit. A little bit about the platform. It was 100% cloud native. It was giant and rack space, not Amazon. The entirety of the company was cloud. It was not just the database architecture. It wasn't just the log. Everything that we did was cloud-based, so there was no actual physical hardware. What we found out, well, what I found out after leaving that type of environment, was it didn't really matter. What the cloud meant for us was that we needed small to mid-size instances because we needed many of them. You don't want large instances for many different reasons, but with your many instances, you get lack of control over your physical resources. You don't know what else is running on your hardware. You don't know what else your resource can tending with. Even though that's typically not supposed to be a problem, everybody knows it actually is. There were many times that four o'clock, the entire cloud becomes slow because everybody just got home from school and they're playing a game on the other VM. It wasn't pleasant. There's often no delay in expanding, so when you find your under load, you spin up new VMs, which is fantastic. That's the whole benefit of the cloud, besides not having to pay for your own hardware. It's that you just get more. It's there. You expand. You contract. But at the same time, you don't have the options over your storage, so it's the same thing over lack of control of physical resources. You don't necessarily know how your storage is attached to your database. These are the benefits trade-offs of clouds. Redundancy in cloud extremely easy, but it's absolutely necessary because it's not just a prediction that you might lose your VM. It happens. It happens a lot. It's not just a possibility that you can lose your entire cloud because if you're on Amazon, you've probably done that. These are the benefits trade-offs. These are the things you have to architect around. Unlike having physical hardware where you know your limitations, it adds an extra layer of vulnerability. Just to reiterate on that one more time, those same principles that you build around cloud, even though your necessity is building around them for cloud, it still works for hardware too. You don't want to overcommit your resources. You want the small backups. You don't want large backups. You don't want things taking over 24 hours and then skipping. These are the resource limitations that even though you're making them because you're in the cloud, there's still good rule of thumb when you're on physical hardware. What does your data look like? In our case, we were using classic transactional log data. We were an email service provider of sorts. The analytics that came with sending email were what we were dealing with. How many messages per hour? What channel were they? Were they marketing? Were they direct transactional type? How do you store that volume of data, not necessarily in flat files because we wanted the ability to comb over it? How do you get it into a database where you have full SQL to go over that? By the nature of that type of platform, logs from distributed sources, they're already distributed. We're not dealing with, say, a web application that has a single entry point for writes. We have the ability to take every single entry point where these logs are created and get them into a local warehouse. That's fantastic. That's absolutely fantastic because one of the major issues that comes with an application is dealing with a single master. But in this case, when you're dealing with distributed data, that problem simply goes away. We needed both long and midterm storage. Short-term, midterm storage was the ability to go through everything and analyze it quickly and then archive that off. For legal reasons, for whatever, this is a common practice among data is the ability to use the data that's hot and to store that data long-term. By long term here, we were meeting three months, not necessarily forever. That's another rabbit hole. Eventual versus immediate consistency. The approach that we took here worked because we didn't need immediate consistency. Again, this is log data. Logs can get held up. They can be absent for a while. They take time to write. Nothing about this type of system necessitated immediate consistency. In that type of instance, this wouldn't necessarily have worked, but again, it was a nice thing that solved a lot of problems. The important thing that we needed overall of this was the speed of retrieval because the endpoint of this data is API, customer statistics. That's a very common high demand thing for most companies is the ability to tell customers what you've done, how fast you've done it, and give them proof that what they're paying for is valid. Most marketing will just love. That's the one thing consistently throughout my career that people have wanted is marketing wants data. This is the eventual output of this is being able to tell your customer you have value to what you've done. I put the slide in here because I realized that not everyone is going to be high level here and for developers and for long term DBAs. The two hot things that we're dealing with in this are OLTP. Online transaction processing, this is the log data that I'm just going over. Things that are one time actions that you need to get to disk heavy on writes versus the analytical processing OLAP which is combing over all that data and deriving your statistics from it. The requirements in this case, we're absolutely no data loss. That goes back to redundancy. At no point in this could we have a single point of failure. It also meant that we needed the ability to change masters, to pause replication, to pause writes, to promote to any type of critical maintenance. This is a point where historically if you're not designing for this from the start, it will be a major issue in the future. That safety net, that building redundancy into your system where if the database goes away, your system doesn't crash is a huge point to build around. How many of you have come across a point where you have a web application and your database goes down and your web application completely stops? Yeah, don't you wish you had a cue or a write cache? This was from the start an immediate need. Secondly, we could not be the bottleneck. Again, this was the output of the service. In no way can your analytics stop the core of your actual service from running. If at any point the write slowed down or the cue wouldn't take answers, there was no way that we could let that stop service. Again, this is the idea that the data itself is a service to the rest of the company. The other thing that comes with this type of transactional data is out of sequence events. If a VM becomes unresponsive and comes back later, there's valuable data there. You don't want to just throw that away. It's also possible things just got paused or you had network hiccups. The ability to take that data and fold it back in at the proper point in time was a requirement. Along with that is you're going to have bugs. They happen. I've never seen a piece of software. Has anyone seen an absolutely perfect piece of software with any degree of complexity that was free of bugs? Yeah, I haven't. To that degree, what we needed was the ability to go back in history and restate analytics. Even over the course of this, there were query errors, there were issues, and Alphas always have that. That was a fundamentally awesome thing to have. This really sounds like Splunk. I mean, really, the ability to go over logs, to perform ad hot queries, to show data. Why not? Splunk is really expensive. The more data you have, the more expensive it gets. That was not an issue here. However, this was a customer facing API. That's not exactly something you want to throw in front of Splunk. It's a rest interface in front of Splunk and hand that back to your customers. It was also defined analytics. These were preset things that we wanted to specifically search for, analyze, condense, and have out immediately to a customer. The difference being your internal debugging and log combing versus what you're going to show as a service and as a business class deliverable. The tools that we chose to use, not all of these were the best choices, but some of them were absolutely fantastic. Chef for server automation, which in cloud, even not in cloud, it's such a wonderful thing to have. Does everybody here familiar with Chef? Not as many people as I would assume. Chef allowed us to bring up servers in minutes and they were identical and they were fantastic. You could say the same for Puppet or for CF Engine to lesser degrees, but some type of automation tool, especially when you're working on several different clusters, especially across different locations, incredibly useful. Liquid base. Liquid base is the schema tool of choice for myself for schema management. I assume that's fairly known here. Yes, liquid base. No. What do you guys use for schema management? SVN. Liquid base is similar to, I would suppose, Git, although we use it inside of Git, but it's specifically to roll out merges, changes, updates, rollbacks, and keep consistency in your schema. It allowed for Chef, which set up the original database and the users and permissions. Liquid base would run on top of and set up your full schema. Automated rollout with tracking. It made the ability to do single line schema updates over however many merges that you might have incredibly easy. I'm actually surprised that it's not a more known tool. The queue that sat in front of this was Hornet queue. It could have been anything that simply because we were using a Scala implementation. It could have been a Redis queue. It could have been a Rabbit M queue. It could have been Rescue. It was simply a queue. Rep manager. Looking back, that caused more problems than it solved. Rep manager was in place for promote and for redundancy with master slave failovers. I don't actually think that's necessary. Has anyone used Rep manager? What did you think of it? That's exactly what we did. I no longer use it in the stack that I'm using just because the return value wasn't high enough. Finally, Postgres, the star of the show, which I assume you all use. This is the knowing that you all not of yes, you do. You don't actually need it. I've had several conversations about that. Everything that Rep manager did can either be done in bash or with just straight Pgctl promote. Unless you really need automated promote of master, in the case of failover, you really don't need it. Even if you do, bash script it. Honestly, I would rather wake up at 2 a.m. or 4 a.m. and do a manual promote than have it be automated for me. I'm sure some of you feel differently about that, but if my database goes down the middle of the night, I want to know it. I want to see what's going on. It's more critical to me to be able to do hands-on, get up and get to a computer, than failover and possibly have more severe side effects. Writing the raw data. Distributed OLTP. I got into this a little bit earlier. By nature, all of that log file, transactional data, created distributed architecture that could be per cluster and per web host. Very elegant, very easy to create redundancy within your cluster and cross cluster. Because it's log data, it's not relational. Actually, nothing about this project was relational. That was a rather big shock for me, being a relational database person. This is the first project of any scale I had worked on that wasn't specifically tied into foreign keys and that type of data. But it was rather elegant. Your disparate event types can be written to separate instances. If you have a log sending events and a log of errors and a log of updates, all these things can go to different databases in different instances because there's no need to consolidate them back. That's done at a later level. You don't need synchronous multi-masters. Again, because you're not dealing with data that has to be relational. You're not querying a master expecting the same result from every other place. You're holding results from your single instance or for your single type. The next piece, batch process writes with copy because it was absolutely essential. That was one of those things that you think, oh, inserts are slow. We'll add a copy. It'll be an optimization for when we need it. That was absolutely not a premature optimization from the start at a very low volume when we encountered issues with copy who has not encountered an issue with copy. Yeah, that's the second point. We found that the system absolutely so do a crawl because the safety net for this was individual inserts. Individual inserts even at a very, very small scale were drastically bad. This is a must have from the start when dealing with any type of volume, but you must have a safety net. When copy breaks because copy does. We reverted to individual inserts, but that had bugs too. Developers going through this would go through the copy, list of copies, find the line that broke because copies all are nothing. As soon as they got to the logic was find the line that broke, throw it out, go back to copy. Yeah, you get immediately why that's bad, right? Everything below that line got thrown out. So beat it with a hammer. Test and test and test again because developers don't always make the right choices. That goes without saying. But in this case, it's one of those logic things that as a DBA or as someone familiar with how this works, it makes perfect sense. But if you're a developer, finding the error and moving on makes better sense. In this case, it's completely illogical. This is the thing I love most about Postgres. And that's a big statement. That's a big statement. Over 15 years in the same software, an inheritance is what I love most. It really is. So back to the disparate event types. As you're writing your types, parent per type, so my sends have a single parent. And if those sends are such a volume that you need to shard, and I'm assuming you do, shard by time was the approach that we took. I'm sure it can be done in multiple different ways depending on the data. But this was right dependent. So the volume that we chose to start with was one child per day. And that could have easily been up to a child per hour or a child per half hour or whatever your volume demanded. But by keeping those children small, you create the ability to do individual backups. Your queries are more efficient. And it creates sanity. So those individual children could be archived off one at a time, pgdump-shtable. The caveat to that is that say in three months, you go back to a single child that was dumped. And you try and back that up to go over that data again because you found a bug. But your schema has changed and back up fails. So with this, one of the ways you can fix for that is back up into its own database, run liquid base, get the table itself up to the current version and then back up. But you have to make sure that you plan for these things. Because the last thing you want when you have a critical bug, and again you're up at 4 a.m., is your back up to fail. So you know that, you plan for that. You know that you're not backing up the entire database and it's not going to back up perfectly unless you're in the same state. So the other thing about inheritance are your constraints. Like indexes, your constraints are type specific. So if you hand a date, if you hand a timestamp constraint, a timestamp where clause to a constraint on date, inheritance will ignore that and you will end up going through your entire data set. So everyone familiar with constraints on parent child and why they work so well? Right. Make sure you use the same type. Otherwise, it's going to take a very, very long time. Our event ID was the other, the serial primary key which allowed us to close a shard and always explain analyze. The queries that are going to be going over this data, compressing this data need to work and they need to be fast because this is your primary right master. Even if you're doing it on slaves, you don't want that bogged down by something going over your entire data set. So this might seem redundant and it might seem like elementary, but explain analyze and make absolutely sure you're not going through that full data set. This is a thing that doesn't show up in tests and it won't show up in black box unless you're under scale. Because small scale testing where you build up your test database, run through it, small data set, you're never going to find that. So specifically look for explain analyze or do load testing. It's the only two places to find this. So when using inheritance like this, you need to make sure that you have a place to write which means that in advance of the time when you're going to be using that shard, it has to exist. We used Quartz for cron just because we were in a scholar environment. However, you can use basic cron. It really doesn't matter. Code to go through, create the actual shard that you're creating, including all of your index, your check constraints, and complete. I did not optimize any of these and they're actually kind of simplistic for the sake of just getting them on a slide. So there's no pre-declared variables or other extreme optimizations. The other important part is closing the shard. And by that I mean while you're actively using a child, you're running from a base event ID, primary key up until the time that you stop. And if your query is using an ID to constrain itself, if you never determine your final ID for that time period, again, you're going to go through your entire data set. So when you're done with this shard, nightly, hourly, when you have passed the time that it is a viable table to write information to, you must close the shard. So this was the function created to go through. Select all of the tables, parse out the names, and then loop through everything that is not closed yet and determine your minimum and your maximum event ID so that you can replace the current constraint which just has a start with a start date and an end date. Is that clear? The reasoning, the logic behind that? This is actually a more advanced crowd than I thought so. So star and snowflake analytics. This was again because this was a client facing analytics engine. The API had to be fast. It had to be sub-second fast. So star and snowflake was basically the only choice for that, which added also the bonuses of being able to partition it, use table spaces if you'd like. Very basic star, snowflake data warehousing techniques. So again, we were using Quartz. And it was a three-part way to get data from our transaction warehouse into our analytics warehouse where we pull all the aggregates in. So reach out to all of our transactional data. Update and add into the base fact, just the very base that you want, and then cascade. So in our analytics database, our children, as we discussed, were pulling by sequential ID. So position, pull, update, look up, pull and update in a single transaction. And saving the state so that you can go through and do analytics on how much you've done and how your performance is. That was a pretty awesome thing for us so that we could determine what our resources were and what we needed to throw out of cluster. So this is the code for an aggregate on pull. This was done in PG9.1. Now we would use foreign data wrappers. This is also the side where I recall you. Currently, foreign data wrappers with 9.3 and the ability to write back into your primary source would have been far superior. And the reasoning for that is to keep track of your ID. That was a point of contention, where to keep track of your pull. So whatever ID your last pull stopped on is the point where your next one will start. Keeping track of that or keeping a work log in your transactional database, if your analytics database goes down and another one takes over for it, say in a multi-cloud situation where you have one cloud picking up for another, if you cannot access your analytics engine, keeping that on the transactional database is absolutely essential. But if you keep it in your analytics, it's easier to get to and it's just easier to process. So that was a back and forth where we ended up actually just keeping it in both places. So this I touched on before. When you aggregate data, so DB link, FDW from your transactional data, only go into your finest facts. So if your star snowflake has five dimensions deep, you don't want to aggregate into every single one. You want to go to your finest one and then you want to continually condense down. So this is where the query or the code gets a little yucky. Updating into the base fact. So your event source, I should have cleaned this up. I really should have. However, the idea is that when you aggregate on that pool, you create not a temporary table, but you actually create a table in your analytics database that holds all the aggregate data. And at that point, you select and you condense from that table into your fact table. It's not a temporary table because temporary tables are not item potent. This is the case that we wanted several workers to be able to come along and aggregate a table back into the main fact. We wanted things to be if this was a interrupted transaction. We don't want to have to re-aggregate that because that's the expensive part of this, is going to your transaction processing, grabbing all that data, sending it back over the wire, and then pulling it into your fact. So that's the point where everything has to be set on disk and solid. That's the item potency of this. And it gets worse. This is where you take the base fact and in two phases, update everything down the line and insert all the new data down the line. And that includes all of your data that might have come in delayed, needs to get aggregated back into the proper time period of which it came from, or the proper whatever it came from. In our case, we had multiple different ways of aggregating based on channels, sessions, and date. But it's the idea that historical data needs to get filed properly, historically. So what we found was that aside from being a service that we provided to customers and an analytics and marketing information piece, everything showed up here. Every little problem that we found higher up, you see the end here because it's not just for marketing data. This is performance data. This was something went wrong here and I could see that because we accidentally sent 55,000 emails to that one guy in five minutes. No, we didn't, but we did send text messages. That's what happens when you have your text routed through email. So don't always trust your data. If something looks funny, don't assume it's your problem. Don't assume that you have a bug. You might, but don't make, don't immediately think the worst. Maybe it's somebody else's problem up the chain because this is the funnel where you will find everything. And it's also really kind of nice when you can say, nope, not my problem. So putting it all together, what it looks like, inputs from our transaction processing. In this case, it was the queue to our analytics, sorry, to our transactional processing databases, different types, different copies of types into your first warehouse and out through your query head. So this was a base system that can have one or more OLTP databases and just very simple, very basic cluster configuration. The next scalability point is when your log files or when your transactions get to be more than you originally wanted. Each of them can scale independently and still go back into the same analytics warehouse. And the further scaling of this is to go into separate warehouses. So multiple transactions and multiple analytics and all out through one or multiple query heads. And in our case, everything was based on Dune. So we started with Harvester. Harvester gathered all of our data and the data had the flow, so it was our spice. And because our analytics saw new everything, we had Alia and out through the mentat. I can't take credit for that. I just named a couple of them. So questions? Yes? That was one of the requirements. We were using streaming replication and at the time, some of the bugs weren't known. But we were reading off of the master as well as writing to the master. The slaves were for hot swap redundancy, not necessarily for reads, writes. And because they were small instances, we were able to scale that way. Async. This was before sync. Again, the slaves were there for hot swap redundancy. What we were doing with no data loss was primarily having the queue layer. So everything wrote to the queue and the queues were redundant. So Hornet writes through to disk. It's not an in memory only cache. So we had both Hornet writing to disk across and it had slaves as well. And then from that, the copy batch into Postgres. If there were any issues with there, the Hornet queue was not cleaned, so it would retry. And then the retry at the copy level as well. So if the entire copy failed, it would revert back to inserts. And then from the Postgres level, once you have it there, we were reading, writing off of the master and keeping the master small enough and light enough to load that we didn't necessarily have to go to the slave. But if we did, we had instantaneous or not instantaneous, but very, very quick promote. And we could go back over the master if needed. How long ago was this? I was interested in rep manager part because I'm looking back. How long ago was that? This was a year ago. And at the time, one of the major things I disliked about rep manager was that it has a 5000 file limit to keep on disk. It assumes that you have a very long time between when you start your rsync to do a slave to stop, which in our case was absolutely not the case. And you can set that lower, but it was a pain. Yes? It was a manual basis. So this system was tested up to 200 gigs a day. And it was put under load for several days during load test and then for production. Sadly, the company went under before it got to see that level of load. So I don't know what the final numbers were, but they were not bad. But the scale out was by hand. And usually you can see it projecting enough, at least in our case. But that would be an easy thing to automate out because once you have the chef tools in place to spin up a new cluster, it's really easy to set limits on write numbers and whatnot and kick off a new cluster. Yes? Being a start-up, this was a company of 30 people and I wrote all the SQL for it. There were two other developers, like I said, that helped in the REST API and writing the end points and whatnot, which is how we ran into the copy bug. But all the SQL itself, I was lucky enough to touch directly. This is a perfect case for a DAL. And this was in itself a data access layer. It wasn't something that had we grown to the point where developers wanted to add more statistics, it would have remained sequestered off into its own access layer. It had the REST end points. Everything that would have been needed to be pulled outside of it would have come off of that REST endpoint. Yes? No, well, it was an invalid row where the logic on that was the copy failed and copy is all or nothing. That was one of the requirements I set out in the writing of the inserts. So the fallback to that was to do individual row inserts. The developer who went through that went through insert by insert, starting from the top, until he found the error. Once he found the error. Yeah, it was not an actual bug with copy, it was a logical bug with not continuing on from the point of error. If you went to the tutorial yesterday, the secondary tutorial on Inet, did anyone go to that? There was a cute way of handling copy bug where everything was parsed into a sequential ID column and a secondary column that had all data and just a straight copy into as one nice large blob. And the secondary piece of that was to split to array based on some item in your data. So from that, it's a two part copy into that alleviates a lot of the problems. I thought that was a nifty little trick. It would have been a lot slower in this case, so I wouldn't have coded around it, but I might have used that for my safety net. So my safety net being my copy into broke, copy is an entire column, split that column and then try and copy those into something else. What else? Colin? All right. Thank you. Thank you.
little bit about the platform. It was 100% cloud midterm storage. Short-term, midterm storage was take that data and fold it back in at the proper interface in front of Splunk and hand that back to start with was one child per day. And that archived off one at a time, pgdump-shtable. The to get data from our transaction warehouse into analytics engine, keeping that on the temporary table, but you actually create a table streaming replication and at the time, some of