Events Add an event Speakers Talks Collections
 
Duration 36:26
16+
Play
Video

PGConf India 2020 - PostgreSQL: Pushing the limits on a single machine - Ashu Pachauri - Clarisights

Ashu Pachauri
Chief Technology Officer at Clarisights
  • Video
  • Table of contents
  • Video
PGConf India, 2020
February 28, 2020, Bengaluru, India
PGConf India, 2020
Request Q&A
PGConf India, 2020
From the conference
PGConf India, 2020
Request Q&A
Video
PGConf India 2020 - PostgreSQL: Pushing the limits on a single machine - Ashu Pachauri - Clarisights
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Add to favorites
315
I like 0
I dislike 0
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
  • Description
  • Transcript
  • Discussion

About speaker

Ashu Pachauri
Chief Technology Officer at Clarisights

Ashu is the CTO at Clarisights which is a fast evolving SaaS platform offering realtime, interactive and contextual analytics for performance marketers. He is involved in solving the scalability challenges at Clarisights. Before Clarisights, Ashu used to build databases in Facebook's core data team and is an official Apache HBase committer.

View the profile

About the talk

With more than 2 billion writes and 400 billion records read per day on a single machine, this talk is the story of how we, at Clarisights, scaled a single PostgreSQL instance more than 50x in last one year.

I'll outline the approaches we took to scale our use case on PostgreSQL, including how we identified and implemented the avenues for scaling both reads and writes, sequential and random.

Share

so basically, my name is a true and they already been introduced. This talk is about how you would scale a single machine. Basically, the whole idea of a database is that you want to be able to use the hardware resources that are present on the machine to the fullest extent before I go into How do you do that? The question is, why do you want to do it? While all the talk is about having horizontal is Cable Systems, having multiple machines to be that that you can control inside a cluster or hundreds of incidents. Reported

SQL to serve your application. Why do you want to do it on a machine? Here's my answer. You want to do it because this makes sense from a cost perspective, this graph represents the cost comparison of multiple machines versus a Singing Machine. Having the same number of as you can see, the graph is pretty much linear, both the grass pretty much go inside. So you managed to give you the cost advantage in a single machine that you're getting multiple machines. So you

know, no longer holds that if you climb up a lot of resources on a thinking machine, you'll pay more Now, the point is, if I don't have the gas Advantage, do I really want to manage the complexity of multiple machines? Because when you have a lot of machines, you still, you have to manage the complexity and you have to pay the tax Basically the CPU resources, the memory. The operating system is going to take the show that the organization is going to do. Extra monitoring that you are putting on each of those machines. Anyway so

unless you really need it unless you are actually going out of the limits of a single machine. I personally think it doesn't make sense and a graph also tells me that it doesn't make a lot of sense to over-engineered on multiple machines. So before I actually go and talk about how I just delivered of introduction, why you should listen to me? I've been building a business from scratch for more than six years. And I've been maintaining damage Protection scale from zero to 10, Cepeda bites,

but on multiple sites, as part of this presentation. Also, I am currently an official bocce at his computer, and in the past, I was doing great and respected at Facebook and I drop kick field in California. So before I go into, how you supposed to understand, how was this girl is being used at like a site for that, we need to understand what I said about is the best smartphone real time in real time. Interactive contacts for reporting that the exact status. I

just copied from a website, but what it means is that we are targeting Performance Marketing team. Did I leave the maximum value out of the large data sets that they get from marketing reports? It's a thing about to go solution to solve three problems in a single product Facebook, Google LinkedIn, Snapchat, different advertising, transponder data, and which the data so that performance marketers can use their data because marketers are not Engineers. So, to expect them to

be able to extract value of the data is not correct. So, you know that you needed tools that does data and information on its own. How to do to interactive analytics, what it means is that you are able to run electrical queries on billions and trillions of rows of data within a few seconds and minutes and hours. This is what classes does. Basically it take data from Facebook Google Alton dentist. You get report. On this data on different dimensions and different metrics. For example, I want to ask the question, what is my best

friend in a particular city across all of my data sources like Facebook Google and like any place better when I'm putting my iPhone. So this is the common question that I want to ask you to specifically for Facebook on Google, right? So this is exactly What that leads to is there an interesting data engineering challenges that arrived at classifieds that you have a great time series for each eye. What is keyboard with any object that you see on the advertising? there are hundreds of

four dimension but I mean, by Dimension is Party City in Bangalore all the artists that were targeted users in Bengaluru, Bengaluru becomes the value of the nation So what this means is that there has to be some level of data enrichment, that is dependent on the kind of customer. Like, some people are interested in the city Dimension, but other people might be interested in the type of product. For example, for delivery company will be interesting brothers,

burgers versus Pizza So this is the dimension that created by users. After the data is in Jester to that is dynamic that's very different from what the usual or lab work would would look like that. He would have all of your data Define before then just would have asked you and place you would have that. I mentioned Define emplaced, this actually increases the complexity of the problem, then you have met my associate. With these donations are also verified draws more than 10,000 metrics. These metrics also update that I can update their

1 month ago that they will also change. So what we're doing is high tide at Mission electric on data. Noted that changes What is mrs. What we need is an archetype system hybrid project. No longer takes processing system update for de more than it should. So yeah, basically what we did a lot of research model systems out there that can that can handle this kind of skin. They don't work in a higher dimensional space so I mean or did not find a single system that's all set. So we started asking this question, why isn't there a single system that I can solve this

problem? And this actually goes back to what is Thumbkin texture? Duncan Jackson is basically a triangle. There are three vertices of a triangle read update and memory. What this means is that any system that is out there? If I do fully optimizing system on, only two vertices of the triangle, what tree do you get off? You might as well write something they can. I listen free its base Cassandra, but I think we all agree here that nobody wants being extra dollars for X in rupees. So, you said it in my face. What I've seen is that the memory of the fixed or

text, you are always trying to optimize phone memory to the extent that you can, but then you try to compromise Alexa spiders in the weather at Scales. Well, for each person's rights or active adaptive solution to the problem. You find a balance between rights and reads. So how did he get around from conjecture instead of introducing Bond system be introduced to assistant in the mix? Something's wrong with the Slade. It's supposed to Okay, I don't know what happened. There was a diagram here, which basically,

basically you're right, and update, but it is not optimized for, right? That is the problem. And you are optimized for it so that you can read from the system Android app system, becomes the fruit roll-ups. So, before we talked about killing, how do you scale postgresql with this kind of work. We're going to the pool at system. Also, before I go into the specific approaches, we had a bunch of General approaches in mind. That will basically go about doing them.

We could adopt two approaches quickly Either to say something or do country, tuning on progress, something to do, with users of controls the other way of doing it, you actually introduced romick says that it is more conducive to the adoptive. So the very first thing that we thought about, okay? Let's try to optimize what Super Why was this girl? Flight model is asynchronous. That might sound like a surprise to a lot of people because you do realize we getting acknowledgement is consistent with

their, you believe it, right? That's what it is. But under the hood, it is actually backed by an asynchronous Alexander. Also, how that is, but what I meant for, hire, right through blood, but not better than see, what is true for you to do. Very good at giving, you both better read report and butter dietary latency anyway because it's not the system which the customer was this is not really late until Thursday. So, how do you optimize, what support message

number of clients are posted more than 10,000 + connections at a single point of time to a single machine. What is the problem? And both groups have a memory limit of a single slab of memory, allocation of 1 GB. This is hard-coded inside the chords. It's not a conflict that you can do, if you try to allocate more than one, gigabyte that you can put was this will do it and then it will try another one ways. We're close to 10,000 collections after that, queries to the catalog table started failing because of Connecticut telephone number

So yeah, that is not really the solution we want to do more than 10,000. We want to throw him. One General approach that people use proxy on top of buses. So we right now, if it supports three different poem old fashioned pool in transaction pulling statement. And you don't get the guarantee. Is it profitable to you guys? No. Okay. All right. But the numbers are right? I don't you think Foundation pulling mode of portrait of Billy Moser while keeping the same number of client connections. So this is

how we do it more than ten thousand in this nap. We were actually hitting 6600 client connections for that. We're only having 164 several connections to post office that obviously. It doesn't work every time. It depends on how a buttload is, if you're very short, introductions, you have this time but then you should have that has other problems that I love. So explain later. But it's not a win-win situation. We do lose something by using transaction pulling in.

That is so prepared. Statements are not available, not available, then just buy a raised migration is because we will we lose all of those capabilities, but that was an explicit choice. We made to be able to drive motor boat through four Crest. We will lose all of the session specific features. They're not very important for us. For example, prepared statements, when we found that they are prepared statement, don't really helpful because the planning time to query planning time is very small. Compared to 30 seconds. So now that

we have tried the neighbor project throwing more work to do trim work and converted into problem. Now, we're going to see what is going on there. So first find out was killing weeds, periodically. Towing company on laptop without explaining that periodically we sing from the sphere 2. What is Curry's look like be equally over several days worth of data goes over a few billion rose look like this. Don't you don't need to read that. That is a sum, which means that allegation function,

that is involved on Group by a bunch of things, you have to do a goodbye and then they do the application from post office. So what we tried was the initial approach was, will just use something called find in batches that is their entrails. You don't need to know how it works, but the idea is that it changes. The query that is being thrown to post in a very specific V8 cards and order by on the primary key of basically if you're trying to attract a batch of thousand thousand thousand thousand thousand thousand thousand

one to miss the new records. If you, if you were actually starting on primary key and the right that came in between. So as you can see, this is like this Then just knock. This is not work. There is a lot of the same multiple times. It's happening. Every time you have to do a group, I only then you'll do the opposite and leave it. So, there is a problem with the transaction. This is the query that takes long, we are doing transformation of a lot of data. It will take 30 minutes to an hour to do that. We don't

want to hear transactions open for that long. So let me know if you mention that you did, okay, let's not use Foundation has left his curses that artist outside of the transaction has been told no problem in our anniversary. We're already made the choice to forego all this action specific features, okay? Can we get rid of Plantation pool and come back to session, pulling, but based on benchmarks and how much, how much code changes that we have to do? Just

to support the specific feature, they were a lot so we decided to figure out a different approach. It had approached that we talked about was okay that she does not realize using posters. The problem is it doesn't get rid of older data so you have to get it somehow anyway. So we thought we'd give him his temporary tables in progress and on this table here you don't need you after. You don't need the other by, you don't need to go by there tonight. Seems like it will push me push to staging to production. You happy? As soon as

opposed to production. This is what happened. I would application stopped working. And the reason start working is temporary table. So I still didn't unlock why. I don't know what the point is. So that is okay, I might be wrong but there's something that I've tested at least with was this version 10. So that is why that is better than explain. Musically still doing a lot of replication and I'm talking about application because that is the only thing that broke But a lot of things God impacted you are still

doing a lot of rights to whatever. If you have, if you are spending a lot of rights that do support unlocked, the give you the ability to not write to write a novel only needed for a small bit of time. Why do we want to lock it? Even if he loses the job is going to say it again. So it's an important operation. Use under a log tables, Norris retired, log replication, doesn't need to see many things, I need to recreate anything, but one thing that you still have to be aware of it, still a right to

the desk. So it's a good idea to print out these temporary workload, study for on a separate disc in more detail in terms of how you want to segregate your traffic. But we still do something. Because we are using normal tables. Now, we have to delete those tables. It's not as bad as deleting. Individual records from the table, from something that has all the data. But still, you need to have a table to table that you start seeing fragmentation MPG plus starts being pregnant Asian,

your whole positive slowly becomes law about a month ago. So it's not as bad because it can grow slowly, but this is something that will be aware of if you're doing a lot of these operations. Novel ever deeper. We tried killing the biggest market for it, but now let's look at what we want. We want to attack the impact. Full car is not just local is that was working on the assumption that we just need to put in Texas or we just need to attack mice. The query data

slow, based on the addition that are giving my toes like a hero by David Caldwell on a specific access, but there is still a taxes. You sent you a text that I am packed for. What does that mean? It means that it's possible you are doing queries, which I'm not slow enough to be caught by this to us, but you're just doing them so many times and your day, Crystal want to be able to optimize them. You still want the view, something that tells you that, okay? These are the qualities that are impacting your system whether you can talk to you. That's the question,

but do you still need that exists? You still in that video that you can look at it. So the second approach was, okay, let's try to look at it from the application perspective. If we try a sampling profiler from the application and try to profile calls to PG. From the application site, will be able to see the queen that are happening for the longest time. Do you get both of them are the problem with this is doing it on the application site is it to a specific piece of cord? If later we have a different but know that I'm different language like golang or Java you have implemented death

So why not try the same problem and post the same solution and post best? I'd have a sampling profile on this day with everything that is happening right now. I'll look at it directly quoting but anything that is active Idol that goes this is working on right now that is being shown in the Bee Gees that activity. The problem with that is it doesn't show you the duration but it does show you all the individual credit even the Panama tries in the rest of them. As opposed to on the PG statement activity, it is start statements shows you

So against the diagram is missing, the point is that we are going to do a snapshot of Bee Gees at activity every 10 seconds and she brought to an external system. Elastic cars allowed on an application site. If you don't do that, you can still do a hash of the credit before you ship it to your exhaust system. So what did I tell you to do? Is you will basically be able to see Something like this. Go. We have this pictures so you can see what we have on. The y-axis is the count

of how many times apart a query on the x-axis voice thing. So it is this account in a 10 x hundred times but it's possible the same could he was running for all of these hundred seconds are in a thousand seconds. So that lets you basically mix your intent. It tells you the exact in time that I want to just look at the impact of God, he's not just dislocated. Know that we are done with getting rid getting right as I pointed out earlier is not right of / Beauty Plus

all the operations there to do in postgresql supposed to do, it will be at 8. That will need to be flattered later point of time to the disc. Again, that's what was this caused dirty images. So whenever you write even a single column in a single row it will have to pull out that page modify and please write a new version of the truth of time. A separate title process will come in and see that this page is dirty advice to the disc. What does that even if I'm doing one by 10 x 1 kg of,

right? It will still like the whole page with a kilobyte Lord, I got mine. Just going to skip over at the point is that the The Flash to the back and do the disc happens in an asynchronous. So then that is introduced. That's what we call Brooke, it's our group riots and we should be able to make it to optimize 4 to put the baby do it. Is if I borrowed some traffic shaping from the field of network, networking applies equally. Well here's what we can do, we can change of a title to that flash

to that, that is happening. The face that is hurting. GeForce Graphics capability or at least in such a way that most of the dirty rice. That happened to happen at the same time. So you write only one single page at a time or write small. Newest model number of Lights. So, to be able to do that tells you your group is on the application side, right? To the same table on the partition in bulk. So, somebody to do, you have some level of buckling, on the

application site? Maybe a coffee ice cream, or depends, how you write a check. But if you do want to impose, this was more than just by introducing bulb lights. End up telling me this the number is malt. So what the customer of Congress, basically, it's what we can do is we can introduce a layer in between the price to group flights to the same partition of a puzzle. Stable part of the same table, which increases the probability that are coming at the same time to the same page of Paws vet, Does that answer your question?

Okay. Yeah, I will look at the property after the dock but the idea is that you want to reduce the number of pages. I got to put that which side was traffic, citation, reduced the number of fireworks that we do for every unit of data. Iowa car dealers in line has been very intelligent. They try to group, right, or ribs to the same location of the disk. Disk location is busy. So they marched to the same location. So, what we do is that a cricket different kinds of access button if you understand your fondness for mix, vol. 1 days

for very specific, kind of work. Barbie stuff on this list. We were able to optimize the number of d d. I r Wait that I await that you are getting on each of the desk by telling that. Okay, is that posed to be grouped together? So as you can see the numbers for ride, I wait for average average weight rate in terms of kilobytes per second. Flight number is have for the base that has similar. But Lord, they said similar tables. Where is the chances of accessing the same location of Hyatt compared to mixville Road where the chances of accessing the same location of this. So I'm just

going to skip over. This is the only 5 minutes getting back out. We can't answer afterwards was getting Ops one thing that to be wanting to be really, really aware of. Is that Phil has this 1 GB memory limit, single block, allocations single chunk of my location to try to look for the data files that are the problem if the leaves grow over one gigabyte and then do it. So, this becomes a gigabyte limit. If you have a lot of updates, if you have a lot of delete to your tables, are you may end up into

a car into Auto vacuum, falling off a cliff in terms of performance. We did a Crest about welcoming change. The number of Acumen convicts or do you still have this problem? I'll explain how to mitigate this. Again, if you have a lot of Records in your table. Index operation will take longer Baked sweet potato conflict with your musically was that provides comfort index in Great Falls. Most of the problems of long-running transactions single solution to both of these problems and that is

partitioning. So if you basically one Alexis smaller so you're creating index in smaller chunks, you are part of vacuuming in smaller chunks, you will you I'm less likely to hit, d10vh limiting, Focus, There are bunch of other not return. Again, I lost her just to be aware of in postgres. If you're using hard drives versus West Indies, ssds have vastly different different cost of exiting, a random location compared to sequential location to White. Sorry ladies, but that's just don't so

be aware of your block device. Again, real hard at something that you try to understand and tune activated system, kind of fire system and to find some options that you're putting underneath your desk. For every excess of 25 Lennox, has to do one more operation update time. You don't need that. And last, but not the least understand that you are using pose with its Aurora database is not filled. It is still going to redraw it. Still going to read the page. So better that I read amplification, if you are coming up all of your data into smaller number of

tables. If you have logically different database being logical, different tables are different, tablespaces. So pay attention to me. Any questions? Time for only one question right after work.

Cackle comments for the website

Buy this talk

Access to the talk “PGConf India 2020 - PostgreSQL: Pushing the limits on a single machine - Ashu Pachauri - Clarisights”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Standart

Get access to all videos “PGConf India, 2020”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Ticket

Interested in topic “IT & Technology”?

You might be interested in videos from this event

September 28, 2018
Moscow
16
179
app store, apps, development, google play, mobile, soft

Similar talks

Kaushik Iyer
Software Developer at Newfold Digital
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Denis Mekhanikov
Software Engineer at Facebook
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free
Pavan Deolasee
PostgreSQL Developer at EDB
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Buy this video

Video

Access to the talk “PGConf India 2020 - PostgreSQL: Pushing the limits on a single machine - Ashu Pachauri - Clarisights”
Available
In cart
Free
Free
Free
Free
Free
Free
Free
Free

Conference Cast

With ConferenceCast.tv, you get access to our library of the world's best conference talks.

Conference Cast
816 conferences
32658 speakers
12329 hours of content