Who's using Postgres in here?
Yay!
Thanks for being here on Sunday and our next speaker, Charlie, is going to talk about reducing
the costs and reducing the costs.
Those things out there.
And other things as well.
Good luck.
Thank you.
Thank you.
Good evening.
So yes, welcome.
Today we're going to talk about how to reduce costs and improve performance.
Doing really easy stuff, really easy things.
Well my name is Charlie Batista.
The presentation is not about me and this was made by ChatGPT so we can get these lights
later.
Sorry.
Okay.
Okay.
Good to go.
Nice.
So what we're going to talk today, so we're going to have a bit of a review on what's
about this talk.
So we're going to review some concepts on how the hardware works, right?
So try to understand a little bit what is cash, how Postgres stores data, and the summary.
And thank you guys.
You are good to go.
So I think it's gone.
See you next year.
I'll be a bit fast because I have a lot of slides and not much off time so I will try
to get.
If you have questions at any time just raise your hand.
That's fine.
So just interrupt me.
Or if you would like to, you can also wait for the end.
We try to answer as many questions as you can.
So what is this talk about?
So this talk is not about go there and modeling a business.
It's about how to model database, right?
We try to understand a little bit how the underneath hardware works and how we can play
nice with this and how Postgres can play nice with this, right?
So we see some concepts about how computer stores data, how Postgres stores data.
And it may be get a little low level but I'll try to keep it as more higher level as possible
so we can all follow together.
And we hope that the end of this talk will be able to understand a little bit and save
some money, especially for those running on cloud, right?
You know that space and these and those things cost a lot of money.
And well, that said, let's start.
We're going to do a quick review on the hardware.
So I suppose most of you guys have seen this picture before.
So this is the memory architecture.
So how the memory is divided on the hardware.
If you see down here, we have those secondary storage.
This is your hard drive, SSD, HD type if anybody still use that thing.
So this is the thing.
If you see it's quite large but it's slow and usually inefficient.
The latency is high.
As we go up to the top, we go to the CPU registers, they get really, really fast.
That's where the magic happens but they also get really, really expensive, right?
We want to do our best to always use them in a very efficient way.
Things to understand about memory.
Memory is either volatile or non-volatile.
That down here is non-volatile.
That's where you save your data and you should save your data there if you want them the
next day.
Because if something happens and power loss or whatever, so everything that is up memory
on those non-volatile, they're going to be lost, right?
So also, as I said, the down is cheaper, the upper is higher price.
Memory can also be basically accessed in three different ways.
We have random access, direct access and sequential access.
We'll see that most of the times we're doing random access, especially in RAM, RAM is basically
random by nature.
We also always try to do, when you go to the hard driver, to do sequential access both
write and read and you try to understand why.
So if you see here, see I have four CPU cores and I have the IO controller.
One thing that you need to realize is the CPU is not connected to your disk.
There is no physical cable or path away that the CPU talks to your hard drive.
It doesn't matter if it's SSD, ADDD, tape or whatever.
It needs to go to the memory controller.
The memory has physical direct access to the hard drive.
So every time that you need something, your CPU asks the memory and the memory catches
that thing inside of the hard drive.
And then it moves up all the way to the CPU.
Also a very interesting thing that most of the developers do not realize is we don't
write bytes on hard drive.
When you're a software, open a text file and you save your name, my name is Charlie, five
characters, five bytes, no, I still save one block in which most of the systems are four
kilobytes.
For that very simple operation of five characters, I'm dealing with four kilobytes block on the
operational system.
And that is for everything.
The database also do the same.
So if we can start doing more work with those four kilobytes block, things can go faster.
That's one of the main ideas.
Another thing that you see here, ADDD, they're really slow but still have a lot of companies
and people using them because they're quite inexpensive nowadays.
But random IEO is terrible, is low, is horrible.
Every time that you need to do a random IEO there is horrible.
And the problem is it's a mechanical device.
So most of the people believe that the performance problem is on the plate, that the spinning
plate.
Actually that one is fast.
The problem is on top of the spinning plate you have a literal ah that needs to move back
and forward.
And this movement is really, really slow.
Really, really slow.
So if you do a random IEO and the arm keeps, needs to keep moving back and forward, that's
going to be horrible or whatever application.
And especially for database.
That's going to be really, really bad.
So on SSDs, it's not that bad but it's still, the performance or random IEO is still not
as the same as the sequential IEO.
Both writes and reads.
So writes are closer but they're still not as the same.
So this is a little bit about what I mean about sequential axis.
Sequential axis is when you write one block after another.
Sequential and random is when you have that mess.
So most of college people there when you go to their bedroom they had random axis there.
So you can think about that thing.
So but yeah.
What is this cache?
We're talking about improving performance and talking about those things but what does
it has to do with cache and performance and database?
So cache on its very simplistic definition, I got it from the Wikipedia, it is a hardware
or software component that stores data so you can access them in the future in a faster
way.
We have many, many different levels of cache.
So we have natural cache, we have hard drives cache, we have application cache, most of
the database they do have the application cache and we also have the CPU cache.
This is the one that we really interest for us today.
And we have some definitions here.
So what is a cache heat?
Anybody?
Come on guys.
Exactly.
Let's say for example I want to do a select and I want to get the Charlie, the first
time that I do select and get Charlie's information and goes for the disk up to memory CPU.
So but it stays there.
The database doesn't throw it back because next time that I do a select for a Charlie's
information again the memory is not there.
The information is on the memory.
If luckily on the CPU cache, remember that high top we also have cache there close to
the CPU which is really, really fast.
This is a cache heat and a cache miss is the opposite.
So if I do a select and that row has never been selected before it needs to go to the
hard driver so that's a miss.
It needs to go and then all the way so that's going to be really, really slow very inefficient.
So if you have a heat ratio higher so more cache heat than cache misses the better and
the faster is our application.
So we always try to improve that metric there.
It's a very important metric.
We also have some writing policies.
So we have the write through and write back policies.
So the write through policy is when you send information to the cache especially when we
save in data to the cache so the database is saving data it can keep the information
in the cache now and save later or can immediately save that information both in the cache and
in the hard driver.
So a write through is immediately save that information.
So it stays in the cache but then immediately saves information.
So what's the problem with that?
The problem is latency.
So we increase latency when you use that policy.
Oh is it all bad?
Some applications are fine with that.
So some applications that need higher reliability will implement that policy.
And so we have tradeoffs here.
The other one, the write back.
So the information stays on the cache and eventually that's up to the CPU if it's a CPU cache or
up to the aggregate of the application to eventually save that data back to the hard
driver.
Remember, everything that's up there if we have power loss we might have a problem.
So in this case we might improve a lot of performance but we may lose in reliability.
So we need to have that tradeoff.
And then we have the prefetch.
Different CPUs, they're very smart.
At least they tend to be very smart.
So those theory that when you go to cache, the information that you get now you probably
will get more information around that data.
So we call it cache locality.
So based on locality if I have Maria, Charlie and John, if I select Charlie, the probability
that they'll need data from Maria and John is higher than that I get down the line.
So it's playing about probability.
We also have the time locality.
So the information that they just accessed now has higher chance that they're going to
access in the future, like in five minutes and a few minutes.
So what this prefetch does, when we ask for one block for the CPU, the CPU, oh, this guy's
accessing this block so he's probably going to need the next block.
So the CPU prefetch loads in advance that block and puts in cache.
So if I need that block, it's already in cache.
The CPU doesn't need to go back to fetch that information again.
So that's improved performance.
That's awesome, right?
Now comes the problem.
Cache are expensive.
So when it gets close to the CPU, they get really, really expensive.
If you're going to buy a laptop or device or whatever and you choose the CPU i9, i20,
you're going to see they have those L1 cache, L2 cache, and they're usually in gigabytes,
right?
Thatabytes.
They're usually in kilobytes.
You're going to get kilobytes of cache.
What can you do with kilobytes nowadays?
Almost nothing, right?
Another problem is what we call cache line.
The cache line is literally the line that the cache is divided.
So the cache is divided in many lines and each line has a specific size and usually depends
on the CPU word size.
What's that CPU word size?
So if I have a 64-bit CPU, the CPU word size is going to be 64-bits and the cache line
likely would be 64-bits.
So why likely?
Because when we go to the CPU, we start not counting the time in times anymore, in seconds
or nanoseconds.
Now we're counting the time in clock cycles.
So inside of the CPU, it has those things that they call registered that are really,
really fast.
It takes only one clock cycle for the CPU to get information there.
So it's really fast.
When we go to the cache, the L1 cache, it usually takes between three to seven clock
cycles.
It depends on the CPU and depends on the cache.
So things start to slow down.
People always tell you memory is really fast.
Memory takes 215 clock cycles.
Can be 100 times slower than the CPU cache.
So memory for the CPU point of view is really, really, really, really slow.
So we don't want to go there.
Can you imagine your hard driver?
They didn't even put here because I don't know the number of years of data to fit there.
That's insanely slow.
So that's why we always try to put information there.
Remember that thing that I said about the line size?
So we might have a problem.
We want to fit everything inside that size.
We don't want to waste that thing.
So can you guys spot a problem in this here?
I put a really simple algorithm here.
So a tip is not the code or anything.
It compiles.
So that's not a problem.
Anybody?
On the line.
On the line.
That's exactly on the line.
So most of the developers, at least a lot of them, will believe that information will
fit in memory just like this.
So we have the int, the boolean, the int, the boolean again.
The problem is it won't.
Why?
Let me see if this thing works.
Can you see?
No, that pointer doesn't work.
So you see from 0 to 7, it has 8 blocks, little blocks.
So it is 64 bits there.
8 bytes or 64 bits.
This is in this example the size of my cache line.
So because the CPU can only fetch one word size at a time, it can only fetch that information.
So the boolean that is only one bit here will have to go to the next page.
And see all those white stuff?
We call it padding.
It's waste.
Waste of money and waste of time.
So in this example here, if we go back, we have one cube, big blocks, right?
We could fit them in two big blocks if we aligned them properly.
So we will have two CPU blocks to fetch that information.
But actually, we're going to have four, double the time.
And we only have four variables here.
So we mess it up things really, really quickly.
Keep that in mind because that's going to be really important for our discussion here moving on.
So yeah, how POSC is organized the data?
So POSC and most of the database has its own file organization.
Those are the most common one.
We have the B3s.
I just put here in ascending order.
So no special things.
It's not that one uses more or less on B3 or one is better on others.
It's not the point.
So but POSC uses what we call hip files.
Hip files are really interesting things because they are very simple.
If not the simplest one, it's one of the most simple.
How a hip file does work?
So it's basically one spaghetti of information.
You put one block after another.
That's it.
That's a hip file.
There's no order, no guarantee of order.
There's nothing special.
You just have one block after another.
This is a hip file.
So it's very simplistic implementation, right?
But it can also be very efficient because remember the locality thing.
So if you just have one block and if you prefetch all that information,
that's going to be sequential.
A problem for indexes, sometimes people do not understand why POSC
please do not pick that amazing nice index that I just created for my application.
So it was there.
I have an index there.
Sometimes, you know, but the database doesn't pick it up.
And a lot of people, they want to facilitate the database to pick things up
because they think there's nothing in the database.
Well, sometimes they also do and then they realize they're not.
But it happens.
So one of the problems is the index is a B3, most of them.
So B3 by nature is random.
The access is random because you have one block here, another here,
another here, another there.
So you are changing the access pattern from sequential to random.
Remember how random lies expensive, especially on the ADD thing that they spin in this?
Yeah, that would be horrible, even for a really efficient index.
So that's why the database say, nah, not today, maybe tomorrow, you know, today I'm fine.
I just do a full table scan, let's just get everything.
And more often than we sometimes think it's faster.
So but if files in postgres, they have some very interesting properties that go back here.
If I put somewhere, no, here, no, here.
Well, they are eight kilobytes.
So the block on the hip file, I think, yeah, eight kilobytes size.
And each file has the limit of one gigabyte.
So it means that we can only have one gigabyte table in postgres, right?
No, limit one gigabyte, just create another one, and another one, another one.
I have one terabyte information that I'm going to have 1025 files.
So be mindful of that, because depending on the file system that you use,
some file systems, they do not play well with many, many files on the same folder.
Right?
Be mindful of that as well.
That might be a problem.
So it's of eight kilobytes size.
Keep that information in mind as well.
That's also going to be quite important when we move on.
So as I said, one row is just appended after another.
So not in fancy.
There's no fancy organization or whatever.
And if you go for postgres, insert all your data in a nice way, index it there, okay, yeah.
And if you do an update on that data, and then you search again,
if you do not put another thereby, that's going to mess up your order.
So be also mindful about that.
So the hit files has no guarantee of order at all.
Right?
So this is basically how it's organized.
And every single block, it has its own organization.
So it has a header.
The header has a lot of data.
I put the information here, as I said, I'm not going to go through all of them,
because that's not the point.
But you can go and search on the documentation.
They're really nice.
Now what matters for our discussion here is how these things are organized.
Right?
So this is inside of the block.
This is a picture of the block inside of the database, inside of postgres.
So we have the header, and then we have the data.
Nice thing is data is started and stored by the end, not by the beginning.
We have an index, sorry, a pointer that points to the data.
So the data goes towards the center from the end of the block, and the point also goes
toward to the center.
So when they get close together, that means I don't have space anymore on that block.
Right?
The block is full.
The database needs to create another block to put more information.
Also, postgres doesn't split the data between blocks.
If your block doesn't fit inside, if your data doesn't fit inside one block that is
8 kilobytes, actually we'll see that if it doesn't fit inside half of the block, it's
going to do something else.
Otherwise, you're not going to lose your data.
But yeah, the database will handle that.
So also, those rows here that we see here, those tuples, tuples and rows, we call them
interchangeably the same thing.
So the rows here, they also have their own organization.
I put here all those things.
And I will go for a couple of things that are important for the discussion, not all
of them.
They are fixed size.
Well, they have a header as well, that the header is fixed size.
A question, does anybody know how postgres stores new?
If I have a table and I have a lot of columns that can be new, anybody?
The null bit map.
Sorry?
The null bit map.
The null bit map.
Does anybody know what is a bit map?
Okay.
Yeah, a bunch of bits.
So you have what we call map, actually, you have the sequence of bits.
You have 11111010100001.
That's the map of bits.
So what postgres does, it has this sequence of things.
And the position of the one of zero on that thing is the position of the column inside
of your row.
So if it's zero, it means there's no null.
If it's one, that means you have a null.
So it's highly efficient and compact the way that stores null inside of postgres.
Yeah.
This is how it works.
And also what is very important here, this is, again, another photo of the row, is this
padding.
Remember the CPU padding?
Well, the database also tries to keep things aligned with the CPU.
So if things don't align with the CPU, the database will add padding.
And we'll see how nice that can play.
Right?
Remember that I said if the data doesn't fit there, the postgres will save somewhere else.
This is what we call toast.
This is the oversized attribute storage technique.
And usually goes really well with coffee.
So postgres uses another file, that's not your data file, to put the information that
doesn't fit inside of the block.
And then it puts a reference inside of the block, a pointer, to the beginning of the
data on the other file.
So the database does it automatically, you don't have to do anything about that thing.
So every time that you have text, VACA, 3000 and something, that, you're going to have
a toast because that won't fit in 8 kilobytes page.
So there are a lot of improvements since being created with compression and those things.
So it plays quite well.
And when we understand how those things work, we can also change our data organization.
But that by itself would be another talk.
But I can give a just a small example here.
So let's say we have the table user, where the user does the authentication, right?
So for the authentication to work, we only need to compare the username and password.
But then when the user has been authenticated, sometimes you want to show the pictures, the
bio and a lot of information that's there in the toast.
So if we create a table with only that information for the authentication, the authentication process
that's a complicated process can go really, really fast because you can fit a lot of things
inside one block space.
And the database doesn't need to go to the toast.
We have the toast in the information and the modeling.
So and then when we need to show the information from the user, we can use, still use that
same primary key that we have a reference for for an key and get that data on that very
specific time.
And for most of the applications, the authentication is a hot path.
Everybody knows goes to the authentication.
But really few people go to the bio page.
So we can improve the performance of our application just doing nothing.
Really simple changes.
And we get a boost on the performance.
So we already got the things, the performance improvement.
How those works.
So as I said, when it's filled a percentage of the block, it will be saved elsewhere and
has a pointer to those things.
And now we come to what really matters.
So if the data is too large, they go into toast.
But what if I have too many small problems?
Well, if you have too many small post-process has a limit of how many columns you can fit
inside of your table.
Because in that case, it won't fit on the block, right?
So the question is, if we have too many columns, how does it do if it doesn't split?
So you just have the maximum of columns.
Depending on the data type we're using, for example, if you're using big inch, that is
eight bytes, so you have that number.
If you're using smaller inch, you can fit more.
So but you have a hard limit because of the block size.
That is what it does.
If it doesn't fit, you need to split the table in more than two, three, four, whatever many
times you need.
But that would be insane.
Like have hundred or six, 64 columns inside of the table.
Can you repeat the question because you're not talking about it?
So the question is, the post puts the information outside because it's a larger than the block.
But if we have too many rows, too many columns inside of the row, how post-process handles
that?
So that was the question.
And again, post-process just has a lot of limits on the number that you can put there.
More than that, you need to create more than one table.
That would be the solution.
Now we come to the data alignment and padding.
So remember that I said the database also does padding and post-process also does.
So the natural alignment on post-process padding is of eight bytes or 64 bits.
That's the natural alignment.
So what does that mean?
It means that every time that you put one integer, integer is four bytes, you need to
put another integer together to get a perfect alignment.
If you put an integer of four bytes and a big integer just after that integer, so that
big integer is going to go to the next one because it doesn't fit the natural alignment.
And you can ask me, well, what's the problem?
Just go to the next one.
Not a big deal.
So every type has its own alignment here, as you can see.
Shaw has basically no alignment needed because it's variable.
It doesn't mean that's good to use car.
Actually it's good to put at the end, not at the beginning.
And we'll see why.
So we see that Shaw has alignment of two bytes.
It means, yep.
So does the order of the fields or can optimize the order of the fields?
So when I decide my table, do I need to think this?
I got it.
So the question is, does the database automatically reorganize internally to put it in the best
way?
Or does the DBA need himself to change the order of the fields, the columns, to put
in the best way?
The database does not.
This is work of the DBA.
The DBA has to do this work, and it's a really good question.
And we'll see sooner or later why it happens.
As we see here, every type has its own alignment.
This is on post-presokumentation.
It's just a copy and paste.
So the two bytes on most machine, that means that one will occupy two bytes there.
So for example, we are aligned with eight bytes.
So I can fit four of those data types one after another that does eight bytes.
However, if I put one of two bytes in a big integer that's eight bytes by itself, we're
going to have six bytes, pageant, waste.
Remember, pageant is waste.
Most of the time, waste of money, especially if we're running on cloud.
All right.
And it's really possible to optimize those things to make them to work in a better way.
And this is an example.
So in this example here, what I did, yes, let's say I create that table.
See, we have a really few columns there, not many.
I just put in a random order, like I put an integer, and then I put a bar card, and then a timestamp.
So that was a very small table.
I only inserted one million rows.
So on that one million rows, I got a certain size, and then I organized it just to align better, to remove the pattern.
The alignment saved me 25% of space.
How much would be 25% less in your build on AWS of storage?
We make and buy a burger, right?
We never know.
So we'll see a couple of our examples.
Wait for people to take photos.
All right.
Yeah, question?
Jason B, it has its own, if I test the Jason B data type, that's the question, right?
So Jason B has its own specificities, so, and most of it doesn't go inside of the block, because most of the time it doesn't fit inside of the block, right?
The problem about Jason B, it has its own algorithm of optimizations.
So, but I haven't answered your question, I did not, but that would be really interesting one to do, to see how that works, how that plays together.
Especially because Jason B has a binary format, so the binary itself, algorithm should be able to do a lot of optimizations on the way.
But yeah, I'll take notes of that one, because now I'm curious.
Another question?
So the question is, if there is any analyzer tool that we can use to see how much space we're wasting, right?
Not that I know.
That might have there, but not that I know.
That also would be a really nice open source tool to develop, you know?
People looking for ideas, that's a nice one.
Back to Jason B, the data base, the table.
Like, there are a few things about the Jason B, is that like 60 bytes, 32 bytes, 64 bytes, how do you organize the database?
Okay, the question is back to Jason B, how does it organize it?
It's organized in 64, 32 bytes.
I don't have an answer for that question, because I really have no idea.
I haven't played with Jason B much.
Most of the work I'm doing with those things, performance, and how I would say, netrodata types, right?
So, but yeah, I don't have an answer for that either.
How long do you take to review everything and optimize it?
And are there any tools to make the process much quicker, like any automated scripts?
The question is how long does it take to do the full reveal and the process, and if there is two?
Well, I don't know of two.
And the time, it highly depends how complex your database.
If you have a small database, like the one that they use for the TPCC, it took me like a few minutes, right?
But to have really complex database, it can take you some time.
But it's time that works it.
The question is if the alignment only works on Postgres or if the other database is as well.
If I'm not mistaken, Oracle also does, but its implementation is specific.
It's from database to database.
And I have not played with those, especially because this type of documentation is not highly available.
And if you do not have documentation, you really need to go to the binary and it's really time consuming.
And I haven't been working with other databases for like 15 years.
So yeah, but they probably do.
The question is if we have a way to ask Postgres how the alignment is, right?
If we have tooling, yes, we do have some extensions that we can go deep and see how the organization is.
We even have some extension that we can check the memory for from time to time.
Okay, moving on because I only have 15 minutes.
Thanks, sir.
Probably now it's less because he's been showing me that for five minutes.
So, okay, what are the implications about those things, right?
I showed you one example.
Now I'm going to show you another one that they use a tool named sysbench that they did a TPCC-like experimentation to see.
So this is just an example of one of the tables.
This is how sysbench created the table.
This is normal stuff.
And you see it's really small table.
There's not much.
And most of the columns are integers.
So nothing going on there, right?
And this is what I did.
Besides the flashing thing, did you guys not see anything?
I changed some orders of the column.
It's a shame that this point is not working.
But yeah, I changed some orders.
See, I put all the integers on the top and then I put the small integers on the way that they are in pairs to improve the alignment.
And then I put the other columns.
See that I put the timestamp.
A timestamp, if I'm not mistaken, is also eight or four bytes.
And after that one, I put the another small inch because they can still use the same space on the alignment.
So I have four and two, still have six.
So the only padding that I'm going to have is after that one.
So I tried to minimize the padding as small as I could.
Back to the other one.
See, it's just not that bad.
We had an integer as small as small integer and then an integer.
So not so bad.
So it shouldn't be of huge difference.
So this is what happened.
See the new on the left, the schema name.
The schema new are where I created the new ones.
The schema public are the old things.
If you see the total size of the orderline one is 3,000 megabytes or three gigabytes, 3.8, to be more fair.
The old one was 4.1.
It's about like 15% increase, right?
And also interesting, look at the index size.
We also have improvement on the index size and they have exactly the same indexes.
Let's go back.
See the index, the primary key here, we have the column one, two, three, four, and then another two columns for the other index.
And the same.
Exactly the same indexes.
Don't change the name.
Exactly the same color, exactly the same order of the columns.
I didn't even play on the index for the optimization.
I just left them there.
I just did for the table, right?
And this is what happens.
And I'm highlighting here for one of them how much space is saved.
One table.
And a very small table, right?
So, but how does it play with performance?
Because, okay, one thing is I can save any space on this and performance.
So obviously it does a TPCC type test, right?
Try it to use as well.
The answer is I got an average 8.4 performance improvement.
About around, on average, for this example, this load, 19% disk space production.
I'm cutting down at 19% of my disk space views on cloud providers.
I think that's why they never approve my talks on their conferences.
Now thinking about that, I think it makes sense, right?
The latency, I reduced it about 15% latency improvement.
Just shuffling the columns around.
The application does not even need to do.
And I'll tell you, when I created those tables, because the SIS bench has its fixed structure on those, the insert and updates,
I had to trick the SIS bench.
I had to create views and then rows inside of Postgres to make those views insertable, updateable and deleteable.
So on top of this, I still have latency on the database I had to put because of the tooling.
And even though I got almost 9% improvement.
Can I just clarify, I'm confused, your average write and read around 8.2, 8.5.
So I thought the latency would be around between those two numbers.
But obviously you mean latency in a different sense, or you're measuring?
Latency on the application side, because latency is not about how fast or as low you get the data,
but also at the end how fast or as low you process the data.
So you have more data in smaller blocks.
So on the application side, latency is going to be a lot better.
So I'm also improving the application for free.
You have some kind of improvement, but what are the things you need to do to reorganize the columns?
So your question is what happens if I need to go to the table and reorganize the columns, right?
So, yeah, if you already have an application, you may need to do the trick that I did here.
Right, first create the new table, so you're going to double that for a certain moment type.
You create the new tables, and then you create views and you create rules inside of the database.
So where your application will insert, update, and delete, obviously select as well on those views until you change.
Or if you use a tool like, if I'm not mistaken, PG...
I forgot the name of the tool, you know.
Actually, the guy had a really nice talk on Friday on PG Day that you can do online data change for your thing.
So your application does not even need to know that you're changing the database.
So you have a few different options.
What's the name of the tool? PG Online Scammer Change?
That's from ISQL.
Oh, PG, okay, yeah. PG Online Scammer Change, that's true. Thank you.
Why was there is a reason for the project not doing the organization for you?
The question is, is there is a reason that the PostgreSQL does not do the organization?
Yes, because even though we can have really smart things on there, the database doesn't know the full story.
So it may try to reorganize the data and mess up the things on the way.
So it's always safer for the DBA to do those things.
And at the end of the day, the database should keep the data and retrieve the data for you.
So you need to know how the data plays inside as well.
Yeah, I wonder if it's important which columns can be known and how often these rows actually contain those columns?
So the question is, is it important for the column to be known or not and how often it plays?
So it is important, not for the column itself, but the following ones.
Remember, PostgreSQL doesn't store the know, right? It stores on the bitmap.
So then will be a pointer there and nothing on that place, right?
So yeah, it does play a role. I haven't tested that to see how much impact that would be, especially because I just used the tooling, right?
So but yeah, it definitely should have some impact, for sure.
Would it be fair to say that this benchmark measures bit tree more than anything?
And let's say that we're not prioritizing latency, but rather we're doing like the same sort of dense joints and we want more bandwidth.
Is it true that if there's something like grid, right, we're going to be able to get as much bigger improvement in bandwidth points?
Can you rephrase that? I don't think I understood the question.
So for this benchmark, it seems like it measures bitmap scan.
So this is like a bit tree benchmark.
And let's say that we're not after latency reduction, but rather we want to get more bandwidth.
Let's say in a case where we would have distinct or time-based joints.
Could we use something like grid to effectively get a much bigger set in bandwidth if it's going to be dense enough?
If I understood it correctly, the question is it's fair to say that the benchmark mostly tested the bit tree performance, right?
So not really the density, if more density of data that would improve.
Well, actually, as I explained, post-crisis, especially for insertion and things, we're going to have on the heap file.
So we often do not do, at least in this type of benchmark, not do with the bit trees and performance would be really marginal,
especially if you saw we don't have many indexes.
It's like most of the tables here are the primary key, and I feel them, they're only one index.
And the only bit tree structure on post-crisis for this example are the indexes, because the table itself, they are not.
They're just heap files, right?
So in that sense, I would not fully agree, but definitely density would play, like if you could have more dense tables.
And the example that I gave for the authentication, so what do you do when you change?
You just put a few columns there for the authentication.
You are increasing the density of the information you have on that table.
So on the same block instead of having three or three, that will have a thousand, right?
It's a lot more dense, so that per se makes it a lot faster, especially if you think about that, time is also wasted on the network, as you mentioned.
And network is not only the bandwidth of the network inside of the computer itself.
Well, we have five minutes. Let's rush to the end.
So this is a summary.
So post-crisis stories.
And actually, if you, now wait for the photos, almost, okay?
So if you want to take something, this is the summary.
Every data type has its own alignment on post-crisis and can cause padding.
And it's really, really dangerous.
So we can get really, really mass with all data, especially because most of the tables in our application has like 20, 20 something columns.
And we are not careful on how we put them.
Yeah, we can mess up.
Questions? I think we have two minutes.
Possibly, yeah.
Yeah, possibly.
When you have fields with varying size, like the text field, what are the problems?
The text, the VARCA, right?
So they do not play well with padding because as the variable, so the database doesn't know how to optimize them on that way.
So it highly depends on how your information is put.
And that's usually best to let them to the end.
Sorry, can you say that again?
Okay.
Uh-huh.
Yeah, the question is, if I test it on larger database because smaller database might just fit fully in memory.
So yes, I did.
And what you need to realize is all the data, all the padding that we have here goes to memory and all the pad that goes to memory goes to network.
So in all the padding that goes to network goes to your application.
So you're wasting space in your hard drive, in your memory, in your CPU cache, in your network and your application.
What are the numbers?
Well, it highly depends on application to application.
So I would say that's empirical.
There's no science on that one that you can get up to 30% performance improvements.
That's all.
If you have more questions, thank you guys.
Here is my link again.
Thank you.