Nikolay: Hello, hello.
This is Postgres FM, episode number 107.
My name is Nikolay, founder of Postgres.AI, and as usual, my
co-host is Michael, pgMustard.
Hi, Michael.
Michael: Hello, Nikolay.
Nikolay: So, you chose the second most boring topic, in my opinion,
after security.
Tell us what it is.
Michael: Yeah, I can blame our listeners for this one.
We had a… In my opinion…
Nikolay: Blame someone else, right?
Michael: Yeah, exactly.
Always.
That's the first rule.
Nikolay: This is my favorite methodology in troubleshooting,
of incidents.
Michael: No, blame this culture, right?
So we had a great listener suggestion to talk about compression.
And I guess it's kind of surprising we haven't covered it yet.
We've covered various topics around this, but they are specifically
in the context of a couple of extensions that offer compression
options.
I thought it was a good topic to cover generally.
We can talk about all the different things where compression
is available in Postgres.
Kind of a broad but shallow topic maybe this time.
Nikolay: Yeah, we will talk about physical, like not lower level
of compression, not like modern ways to compress data when you
tell some LLM, I have a lot of data in this table, in this column,
it's a huge chunk of text, let's just summarize them and drop
the detailed texts and so on, right?
It's actually my favorite way to compress.
Michael: Well, that's a good point, actually.
I guess I assumed automatically we were talking about what would
be called lossless compression.
Yeah, so like with images.
Nikolay: This is definitely lossy.
This will lose some details for sure.
And it will use some details that don't exist.
Not only it's glossy, it's also glossy, I guess.
So,
Michael: Does it stand for lossy lying machines?
Is that what they stand for?
Lutsky
Nikolay: Yeah, yeah.
So we talk about lossless, lower level, transparent compression
when we just enable something and data suddenly takes less disk
space or space when we send a network, like sending something
in transit, so to speak.
And we are able to uncompress it without any losses.
But it's all fully automatic and users just use it, enabling
some features probably.
This is our focus today, right?
Michael: Yeah, exactly.
And I think that's a good transition into kind of the 2 main
benefits.
And I think they're obviously related, but I think they are somewhat
different and probably in some ways trade off against each other
a little bit.
1 is compression for the sake of storage.
So if we have very repetitive data or data that compresses really
well, we could maybe spend a lot less money and less resources
by compressing it.
But so storage is obviously a big 1.
But there's also the performance side of it.
If it can take up less space, it might be, depending on where
our bottlenecks are, it might be that overall the cost or speed
degradation of compressing and uncompressing or decompressing
the other side is still faster if we've had to only transport
a lot less data or do calculations on a lot less data, that kind
of thing.
Nikolay: Let's maybe start with things we have for many years
in Postgres.
And then discuss some new stuff.
And then discuss and compare what's beneficial, what's less beneficial.
We have, first of all, compression at WAL level for full page
writes, full page inserts, right?
FPIs.
And full page inserts need to fix the problem of difference between
the size of buffers, the pages in memory, 8 kilobytes in most
cases, and the size of block and file system, very often 4 kilobytes,
x4 for example, right?
And to avoid partial writes in the case of failures, Postgres,
after each checkpoint, if the buffer was changed for the first
time until the next checkpoint, Postgres doesn't write only the
change itself, it writes the whole buffer.
And if by default WAL compression is not enabled, it means whole
buffer 8 kilobytes is written as is to WAL, consumes 8 kilobytes,
right?
But if we enable WAL compression, this page is compressed.
And in my opinion, in most cases, if we talk about significant
load, we should consider enabling WAL compression.
It can be beneficial, especially if you have short distance between
checkpoints, because in this case you have more frequent full-page
writes happening.
Of course if you increase distance, then maybe the same page
is becoming dirty.
It means writes happen inside it many times, multiple times,
and only for the first change it will be full page write.
Subsequent changes will be written only like deltas, right?
Only tuple, which was changed.
But if you have quite frequent checkpoints, Enabling WAL compression,
you can significantly reduce the size of WAL written.
And this has very good positive consequences, such as less data
to write to backups.
WAL archiving, archive command will archive less bytes, fewer
maybe like gigabytes per hour, for example.
And second, replication as well.
WAL is smaller, so replication has less to transmit over the network.
Of course, compression needs CPU cycles and decompression needs
CPU cycles.
And I saw on Twitter, some people mentioned they had problems
when they enabled WAL compression.
But in my cases, I observed we always decided to switch it on
and CPU overhead was worth it.
So there is a trade-off here, CPU versus I-O, and we always chose
in favor of less I-O.
And with synthetic tests, I just showed you before we started
this recording, I showed you we had a simple PgBench experiment
with max WAL size 4GB, checkpoint amount 15 minutes.
We saw WAL reduction, I think, on regular PgBench workloads,
which include writes, of course, inserts, updates, deletes.
We saw 3 times less WAL created, generated, or written, it's
the same, right, when we enable WAL compression, which is a
huge benefit, 3 times less WAL.
But if you had the checkpoint tuning, as we discussed in some
of our last, like, previous episodes, if you had it and distance
between checkpoints is quite large, especially if you have Patroni
and modern Postgres, which in case of failover or switchover
doesn't require restart of all nodes, you can afford a bigger distance
between checkpoints and have a maximum size set of, I don't know,
like 100 gigabytes.
In this case, it's unlikely the benefit will be so huge, not
3x.
Of course, it also depends a lot on the data nature, right?
If it's very repetitive, it's easier to compress.
Michael: Well, yeah, I mean, that's pretty true across the board
for compression isn't it?
On the WAL front, 1 thing I think we probably didn't talk about
when we talked about WAL and checkpoint tuning is on the compression
side as of Postgres 15 which I'm guessing is pretty new so you
might not have this kind of in the wild experience yet, but we
do have more options now.
So in the past, we could only turn WAL compression on or off.
And now we have, instead of on, we have 3 different options.
We have the previous option, which is the PGLZ algorithm, but
we also have LZ4 and ZStandard options now.
And for the people that had, that kind of complained about the
CPU side of things, 1 option might be, LZ4 I believe would be
less CPU intensive.
It might not be faster.
Huh?
Nikolay: Faster and more lightweight in terms of CPU consumption.
But the compression ratio is worse.
Michael: Well, it's not that different to PGLZ actually.
It's yeah, but compared to some other modern compression algorithms,
it tends to lose.
But compared to PGLZ, you don't lose much.
I think it is slightly worse on average, obviously depends a
lot on the data.
But I think it's a pretty good option if you have that issue.
And if you're testing, turning this on, on a modern version of
Postgres, worth trying at least, whether, depending on your constraints,
trying those different new options.
Nikolay: Yeah, that's a great point.
And 2 ideas here I have in mind.
First of all, I remember these options, the standard and LZ4,
they require compile flags to be turned on.
I guess, as you rightfully said, I don't have a lot of...
It's fresh things, so I don't have production, rich production
experience.
But these flags, I guess they are turned on in the official apt
packages, right?
If you just install it on fresh Ubuntu.
I hope it's so, I hope it's so, but worth checking.
This option should be turned on.
So Postgres should be compiled with support of these 2 algorithms.
And second thing, I think it's worth new research.
Maybe with our bot it should be quite easy, and we should just
research maybe with different workloads and maybe with different
Maxwell size.
As I said, it's very important in this case, the distance between
checkpoints.
And just with regular PagerBench we could check all available
algorithms on fresh Postgres versions, and maybe draw some charts,
right?
Plot some charts.
So yeah, we just like hands, but bot is definitely ready for
that. So I think it's a great direction.
I wish I had more hands, by the way.
Like I wanted to say we are looking for maybe part-time database
engineers, people who want some fun with this kind of work, like
research.
We usually do it public, so it's kind of interesting for community
as well.
So if some people listening to this podcast want to participate
and work part-time with us with Postgres.AI, definitely would
be interested in discussing.
So maybe we will have more hands and do this benchmark, for example.
Michael: What's the best way for them to get in touch with you?
Nikolay: Email or Twitter or LinkedIn, but email
nik@postgres.ai is always a good way to contact me.
Michael: On the packaging front, I don't know, I haven't checked
whether they have compiled Postgres with those flags, but I was
pleasantly surprised.
I'm on Google Cloud SQL for my own, for the pgMustard database,
and recently upgraded to Postgres 16, finally.
And was pleasantly surprised that I was able to turn on WAL
compression with LZ4 and TOAST, I was able to switch our default
TOAST compression algorithm to LZ4 as well, which is really cool.
Nikolay: On 16 you said?
Michael: I'm on 16, but I think those are available as of version
15 for WAL and version 14 for TOAST.
Nikolay: That's great.
So what benefits, like, do you remember some numbers?
Michael: So in my, I didn't do like extensive benchmarking, but
like the thing we use it for most is we have saved plans.
So EXPLAIN plans compress really well.
It's a lot of repeat.
Nikolay: Yeah, many.
Yeah, it makes sense.
So it's in JSON, right?
Michael: Well, both like text plans compress well too, But JSON
plans compress extremely well.
But obviously JSON plans are bigger in the first place.
So there's not...
Nikolay: Yeah, that's good.
Michael: So yeah, it compresses really well.
But the main thinking was, we don't mind spending a bit more
on storage for those if the speed of retrieving them will be
quicker.
So people save a plan, they might share it with somebody on their
team and that person needs to load it.
Obviously, it's like we're talking small amounts of time, But
if the storage wasn't that different and the speed was faster,
I was happy to make the switch.
And yeah, it turned out the storage was slightly worse on average
for the plans I tested with LZ4, but the retrieval speed was
faster.
So I bit the bullet and did it.
The cool thing is, the thing I didn't realize, I thought it would
be a complex migration.
But like, what do you do?
Like, I thought you might have to change existing plans or existing
data.
But you don't.
If you change the setting, it applies to new data.
Nikolay: Right.
Yeah, actually, this is a good point we didn't mention.
So we're shifting to discussion of storage, compression, and
host, right?
But it's a good point we forgot to mention about wall compression.
It does require restart.
So you can switch it on, switch it off, and all new writes will
happen according to the new setting.
Of course, you need to send the SIGHUP signal or select PgReloadConf,
so configuration changes are applied without any restart, which is
great.
And it also means it's easier to try.
If you have monitoring, if you're prepared to roll back, it's
easier to try.
And if things go wrong, you can return.
So TOAST compression is interesting.
And so, again, like, sorry, I'm not paying attention to details
today somehow.
You chose this standard, right?
Michael: I actually chose LZ4.
I was more interested in the speed of compression and speed of
retrieval than I was for total size on disk.
Nikolay: Meanwhile, I asked our bot to double-check how apt packages,
official pgdg packages are created.
Of course, these 2 options are there, so if you install Postgres
on Ubuntu, you can try various compression algorithms.
Well, just to double-check.
Okay, so TOAST, what else?
We don't have a good ability to compress table data, right?
By default in Postgres we don't have it, And it's actually not
a simple topic in restore.
In restore, we have, yeah, so tuple by tuple is stored and it
can go in different pages.
So like, If we compress it, we can transparently compress it
switching to ZFS, for example.
We saw benefits in terms of disk space, like 10 to 30 percent,
depending on the data nature.
But ZFS brings new challenges for administration, for sure.
It's still good to shave off 30%.
And this is what you get by default if you install DBLab because
it's on ZFS.
So if you have, for example, a terabyte size database in DBLab,
it will look like 700 to 800 gigabytes only.
It's much better.
But yeah, so But in Postgres itself, there are no good compression
options for heap.
Michael: Except for single large, like if you have 1 column that's
large, I think toast is very good, but not for like, smaller
values.
Nikolay: Oh, actually, yeah, big values are compressed in heap,
right?
Now I remember before going to TOAST, like Postgres tries to,
to, to squeeze, like, to, to fit them in size, like 2 kilobytes
and have 4, 4, roughly 4 tuples per page.
Roughly, like it's like kind of things I remember.
Michael: Once it's compressed, it's under 1 kilobyte or
something like that, they put it in line on the page.
Nikolay: Or in line.
Michael: Yeah.
It just means that if you have values that are multiple kilobytes
and compress well, even the default PGLZ will give you quite
a lot of compression out of the box, transparently in Postgres.
It's just, if you have lots of repeating small data, like time
series data, that could compress as a whole very well, the row
store...
Nikolay: But this compression is applied to a single tuple, single
row version.
So a single row version, it means that we only have 1 timestamp,
for example, 1 temperature, and so on.
Several different columns, maybe a couple of timestamps, but
different nature of timestamps.
For example, created that and I don't know, like registered that,
something different timestamp.
And Postgres tries to compress the tuple and TOAST, like we didn't
cover and there's no goal to cover it deeply.
We had another episode on it.
Right.
So Postgres tries to compress.
If it doesn't fit, it shrinks it already and chunks are stored
in separate so-called TOAST table, which is actually also a regular
table, which is kind of invisible to user, but you can inspect
it if you want as well.
Then the compression and reconstruction of tuple is occurring
when it's needed.
But what I'm trying to say is there is no...
For the heap itself, there are no rich capabilities to control compression.
And even if we had them, expected benefits would be not high
compared to analytical column store databases, where, for example,
we have all temperatures or all timestamps stored in a separate
file.
Only this column for all rows is stored here.
They all are temperatures and maybe these temperatures are coming
from, for example, if they're coming from a single source, they
don't jump.
I don't remember the word in English, sorry, but the changes
are not acute.
If they change, for example, if temperature is increasing, probably
it will be increasing for some time.
It means that compression could be done using various algorithms,
for example, applying deltas and storing only deltas.
We will probably discuss TimescaleDB soon because this is pointing
in the direction of TimescaleDB and what it provides.
But so for ColumnStore you can compress like 10x which is not
possible
Michael: for rows.
Yeah, or more even.
But I think you're missing the multiples interesting, right?
But you've missed the fact that, like, let's say we had a row
that is, let's say our tuples are like a megabyte, but 99.9%
of that is a single JSON column, and we have 10 other tiny little
integer columns.
We get way more than 10x compression ratio just with TOAST, you
know, as if that block is as well.
Yeah.
So, I
Nikolay: don't...
As you know, probably, I, like, when I was very young, I participated
a little bit in XML function and data type development in Postgres,
and I remember that time XML compression was the thing, big thing.
And I even remember hardware accelerators companies were selling
to compress XML on the fly transparently.
It's crazy.
It's because a lot of structural pieces of the values can be
compressed well in XML for sure, but less than JSON, but still
also all those parentheses, braces, quotes and so on.
Or maybe it's also some, if it's JSON, not JSON B, a lot of white
spaces.
Right?
Yeah.
So, yeah.
If you have JSON and, yeah, so in some cases, compression and
TOAST, like tuning it, it's interesting.
And maybe also we need benchmarks, so maybe for JSON as well.
It's good.
Michael: Yeah, it Nikolay: would be
Michael: interesting, but what I mean is more like the compression
ratio is a lot, is varied, obviously extremely dependent on the
data.
But I think
Nikolay: I'm very curious what you have, for example, if you
know that some old values are not compressed and some new values
are compressed, you check how much storage is occupied by, I
don't know, by like 1000 rows or so.
It's worth checking.
Michael: Well, you can't, it's like impossible to store large
values in Postgres without, actually no, you can turn compression
off, but TOAST, you can turn compression off with TOAST, but
the default is on.
So I have never tried it without.
So I don't have that to compare, but just give you an idea.
We didn't use to compress plans in so we only store plans in
local storage by default, I can browse local storage, and that's
an extremely limited resource.
Like in some I think in Firefox, it's like 5 or 10 megabytes,
depending on the version.
So that's not very much when you're talking large query plans.
Like, obviously, it's a lot for most people's query plans, but
some people will come up with query plans that are like, dozens
of megabytes.
Nikolay: Varlena data types accept up to 1 gigabyte, right.
But as I remember, It's better not to go beyond a couple of hundred
megabytes because the performance will become terrible.
Michael: Well, just to give you an idea though, quickly, these
plans were compressing from megabytes down to kilobytes.
Like it was easily, yeah, It was more than 95%.
I think in some cases, like, yeah.
Nikolay: That's cool.
But still, it's all compression of only 1 tuple and actually
1 value, not tuple.
Michael: 1 value, yeah.
Nikolay: But It feels like it would make sense to have some compression
at maybe at page level as well and so on.
This is what ZFS provides transparently if you put Postgres on
top of it, right?
So compressing pages, I think, even if it's still a raw store,
maybe having these abilities...
I remember Peter Geoghegan talked a lot about new features in MySQL
many years ago.
I think MySQL has more settings in this area for compression
of storage.
Have you heard or remember something from it?
We have mostly questions today, right?
Michael: I didn't know that.
Well, they seem to be a bit further ahead on the storage engine
side of things, don't they?
Like they've had multiple storage engines for quite a lot longer
than...
Well, like we don't have...
But yeah, so it wouldn't surprise me if they were ahead on that
front.
But yeah, you mentioned Timescale, it feels like we've had a
history of a few extensions that have offered the option for
columnar storage.
Nikolay: Before going there, let's just touch a little bit on
options that Postgres could have, and there are some discussions.
Like, what else could be compressed inside?
For example, temporary files.
Yes, good idea.
Michael: But we should also probably talk about things that we
actually haven't mentioned, like backup files, pgdump files.
Nikolay: I know, this is already...
Yeah, I wanted to do it, but look, before going to backups or
dumps, backups and dumps, it's already going outside of Postgres.
But imagine we have Postgres, we run some queries, we discuss
the WAL, which is the first thing we should discuss because
data is written there first before it's written in data files
or pages and so on.
So we discussed WAL, we discussed the storage itself, TOAST,
this is what we have and that's it.
Not at page level, but it can be achieved by a file system, but
that's it.
Indexes, I have no idea.
Maybe deduplication, which was done in Postgres 13, 14, but by
Peter Geoghegan, Anastasia Lubyannikova, maybe this can be considered
as compression actually, right?
Michael: That's a good shout, yeah.
Nikolay: Native compression, you know, like pieces of compression
because it occupies less space, right?
It's kind of...
Why not?
So, But it's optimization, but still.
And last time we discussed, what was the topic last week?
Michael: Out of disk.
Nikolay: Out of disk, right.
And remember we mentioned, I named it exotic and some listener
commented on YouTube, By the way, thank you all those who write
something on YouTube or on Twitter or LinkedIn.
It's very good to receive feedback.
So they mentioned that temporary files is not that exotic.
And if you run huge, heavy queries so they had it, you can be
out of disk space because temporary files were huge and consumed
a lot of disk.
And the compression of them makes total sense to me, right?
What do you think?
We don't have it, right?
Michael: I was watching a really good video that you sent me
a link to by Andrei
Nikolay: Borodin.
Right, right.
Michael: Cybertech 2023.
Yes.
And I watched it last night and he mentioned that in order to
be...
I think there was a problem with durability.
If you want to be...
Nikolay: Forget about problems.
This is like...
I'm just...
It doesn't make sense.
My answer is
Michael: yes, it does.
David Willis He started doing the work and it's more complex
than it might sound.
But yeah, it makes sense, but it's complicated.
Nikolay: Full disclaimer, I just chatted with Andrei this morning
and this is what he told me and I'm just shamelessly using his
ideas.
So it's worth compressing pages, as I mentioned, and it's worth
compressing temporary files.
Andrei says it's just lack of time and hands, but it's worth
implementing and proposing Pudge.
So, Yeah, it's a great idea.
I hope he will find time to work on this and other people probably
will find time to move Postgres forward in this area.
And in this case, imagine if temporary files are compressed.
In this case, I would be more right saying it's exotic to be
out of disk space when you have temporary files occupying a lot
of disk space, right?
Michael: I think it makes extra sense because the type of things
that are generating temporary files like sorts and hashes, it's
probably a lot of similar values.
I'm guessing there would be a fair amount of repetition which
would naturally suit compression as well.
So I'm wondering if there's like a...
Not only would it just take up less space, and obviously there's
benefits there, but I'm wondering if there might be performance
benefits too.
Nikolay: Yeah, that's a good point actually.
When we talk about saving disk space, sometimes it's also about
performance.
If disk is slow, It's better to consume cycles of CPU for compressing,
decompressing, and things become much faster.
I agree.
This is a good point.
But it's not always so.
It depends.
In general, I think I saw somewhere, there's a general point
is that databases are I-O hungry.
And like, as you very well know, we talk buffers, buffers all
the time.
It's just like confirming it's all about I-O usually.
But I also saw CPU 100% and lag CPU, especially for example,
we are on Graviton or like ARM and we only have up to 64 vCPUs
and That's it.
These days it's not a lot, 64, right?
It's like moderate number of…
Michael: It's a lot for some people, yeah.
Nikolay: Right, right.
But it's not 360, it's not 800 like you showed me on AWS for
Intel scalable Xeon.
So if it's 64, a lot of clients, in this case CPU becomes a very
valuable resource.
Right?
Michael: Of course.
Nikolay: Right.
So in this case, probably we might be preferring to spend more
disk IO cycles.
Okay, what else can be compressed in Postgres?
We talked about ideas which are not currently implemented.
Temporary files, page level compression, maybe protocol level,
which has pros and cons.
Maybe let's not go there, right?
What else?
Michael: Anything else in memory that could be?
Nikolay: Oh, interesting.
Maybe.
I don't know, But I think if we, for example, shifting maybe
to some additional projects, I think we will talk about Hydra
and TimescaleDB.
If, for example, we have a storage engine and we have some tables
defined as column store, this is what Hydra provides.
In this case, it makes total sense to compress those tables.
Or, for example, if we mentioned last time, talking about disk
space, or we have raw store, but we have partitioning and some
old partitions we consider as having archived data, and this
data we want probably compress, and maybe we want to store it
not on the regular disk, but on object storage, and we discussed
PGT, Tempo developed.
So kind of bottomless Postgres, but we want all data to be compressed
and rarely used.
Right?
Michael: Yeah, I saw 3 different approaches from different extensions.
The Hydra 1 you mentioned, I think came from Citus data originally.
C-Store FDW I think was the original.
Nikolay: It's AGPL from Citus inherited by Hydra.
Michael: Yeah, so I think that is very much like from the start
of a table's life, you choose whether it should be row store
oriented or column store oriented.
That's 1 approach that makes a lot of sense.
The timescale approach seems to be...
Nikolay: And column store, of course, we want to probably compress
quite a lot because the ratio usually is good.
Michael: Well, yes.
But I got the impression that the main aim originally with the
Citus ColumnStore approach and therefore Hydra, was yes, you
get some compression for the storage benefits But the main aim
seemed to be so that we can make analytical queries faster So
again, so it's that performance angle That was the seem to be
the driving force of why should we want it to be column store
in the first place for performance of analytical queries that
tend to be aggregates over a single column.
And if we've got if we've got column oriented data that's compressed.
Nikolay: It's massively a lot of I.O.
Of course, we want to use this I.O.
And then already deal with it in CPU, but also nature of data
is like a lot of similar looking values and compression.
Michael: And I think once it's organized in column, like by columns,
you can also start to store much easier, like the metadata of
like min max values and do some like shortcuts, I think on that
data as well.
So I think there's some like cool tricks as well.
Now but But there's then I think there's 2 other approaches that
I've seen.
1 is the Timescale approach, which seems to be on older partitions,
like on older data.
Everything's raw store at the beginning.
And then after a certain point, you set a policy that it gets
converted to column store later once it's unlikely to change?
Nikolay: LBK I remember differently.
I remember it's always row store but compression works in 2 dimensions.
Like for example, I'm not sure if they converted to column store,
maybe I'm wrong, but what I remember is still row store but with
understanding of vertical dimension, so to speak.
For example, storing deltas instead of row values and applying
compression.
Michael: Well, I mean, it's still in Postgres.
It's still in Postgres, so it's still row store under the hood,
but it's column-oriented, like it's organized by column.
Like, if you look, the Postgres docs are really good on this.
I'll share a link.
Sorry, not the Postgres, the Timescale docs.
Nikolay: I like understanding here, apparently.
Michael: But there's a third approach as well.
I think that's over the Timescale approach I think is more optimized
for space.
I think the compression is more on the, let's make older partitions
take up less space because you have so much data.
Like 20 times less.
Like 20, yeah, like some of the numbers they mentioned, like
95% compression.
We have impressive
Nikolay: numbers observed, yeah.
It's really good.
Michael: But yeah, but that's the idea is with time series data,
you could end up with hundreds of terabytes like, and they've,
they have themselves.
So I think it's, it's the kind of time where you could actually
save a lot.
Now, obviously they also benefit from the performance on analytical
queries and they have some cool features there, but it feels
like the way they've implemented it was primarily for those storage
benefits.
And then the third one, I think, has popped up relatively recently
in the grand scheme of things, is this idea of, as you mentioned,
the tiering or like the moving to a file like format like Parquet.
So exporting data out of Postgres into a compressed format on
object storage that's normally column oriented so that you can
get these fast analytical queries and they take up a lot less
Nikolay: storage space.
There's some big limitations for data types, I suspect.
Only a limited set of data types supported for those kinds of
things.
Michael: And how do updates and deletes work?
I don't actually know all of the details.
Not Nikolay: possible, I don't.
Michael: Yeah, so there's definitely limitations and differences
between these.
A lot of, we're not gonna be able to describe them all here,
obviously, time-wise.
But I found it really fascinating that there's these 3 different,
quite different approaches.
What do you think?
Nikolay: Right.
Yeah, well, it's super interesting to observe progress here.
I like, probably again, like, for me, this episode raising more
questions than answers.
I think after this episode, I will be planning more experiments
to study benefits.
And I guess we always need to take into account several metrics,
compression ratio, speed of compression, speed of decompression,
right?
Maybe the CPU overhead itself, if it matters for us, how much
CPU we consumed.
So yeah, TimescaleDB and Hydra are interesting in this area.
And I still, like, I'm still, I remember my big impression reading,
from reading the TimescaleDB details in their blog post, how
they implement compression.
I think we forgot, because of me, we forgot to talk about compression
of dumps.
Oh yeah.
PgDump has compression options.
And also compression of wall files as a whole, which I think
Postgres doesn't provide it, but both PgBackRest and WAL-G, I
think the most popular backup tools, they both do it, right?
Because if you have 16 megabytes file, if you compress and you
have like 3 times less, for example, as I remember, like 5 megabytes
maybe or so, maybe even less in some cases.
Again, it depends on data.
In this case, it's much better in terms of storage costs and
transfer speed and so on.
But this will consume some CPU cycles and usually we archive
command is working on the primary.
This is the key here.
Usually, to avoid risks of having longer delays, lag of archiving.
Because we need walls, it's part of our DR strategy, the disaster
recovery strategy, so we want to archive wall as fast as possible.
And this means we do it on the primary.
And if we archive whole wall, we need some CPU.
And if a lot of wall generated, probably we will need multiple
workers.
And then you see already more than 100% of single CPUs, meaning
multiple cores are busy.
200 percent out of our 360 cores, vCPUs, We allow 2 cores to
be used to compress and archive walls.
It's just like some random numbers in my head, but definitely
if we talk about compression of whole walls by WAL-G or PgBackRest,
we need to keep in mind this, the most valuable CPU resource,
which is on primary.
We need to think about capacity here.
And decompressing usually is not a big deal, especially if we
can fetch walls using multiple workers from object storage, like
S3 and decompress them.
Usually it's not a big deal, but still worth remembering.
So usually we have compression there.
Unfortunately, Postgres right now doesn't do it officially, so
this is only...
Here I talk about, again, third-party tools which are very common,
popular, WAL-G, PgBackRest.
Others as well, I think they also compress.
And PgDump.
PgDump is official, very official.
PgDump, PgRestore.
They support compression for custom or directory or both formats,
right?
I always mix
Michael: them up.
Yeah, but, and this is 1 of the oldest, like this is, this has
been in forever, right?
Nikolay: Right, but compression was forever, but what about options
in terms of algorithms?
Oh, yeah.
ZStandard and LZ4, so I think it's relatively new.
And it's worth again experimenting, benchmarking, and studying.
I think there are articles from cybertech, right?
Let's check.
Yeah, There are articles comparing different types of compression.
and decompression.
So ratio, I think you can even control a ratio there if you want
to spend more time and CPU capacity to achieve a little bit more
better compression ratio, it's possible.
Yeah, I remember some surprising results from those articles.
I don't remember details, so let's attach them.
But definitely we want to study this as well.
Again, mostly questions today, not answers, not exact recipes.
But it's good that the only thing I don't like in PgDump, PgRestore
is that you cannot use parallelization and compression on the
fly.
What if I don't want, this is 1 time operation, I want just logically
migrate from 1 database to another database, and I don't want
to send a lot of bytes over the network.
I want to compress and decompress and use multiple workers because,
for example, these servers are not used right now.
So I just need to migrate out of RDS to self-managed Postgres
because I feel enough level of confidence and I've already worked
with great guys who know how to do it and RDS is not needed for
me anymore and so on.
So, in this case, you need to, unfortunately, you need to first
save it to disk and then restore from there.
Yeah, so this is, I think, a big missing feature of Postgres.
And Dimitri Fontaine created pgcopydb, I think, but I quickly
checked before our recording, I didn't see anything related to
compression.
It talks only about parallelization.
Let's have like 16 workers or, I don't know, 4 workers to speed
up the process, which is in my head quite relatively simple idea.
We just create repeatable re-transaction, keep it, export snapshot,
create other transactions, repeatable read transactions, and
use the same snapshot so all of them are synchronized and you
can even read 1 huge unpartitioned table in chunks.
It will be a fully consistent read using multiple workers.
But no compression options.
I couldn't find it.
Maybe it's not an easy idea to implement.
Maybe it's a good idea.
So what I would like to have is just pgDump and pgRestore supporting
both parallelization and compression without the need to store
intermediate file Or directory.
Would be great.
Makes sense.
Yeah, I needed it yesterday.
Copying huge, not huge, not huge at all, like 10 or something
gigabytes tiny table with big vectors.
I mean, a lot of vectors.
By the way, let's make a full cycle and in the end mention that
although we joked about lossy compression using LLM, under Liontag,
when you have a lot of dimensions, huge vector values, it means
TOAST is involved definitely.
It's interesting how it's compressed there.
In this case, just simple idea.
Sometimes reducing the number of dimensions means kind of lossy,
but it's also kind of compression, right?
And OpenAI speaks about it.
It's also an interesting area, how to like, not losing a lot
of quality.
Michael: On vectors, though, I think Jonathan Katz made a really
good point on the episode we did for Postgres FM, that because
it's mostly, because the vector data, like at least the embeddings
that come back from models like OpenAI, it's mostly random integers,
it doesn't compress well.
So there's actually, I think, I'm not sure he's done it yet or
he's planning to do it.
There's work to look into whether you'd actually be, it'd be
beneficial to turn compression off for TOAST of vector data because
you're not getting much compression, there's no point paying
the overhead of the compressing and decompressing each point.
So I thought that was super interesting as a use case for turning
it off.
Nikolay: Yeah, interesting.
I would like to explore it myself.
It's a very interesting area.
So I think we discussed maybe 5 different directions for benchmarking.
I would like to conduct these benchmarks.
Michael: Cool.
Let us know how you get on with all 5 by next week, right?
Nikolay: Yeah.
Well, yeah.
So thank you.
It was interesting.
Many questions.
Michael: There you go.
Not boring after all.
Thanks so much, Nikolay. Catch you next week.