Michael: Hello and welcome to Postgres.FM,
a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard,
and this is my co-host Nikolay,

founder of Postgres.AI.

Hey Nikolay, what are we talking
about today?

Nikolay: Hi Michael, we are talking
about some article which

attracted my attention.

Michael: And I hope not just the
article, because it was the article

was about best practices, and I'm
I'm hoping to discuss that.

Nikolay: Yeah I always like like
15 mistakes, 25 recipes, this

kind of titles.

Michael: Clickbait.

Nikolay: Yeah and honestly I chose
it like let's just choose

this article, and it attracted my
attention just by title.

7 crucial Postgres best practices.

Good title, right?

Michael: Yeah, and we haven't had
an episode called best practices.

So it's an interesting, it's a interesting
omission on our part.

Nikolay: Really? Yeah. Yeah, okay,
best practices.

We have something like mistakes
to avoid maybe, right?

Michael: Oh, maybe, yeah.

Nikolay: From my experience being
program committee of various

conferences and just general conference
experience, people love

to hear about mistakes.

They also love to hear pieces of
advice, but mistakes we somehow

love more because it's like things
to avoid.

And yeah, and best practices.

What does it mean best practices?

Do this, not that, right?

Michael: Yeah, I've been thinking
about this.

What's the difference between best
practices and good practices?

Like, I think it's like shorthand
for if I'm new to this and

I don't really know what I'm doing,
what are some things I can

do to not look stupid?

Or to, you know, to at least cover
what people generally say

is a good idea because in anything
complex, especially databases,

we know often it depends, like it's
going to depend on exactly

what you're doing as to whether
you should do X or Y.

So I think these lists and these
suggestions are almost fraught

with danger from the beginning,
but I think they can be useful

educationally.

Like, they can be useful for people
to say, in general, this

is a good idea.

In general, this is a bad idea.

Nikolay: You could be a great consultant,
you know.

Because you sound like one.

Michael: Yeah.

Well, what do you think best practices
mean?

Nikolay: For me, it's not about
avoiding to be, to look stupid.

It's about, I like the word shortcut,
like to quickly find what

other people already experienced
and choose maybe not best, but

something better than you would
choose on your own, right?

So.

Michael: That's a good point, actually.

Not so much about looking cheaper,
but maybe to avoid things

going wrong.

Like to avoid the most common traps.

Right.

The goal

Nikolay: is not about how we look.

The goal is not to look bad when
making decisions, engineering

decisions around polygons, but
the goal is to make database work

better, more reliably, more like
better performance, avoid data

loss, avoid downtime, these kind
of things.

Actually 3, well, correctness is
1 of the goals, but naturally

Postgres has very good, in most
cases, if we don't touch the

null topic, right?

Null topic.

In this case, if we don't touch
the null topic, most of the time

Postgres works in a very correct
way and predictable.

I expect this, I got what expected.

And so in general, 3 areas, big
areas are good performance, reliability,

and well, avoidance of data losses,
big fear of database guys,

right?

And also no downtime or very short
downtime.

Michael: Yeah, perfect.

Nikolay: Big 3 areas.

Let's dive into this article.

I know we both are not thinking
it's super great material.

Michael: Yeah, sadly.

Nikolay: Let's be honest.

Well, it's an attempt.

Michael: It looks to me like somebody
has asked their favorite

LLM for a list of Postgres best
practices and almost copy and

pasted it verbatim into an article.

That's what it looks like to me
without much review.

Nikolay: Well maybe it's written
by LLM, yeah I'm not sure.

The author name is Zak and I think
it's good that the good attempt

is made to bring this again.

And actually it was on Hacker News
Top, I guess.

That's how it caught my attention.

It's a good attempt, but I agree
with you some parts.

Maybe it's written purely manually.

It's possible.

But I would like to just reflect
on the fields covered, 7 fields.

From the beginning, I don't agree
that we have 7 practices here.

We have 7 fields covered and inside
each field we have some practices

mentioned.

And you also mentioned that you
think there should be more fields

covered if we try to come to have
this like kind of digest of

tips, right?

But let's touch, my idea, let's
touch every one of these seven, bring

up to three our own tips we should,
like, should be noticed, like,

involved into consideration.

And then in the end, I'm very curious
what you think is missing

in terms of the fields, the areas.

Right?

So, deal.

Okay.

First, the first field is database
design and discussion is about

naming conventions and so on. What
what you think?

What's your number one take when
we think about how to make database

design good?

What what makes you good?

Michael: Yes, I mean I'm surprised
it starts with this.

I actually saw some of the Hacker
News comments and it's an odd

choice of where to start but I
do see a lot of people making

the mistake of using spaces in
their names for objects or using

mixed case and things and technically
if they followed this advice

they wouldn't do that so I do see
people making the opposite

mistake so yeah I don't think it's
bad advice and generally people

do have conventions.

I actually follow a lot of the
ones that are listed here.

So yeah, not bad.

How about you?

Nikolay: Well, first of all, I
think it's not a mistake if you...

My previous startup had camel case
table names.

It was inherited and I understand
it very well that it's not

super convenient.

I left it as is because it was
intentional.

I also think one step back, database
design being the first topic,

and then performance will be the
next topic and leaving reliability

like backups and HA later.

It just shows a tendency that probably
this article and this

set of tips are targeting more
backend engineers, developers,

programmers, or those who create
some systems rather than purely

database folks.

And it's totally fine.

And also, like, if we take managed
service, managed Postgres,

like RDS or something, The important
areas like backups HA are

presumably solved already.

So we have opportunity to focus
on things we deal with every

day, like database design, performance.

These things are closer to us.

I can understand this choice of
order.

If we talk about database design,
I think the main idea is to

have some convention.

It can be even camel case with
spaces, but you understand you

will be forced to use double quotes
all the time, everywhere.

But if it's choice, well, it's
inconvenient.

I know I was there.

And I was there after a couple
of other startups where I was,

how to say, like, very, very, like,
polishing every symbol, you

know.

I think this is what matters here.

Like, convention is important for
yourself, for people who work

with you also, right?

And for LLMs who work with you
as well.

Michael: But it's a good point.

It doesn't really matter why is
it number 1 in this list.

Like, yeah.

And there's also no mention in
database design and schema design,

there's no mention of normalization,
which is a bit odd.

Nikolay: Well, let's shift to what
we think matters.

So you say convention and also
we need to normalize the tables.

I tend to agree.

And also, if you think about convention,
I love also to think

not only about how we name database
objects, but also how we

write SQL.

I'm a big fan of Mozilla SQL Style
Guide, which is closer to

normal programming languages and
moving away from this central

space colon, right?

How to say like central space thing
like in...

We all...

Michael: You mean like an underscore?

Nikolay: No, not only, so SELECT
space blah, blah, then you're

from and you need to move from.

So you maintain the central space
vertical line.

Michael: Oh, alignment.

Nikolay: Alignment of spaces.

Alignment, yes.

I honestly, I hate it so much.

I know it's old-fashioned approach
and many books follow this

rule, but it's so inconvenient,
guys.

Just take Mozilla SQL Style Guide,
publish it inside your company

wiki or documents, like any confluence
or something, what you

have.

Adjust it for your flavor a little
bit and that's it.

And agree, okay, we are following
this convention.

As for normalization, I guess let's
mention the episode we had

a couple of weeks ago, 3 weeks
ago maybe, with Franck Pachot, about

NoSQL versus SQL.

And we talked about normalization,
denormalization quite a lot

there.

I agree this is a very important
topic to agree on and follow

this.

Well, also, for me, I think constraints
are super important.

Like, I would say, I would put
this as a big, like, it's a best

practice to use database constraints,
all kinds of constraints,

understanding the power of them,
and also understanding overhead

as well.

Michael: Yeah, I think it's good
default, like if in doubt, add

the constraint, and you might want
to remove it in future if

you get to a certain scale, but
I like the idea of having it

by default.

Nikolay: For example, we discussed
in the same episode with Franck,

we discussed maybe we should have
not NULL constraint by default.

Not that, unfortunately, it's not
so usually in all systems.

But we can agree, let's put it
by default and lift it only if

we know NULLs are going to be useful.

So yeah, that's it, I guess 3 tips,
enough, right?

Michael: And what about schema
namespacing do you ever do you

use it oh

Nikolay: it's interesting I I'm
I'm shifting back and forth and

in my career use namespaces schemas
or not use them depends on

the project here I am like with
you it depends if it's

Michael: Also it doesn't matter
that much, right?

Nikolay: Well, it does, because
it's about convenience as well.

If you think about others, they
can be spending a lot of time

trying to find your table.

Again, consistency here is the
key, because if you chose to start

using namespaces, schemas, suddenly
after a few years of development,

and the majority of your tables
are in the main...

And it happens all the time.

Like, I see it all the time.

Yeah, sure.

The majority of the tables are
in public schema, but suddenly

you start using some additional
schemas and hide tables there

basically.

It's maintainability as well, for
example you can control permissions

better and it also sometimes namespaces
are serving some trick

like you can have multiple tables
with the same name sitting

in different schemas and but this
can go this can lead to some

messy state and problems yeah so
but I would say this is part

of conventional agreements you
how we use schemas so yeah sometimes

people choose you know like choose
this is good topic by the

way for maybe 1 day we should discuss
it.

Multi-tenant applications and architectures
and how to create

multi-tenancy with good properties
in database.

I just recently learned that if
you go to Timescale cloud, you

cannot run create database and
you cannot have multiple logical

databases in a single server.

So in single Postgres, usually
we are allowed to have multiple

logical databases.

And their recipe for multi-tenancy,
they have an article about

this, like, let's use schemas and
just that's it.

But for me, schema is a very weak
wall between data.

Logical database is a big wall
between data because you cannot

read from another database being
connected to 1 database.

You cannot read from another database
not using something like

postgres_fdw or dblink.

Because this is how Postgres works.

Database name is a part of connection
properties, the core connection

properties.

Unlike schema, schema is kind of...

It's something...

Michael: Namespace.

Nikolay: Well, yeah.

It's namespace, and you can revoke
rights to use some schema,

it's good, but still it's inside
the same database.

So if you messed up with permissions,
you can end up having security

issues.

If you want strict segregation
of data, separation of data for

your tenants.

Okay, This is a different topic.

I think quite good, like we have
at least let's move on to the

performance topic, the second topic,
right?

Michael: So, they talk about indexing
and they talk about query

optimization.

I think, right, we've in the past
talked about a couple of things,

like micro optimizations and macro
optimizations.

And I think we could maybe, like,
think in those terms instead.

I do think it's obviously performance
is a lot about indexing.

And about individual query optimization.

But I do think there's some best
practices around macro optimization.

Like in Postgres, by default, we
don't have pg_stat_statements

turned on.

That feels to me like a pretty
much a best practice that we should

probably have pg_stat_statements
on or some or an extension for

doing that analysis that's on the
on the wider system level.

Maybe some logging like I think
there's some performance logging

of slow queries.

Nikolay: Just choose just 1 tip
right now.

It's already too many.

Michael: Oh, A, best practice.

All right.

Yeah, best practice.

I would say, well, I like their
first 1, I guess.

Use indexes.

Nikolay: Yeah, use indexes is obviously,
but yeah, I think my

take would be don't leave autovacuum
untuned and don't postpone

bloat control.

This is how Postgres works right
now, right?

So we must be aware of what bloat
is, what dead tuples are, and

distinguish them actually.

And...

Michael: They cover that in maintenance.

Yeah.

Nikolay: Yeah.

And this is what nobody do it well
among managed Postgres platforms

right now.

Nobody.

I mean, they don't give you good
tools.

Some of them have basic autovacuum
tuning, right?

And that's it.

Basic autovacuum tuning.

And that's it.

Some of them provide pg_repack,
and also have it indexed concurrently,

but that's it.

Obviously, I see future where more
platforms will provide more,

better tools to help you in this
area.

But so, knowing that right now
it's on your shoulders when you

build some system, this should
be not postponed, right?

Index is obvious, like everyone
knows index.

Let's create indexes.

Michael: But 2 important things
that aren't mentioned here are

do it with concurrently and don't
index everything.

We've done a whole episode on over-indexing,
but I feel like

a whole beginner category of issues
are caused by folks creating

indexes everywhere

Nikolay: yeah I agree and well
there's There are dozens of tips

we can put there around indexing.

Michael: Yeah, you said only a
couple, right?

Nikolay: Yeah, only a couple, but
you already named more than

5.

Pre-registered statements.

I agree.

While we are all sitting here and
waiting until hackers decide

to move this extension to core,
we need to keep this tip active.

And register statements must be
installed in any database.

Understanding some very edge case
or maybe corner case overhead

we also talked about in the past.

Yeah.

Good.

Yeah, okay, good.

And that's it maybe, right?

Well, performance is a huge topic.

We love it.

Yeah, we had many episodes about
performance.

But again, autovacuum tuning,
learn indexes, many best practices.

And the predecessor sentence must
be installed.

Agree on these 3 maybe, right?

Good.

Good.

Good.

So next field, again, the list
is far from being complete.

It's just like the tip of the iceberg.

So next security, suddenly.

I would say make sure you don't
have Postgres open

Michael: to the world.

That's the main mistake I see people
making.

Yeah

Nikolay: What else

Michael: but but obviously if you
if you're using certain like

managed services, that's on My
default in a lot of cases so then

it becomes well of having good

Nikolay: Right, they open it to
public, but protected, maybe,

right?

Michael: Yes, but...

Nikolay: But protected how?

Michael: Then it becomes about
good password policies and things.

Nikolay: Right, right, right.

But this started with Heroku, I
think.

They also have Postgres open to
public and security by obscurity

when hostname is a very large hash.

Yeah.

And username also.

Everything is like large hash,
but it's weak security.

But the password is strong, and
if it's not MD5, but Scram, it's

something better already.

But I agree, if you're a serious
project, this should not be

the case.

A port should not be available
to public.

I think it should be only like
local IP address and available

only internally in networks VP
Yeah, so Good.

Michael: I'm saying Are you saying
it depends?

Nikolay: I'm saying that I'm not
against like I can understand

people who have well platforms
who have it open to public for

because sometimes we want to start
very quickly.

And at the same time, I would not
allow this in a serious project

with data of customers, PII, and
so on.

I would not allow this at all,
to let this port to be open, even

if the password is absolutely difficult
to find.

For example, we have 2 factors
of authentication everywhere,

but here we have just 1 password
well what about if it's leaked

right

Michael: yeah this is this is honestly
the difficult thing about

security best practices is because
Best practices with security

means do everything you possibly
can to secure everything.

And that's just often not practical.

Like at what cost?

At what, you know, what are you
trading off?

Often you do trade off things for
better security, like as you

were saying, like quickness of
getting started or developer experience,

like ease of use.

So there's like a, I do think it's
a tricky topic to give best

practices on because you don't
ever want to say do something

that's slightly less secure because
it has these other benefits

because it's it's not a it's not
a best practice anymore.

Nikolay: Yeah it also makes me
think we could we could have bad

pieces of advice episode you know
we could have it.

Yeah we did like we

Michael: did go through there's
a wiki.

Nikolay: Don't do this.

My next advice, next tip for security
is divide and conquer.

So and unfortunately, many teams
don't follow this.

So use different users for different
parts of workload.

And for example, humans should
have different usernames in database.

So we can distinguish them quickly
and probably limit differently

and so on and parts of application
also it's it's wise to to

avoid using the single user name
for everything yeah

Michael: I really like

Nikolay: yeah I

Michael: really like that about
humans as well.

I mean, we don't like to think
about it, but if you were to need

to remove somebody from a project,
it's much easier to do that

at the user level if they've got
their own user than it is to

roll out and, you know, to think
about what did they have access

to, which passwords did they know
that were team-wide or something

like that.

So yeah.

I think I've heard of a similar
view, in fact I think this article

mentions it, but there's a general
concept of principle of least

privilege.

Nikolay: Yeah, yeah, yeah.

Well, this is super important principle,
of course.

And it just, it should be a habit.

It's hard to enforce it.

It should be a habit of all the
team members to follow this principle.

Michael: But it is also painful,
right?

Like, it is painful because it
means anytime you add anything,

you then need to add permissions
to it, like from all of the

things.

I get that it's good for security.

Nikolay: Default privileges and
Postgres, the concept of default

privileges, sometimes you can define
them as well.

Michael: Is that the least?

Nikolay: Yeah.

Yeah, well, yeah.

Another, maybe, let's do the last
tip.

I'm grabbing the ideas from article,
but we're trying to unwrap

them here.

Password rotation.

Michael: Oh, I hate it.

I've written yuck about that.

Nikolay: Well nobody does it actually.

Well, some people do it but honestly,
honestly, it's first of

all it's a pain.

Michael: For me it's a relic of
when people used to use the same

passwords across multiple services.

So I think the risk, when I say
used to, obviously a lot of people

still do.

You think

Nikolay: it's a bad advice?

Michael: My advice would be make
sure all your users are using

Some password generator or password
manager so that they have

a unique password per service because
then the risk of their

password getting leaked by a different
service that then they

can use as an attack vector on
your service is 0.

So it's only if you leak the password
that it becomes an attack

vector.

Which, you know, do you see where
I'm coming from?

Nikolay: Yeah, yeah, yeah, I understand
that.

Also, like, yeah, I agree.

I think actually Postgres could
have improvements in this area.

For example, you could say, if
it would be possible to define

password policies and so on, like,
for example, to forbid short

passwords, simple passwords, and
Postgres would ensure it's not

happening.

Or also PgBouncer, I don't know,
like Postgres first of all.

And maybe password retention could
be 1 of the policies if people

choose to use it.

Right, I don't know.

Like, I don't believe in the rules
here established and followed

at all.

It's like I just see companies
have this principle and nobody

follows this principle and attempts
are like hard.

It should be Postgres who enforces
this rule.

To like avoid weak passwords, for
example.

Michael: Yeah, right.

Nikolay: Yeah.

Okay.

Let's...

Well, the article also mentions
RLS, but I think it should be

very connected to performance and
to be used with big caution.

So it's a controversial topic to
just put it, oh, just let's

use RLS.

Well, yeah, let's check performance
additionally.

OK, next, backup and recovery.

If you're on a managed service, well,
supposedly it should work

good.

But it depends on the service,
on the provider.

Not everyone has good capabilities.

Michael: I would say also a best
practice would be to have a

version like to take I know we've
talked about the difference

between a backup and a logical
backup or a dump, but if you're

on a managed service I would still
recommend having a copy on

a period that you're comfortable
with off the service.

Yes,

Nikolay: Automatically created.

So diversification of backup locations
and some platform can

go down or your account can be
stolen.

And you, you must have the backups,
maybe not super frequently,

but some backups additionally in
secondary provider or location,

like isolated.

It's similar to having backups
on the same Server where you work,

but just a different layer.

Same principle, different layer.

If all backups are in the hands
of RDS, well, and single account,

well, it's not good.

Yeah.

And unfortunately, if you want,
if you are on RDS and you want

to follow this principle, you need
to deal with dumps.

Logical backups.

Michael: And it's not unique to
them, there are quite a lot of

service providers that that's the
only option.

Yeah, yeah.

Nikolay: I agree.

What else about backup and recovery?

Testing sometimes, right?

Michael: Yeah, I guess that is
the other big 1, isn't it?

It's not a backup unless you know
it could be restored.

Nikolay: Yeah, shorting the backup.

Michael: So some amount of testing
that restores.

Nikolay: And I don't think platforms
test all your backups.

They test only some of them and
who knows, right?

So I think it's a good idea to
test even if you trust them and

don't hear bad stories, still have
some testing.

Michael: Yeah, what's the age-old
saying, like, Trust but verify,

right?

Trust them to do it, but verify
it.

Nikolay: Final responsibility is
yours.

If you lose everything, well...

Yeah, so what else?

Anything else in this area?

Backup and recovery?

Understand RPO, RTO?

Michael: Yeah, I think discuss
that.

RPO, RTO, I think it's about discussions
internally.

Like have those discussions.

I think RPO and RTO are really
good tools and good things to

define, but more importantly, they're
good to enforce.

You've even had the conversation
with, you know, senior management

or whoever it is, maybe co-founders.

What are our trade-offs here?

Like, are we willing to pay this
much more for, like, a reduced

amount?

Or are we willing to lose a tiny
bit of data to bring our costs

right down?

I think those are difficult discussions
to have, and not everybody

has the same...

Not everybody would make the same
trade-offs there in different

situations.

Nikolay: Let's move on and next,
it was number 4, field number

4.

I would jump to field number 7
here, high availability, because

in my opinion backups and like
DR, basically, that disaster recovery

and HA, they should consider together
as a foundation of good

database system platform or something.

So HA, right?

HA, it's interesting.

If My number 1 advice is avoid
systems which are not HA systems

but split-brain systems, like replication
manager, avoid it.

Just avoid it.

And I don't know...

Michael: Well, if you're going
to say a best practice, maybe

we'll just say Patroni?

Or what would you say?

Nikolay: Yeah, just Patroni.

But you know, right now, CloudNativePG
is gaining popularity,

and it's not using Patroni.

So I wrote on Twitter, anyone experienced
already split brains

with CloudNativePG?

I'm just wondering, I don't know.

So Patroni is definitely passed
a huge path to achieve very good

quality in terms of avoidance of
split brains and maintaining

very low downtime in case of primary
disappears and so on.

Yeah, this is number 1.

Like I would just if possible choose
Patroni.

This is my advice.

What else?

Michael: Or managed service provider.

I think that this is where the
big, well all the cloud providers

that offer this.

I'm so glad they handle this for
me.

Nikolay: In this case, like I can
speak about this openly.

Crunchy Bridge, please don't do
backups from primary, physical

backups.

They still do.

You need to fix this.

If you follow us, like right now,
full backup, if it's not snapshot,

even if it's not snapshot.

So it should be on from some replica,
some physical Standby,

because it's very I/O intensive.

Even cloud snapshots can be I/O
intensive.

And of course, wall archiving should
be on the primary to minimize

the lag and minimize the RPO.

Improve RPO, right?

But full backup, if you do it from
primary, it's huge stress

for the primary, and if it's heavily
loaded project, it suffers.

What we recently observed, it was
not good.

So advice to backup, if it's under
your control, organize backups.

So full backup or Delta backup
or snapshot is not done, not on

the primary.

It's possible.

pgBackRest backups, pg_basebackup,
they support non-exclusive

backups, right?

So we can do it on replicas.

On the...

Michael: Makes sense.

And if we've got HA in place, the
replica's not doing anything,

right?

It's not a read replica.

Nikolay: Well, yeah, it depends
here, but I agree.

It's good to do backup from replica,
which won't be primary.

So if we talk in terminology of
Patroni, it should have no failover

tag.

Probably also no load balance tag,
but definitely no failover

tag.

It's Patroni terminology.

You can put the tag on the replica
to say no failover, so if

failover needs to happen, Patroni
won't choose this replica.

Michael: Got it.

Okay.

I was actually thinking you did
want it to be that 1, but yeah,

sure.

Nikolay: And then you do backup
from it.

Well, there's another topic.

If you do backups, only full backups,
only from single node,

what happens if it has some local
corruption and other nodes

are fine?

So all your backups are corrupted,
but it's an interesting, already

deeper topic.

Let's talk about HA, we talked
about Patroni, what else?

Lag control, right?

Michael: Lag control.

Monitor lag, yeah.

Nikolay: Well, also not monitor,
monitor, but also it can be

tuned.

Oh, sure, yeah.

To have a very low lag, and also
when you do load balancing it

can be very smart logic.

Many people already implemented
it or else implemented it in

core already.

So it can be like if some write
happened, this session deals

with primary for some time.

It can be even smarter, right,
comparing other sense and so on.

So you have some, it's not strictly
about HA, but it's about

load balancing mostly and how we
use our replicas.

It's like a dressing topic.

What else about HA?

We don't have a lot of time.

Unfortunately,

Michael: we haven't.

We have a whole episode on it.

Let's link to that.

Nikolay: Yeah, yeah, yeah, yeah.

What else in this area?

Any other tips?

Test failovers, right?

Or switchovers?

Michael: Yeah, true.

Nikolay: Yeah.

I remember I chased RDS guys asking,
you have this checkbox HA,

which makes me pay double price
for primary, but how can I trust

you?

I mean, I trust you in words, but
in action, how can I test it?

And they implemented it, you can
test failover, which is great.

So simulate primary failure and
see how failover happens, how

your application experiences it,
for example.

Important, another tip of advice
here is to write applications

so it has retry logic, not to lose
writes.

Okay, let's move on, because this
is like huge areas.

Each of them are huge areas.

What's left?

Maintenance and monitoring.

Wow, huge area.

Development practices.

And that's it.

Only 2 areas.

And I think it's not possible to
pack it into a few minutes.

Let's only maybe use 1 or 2 tips
everywhere.

For maintenance and monitoring,
we already had a tip to always

install pg_stat_statements.

What else?

Your choice.

Michael: Well, a big mistake I
see people doing sometimes is

turning off autovacuum.

And this, the author of this doesn't
seem aware of autovacuum

so that that would be my main tip
is keep autovacuum on and

tune it I know you

Nikolay: mentioned my tips are
2 of them like bloat control and

index maintenance and we had we
had several episodes on these

topics And just these must be implemented
in any project which

aims to grow even not to huge numbers,
but some moderate numbers.

Okay, and finally, development
practices, and then your secret

area as well.

Development practices, what do
you think?

Version control, code organization,
well?

Michael: Yeah, I mean, version
control has been a best practice

for years, but I still think it's harder than it should be.

I know we did a whole episode on this.

But some basic, however you choose to do it, some way of tracking

what changes have you made to your database, some form of version

control is so much better than not having anything.

Nikolay: Yeah, well, my advice is straightforward.

Based on what we do last 5 years, use proper testing during development

and before deployment.

And this testing should involve a lot of data.

So thin clones or database branching are great.

Michael: Or a staging server that has like just some not has

some reasonable amount of data.

Nikolay: Well, if you're alone, yes.

And also, even if you're alone, if you work on 2 tasks separately,

it's still like single staging is not enough.

You need to have, And what if you started doing something and

made everything wrong and data is changed?

How to restart?

So database branching is the key.

Michael: Or best practice.

Nikolay: Yeah, so I recommend in development practices, grow

your level of development methodologies, so database branching

starts to be involved into all development and testing activities.

It will be level up, huge level up.

So this is what we do at Postgres AI.

And okay, what's your secret topic, secret area?

Michael: Not secret, we've discussed it a bunch while we're talking,

but I couldn't believe that config tuning wasn't in, Like, no

mention of changing shared buffers even.

Like, I don't understand how you can talk about best practices

for running Postgres and not talk about changing settings.

Nikolay: Again, if you are a RDS user, I can imagine, like, we

talked about HA, but we talked about backups, and it's very...

In this article, it's very development-focused point of view,

like, developers point of view.

Like, things already solved, and configuration partially also

solved, but we...

We all remember, like, Crunchy Bridge tuned a random page cost

after our complaint.

Michael: They did, but I don't think RDS have yet or most of

the others.

So random page cost is still the default on most major providers.

Nikolay: So many things are still default.

I'm happy I have work doing consulting.

Michael: We help many people still.

I know that could be covered, but it really just wasn't mentioned.

And then the other big 1, I'm sure there are others that have

it's hard to spot like the absence of things right when you go

through something but I couldn't believe I didn't see a single

mention until the conclusion of upgrades of staying even in security

we didn't mention staying up-to-date
with the latest minor versions

and in performance we didn't talk
about like upgrading to major

versions.

That feels to me like a best practice
should be to stay up to

date.

Nikolay: Yeah, yeah I agree.

It's part of our healthy Postgres
vision.

Definitely minor upgrades, major
upgrades for sure.

It's part of our health check we
do.

Michael: Everything that's in your
health check in my opinion

should be your best bet.

Yeah.

If that makes sense.

Nikolay: And so also still I always
have a feeling that it's

not enough, we need to extend all
the time.

Some things go away, like upgrades,
we will have, in 2018 they

will have statistics, so this will,
this tip will go away, but

so many tips are still coming.

Okay I think it's great like maybe
you're not very well structured

but I hope some some people heard
some good pieces of advice

today.

Let us know in the comments please
or on Twitter or LinkedIn

somewhere.

Good.

And we have document actually,
maybe it's a good time to remind

that we have a document with ideas
and also if something is missing

and we could dive into some topic.

Michael: Yeah, you mean, so we've
got a document that lets people

request or suggest episode ideas.

Nikolay: Right.

Michael: Yeah, topic suggestions.

Nikolay: Yeah.

Good.

Michael: Nice.

Nikolay: Good.

Michael: Thanks, Nikolay.

Catch you next week.

Nikolay: Thank you.

Bye bye.