 And 30 seconds early, but I'm Gabe. I'm giving the talk on Postgres 10 performance in U. So just to give us a quick kick off, who? Slides not working. Who here uses Postgres? Kind of show of hands. Oh, a whole lot of folks, okay. Wasn't sure if we had any MySQL users or anything else, so. So we're here for a lot of the same reasons. We recognize that Postgres has a lot of really great things that are going on for it in the community. Native JSON support, native hash support. There's a lot of great community standards for data integrity and insurance. Continued excellence in a lot of really large and really expensive subqueries. And a lot of numerous native extensions and extensions supported by the community with standards built in natively. So we're all here for the same reasons. But Postgres 10 got even better. As a performant database. So some of the things we're gonna talk about today are native table partitioning, hash index resiliency. A big one for a lot of folks which is full text index searching on JSON and JSON blobs that is native now. And a big one that I'll probably talk about which will be a little more in-depth parallel queries and how they affect large joins indexes and other portions of the code base. And query analyzer accuracy and changes to how the query planner does efficient work as well. So you'll hear me talk about some phrases that you'll probably sound familiar if you're an avid user of Postgres but I'm not gonna jump into in-depth how much they work. So I'm not gonna go into how much bloat or vacuum sequential scans or replications are working as well as pruning and statistics. These are also changes that happen in Postgres 10 but I'm not gonna cover them in this talk. But a little bit about me first. I joined Heroku a little over a year and a half ago. I've been primarily working on Sinatra and Ruby on Rails applications for the better part of the last four years. Primarily right now in my role, I'm focused on Postgres and high availability modes and large data growth. So growth in the terabytes of data. I've worked on a lot of different data service engines more than I can list. I'm not gonna go through all of them all there. That's too much time. But on the side, I'm trying to be a lot more positive. So I'm trying to do a lot of self-help and life coaching things. So I'm trying to focus on a lot of inspirational things. So on the side, I do some super secret project work on this social interest platform because I like Facebook, but there's a lot of negativity on there. So some of the things that I'm looking for are some basic things that any social interest platform would provide, which is profiles, but I'm also looking for curated content. So inspirational quotes, TV characters, movies, TV shows, foods, the list goes on. And I'm imagining a lot of usage. So we're gonna just jump right into it and the first thing that I'm gonna talk about is inspirational quotes. And this is one that I actually saw when I was traveling to New Zealand which I thought was fantastic, which is happiness is not a destination, it's a way of life. And I'm hoping to see millions of these populate throughout the code base and through the platform. But wow, that grew fast. So even though quotes aren't very big, they're just small string texts, most databases even at large scale have significant amount of RAM, tons of disk space, but some usage scenarios that most quotes are never updated or revisited after a week. And most people only want the most recent and up-to-date quotes, they don't wanna see stale stuff. So that's kind of tough. That one table grew really quickly, some quick math there. And that's way more memory than I have. So a little bit about how Postgres and some of its caching system are gonna work here, it's gonna attempt to load as much stuff in memory as possible. And then it's gonna try to use what it has in memory. And if there's overflow, it'll start doing disk writing with pointers to the files. And it'll open them up at one at a time as it does file writing. So it actually will manipulate the disks a lot more. So caching is done this way because if you access the data, obviously, you're probably gonna need it sooner than later. So it's gonna try to do that in a couple different ways. But the other problem with this is that temp disk space is not free. I know we'd all like to believe it is, but it's actually not. There are a lot of other overhead costs because usually temp disk space also happens with where the data is stored on the database on disk. And caching records in large quantities can have some significant impacts, especially as you're trying to concurrently access the same table multiple times with large data sets. So one of the real issues, and this is something that some people might have seen in the past is that we can easily stop connecting to the database entirely if we run too many of these simultaneous really heavy bloating or locking queries. So who likes fast queries? Everyone, I'm hoping everyone likes fast queries. And databases staying up and continuing to connect. Yes, yes, big thumbs up in the front row, thank you. So a great thing that they actually added as Postgres 10 is native table partitioning. There's been extensions in the community for a while that do this, but let's go into some of the big wins on native partitioning. So native partitioning actually can entirely avoid the vacuum and bloating process. So bulk loads and deletes and offloads can happen without causing overhead to your Postgres internals. So slow accesses to your database connections, things like that, they're gonna be a lot less costly because you're not stressing that one giant table as much. Our most common used rows are only gonna be the sections of the partitions we care about. And a lot of the seldom used data can stay where it needs to on slow disk storage or even off on cold storage. We can move it off entirely. And one of the great things about this is that queer performance improved dramatically. We got a lot more bang for our buck when we actually do the lookups. Previously, we'd have to do best effort random index scans and then random access reads to the disks. With large data like this, disk data storage is kind of fragmented in some scenarios. And so in average and worst case scenarios, if you actually do complexity, some of these cases actually end up being worse than sequential scans. So native partitioning is a great way to optimize for this. Another key note to have here is PG Partman is still in 10, it's still supported, but it doesn't have some of the parallel support that we're gonna talk about a little later in this talk. Some other small noteworthy items for native partitioning. It's not natively supported in ORMs. Obviously, you saw me writing raw SQL in the migration script. There have been gems to try to make this more consistent in things like active record, but none of them are really well supported right now. A lot of, another great thing about this though is tables can be created from your partitions. So if you need to do what I'd like to call YOMO tables, your month tables, this is, or even week tables. This is something that you could absolutely do. And this is a great way to even segment, even data, even further as it migrates through. And it's an easy way to understand your data over long periods of time. And you can even create partitions from your partitions. I would add a partition meme, but I feel like they're overkill. But so now we wanna do a pivot. So we've had a lot of use of the platform and people are starting to create products. And now we wanna do home delivery. So how do we get that done? Well, we obviously need customer addresses. So this seems pretty simple. And we want a mobile app to be able to access lots of different customer addresses when they're going and making deliveries, right? We wanna take customer service seriously. But we do have a lot of customers and we have a lot of addresses. So addresses aren't uniform across North America. Actually, they're very different even between the US and Canada and even between different states. Different states have different rules and regulations about where delivery drivers can stop and other really important features and gotchas like that so nobody gets ticketed on the street. So there might be some important instructions we also need for not just customer satisfaction but for driver notices as well. So we can kind of store this unstructured data already in Postgres, right? HStore already exists today. A lot of people I'm sure use it. It's not new. It's a key value store. But implementing it seems straightforward enough. Let's just add the hash, right? Yeah, man, those address lookups are real slow. Real slow. The other problem here is that when somebody's trying to make an update to this, our drivers aren't gonna get it in real time. It's not gonna happen. We could add an index to improve lookups though so let's try that and let's see what happens. So let's add a hash index on usage and hope for the best, right? It seems like it should work. Maybe things improved. But ooh. Show of hands, anyone seen this error message before? Show of hands who uses hash indexes in here? Not many people. Okay. Well that's good to know. So probably a lot of people know for good reason that hash indexes aren't really well supported in Postgres 9x for a lot of different reasons but the key one being replication is not supported. So it's not treated as a first class citizen. The other one is you get a lot of misleading failures like that that kinda looked like a data corruption issue but actually that's an index corruption and that's because the hash index can't keep up. So there are other things about this is that if we have extremely large data sets to pull from sometimes you have to completely access the hash index and rebuild it from scratch which is not a great experience. What's the solution if we can't rely on this hashing mechanism if we really wanna use age stores since it's mostly supported? Well Postgres 10 actually finally brought it into the light and made it a first class citizen. So it added first class citizen support such as replication. It made it crash safe through shutdowns and failovers. Replication is done faster lookups so the growth of each page for that index are actually consistent with regular indexing practices and the pruning happens much more consistently as well so indexes don't go as stale as often. So going back to this we can rely on this much more consistently and have better peace of mind and we do see actual improvement now checking through and updating the hash itself. The biggest thing that I'd like to point out here is the locking concurrency issue was a huge problem and we use age store at Heroku for a lot of different use cases. The reduced locking for concurrent access for records is a big win for us even though we don't use a lot of hash indexes age store in general for the few places that we do that this is a really big important thing for us. So going backwards we're talking about more curated content on my social media platform and I wanna talk about movies and TV shows and characters that we love that we can't get enough of for any big nerds out there. I'm a huge Flash fan. I met John Wesley ship for the first time earlier this year which was fantastic. So I wanna list all these movies and TV shows and I wanna get the characters for all of them. So each show can have potentially 50, hundreds, maybe even thousands of characters if anyone watches Game of Thrones. I'm sure everyone's excited. So each record can have a lot of varying data over time and some things that we'll wanna try to watch is genres, catchphrases, famous accessories and items or even just certain ways that they dress or look or interact with other characters. I'm sure some people are gonna get a little schwifty after all of RailsConf. So we need some way to keep that data unstructured but co-located with the general structure of how we wanna address media and characters in general. So let's use a JSON blob because this seems to make sense for this scenario. We can query third-party data sources and we can get them in consistent JSON formats so we can reparse them over and over again and we can get updates. The great thing about this is that we do have this flexibility but it opens us up to a lot of other gotchas, one of which is each row is dynamically sized because of this. So it's not consistent what size each record is and because we can have so many different data sources analyzing each character, each record, this could potentially get a little hairy in tracking how this information changes over time. So how can we keep this unstructured and not have to parse it all or create constant relationships through this? So we could create multiple tables. We could add a lot of one to many relationships. We could modify our Rails app entirely to manage this information. We could even do partitioning based on some subset of where the data is co-located per show. But as much as we could do all those things there is some other easier item to do and that is using the full text index searching that's been added in Postgres 10. So because we can store whole request bodies, we can store custom made JSON blobs. This allows us for a lot of more no SQL dynamic schema operations and this is what we're really caring about especially when we're trying to get this off the ground in initial stages. This adds a lot of flexibility. So one of the great things here is that there's a lot of examples of how to do this online now on the Postgres materials but this keeps our data structure unstructured and flexible. So the big thing about this is indexes can actually filter on a lot of different views and kind of perspectives that you wanna do for the type of index searches you wanna do on your JSON since they are treated as first class citizens as well. The other great thing about this is this gives us ways to target folks on the platform that we're looking at if we wanna target them for being big Marvel fans or maybe somebody can't get enough of Joey from Friends. Maybe he just wants to say how you doing all the time. But I'm a big fan of Thor so a lot of hammers for me. But let's jump back for a sec. Quotes was our big take-off item. That was the big one that got the platform recognized and people love Throwback Thursday. So let's create a throwback stream of quotes for folks who wanna know. On this day, my favorite posters posted these quotes. So we wanna be able to replay a stream of old quotes. Well, streams should include all of the posters that I liked and followed, not just my own posts. So that's a lot more quotes than you would normally see and even getting one month of posts. And that's a lot of queries, especially if you're following a few hundred other people. That's a lot of quotes, especially for power posters who post multiple times a day. So, yeah, here we go. So Postgres 10 did a lot of things to optimize how we do these types of queries and how fast they come back. So we're gonna, I'm gonna touch on three. There are more optimizations in the parallel areas on how to return queries that Postgres 10 did. I'm only gonna touch on these three because these three are usually the big ones that people talk about. So we're gonna talk about parallel bitmap heat scans, gather merges, and merge joints. So let's jump into it. So in nine six, parallel searching and scanning was introduced. It was a little limited though. So it used to only do sequential scans and sequential scans were only done on the primary tables. So this kind of created the problem where you couldn't rely on indexes. We had a little bit of a trade-off here where you either had to rely on your table caching to be really effective or your indexes to be really good, but you couldn't get an overlap here, which was kind of painful. The big thing about Postgres 10 is that changed when they added way more parallelization to every level of your table schemes, including indexes. So you can do parallel searching through your indexes first. You'll actually go through and construct the only the index files first so that you are much more methodical and strategic on how you do your queries and how the results are loaded. And so this requires a lot less data loaded up front, a lot less caching, and way less results that's done to parse through and a whole lot less disc reads. So the next one we're gonna talk about is gather. And gather is actually the mechanism in which Postgres introduced how to do parallel query construction and result retrieval. So it was introduced with the idea of workers, obviously, to do asynchronous processing. And the big thing here was that there were multiple layers on how results were returned. A lot of the time they were returned in arbitrary fashions, mostly in the order of how fast they returned from each worker. So unfortunately, if you're adding searches or sorts to this portion of the mechanism, this is gonna add a significant amount of overhead because you're requiring not just the search of the B-tree, but you're also requiring an extra order layer to happen after the initial result sets are retrieved. So a small example here is, I have a bunch of IDs in my B-tree and I just wanna retrieve the primes, right? So here's a small example of a B-tree. The issue here is that when we're going through and checking through primes, the leaf nodes in some scenarios have more content than either the roots or some other leaf nodes. So certain leaf nodes will return faster than others. The root node is our first returned response. So 17 was actually the first node. I'll go back here. So 17 was the first one that we actually saw on the left side. If you'll actually look on the three leaf nodes to the left of 17, 11's only got one. So our first records are gonna be 17, 19 and 11, depending on how fast they return, then two. So we actually get stuff out of order. So another pass is gonna be required to sort through the somewhat strange return results of the workers themselves. And so they were only trained to return as fast as possible with no context. Well this changed in Postgres 10. So Postgres 10 changed this by optimizing the worker collection results. So gather merges actually sped up the parallel worker result retrieval because they also kept context of where they encountered the data that they actually cared about and they kept them into account as the result sets were returned. So it removed a lot of overhead for sorting after the fact because at the end it ended up being mostly just a flat line. So this was really beneficial especially for really large data sets traversing huge tables. So kind of revisiting this. We can see that we have a whole other side of the B tree we didn't touch, right? I kind of glossed over it, but for good reason because really when we parsed it down we actually held context. It did actually move through the B tree and construct how we thought we were gonna see everything. The reason we kept context here is because we kept context of the locations that we visited each node at. And so this is much more natural ordering of operations from an intuitive mental standpoint. So it's easier to mental map how our data is retrieved as well too. So the great thing is is because as we merge the leaves and the roots we basically can turn this into a link list and it becomes a simplified array that's mostly natively ordered even before we add other order clauses. If you add the order clauses they're kept in context along with the parallel workers. So they know what to do and when they're ordering as they return results. And then the last one we're gonna cover here is merge joins. And this one while maybe performance wise doesn't always pack the most wallet is one of the interesting ones for me as well. So kind of applying this to checking all the other posters I care about for throwback Thursday. I need to join on the other customers quotes, right? So previously 9.6 only allowed this on nested and sub query loops or hash joins. This doesn't always happen on the primary query of the table. So a lot of our sub queries were optimized for this but not necessarily the originating one that we cared about. So this adds a lot of overhead and even though indexing and ordering is great it could actually make things worse and not better especially the indexing portion which is surprising sometimes but it can be a gotcha. So parallel merge join was added to consistently parallel do parallel operations for all of the merges at all levels. So all of the sub queries all the way up the stack from each sub table or even tables you've had to rejoin to get different data sets for temporary tables for example are now gonna be done in parallel as well. So this is gonna make things much more efficient. Outer sides of the joins especially when we do null comparisons or checks like that are gonna be the most effective because we're not gonna be as restrictive here and it's gonna be much quicker to run through certain sections of that with less data and combined with a lot of the parallel changes to the indexes gonna be way faster in getting all of the data that you care about. Again the parallel changes that also happen to gather merge will give you some natural ordering by default even before you specify order but will still maintain context for adding order as well. So the last thing I'm gonna touch on which I did mention this is a real world example actually from something that we track at Heroku which is part of tracking our issues on our internal repos and external repos as well so that we have a good list of items from when we get feature requests, issues, bugs, things like that and that is actually even though this table is pruned pretty consistently for the issues mapping the GitHub issue to a repo ID it's not huge but it's not tiny either. Here is the query analyzer for the table for a given repo ID. Execution time's not great. This index is basically not being used even though we expect it to be used in 96 it definitely didn't get used at all and so we end up doing sequential scanning through it and it gives us some pretty crappy runtimes. Here's the great thing. With all the changes with Postgres 10 the parallel index got figured out by the query analyzer and it's being used now. Even on a tiny table that's ridiculous. Gonna let that sink in for a sec. That's a half a gig table. That's not even a big table. That's a tiny one. The changes that have occurred in the performance gains are huge. I'm not gonna go through query analytics. That's another talk in and of its own. That is way too much time for what we have to get through today. So unfortunately that's, if you wanna hear me talk more about it or explain anything more that I've seen of my last year and a half, two years come talk to me after. But there are a few last minute gotchas with Postgres 10 with Rails that I wanna go through. When I touched on some of them but maybe some of you might not know. Rails 4 doesn't support Postgres 10. Unfortunately the connection protocols changed in how Postgres 10 works and so unfortunately Rails 4 only supports up to Postgres 9.6. If you wanna make the move to 10 you will have to be on Rails 5. Again, as I've stated before, partitioning is not native to the Ruby and Ruby on Rails ORMs. So our specific index types like hash indexes and JSON indexes for the full text query index searching we went over today and the analytics queries are all not available except for in raw SQL as well. So some things to note but that's one of the reasons I love working at Heroku is because we try to offer a lot of tools for stuff like this. So with the release of Postgres 10 and the support for that, we've added a lot of extra support in numerous extensions to support Postgres 10 as well. We've added managed Postgres credentials because the permission schemes are very confusing as we've found over the years. New updated hardware plans at the same price points improved CLI analytics because our analytics CLIs use the same query analyzer that they do there and the same PG internals that have had the optimizations done as well. And we're betaing a new feature right now for those to test out called Postgres connection pooling. So the Postgres extensions that we offer are numerous and most of them are natively offered. We offer a few external ones like PostGIS and things like that. If you want the complete list, go check them out. But the big one that I love here is the time travel one. That's a big favorite, especially with the new changes to partition. Postgres managed credentials was something we actually released late last year, but we did this for a lot of different reasons. We had a lot of customer asks for this. So Postgres permissions are really difficult to reason about. They're at a lot of different layers and they have a lot of nuance into creating granularity, understanding revoking permissions, things like that. We still allow customization when we issue managed credentials, but we've tried to take a lot of the guesswork out of initially creating a lot of these credentials and roles for tables. So we've tried to consider things like special tables or special characters. No one likes SQL injection. Bobby Tables can stay at home with his mom. Name lengths are another big gotcha. There's actually certain character limits that if you blow them, Postgres silently doesn't tell you. So that's another strange one. And we've also published some of the best practices as well. So if you wanna see some of the other things, you can check them out at the Postgres credentials dev center. But I encourage everyone to, even if you're not using managed credentials, consider things like separations of concern, principle of least privilege, and even better external control access. If you're using a third party vendor that needs data access, you wanna give them very restricted things. You don't wanna give them access to PII data, for example, to some of the things you insure for your customers. So the other big one that we did earlier this year was Heroku PGX plans. I was directly actually part of this project, which was fantastic. I was very happy and excited about this, which was we were able to utilize the latest hardware virtualization for better IO, better CPU and query optimization. And also we've added all of these plans under dedicated hardware so that you get consistent and stable performance. We've documented about it in the blog post, but the big wins here are that we've doubled CPU cores at tiers four and up. We're exposing some of the dedicated provisioned IOs for plans and we're increasing data disk size at every tier and prices either stayed the same or lowered. So please go check that out if you're interested in checking out some performance on the hardware side. The one that we do for a long time is one that I use on a daily basis, especially when people file support tickets for us is the Heroku CLI, especially for Postgres. We've used PGDiagnose and this is something that has been out there for a while, but I wanna reiterate because Postgres database tuning has been a big deal and other people have talked about it at the conference. We've had a Dev Center out there for a long time and we have the extension CLI to add on to the native Heroku PG CLI commands. So the big one is PGDiagnose, but if you're looking at sequential scans or caching and hit rates, some of the things that I might have talked about today, we actually offer pre-defined ways of querying that information through all your tables and schemas. So please go check that out as well. Now, the one that I know a lot of people have heard about and a lot of companies have started using is PGBouncer. So PGBouncer, for those of who are familiar or not, have added management for Postgres connection pooling. This is basically because there's not really this idea built into Postgres, so connection pooling is done outside of Postgres and this kind of adds insurances to making sure connections don't stay open longer than they're supposed to be, queries don't kill your database and we've added this as part of the connection pooling. That's how this is done. PGBouncer is in beta as Heroku PG connection pooling. So why use it? Well, one thing Heroku in general loves to see is stats. We love our stats. This is better visibility into seeing how our connections behave. This also gives a lot of guardrails to protect your database and keep it up, especially if you have multiple applications connecting to a single database. And this is really great for asynchronous workloads. For anyone using lots of active job or anything like that, this is fantastic. We've offered it as a beta. We're still working out making the best practices out there. We're still trying to get some feedback from folks who ask for more customer visibility or better use case scenarios. So we're still doing some public beta work on it right now. It is supported out there and it is well documented. So if you're interested in using what's on server right there for PG connection pooling, please go check it out. We still offer a client side build pack that can run on a dyno. It doesn't have as many of the built-in features that we'll be adding to Postgres connection pooling, but if you are interested in using it separate from the database itself on your application layers or something like that, it's still available out there. Whew. And that is it. What a marathon. Thank you so much. Please come check out the booth. I'm the last presenter for Heroku today. And as always, thank you again so much for letting me come and talk to you at RailsConf.