Postgres Architecture

[Timothy Nelson]

Hi all!

I'm a DevOps Manager/Engineer by trade (though the place I work is not,
unfortunately, using Postgres).  I've been thinking quite a bit about what
our ideal architecture at work will look like and what scaling looks like,
both for work and for home projects (where I *am* looking at using Postgres
:) ).

Tonight, (unrelated to work) I took the time to draw up a diagram of an
architecture that I think would help Postgres move one step towards both
more scalability, and better ease of use.

Since I'm not so hot at drawing ASCII art diagrams, I thought maybe the way
to go would be to drop it into a Google Presentation and make that public.

It's a couple of diagrams (existing and proposed architecture), and a list
of what I think the advantages and disadvantages are.

https://docs.google.com/presentation/d/1ew31STf8qC2keded5GfQiSUwb3fukmO0PFnZw12yAs8/edit?usp=sharing

To keep it short, the proposal is that the stages from Parse through Plan
be done in a separate  process (and potentially on a separate server) from
the Execute stage.  The idea is:
- The Parse/Plan servers don't care whether they're read or write
- The Parse/Plan know which Execute server is the writer (and which the
readers), and forward to the correct server for execution

I even wonder if this might not mean that the Parse/Plan servers can be
deployed as K8s containers, with the Execute server being the external
non-k8s server.

Note that in this e-mail, I've referred to:
- The Parse/Plan server (which my diagram calls the Postgres SQL server)
- The Execute server (which my diagram calls the Storage server)

I'm not sure what naming makes sense, but I intentionally used a couple of
different names in hopes that one of them would get the idea across --
please disregard whichever names don't make sense, and feel free to suggest
new ones.

I'm expecting that people will pick the idea apart, and wanted to know what
people think of it.

Thanks!

Re: Postgres Architecture

[Timothy Nelson]

Great!  I'm not surprised it's been around a long time -- I didn't think I
could be the only one to think of it.

Thanks for the heads-up on Postgres-XL -- I'd missed that one somehow.

I'm going to include the words "architecture" and "replication" so that
people searching the archives in the future have more chance of finding
this conversation.

Thanks!

On Tue, 17 Oct 2023 at 02:07, Jonah H. Harris 
wrote:

> On Mon, Oct 16, 2023 at 6:42 AM Timothy Nelson 
> wrote:
>
>> I'm expecting that people will pick the idea apart, and wanted to know
>> what people think of it.
>>
>
> Thanks for the proposal. This is actually a model that's been around for a
> very long time. And, in fact, variations of it (e.g. parsing done in one
> place and generated plan fragments shipped to remote execution nodes where
> the data resides) are already used by things like Postgres-XL. There have
> also been a number of academic implementations where parsing is done
> locally and raw parse trees are sent to the server as well. While these
> things do reduce CPU, there are a number of negative aspects to deal with
> that make such an architecture more difficult to manage.
>
> --
> Jonah H. Harris
>
>