Nikolay: Hello, this is PostgresFM.

I don't remember the number of episodes.

This is Nikolay Samokhvalov from Postgres.AI and my co-host as

usual, Michael Christofides from pgMustard.

Hi, Michael.

Michael: Hello, Nikolay.

I think this is 103.

Nikolay: Okay, not bad.

So we discussed what?

Michael: Yeah, so it's one of those things that's come up in discussion

a few times.

And we said we should do an episode on that one day.

And so we finally got to it and it's soft deletes, which is a

pattern that a lot of us have come across whilst working with

databases more generally.

It's not specific to Postgres, but it's the idea of instead of

deleting data, not deleting it, but hiding it from the application

in some way.

Nikolay: Let me explain it for regular, like how it feels since

I've built three social networks in the past.

I used soft deletes a lot, of course.

And then like Postgres is not good with deletes due to how MVCC

is organized, vacuum and so on.

Deletes are not good, right?

It's like an old idea.

Although each update consists of delete and insert at a physical

level, right?

Unless it's a hot update.

So how it feels in the eyes of millions of users I know, and

it's not only about the projects I've built, like almost any

social media uses it.

You go and try to unregister your account, you press all the

buttons say delete me or something like everything or just I

don't know sometimes you need to write to support or something

but usually it's good services have this button ready for you

right So I want to delete my account fully.

And then you find out that, for example, depending on implementation,

then you find out that your page is, I don't know, some small

artifacts you can see and feel that your page was not fully deleted

or your account was not fully deleted.

One of these artifacts, not artifacts, signs that SoftDelete is

used is You can actually restore it, right?

Easy, right?

You can, for example, Facebook.

Try to delete yourself on Facebook.

It will give you many chances to restore, and even after you

delete it, you still can restore it.

So you can, like, one month later, you can just press a button

and restore.

If you, like, what users want when they are angry about some

service?

They don't want to deal with it
completely, like, something can

happen in their lives, right?

They can see having an account
as a problem, for example.

They think it's a mistake to post
such pictures or to do some

conversations.

It doesn't matter.
I want to delete it.

I'm a new person right now.

It's there, right?

So, delete.

But then the server says, you can
restore.

Did you delete my data or no?

And then compliance, we have also
interesting points.

In some cases, we have mandatory,
like demand to store data sometimes.

Right?

Michael: We have both sides, right?

Yeah, we have laws that demand
we have to store data for a certain

amount of time in some environment.

Nikolay: And vice versa.

Sometimes if user decides the service
must delete data.

So delete, the word delete has
many meanings, right?

It's not SQL statement delete.

When you delete your account, it
doesn't mean the service executed

actually delete.

Most likely they just executed
updates or delete with triggers

and so on.

We will discuss, dive into details.

But for users, it's really annoying
to feel lack of ability to

truly delete.

I must say, like I saw it and I
was on the other side of, I was

on both sides many times actually.

And I know how it feels when you
want to delete for sure, like

100% I won't delete my data, it's
my data deleted.

No, you can restore it means it
was not actually deleted.

Michael: Yeah, for sure.

Well, I think we can come back
to like,

Nikolay: technical

Michael: the user side.

Well, I think there's I think there's
so many parts of this that

are interesting.

1 is kind of, If you do decide
you want this as a provider, implementation

details are quite interesting and
there are different and Postgres

specific details are quite interesting
as well.

But from the usability side of
it, there are different implementations.

I think various companies and organizations
are implementing

features around deletion, around
kind of making sure people know

exactly what they're doing before
hitting delete, you know, write

the words of your account name
in here.

Everyone's come across these kind
of make sure you type out,

yes, I'm sure I really want to
delete my account and I will not

beg support to undelete it later.

Nikolay: We give you 1 month to
make, to reconsider it or something.

Michael: Exactly, Or we can recover
it, but only in the next

30 days.

Type them.

Yeah, exactly.

Nikolay: I remember another way,
another sign that you can see

that soft delete was applied is
instead of having 404 error,

404 error, you go to your X page,
like you deleted it, but you

see something like account deleted.

It means that records still exist
in database, right?

Just changed its status.

So status like active, deleted,
right?

Pending, like pending, pending,
it means you need to activate

it, like email activation or something,
phone activation.

Then it's active and then it's
deleted, lifecycle, right?

Michael: In my mind, that's kind
of like a bug though, right?

Like that's just a poorly implemented
soft deletion.

Nikolay: Yeah, but it's so common.

And I actually implemented, I implemented
soft deletes in many

various ways.

But honestly, this morning, thinking
like walking my dog, By

the way, hello to everyone who
is running, walking dog, like,

I don't know, like riding bicycle.

Yeah.

I know we promised to keep 30 minutes,
but it's good for you

to run longer and ride bicycle
longer and for your dog as well.

So walking my dogs I was thinking,
soft delete, soft delete.

I also chatted with our bot to
like to brainstorm what to discuss

and I realized that if I needed
to implement it right now, looking

like back to my 20 years of experience,
3 social networks and

many other systems and our customers,
all those ways to implement

soft delete, what would I choose
now?

I don't know.

Maybe by the end of this discussion,
I will have some clarity.

But it's so difficult.

It's not an easy topic, actually.

I expected a simple topic today.

Michael: I think it's simple if
you only consider a single case

at a time.

If you want to give generic advice,
I think it becomes more complicated.

And we also dove into the most
complicated version, which is

deleting a whole account which
has multiple things.

Nikolay: Yeah, let's talk about
only 1 table.

Yeah, propagation is a different
topic.

Consider only 1 table.

We have many choices.

For example, 1 I already discussed,
like status.

You have pending, active, deleted,
something, archived.

Or it can be a different column
is deleted by default.

It's false, you can set it to true.

Both are very similar approaches.

Both I don't like at all.

Michael: I quite like deleted at,
which is with it being null

by default, and then a timestamp
as to when it was deleted.

Right.

Nikolay: I would not choose these
approaches.

Neither of them.

Today.

I chose them in the past.

Yeah.

Today, no, thank you.

Why?

Because then you need to deal with,
like, optimizing your queries,

thinking about indexes.

Most of the time, queries work
with active data.

So you need to keep all your indexes
probably partial with the

WHERE clause having this filter
to deal only with active records.

And you accumulate like non-active
data set in the same table,

which is not useful at all, right?

So and also foreign keys.

Michael: Yeah, unique constraints.

There's so many downsides.

Nikolay: That's complexity.

Michael: Well, and you need to,
you need to adapt your, your

application to always view things
where deleted at is not.

Because otherwise people will see
deleted data.

So there is definitely complexity
to that, but implementation-wise,

I think it's on the simpler side.

I think there are some advantages.

Nikolay: I maybe would choose it
only if I know that deletes

will be much less than 1% of all
data.

But who knows in advance, right?

Maybe we will need to delete a
lot.

Sometimes, from social media experience,
spammers, in ways they

come, They can register a million
accounts and you need to delete

them.

At the same time, to follow your
procedures you developed, if

you developed a soft deletes approach,
you're deleting fake accounts

is also deletes, right?

So you need to still keep them
in the same table and it's like

a lot of records which will sit
on the disk and most importantly

in memory, in caches.

Michael: Let's talk about the use
cases for it because I think

they're different enough that I
think it will then become clearer

when we would use different ones.

Like, for example, I think the
primary use case, the only use

case I've ever used Soft Deletes
for in the past, I've always

been at companies and working on
products where we haven't done

soft deletes by default.

I've only ever had it implemented
it once for an application

because several times a year, we
were restoring data that had

been deleted accidentally or prematurely.

So it was a,

Nikolay: and you didn't have that
by slab to, to, to go back

in time.

Easily.

Michael: We could risk the solution.

We, we, this is actually on the
Microsoft SQL Server database

at the time.

And we would actually restore from
a backup.

That's what we'd end up doing.

Nikolay: It takes many hours.

Michael: The larger the database,
the longer it takes.

And it's painful and manual.

And we were doing this often enough
that we decided to implement

soft deletes.

But with a slight difference.

I think sometimes people describe
soft deletes as if it's like

permanently gonna be in that soft
deletion state we would do

it and then have a like a Cron
job that anything older than so

people would delete something by
mistake and very quickly email

support and say, I deleted the
wrong project in this case.

Nikolay: We need to restore.

Yeah.
And restore procedures is complex.

Michael: Yeah, but it was always
quickly.

It was always within a day

Nikolay: or 2.

It's quicker.

Michael: No, sorry.

So in this case, with this application,
people that delete things

accidentally, they knew pretty,
like almost straight away that

they had done it and that they'd
regretted doing it.

So we knew we could, for example,
have this data in in a soft

deleted fashion with deleted app
with a timestamp.

And then we could have a cron job,
hard delete, or actually delete

data that was more than like, that
was deleted app more than

30 days ago.

So in that case, it meant we could
keep the structure simple,

only add 1 column, update the application
only in a few places

to view the data that wasn't deleted
yet.

You know, so it was a relatively
simple case where we were having

to restore data from time to time.

Nikolay: It's a good point.

So I took extreme case when we
discuss accounts and it's like

sensitive to like personal feelings
are affected.

But you're describing basically
the same thing as emails in Gmail

and Recycle Bin or like how it's
called trash folder.

Michael: Yeah, like on a desktop.

Yeah.

Nikolay: Right.

So you put it there basically when
you do deletes and it sits

there for 30 days, right?

And then automatically hard deleted.

Michael: Exactly.

Nikolay: And you can restore it
anytime and users usually appreciate

this feature and consider it as
good if you can undo your action.

I agree.

Like when we talk about some objects
inside the account, right?

So some data.

Michael: The only difference is
in this case is we didn't implement

the ability to view what's in the...

Like in those 2 cases, what we
could have done.

And that's actually, potentially
would have been nice for users.

Nikolay: Yeah, actually in this
case, this approach, now I'm

thinking, yeah, in this case, I
would probably choose some status

approach because it's actually
the same data, we just don't show

it in 1 place, but we show it in
another place.

So it's just some category, that's
it.

Right?

You're right.

So in this case, I would prefer
actually status, maybe timestamp

of changes, but maybe I would put
timestamps to special different

like audit, like log style append
only table to track actions

when some action was done.

Yeah, but you're right, actually.

Maybe this case, let's call it
in-place soft-delete when we keep

data in the same table and so on.

Michael: I was thinking use-case-wise,
that's if you're implementing

it to avoid or to make restoration
easier or undelete easier.

Nikolay: And viewing deleted data,
if some in some cases like

trash, trash data, if you want
to allow users to review it, Why

not?

Michael: Yeah, well, that's the
other like, there's another use

case for soft deletes I've heard
and seen people write about

which is to give you the ability
to examine it or to audit it

or to look through why it was done
or who did what.

So I think there are some...

Exactly.

And I know we've had a whole episode
on audit, but it could be

not necessarily for regulatory
purposes, but it could be for

debugging purposes.

Nikolay: And there we can have
a hard delete button for users

as well.

If like only there, not in main
place, but for like if object

already soft deleted, we could
allow users to actually delete.

But let's talk about Postgres spec.

This is UX discussion.

What else?

Compliance.

Sometimes you do need to delete
data for sure, like keeping it

only in some backups and also have
some policy to delete there

as well.

I mean, backups should be deleted
as well at some point.

Sometimes you do need to keep information
and soft deletes help

to comply some policies and regulations.

Depending on law you need to follow,
like it's hard.

But honestly, I prefer, speaking
of law, I prefer when I need

to delete everything.

But for convenience and as a business
owner, it's actually much

more convenient if you still have
data all the time and you can

review what happened and restore
it?

Well, sometimes, depending on the
nature of your business.

I see more and more guys follow,
like, it's like some compliance

rules, but I see more and more
companies implement the rule that

Slack deletes messages after some
time and everything is deleted.

I don't know.

Maybe it's because of costs.

Maybe it's some compliance, actually.

I don't know.

Michael: Well, I think there was
an age where this going back

to kind of philosophical side of
things, but I think there was

a time where people, I remember
reading headlines like data is

the new oil and things like that
and people thought hoarding

as much data or gathering as much
data as they could would be

valuable in the future for analysis
reasons or for mining, that

kind of thing.

And I feel like with a lot of data
breaches, especially, sorry,

I think I've got a siren in my
background, with data breaches,

I still feel like things flipped
and people started to think

of having data as a liability.

And the more data they held, or
the more personal it was, the

more at risk they are of, or the
bigger target they become for

attacks.

I feel like that's changed even
within the last, well, even within

my career of how people view holding
data if you don't really

need it.

Nikolay: Yeah.

Also performance reasons.

If you keep everything, it's hard.

I see many successful e-commerce
companies, they do need to actually

delete data, and having soft deletes,
they at some point decide

to clean up and actually delete
data.

So in place of soft delete, you
just have a column or a couple

of columns, and then you have a
lot of partial indexes, obviously,

maybe.

Right?

Maybe.

Maybe not.

It depends on the distribution
and selectivity you have.

And how could we improve this?

What about having a special partition
or a set of partitions

and include this column to partition
key.

It would be transparent for application
if we update status setting

from active to deleted.

In this case, the row will be deleted
in the main partition,

like actual working partition and
it will go to this kind of

archive partition, right?

Michael: Yeah, it would be like
moved.

So I guess it's similar in terms
of like write-ahead log impact

and but actually could it be higher?

I guess you're forcing no hot updates
but I'm guessing you're

not going to get hot updates anyway
because it's the delete that.

Nikolay: Yeah.

You cannot have hot update here.

So 0 chances because it's actually
deleted, actually inserted.

I guess hat updates is a good thing
to have, but in this case

you should avoid partial...

Back to a single table and just
a column.

If you want hot updates to be used
when you update this column,

you cannot use this column in partial
indexes in the where clause

of those indexes.

You must avoid it.

It works well only if you have,
as I said, only low part of your

table of deletes.

Like deletes are rare.

Michael: Yeah.

So another, like another solution
I see mentioned that I've not

used is using a view.

So defining a view where deleted
at is, has no value for example.

Nikolay: Yeah, I don't know.

You know like when you're in love
with something many years and

then this love is converted to
hate.

This is my relationship with views
because my, yeah, my thesis,

martial arts master, I don't remember,
was about updatable views

and then updatable XML views and
like, oh, I spent so much time

and I worked with them so much
time.

Now the only views I can deal with
now these days myself in my

projects where I have full control.

Only Postgres views.

Michael: That's it.

Nikolay: Everywhere else I avoid
them because I don't want to

deal with dependency hell later.

Because it's just a lot of overhead.

You just change something or add
column or drop column.

You need to recreate your views
and logs and logs when you issue

DDL, like, I don't want this.

But Yes, views can be used in many
cases, but you cannot create

an index on a regular view.

Again, I'm speaking of performance.

Just views will hide it, right?

And that's it.

I mean, hide some rows.

Michael: Exactly.

But it also makes the implementation
a little bit easier because

you don't have to update.

You don't have to make the application
aware of your soft delete

implementation.

Nikolay: Well, yeah.

Michael: You could just point the
application at the view now.

The Application doesn't

Nikolay: see those rows at all,
right?

Michael: Should we move on to some
other, like...

Nikolay: Approach.

Separate table approach.

Michael: Yes.

Nikolay: So straightforward approach.

Let's just have the table with
same structure, right?

Maybe not the same set of indexes
because we don't need all of

them, maybe, right?

And just insert to that table,
maybe using some trigger when

delete happens.

Michael: Yeah, so the 2 approaches
I've had are a shadow table

for each table that you want to,
yeah.

I don't know if that's the correct
term.

Nikolay: And I also hated this.

Because DDL, maintaining schema.

Michael: But I did see a neat alternative
to this in a blog post

by Brander, who's got a couple
of blog posts about DDL.

Nikolay: So let's discuss why it's
bad, like in detail, just

to clarify.

Good idea.

First of all, you need to create,
if you have many tables where

you want soft delete to be implemented,
you always need additional

table.

It's just overhead.

And also you need, every time you
create a column, for example,

or a name column or something,
you also need not to forget to

do it in the shadow table.

And living with such schema many
years in 1 project, I must admit,

it's a huge headache.

People always forget it.

You can try to invent some procedures
and automation not to forget

it, but still, deployments to production
when shadow table DDL

was forgotten, quite significant
risk.

So It's a lot of objects to deal
with.

Of course, you can put it to some,
say, schema named shadow,

for example, and keep the table
names exactly the same as in

the original schema, public or
something new, if it's a new project.

So public schema is not popular
anymore, right?

So, and it's good because it means
convenience because exactly

the same table names, but it introduces
a lot of overhead, it

requires good culture of automation,
like tooling, not to be,

to be always in a consistent state.

Michael: Yeah, I think there was
a lot, there are a lot of difficulties

as part of this.

Obviously, it does come with some
benefits, like easier to restore

than the next, the alternative
way of doing this.

Nikolay: Let me guess about alternative
way.

I also implemented it.

I didn't read this post by Brandur,
but I can guess because I

also implement I think we walked
on the same paths, right?

So you create a single table with
probably like some ID maybe

some surrogate ID then table name
who deleted timestamp and then

just JSON or JSONB column where
you just put all the data from

any table.

Right.
And you can...

Exactly.

Okay.
Why not?

Preserving column names maybe even.

Right.

So you...

Michael: Yes.

I think they used like a to JSONB
function.

Nikolay: Right.

And you do everything in triggers,
so everything again is transparent.

When a delete statement happens,
your row from any table which

has this trigger is packed and
sent to this table.

Very convenient.

I like this schema.

Michael: It's super.

So again, this is optimized for
like, this is harder to restore

from, right, because it's now in
a difficult format.

And especially if like the schema
has changed in between.

Nikolay: Well, this is a price.

You have trade-off.

Either you survive any schema changes
easily, or you need to

deal with some mutations during
restore.

You need to understand, okay, this
delete happened A year ago,

we had a different schema.

What to do?

You do need to do this.

Or you deal with schema...

You need to maintain mirror schema
changes all the time.

I would prefer actually deal with...

Since restore...

Depending on the project, of course.

If restore is not super common,
I would prefer this schema.

Super convenient, super universal.

Michael: Yeah, me too.

I think there's a lot of benefits
here.

Not for the 1 case I did implement
soft deletes in the past,

where the whole point was to make
restoration easier in a very

small number of cases.

This case is very different to
that.

But yeah, I do think there are
some tricky parts of this, though,

I think, for example, the compliance
thing is tricky here.

Like, it's another place that you
need to remember to delete

data from in the future.

If you get a GDPR request, you
can't just search on normal tables

for that data.

You also have to search this delete
log.

And it might be tricky to search
because it's JSON B data with

like unstructured.

So there's a there I think there
are downsides.

Nikolay: Right.

Is it fair to call this soft delete?

Maybe it should be called just
archiving?

Michael: Good point.

There's no definition of soft delete
that I know of.

It's not part of the SQL standard.

So I think it's more around like
what are the, what's it for?

And maybe we've lost sight of like
why are we implementing this

feature?

If it's for easy undeletes, maybe
this isn't the right solution.

But if it's for debugging or if
it's for different purposes,

then yeah, archiving makes some
sense.

But I guess it is, it's deleting
it in a way that it's not deleted.

That is the, you know, you mentioned
the, as a user seeing some

signs that it wasn't deleted, this
avoids a lot of those, right?

Like it's not in the same table
anymore.

Nikolay: Yeah.

Application can, If we define the
trigger function with security

definer, we can revoke or just
do not grant any permissions to

application user to deal with schema
or this table.

And that's it.

So application won't be able to
reach the data at all.

At very low, like, I mean, at database
level, database guarantees,

it's not possible.

But at the same time, Trigger can
still insert it because it

has...

Trigger function has security definer,
meaning that it has permissions

to run queries which are inside
the function using the level

of access of the user who defined
this function.

Michael: Cool.
I didn't know you could do that.

That's very cool.

Nikolay: It's called security definer.

When you create a function, trigger
function, for trigger you

need to create a function, you
just say Security Definer.

And I like it because you can encapsulate
some actions which

normal user, application user,
When I say application user, I

mean database user which is used
by your application.

Usually there are some tables that
this user cannot access.

For example, this shadow table
or shadow schema with multiple

tables.

But I still prefer single table.

But you can still allow, for example,
specific insert encapsulated

in this function and just grant
permissions for specific action

indirectly via the security definer.

Michael: Nice.

I like it.

Nikolay: Yeah.

I use it all the time for many
cases.

So yeah, better control what can
be done, what cannot be done.

So you basically it's good for
security reasons.

It's good to forbid everything
first as first thing, and then

just allow some specific action
in very controlled manner.

Intrigue.

Michael: I feel like there's a
phrase for this, isn't it?

Like security by minimum permission
or something.

I can't remember what the phrase
is.

It's like a security principle
of least permission, give things

the least permission they need.

Right, right,

Nikolay: right.

Well, in former life, we called
it a blacklisting, white listing.

I don't know why now it's not possible
to tell this, right?

But it means that you will just
say nothing is possible except

this, right?

And yeah, it's good.

So yeah, it's a good approach,
but you are right, Restoration

will be tricky.

You need to develop specific procedures,
deal somehow with schema

evolution.

And in this case, I would say if
we talk about some account deletion,

I would do this.

If we talk about something like
we have some objects user can

create, modify, or delete, and
we want support undo for deletion,

Like in Gmail with emails, when
you just delete email, it goes

to trash, and then you can undo
or delete forever, right?

There's a button, delete forever.

This is hard to delete.

In this case, I would prefer maybe
still in place, like in the

same, like just a column in the
same table status or something.

Michael: Nice.

Nikolay: Right, right.

Both are, we can say, both are
soft deletes with their own pros

and cons.

Michael: Yeah, seems very reasonable.

Nikolay: Do you think they can
coexist in the same project?

Not because they are, like decisions
were made by different persons,

different

Michael: developers.

I think I genuinely think that
the end of Brando's blog post,

he mentioned switching to this
in all but a couple of tables.

So I think they are currently a
good example

Nikolay: of a project that

Michael: has both.

Nikolay: You mean switching to
JSON B Shadow Table?

Michael: Yes, instead of you calling
it in place.

I think they still implement in
place in a couple of places.

Nikolay: The best thing about this
single shadow table with JSON-B

colon is that it's so easy to attach
it to any table.

It's just a trigger.

That's it.

It will slow down, delete a little
bit.

That's it.

Michael: Yeah, because of the triggers.

You could use it for the hard delete
and then have it for around

for a while longer.

Nikolay: We discussed about compliance
and so on, but, and I

mentioned that for business owners,
like there are fears that

like something was deleted, hard
to recover.

It's very good for audit purposes.

For example, you have some account
in your application, and there

are some admins, or like owners,
managers, and there are some

regular users.

Some users do something, they deleted
something, and you just

need to not only see and undo it
but see when it was done by

whom like

Michael: I saw another person in
a hacker news thread talk.

Yeah.

Not just audit but also what really
happened.

So I saw somebody who had implemented
a chat system.

Let's say it's a support business
to business support system

and 1 of your internal works for
your company users said something

they regret saying as part of this
chat and then deleted 1 of

those messages.

It's really useful for an admin
to see the whole history or you

know, you mentioned social media
and a lot of social media sites

that you edit a message, keeping
the old version.

I edited
Nikolay: this update, not delete.

So this leads us to like time travel
basically.

We cannot say, name it soft updates,
right?

But tracking all versions, it actually
also possible they are

the same approach.

And I think I did it as well.

So when you have delete with this
trigger with security, security,

definer function, trigger function,
which just archives previous

record.

In the trigger function, you use
old key name.

This is just record, right?

And you convert it to JSONB and
insert, right?

Absolutely the same trigger can
be used for updates.

With any update, maybe I would
have some checks if some actual

data was changed, not just something
small was changed, right?

But actual data, like message was
changed, right?

Title, message.

New dot title doesn't equal old
dot title, and so on.

If something, just to avoid, you
know, like spam archiving, which

like useless records in this shadow
table.

So if I see something valuable
has been changed with this update,

again, I back old to JSONB and
sort the same, like the same trigger

basically.

Right?

Michael: Yeah, I'm not sure.

It depends.

It depends how and when you need,
how often you need to be viewing

those older ones, because it might
be tricky to show the admin

those if you, how are you going
to select that data back out

to show them the old versions?

I guess it's tricky.

Nikolay: I would, by the way, no,
for updates, I think This is

what I did actually.

For updates, I would archive probably
both old and new records.

Because just, yes, to be able to
show diff.

Yes, it's like too much data to
store, but why not?

In this case, I would extend shadow
table with additional JSONB

column.

Like, delete...

Records related to deletes won't
have it.

Like, they would have only nulls
in that column.

For updates, I would have 2 JSONB
values.

Michael: Interesting.

Nikolay: Old and new, basically.

And then we can build some diff
if needed.

Right?

Michael: Maybe.

I think this is getting beyond
the scope of what...

But this is interesting.

I think my...

Nikolay: No, it's useful and easy
to implement and super universal.

You can again attach it to any
table and have simple audit like

that.

Michael: But what I don't like
or understand is because this

is a single shadow table across
multiple, what are you querying?

Like, how are you looking for that
in a, let's say it was a month

ago, in your, how you're efficiently
finding that in that shadow

table?

What are you searching by?

Nikolay: Well, depending on the
application, right?

I would probably in this shadow
table, I would probably have

some columns which would allow
me to search faster, or I would

deal with searching inside JSON-B.

Why not?

Genetics?

Michael: Well, yeah, maybe.

This slows down updates.

Yeah, there's a lot of...

I think that it's a tricky one because
if it's a shadow table for

all tables, you'd like having user
ID or like having other columns

that you could search by might
not make sense for some of those.

Nikolay: You can have null there.

Michael: True, true, true.

Nikolay: Well, yeah, I agree with
that.

Especially if you add gin, insert
this implicit insert, which

happens during delete or update,
it will be not fast at all because

of gin.

So yeah, overhead.

This is the price.

You have it.

Michael: Yeah, I like that.

It's another trade-off.

It depends what you want, and it
depends how much data you have,

and it depends what proportion
is ever going to be deleted, or

what proportion is ever going to
be restored or looked into.

But yeah, I think it's really cool.

I'll link up the blog post we mentioned.

Any last things you wanted to add?

Nikolay: Actually you know what,
during this discussion, I was

curious what we have in PostgreSQL
system and actually we have

exactly this approach I just described.

We have a shadow table, single
one for many tables to track deletes

and updates.

It's called audit log and we have
data before and data after

2 JSONB columns.

Michael: Interesting.

Yeah,

Nikolay: and actually a lot of
additional stuff.

This is just to be able to show
admins of organizations what

happened.

Michael: In pgMustard, we have hard
deletes.

It's slightly different because
it's just query plans, right?

And you can always submit them
again.

But if you delete them, they're
gone.

So we decided not to at all.

Nikolay: Yeah, and now an application
cannot reach this part

of database at all.

It's only possible like implicitly,
as I described, via security

definers.

So quite a good approach, very
reliable.

Michael: Yeah.

Nice.

All right.

Well, thanks so much, Nikolay.

Catch you

Nikolay: next week.
Thank you.

Good chat.