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.

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.