Michael: Hello and welcome to PostgresFM, a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard.

As usual, I'm joined by my cohost,
Nikolay, founder of

Postgres.ai.

Hello Nikolay.

Nikolay: Hi Mike.

Michael: And today we are also
joined by a special guest, Andy

Atkinson, Andrew Atkinson, as we
were saying earlier, who has

been a software engineer for 15
years at the likes of Groupon,

Microsoft, and Fountain, a user
of PostgreSQL for the past 10,

speaker at several conferences,
and now a published author with

the brand new book, High Performance
PostgreSQL for Rails.

It's a pleasure to have you on,
Andy, welcome.

Andrew: It's a pleasure to be here,
thanks for having me, guys.

Really fun.

Michael: Nice, well, I'm gonna
let you in on a little secret

here that I didn't tell you beforehand.

You are actually our first guest
who has been requested by a

listener.

So somebody specifically asked
for you to come on, which is awesome.

Andrew: Wow.

Michael: And with the topic Postgres
plus Rails.

So that's what we're going to be
talking about.

Andrew: I guess asking my mom to
do that really worked out.

No, that's great to hear.

Michael: Awesome.

Well, yeah.

Who better at the moment, you know,
you've got this stuff fresh

in your mind.

So yeah, in terms of where to start,
I thought it'd be really

interesting to hear from your side,
how popular a choice is Postgres

for Rails?

Like what's it competing with and
yeah, how's that been changing

over time?

Andrew: Yeah, good questions.

Well, the ORM in Ruby on Rails
is called Active Record and it

supports Postgres and MySQL/MariaDB, and SQLite.

So whenever you're generating a
new Rails app, that's one of the

first decisions you make, I believe
by default it's SQLite.

But most folks that have apps in
production, I still think it's

usually, they're using Ruby on
Rails, they're usually working

with MySQL or Postgres.

In kind of prepping for this, there
are a couple of surveys where

folks have been responding to surveys
about how they deploy their

apps.

And there was a 2022 survey from
Planet Argon, which I had

pulled up here.

And there were about 2,600 responses.

And from 2014 onward, Postgres
has been the most popular relational

database with Rails apps.

And I guess what I've seen is kind
of this shift, starting in

maybe the early 2010s, where I
think in large part, thanks to

Heroku having Postgres support
and Heroku being a really popular

choice, easy place, as you guys
have talked about it before,

made deploying your Rails app onto
the internet much easier,

pretty much a git push command,
and took away a lot of operational

toil that a lot of folks might
otherwise take on themselves.

And I think a lot of folks may
have switched to Postgres just

because it was part of the package
there, and maybe they weren't

deeply using relational database
features.

And so I think there was a lot
that had to do with Heroku.

And then of course, as also, as
you guys have talked about, Postgres

gaining a lot of features, a lot
of performance over the last

10 years.

So I kind of noticed this general
setup where a lot of the big

Ruby on Rails, the companies that
famously use Ruby on Rails,

like GitHub, Shopify, Basecamp,
that kind of started in the 2000s,

they tended to choose MySQL at
the time and they've stuck with

it for the most part.

They maybe are using, you know,
clustering solutions and things,

but companies that started more
in the 2010s or mid-2010s, I

feel like a lot of times they're,
they're running Postgres.

And so I'm certainly, you know,
now I'm doing independent consulting

with Rails teams that use Rails
and Postgres.

And I'm certainly, you know, I'm,
clients are coming to me that

are using Postgres.

So for whatever that's worth, there's
definitely companies out

there using it.

Michael: Yeah, I was going to ask
Nikolay as well and then I

realized that all of us have a
super biased sample but that does

that definitely does make sense.

That data you mentioned that survey
is really cool as well.

I saw the question below was
about which one would you like

to be using in production?

And it changes just 2 years earlier.

So you can see that there's that
desire and people wanted to

switch or wanted to use it in production.

And then it did eventually take
over a couple of years later

and hasn't lost first spot since,
which is nice.

You mentioned the ORM already.

Should we dive into that?

How are folks generally sending
queries across to Postgres from

Rails?

Nikolay: And where is Postgres?

If Ruby on Rails, then Postgres
is under Rails or where?

Andrew: Yeah, so with Active Record.

Oh yeah, where is it?

Let's see.

Where is

Nikolay: the place for it?

Michael: On again, isn't it?

Ruby on Rails, on Postgres.

Andrew: Yep.

Nikolay: The basis for Rails, okay.

Modweb.

Andrew: Yeah.

As a Postgres enthusiast, I tend
to think a lot about the database

operations and the relational data
model and almost like Rails

is kind of wrapping, developers
are writing Ruby code that's

doing database interaction and
other, other things.

But I almost tend to think about
things now more from a database

first perspective.

So it's almost like Postgres with
Ruby on Rails or something.

But I think, you know, depending
on how you tend to, most developers,

I think though, it's the other
way around.

They're mostly, most Rails teams
I've worked on, they're writing

Active Record code.

In the early days, I've been around
in Ruby on Rails for a long

time, almost 15 years with some
divergences.

But in the early days, there actually
was a little more competition

around the ORM.

Some of these projects are still
around, but Sequel, SEQUEL, and

Datamapper, some other ORMs that
were written in Ruby and allowed

developers to create queries and
evolve their schema with an

alternative to Active Record.

Different pros and cons, performance
benefits, maybe, that kind

of thing.

But Active Record sort of steamrolled
everything or consolidated

everything over time, as can happen
with open source projects.

So most developers then, in my
experience, they're thinking,

they're working more with objects
and interactions and algorithms

and they're building background
jobs and working with message

queues and things like that.

So they're not necessarily writing
a lot of SQL.

And as I was saying, most Rails
teams, they're going to write

Active Record.

So Active Record then generates
SQL and it can do things for

us as queries are generated, like
annotate them and say where

they're coming from in the app,
which is nice.

If we look at queries within Postgres
and we wanna kind of go

backwards and say, well, where
did this come from in the app?

And then another big way Active
Record is used of course, is

it is the de facto schema evolution
tool as well, which is interesting

because I think Rails developers
just kind of take that for granted.

Like, of course I control the schema
And of course I could ruin

everything with a bad migration
or whatever, you know, like an

incremental schema change.

But that's not always the case
when I've worked on other teams.

Like I've worked at, when I was
at Groupon, actually, the main

applications I was working on were
Java and there were DBAs and

we used Postgres.

But sometimes due to the scale
there, I mean, usually developers,

unless you're working on a small
microservice maybe that you

might have ownership of, if you're
working on any of the core

services there because of the scale
of the operation or how the

company was set up, usually there
were DBAs that would do riskier

database changes and that sort
of thing.

So you might actually just kind
of provide them an example of

the change that you want, and then
it might just be done and

they let you know, maybe via a
ticketing system or something.

So I think, you know, it's interesting,
like if you come to,

you know, your background might
be where as a Rails developer,

you may at a large company, you
may not do as much of the direct

schema control, but certainly for
a lot of small to medium companies

that is what developers do as well.

So they do need to be informed
about good schema design, of course

creating indexes, constraints,
all those sorts of things.

And that's where it kind of, that's
where then the ORM can start

to be limited in its scope, you
know, and you got to kind of

go beyond and learn like, okay,
well, what are the capabilities

I have at my disposal within Postgres?

Nikolay: And at some point you
need to switch from schema rb

to structure SQL, right?

Andrew: Yeah, Michael asked about
that.

Nikolay: I saw several companies
switching.

Why do we have schema rb by default
at all?

Because everyone is switching at
some point while growing, right?

Andrew: Yeah, well, and I kind
of agree with you, but I didn't

always feel that way.

It's actually an interesting little
like microcosm of the whole

spectrum, I think, of being more
of an application developer,

working within your programming
language, which is Ruby in this

case, and kind of thinking of the
relational database as just

a thing that you don't really directly
work with, but it's just

there to like store your data and
access your data.

And then on the other extreme,
kind of like I was saying, like

you're thinking of completely in
the database operations, you're

thinking about the schema, the
queries, the indexes, you're running

query plans in your head.

You're thinking about, you know,
how do we, we have this high

growth table.

Should we use partitioning?

Should we split it out?

Like that's the opposite end of
the spectrum.

And yeah, I think so the, to briefly,
for anyone that's not familiar

with the schema rb and the structure
file by default in Ruby

on Rails.

As you make changes, let's say
you add a table or you add an

index, et cetera.

You generate what Rails calls a
migration, and that would be

that incremental change.

So here's the new table definition.

Here's the index definition.

And it's expressed in Ruby, but
of course, it generates SQL statements

to run and they run against your
local database.

And then what happens is your local
database is then dumped,

its schema definition or its
database definition is then dumped

into a file.

And by default, that's a Ruby file.

So it kind of like translates it
back to Ruby and represents

it as Ruby.

And it kind of insulates you from
the SQL.

But really in Postgres, what's
happening is it's just running,

well, I should say, if you move
to the SQL form, what it's doing

is it's running pg_dump.

And it's basically just taking
the raw pg_dump output and putting

that into a SQL form of the file
with a little bit of extra stuff

at the end, which are those migrations
that you're creating.

Each of them have a version.

So it dumps those versions as insert
statements into the end.

But otherwise, it's basically just
the pg_dump output.

So yeah, what happens a lot of
times for teams is they start

out with the Ruby schema file,
they start to use things that

are beyond what it covers.

And so they might use like a materialized
view or like triggers,

triggers,

Nikolay: maybe, right?

Andrew: Yeah.
So stuff like that.

And, and then what can happen too,
is the open source community

can, can spring in and can say,
"Oh, we can fix this, we can,

we can actually extend the Ruby
form of the file.

So you can keep using it, but you
gotta also run this Ruby gem

with your app.

And now we can express triggers
in the Ruby file.

But I kind of tend to just encourage,
okay, at that point just

switch away or maybe even just
start that way.

But just switch to the SQL file
because it's going to give you

the highest fidelity information.

It's essentially what pg_dump is.

Nikolay: And if some database guy
is performing code review,

it's much easier to see the changes
in Structured SQL than in

this language you don't fully understand.

Basically, if you do it in the project
with SchemaRB, you end

up asking, provide me a full log
or a full dump of everything.

Andrew: Right.

Not to get too philosophical, but
as we're on a team building

an application, the code is kind
of our method of communicating

with the computer and with the
team members as well, you know,

as we express the domain concept.

Or in this case, as we're expressing
the database design.

So if your team has people that
are more, you know, database

only people then giving them a
Ruby file is, I mean, they, they

would have to learn it and it doesn't
actually have all of the

information in it often.

So yeah, it's a little, it's a
little weird.

However, as I was writing the book,
I was thinking about a lot

of these kinds of things too because
I have been on a lot of

different teams.

And I was kind of trying to not
be too preachy about one thing

or the other, just kind of saying
like, well, if your team is

mostly Rails developers, you know,
just know that there are limitations

to the Ruby schema dump, that there
may be some information that

you're not getting in here.

However, you know, and then I sort
of made the pitch for the

structure file maybe.

But yeah, that's a good point.

Like if you're working with a DBA,
you know, you can hand them

a dump file and they'd have a clear
understanding of what's in

the database.

Nikolay: Yeah, I'm curious if things
would be changed with LLM

and so on, which can easily translate
or something.

But it's a different story.

Andrew: Yeah, that's an interesting
idea.

Nikolay: Do you have an understanding
why do we need to keep

two tracks of changes?

So first is each migration is kind
of different, and if we keep

all of them, we always can build
from zero, from ground, we can

build our final schema, right?

Just in steps.

But also we keep this structure
SQL, or schema RB, doesn't matter,

which is a snapshot of the latest
version.

Why do we need both?

I was very curious, I was trying
to understand this all the time.

Why do we need both?

Andrew: Yeah, well some people
even say, so as your incremental

changes contribute to the single
file representation, schema

RB, and like if we're talking about
the Ruby one, some people say

that like the schema RB, it is
basically the same as the incremental

changes.

It's just everything at once.

It's kind of this intermediary
Ruby representation of your database's

structure.

And you don't actually need both,
really.

The incremental changes do serve
as the log, which you'd otherwise

have to...

You could get through Git.

Like if you looked at, you know,
if you pulled changes as a developer

on a team and you notice that the
schema would be changed, but

there wasn't the incremental file
that represented the change,

you could look into the Git history
and try to discern what happened,

but I guess the file kind of makes
it easy.

And also when Ruby on Rails was
started, we didn't really have

Git, so we were stuck with like
subversion or something else.

So, you know, I think Git made
doing that kind of version to

version investigation easier in
my experience anyways, like being

a, I think a better version control
tool.

But yeah, I mean, technically you
don't need it.

And so what some teams do after
a while is they just throw away

the incremental ones.

And so Ruby on Rails also allows
you to just load the schema.

If you're, if you're setting up
a brand new machine, or a new

developer on the team, you can
just load directly from that single

file, the schema.rb.

So that would be like a structure
load command.

And that would work the same regardless
of which type.

So Michael, you were asking about
like a beef.

I think there's, it's like not
a real beef.

It's like a, it's like a faux beef
that programmers invented,

I think.

But it's kind of like, do you want
to preserve the beauty or

elegance of the Ruby code, or do
you want to just like, you know,

throw it away for the ugliness
of an SQL file, maybe what some

Ruby programmers might say, Or
what I might say is like, I actually

think the SQL file is pretty.

It's elegant.

You know, it's got, like I said,
it's like high fidelity.

It's all of the information, you
know, and you can even customize

it.

Active Record allows you to pass
flags through to pg_dump.

So if you need even more information
or you want to change the

output, actually I did think of,
there is one reason maybe the

beef emerged is if you have two developers
running slightly different

versions of Postgres, so maybe
the same minor major version,

but slightly different minor versions.

Postgres changes the pg_dump format
over time, different, like

it could be the ordering or I think
it's usually the ordering.

And what can happen is you get
these annoying diffs as a developer

where two developers are essentially
it's noise.

It's not a real meaningful change,
but it's like, Oh, the triggers

we added now they're on top of
the constraints.

And before they were after the
constraints or something like

that.

I'm just making that up, but I
have a solution for that though

too.

Lucas Fiddle created, this is several
years old, but it's a Ruby

gem that basically does some post
processing on that dump process

and does like an explicit ordering
of all the content.

So that if you have a team that
uses that tool, then you should

have consistent ordering amongst
each other.

Michael: And this was for the .sql?

Andrew: Yep, that's for the SQL
version.

Michael: Makes sense.

Yeah.

Cool.

I could see Nikolay exploding as
you were talking about how beautiful

the Ruby was.

And so for our podcast listeners,
I felt like that couldn't go

unsaid.

Nikolay: There are 2 of the ugliest
languages in the world, JavaScript

and SQL.

They also happen to be the most
popular ones.

Andrew: That is interesting, isn't
it?

Or even C, right?

Like C is still, you know.

Nikolay: Another thing is that
Ruby was created or else in 1995,

same as Java, JavaScript, and PostgreSQL
95 and what else, right?

So many things same year, just
a random fact.

Yeah.

True.

Michael: Yeah.

So you mentioned way back about
some limitations of the ORM.

I think it's worth talking about
like ORMs have a bad reputation

if you talk to database folk.

Is it worth talking about some
of the like more common issues

there or like ways around those
issues or limitations when you

need to break out that kind of
thing?

Andrew: Sure yeah.

Well that was actually a pretty
big premise I wanted to cover

in the book is to show people Show
readers like there are these

other things in PostgreSQL that you
may not be aware of if you've

limited your kind of research
area to just what's supported

in ActiveRecord.

Michael: Mm-hmm.

I

Andrew: mean one that comes to mind
right away is table partitioning.

I mean, there's not really any
support in ActiveRecord for table

partitioning.

It doesn't mean you can't do it
with a Rails app, but you might

run into a couple of small issues,
especially prior to recently,

composite primary keys became supported
in ActiveRecord.

But I was performing a table partitioning
project on a Rails, older

Rails project about a year ago,
and there were some issues with

assumptions code would make about
primary keys, for example,

just like there's only one column
that is the primary key definition.

But yeah, so it's worth noting
that, you know, if you have a

high growth table that you wanna
look at table partitioning for,

you would be likely doing that
a bit on your own with writing

SQL commands and or kind of maybe
researching like a Ruby gem

that you would add into your Rails
project that has done some

of that work for you around creating
the table structure or making

sure your queries have the partition
key column or things like

that.

Yeah, I mean, definitely ActiveRecord.

I think ORMs generally try to bring
some of what the database

does into the application.

I think that's fair to say, like
for example, triggers in Postgres

that have, you know, that can trigger
different trigger types that fire

at different times and that have
different scopes.

There's a whole set of things called
Active Record lifecycle

callbacks that are similar in their
purpose where, you know,

you might want to persist an object,
which would be taking an

in-memory Ruby object, you know,
turning it into an insert or

update statement, basically.

But you could intercept that kind
of before that event happens

and do something within the application
code with an Active Record

callback.

So you could have like a before
save or a before commit.

And some developers might not then,
they might try to design

things within that scope where
maybe a trigger could be a solution

where like if two applications were
sharing the same database,

which is not a great idea sometimes,
but maybe you'd want to

put the trigger in instead of having
it at the application level

so you don't have to duplicate
code or that kind of thing.

Nikolay: What about transaction
control in this case?

Is it inside one transaction and
Rails controls this or can you

do like trigger outside of transaction
so it's not guaranteed

that it will be consistent?

Andrew: Yeah, yeah.

Active Record supports a transaction
concept that maps pretty

much straight up to Postgres, you
know, begin, commit or rollback

transaction.

And actually recently learned you
can pass in, you can do transaction

control.

Like if you want to change the
transaction.

Yeah, the isolation level.

Yes, thanks.

If you want to change the isolation
level, I actually just recently

realized ActiveRecord lets you,
supports that, you can pass it

in as an option.

And then I kind of verified it
myself and made sure that the

SQL statements were generating
those things.

But yeah, I guess if you do want
to have some kind of nested

transactions or if you want a little
more control, then I think

part of what, you know, you certainly
might want to take that

on yourself.

And, But I wanted to actually then
tie that into, I think, part

of why ActiveRecord has been successful
is it doesn't, at a certain

point, you might just say, well,
I just want to write SQL, but

I still want maybe some ActiveRecord
objects to work with.

And ActiveRecord doesn't prevent
you from just writing SQL within

your ActiveRecord code.

And so that can be beneficial if
you want to just say, well,

I just want to write my own SQL
statement here for a query, or

I want to even just use it as kind
of an interface to run some

commands, like maybe, you know,
opening a transaction, although

that is supported directly.

But you can write SQL commands
within ActiveRecord as a string

that then get invoked or get sent
through the ActiveRecord connection

pool, etc.

And then what you can do is you
can leverage then taking a result

set and taking advantage of mapping
all of those database types

into your Ruby object types and
have an object to work with.

Or you can even use primitive types
like having a simple lists

and strings and that sort of thing.

So it's kind of like, it allows
you to, has these nice helpers

to do things like, you know, perform
joins and limit fields and

things like that.

But it also doesn't prevent you
from just saying like, I wanna,

I'm gonna take over here.

I'm just gonna kind of write my

Nikolay: own SQL within this.

Huge recursive CTE I want to write.

Andrew: Yeah, you could do that.

Yeah, there's.

Michael: What proportion of the
time do you tend to see, for

yourself and for others, do you
find that you're, Yeah, you're

using one versus the other.

Andrew: Yeah, it's interesting
because like 10 years ago, I did

see more of, I'd say, writing plain
SQL within ActiveRecord.

And I think it was because a lot
of folks that were using Ruby

on Rails then, they had more that
were more senior engineers,

they had experienced, or they had
experience with working with

SQL and other databases, and it's
just how they worked, you know?

And ActiveRecord was also more
limited in its capabilities.

And then I think there's been this
blend maybe over time, or

this interesting parallel tracks
of things happening.

One could be ActiveRecord has gained
more, it's continually adding

more and more helpers.

The documentation refers to it
as helpers, but like, you know,

as I was mentioning before, recently
common table expressions

or CTEs gained support, which I would
have thought maybe they would

have been around for a lot earlier,
but ActiveRecord has a first-class

method, a helper method for CTEs
now, and then composite primary

keys I mentioned.

So ActiveRecord has gained more
support even when you move beyond

one database.

If you want to work with multiple
databases, you can configure

that in your application.

There's even the ability to take
advantage of automatically sending

read-only queries identified by
the HTTP verb for your web app

to a read replica.

If you have that configured, Active
Record lets you set up a,

what they call a writer and a reader
role, and then they even

added that to the sharding capabilities
too, where if you have,

if you take advantage of horizontal
sharding in Active Record,

you can do some automatic shard
distribution.

And so I think because there are
more capabilities that ActiveRecord

supports, there also, I tend to
see less writing of SQL, but

again, I think it kind of comes
back to the team's composition

too.

If the team's very familiar with
SQL and also myself as I've

gotten more experienced with SQL.

I think my patience for like, okay,
is this supported in ActiveRecord?

record?

Like, okay, fine, I'll do it in
Active Record because that will

work well for the rest of the team
or whatever.

But if I can't find it pretty quickly,
I'll just write it as

an SQL statement.

It's no big deal to me.

Michael: Makes sense.

Nikolay: I worked with Rails projects
a lot, and one of them is

GitLab.

Shameless plug-in of ads for their
migration helpers.

.rb is great, and documentation
is open and great.

But someone in the Rails community
should finally rename disable_ddl_transaction!, disabled DDL transaction, because

people, just because of this weird,
I don't want to say word

or literal, a lot of people tend
to say we are going to deploy

this without transactions, in non-transactional
mode.

Postgres cannot work without transactions.

So it's just becoming like a single
transaction, each statement

becomes a single transaction.

Michael: It's not transactional, right?

And I don't like this part of Rails
at all, for many, many years,

and it should be renamed.

But at the same point, you know,
levels of understanding.

I also understand at some point,
like, I understood, creating this

concurrently, and you need disable_ddl_transaction to run create index

concurrently.

It's not transactional, right?

Because it can leave leftovers.

So things are difficult.

But I see people like Ruby developers,
they say we will execute

this without transactions.

It just breaks my ear, Postgres
ear.

It's not right.

Is it possible to change at all,
to create pull requests or something?

Andrew: Oh, you definitely could.

I've actually seen...

I mean, yeah, we could after this
call, we could create a pool

request to Rails and try to get
that approved, but it'd be an

uphill battle, I think.

Nikolay: It's probably a huge battle.

But I already have 1 battle won,
and I remember the 37signals

team, or Basecamp, or who is that?

Yeah.

They added support of the logic
when we have replicas, asynchronous

replicas, and a write happened
in a session.

So you need to stick to the primary
for some period of time,

because otherwise if a replica
is lagging, you won't see your

own write immediately after.

So they implemented this.

Before that, many companies, and
I also implemented this logic

in several languages, in PHP and
Java, in my past.

But I see this path finally Rails
is going through, and it's

obvious to me that they implemented
this lag as a constant written

in code.

It should be configurable.

So there was a big battle, and
DHH supported my proposal.

I was happy to see, like, let's
make this configurable.

So maybe we can win 1 more battle,
right?

Maybe.

Maybe.

It's not a question, sorry, it's
just my story with Rails.

Andrew: Yeah, well, it is actually
a good, I like that the design

of, so this replication lag, there's
like a resolver class concept

and the documentation talks about,
actually I wanna get back

to your question too, Michael,
about are there any other benefits

we didn't really cover?

I like how the documentation says,
hey, we purposefully have

this simple resolver class.

And if you have greater needs,
then they've built in an extension

point.

They've said like, okay, just create
your own resolver class,

implement this method, and then
you can do whatever you want.

And if I remember correctly, that's
how the automatic replica

switching and the shard switching
works, is they both have kind

of an official extension point so
that you do kind of get some

default behavior.

They took a guess at what a reasonable
replica lag would be,

I guess.

And then, but they said like, if
you want to do something else

or omit some certain, you don't
want to make this type automatic

or you do want to make this type
automatic, etc., you could

do that in your own implementation
class.

Just so we could cover this quickly
too, Michael, you were asking

before about other benefits, like
if you do stay kind of within

the Rails world.

I think like, because I wanted
to mention that a lot of these,

what I would call enhancements
for like higher scale operations

are coming from developers at GitHub
and Shopify and companies

that do have, you know, internet
scale operations, and they're

still using Ruby on Rails with
their relational database.

And they're building a lot of things
into ActiveRecord.

And then I think those are, you
know, great gifts that we receive

as users then of the framework,
even if we don't have that scale,

we have some pathways that we could
grow into that if we need.

And we also get them the benefit
of the ORM there where it's

like, well, multiple database adapters
are supported by the framework.

So you need to make sure that this
works in both MySQL, even

though they don’t use Postgres
at GitHub, from my understanding.

The Active Record capabilities
support multiple relational databases.

So we kind of get that, even though
they're not using Postgres

within the framework.

Michael: Yeah, that's really cool.

And it used to be like, I'm old
enough to remember when people

used to ask does Rails scale and
think, you know, like that,

those old questions.

And it just feels like we have
so many good examples now that

doesn't seem to come up as often
anymore, which is nice and refreshing.

I did actually almost want to ask
from the other perspective

quickly, though, what, like, as
a Postgres community, what can

we keep doing or what can we do
better to make sure Postgres

stays as the number 1 choice
for Rails or how can we make

things easier for developers at
both large and small organizations?

Andrew: Yeah, that's a good question.

I do think that there's a little
bit of a recent popularity with

SQLite within Rails.

It's gained some features that
I think make it more scalable.

And then also, depending on your
deployment setup, it may offer

you a simpler deployment configuration.

And then on the MySQL side, I think
that there's, you know, there's

certainly I hear a lot about PlanetScale.

They're, they've been doing great
with, they're the only, I actually

tweeted this a while ago, but like
they somehow made foreign

key constraints look cool.

I don't know if you saw that video
or not.

They have this well-produced video
where they're like, boom,

we added foreign key constraints.

And I was like, it was actually
kind of cool.

And I mean, I think that databases
are, it's hard to make, to

generate a lot of enthusiasm maybe
around it.

But I would say that like, you
know, some of the companies offering

Postgres as a service these days,
there’s a number of new startup

companies.

They're kind of either advertising
more like we can help you

with multi-tenancy or we can help
you with full-text search or

we can help you with these other
sorts of like capabilities or,

You know, usages that Postgres
supports.

It just might be a lot that you
need to kind of build yourself.

And so I do think Postgres has
like the raw ingredients for a

lot of stuff, but I do think that
if you're not going to use

a managed service and you want
to take this on, you got to do

a lot of work to build skills and,
you know, like if you want

to build in your own full-text
search at a good scale with good

performance, Postgres has a lot
of built-in capabilities and

is extensible.

You can add extensions.

But I think like, you know, just
continuing to maybe think about

the developer experience, I guess,
like at least for Postgres

users that are web applications,
anywhere the Postgres community

can contribute, you know, guides
and tutorials or if companies

that use it successfully with some
of these use cases, if they

can publish on their engineering
blogs, like I always love to

read those.

Like DoorDash is a company I know
that is a Rails and Postgres

company and has a lot of great
engineering blog posts.

And I think those are ways that
show pathways to leverage some

of these capabilities.

Yeah, and then I guess I've been
thinking about, I think the

open source solution still around
end to end query observability

is still like, it can sometimes
be limited in my experience where

I'll like, you know, we, we know
of tools like pg_stat_statements,

but then we want to collect samples
and it can be a little bit

difficult.

You kind of go back and forth between
relying on logs using system

catalogs, but Postgres keeps investing
in that there's new catalogs

and there's new things.

And so I think like anytime, any
of those sorts of things that

make it easier to see what my query
workload is like, where are

the costs coming from, especially
if they're connected to things

I can take action on.

Like, Nikolay, you mentioned recently
how pg_stat_statements shows

the number of rows returned.

You could go through and look at
queries where you could say,

well, we should really probably
add limits to these queries.

Things like that can make really
meaningful...

You can draw a real clear line
between, like, here's information

that's being provided to you within
Postgres that helps you out

on as you're building and scaling
and operating your system.

Nikolay: But this approach is reactive.

If we see pg_stat_statements on production,
it's already happening.

It would be great to try to guess
based on Rails code.

It's not about migrations already,
just regular some code, Rb

file, which serves some page or
API endpoint.

And we can just look at it, we
can say, oh, the limit is missing

here, right?

Yeah.

People at GitHub, for example,
they build a lot of additional

things in CI which help them.

Andrew: Yeah, that's right.

Nikolay: I think something can
be found in public.

So basically, for example, a select
plus 1 thing, which is very

common for ORM, it can be automatically
detected before you deploy.

And even if you test it on a very
small database, not on a full-size

database, or probably workload
generated is not yet in production,

but we already can see something
is dangerous here.

I'm trying to say this is probably
not Postgres's job.

Andrew: Yeah, it's probably not
Postgres's job.

I agree with you.

I was kind of thinking there's
the Ruby open source community,

there are some great command-line
tools.

I'll shout out rails-pg-extras
is one.

And a common interception point
where you might want to take

a more of a proactive approach
would be when you're creating

a migration.

Like, oh, do you realize now you've
created an inconsistency

between your schema and your application?

Like maybe you were checking over
here it was a string type and

over here you've created an integer
type, that kind of thing.

Or that's not the most exciting
example, but that command-line

time, like when the developer is
working, is kind of one touch

point.

Or like you said on CI, you shipped
off some code to a system

running tests and it starts to
detect things and gives the developer

feedback before it goes to production,
that's always a good thing.

Nikolay: Yeah.

Well, I'm trying to reflect on my
experience with Rails.

I can show you another one from 2017
I found.

So Shopify is on MySQL, right?

Yep.

You mentioned this.

So let me describe it.

There's a gem or library, I don't
know, called Delayed Job.

Delayed Job, just a single one.

And in 2017, I saw the issue.

I had a client in 2017 which was
on Rails and on RDS already.

It was maybe around the first time
I had a good production experience

with RDS, which I actually found
liking because it provided some

good automation for cloning, right?

For experiments.

Experiments matter a lot.

So I found this problem.

It was not performing well, delayed
jobs, under scale.

So you have a lot of jobs, a lot
of entries, also like high insert

rate. You need to have a high processing
rate.

It was super easy for me to find
what was the problem and I also

found this issue. It was discussed
on GitHub since 2013, saying

with half a million jobs it gets
really slow.

Half a million is not a huge number
at all for Postgres, it's

a small number.

So I popped up saying it's super
easy, like you just create an additional

index, this 1, and you have already
selected for an update, just

add 2 words, keep locked.

And index plus this, you have a massive
boost for your library.

You don't need to migrate to Sidekiq
or Kafka or something.

That's it.

You don't need to learn PGQ from
Skype.

And they didn't do it.

Now I understand, because it's
from Shopify, and they're from

MySQL world.

They started discussing some Postgres
versions, and I see that

I got a bunch of likes, and I keep
having email notifications,

people thanking me because this
saved their life, because this

improved.

But it looks like not only we talk
about the connection of Ruby and

Postgres worlds and the problem
of how to connect better, but

also this idea of ORM, let's be
abstract, let's keep agnostic

from the database.

This makes life worse actually.

We cannot use what Postgres can
offer.

What do you think about this?

So again, not a question, a reflection
of my experience, but maybe

it's a good discussion I think.

Michael: Well, I don't think you'd
be able to use Postgres if

there was only, if Rails only supported
one relational database

it would not be Postgres I don't
think, based on who runs the

Rails project.

Andrew: Yeah it certainly wasn't
the most popular in 2005 when

Ruby on Rails was kind of really
getting out there for the first

time, which now is a long time ago.

And yeah, I mean, so it does provide
you that indirection point

where you can say, well, we have
a generic adapter and then we

have the MySQL adapter.

And yeah, and maybe it doesn't
support skip locked.

So maybe we can't do skip locked
at all because it's not supported

across all three of our databases,
which is a trade-off.

Nikolay: You can write an if.

If it's Postgres, add two words.

Andrew: Yeah, and actually that's,
there's been, I should have

had a couple of examples ready
to go, but there's actually a

couple of things that are actually
only supported in Postgres

that ActiveRecord supports, which
is pretty exciting because

it's like a little bit of a philosophical
switch to me where

you say like, okay, well we have
these really useful capabilities,

but we're just going to support
them in Postgres only.

You know, sorry, MySQL users.

And that way then you get the best
of both of those categories.

But of course, like, then we aren't
really getting into this,

but there's a whole discussion
about SQL standards and stuff

like that.

So I know like, for example, the
returning clause is not supported

in, my understanding is not supported
in MySQL.

Nikolay: I just checked the docs
for MySQL, like latest version,

and they already started to support
skip locked, so.

Okay, yeah.

Andrew: Well, and it could be that
a framework that chooses to

offer conditional support, as long
as it's, of course, like,

you know, well tested and supported,
then that could actually

apply pressure to another open
source database to add support

for a SQL standard command, right?

It's like if this gains support,
probably not one framework like

Rails, but if everything Rails
and Laravel and Prisma and all

the language communities are all
clamoring for additional support.

Nikolay: You mentioned SQL standard,
but the problem here is

that SQL standard doesn't care
about performance at all.

Nothing about indexes and SKIP LOCKED is not there.

They don't care about performance.

And here we talk about performance,
right?

So it's not easy sometimes.

There should be some addition to
standard talking about performance.

But I don't see how it can happen.

Andrew: Yeah.

I think it's great that you've
provided that feedback, Nikolay,

into the community.

I think there is, to the earlier
question too, I think like Postgres

is going to, you know, has its core
set of objectives with each

release, you know, where the development
effort investment is

going and that sort of thing, which
is going to be a completely

different track than Ruby on Rails.

But like if we have some overlap,
you know, the folks that tend

to maintain the Postgres adapter
code are Postgres fans in the

Ruby on Rails community.

They happen to also write Ruby.

And it's great to have those kind
of overlapping folks.

There's kind of this discussion
around, I know we don't have,

we're out of time to get into this,
but there's an example that

comes to mind too of an in-clause
SQL query that has a large

list of values.

And then some folks notice that
from Ruby on Rails and on Postgres.

And that was one where I felt like,
I think Nikolay, we might have

both tweeted about this at some
point, but like there was kind

of this crossing the streams for
me where I saw people, Postgres

people I follow and Ruby on Rails
people, were like, you know,

we need to do something about this
and improve query performance

for these types of queries.

Nikolay: Right.

Also, .plug, or how is it called?

Plug, right?

This is like when you retrieve
everything, then you do something

like Postgres can do it much more
efficiently.

Don't do work instead of database
on application side, right?

But I guess it's a small thing,
any application language can

be used for this not efficient
approach.

What do you think about the future
of Ruby in general?

Andrew: I think it's achieved that
kind of cockroach status where

it just never goes away now.

I don't know.

There just seems to be enough,
not cockroach DB, but the actual

insect.

There's such a large amount of
companies that there's just a

lot of work to maintain the applications
if they're successful

that use Ruby on Rails.

Of course, the growth is not what
it was in the early days.

But there's a huge community of
Rails developers and there are

new people entering it.

So if someone enters it because
maybe it's like their bootcamp

program or they joined a company
that's using Rails, despite

being 20 years old as a framework
for Rails anyways.

First of all, I think Ruby is a
great language outside of Rails,

but despite being a 20 year old
framework, like there's a very

steady development clip.

There's new features being added
that are exciting.

I just think they tend to be more
front end oriented.

Like Ruby on Rails these days is
trying to capture more attention

from what might have been JavaScript
only apps before and offer

a lot of capabilities on as you're
building your web application

screens, like giving you a lot
of interactivity and low latency,

but doing it within Rails and kind
of getting back to some more

of the full stack kind of application
building capabilities.

But there are things happening
too on every release related to

Postgres and I certainly track
those and it still feels active,

just not where everyone's flocking
to.

Michael: I thought the active might
be a Rails joke.

Andrew: Oh yeah, it could be, yeah.

Michael: Yeah.

On a serious note though, and thanks
so much for all your time,

where can folks go to learn more?

Andrew: Yeah, If anyone's interested
in this topic area and they'd

like to explore the book that I
wrote, it's at pragprog.com.

That's the publishers Pragmatic
Programmers.

And I do a fair amount of blogging
on Postgres and Rails topics.

My blog is andyatkinson.com.

And I'm also on most of the social
media apps.

This year I'm going to be at Sin
City Ruby and PG Day Chicago,

as well, so probably more for Postgres
people.

If anyone's at PG Day Chicago,
I hope to make it to some more

Postgres events, but I love the
in-person events too and being

Able to meet more community members
and learn from other people.

Nikolay: And the book is called
High Performance PostgreSQL for

Rails, right?

Andrew: Yep, that's right.

Michael: Awesome work, awesome
publisher as well. And thanks

again.

Andrew: Thanks for having me.

A lot of fun, guys.

Thank you.