MIME-Version: 1.0
References: <CAEHBEOBuoMFWuhHM3L_Zr6o1enELju-Vns6Pknt4TT+6MFQOwQ@mail.gmail.com>
 <fd47b28c-8f6b-4bfb-a393-160c5c3de8c0@aklaver.com> <CAEHBEOD969YrbPH_z9OEmThWx3-w4sMMaHLhZLOQwqCwE8Y58Q@mail.gmail.com>
 <caf4e99941b3c83bb9eab91e33b144b826b68f79.camel@cybertec.at>
 <CAEHBEOBXzkGTqxQSYqmEFN5hbc=zsGWFpU9h8zf7AAPv4VdOWQ@mail.gmail.com>
 <c9699a6fd331d33864fc269060d8d961f784d827.camel@cybertec.at>
 <CAEHBEOBNoG8RkKuCcQQWkbYppMLMzA0MXq+s0kZ6wKWgD7+45Q@mail.gmail.com>
 <099b49ebae94e23f19afdad3f8c9c6e702a3a2d5.camel@cybertec.at> <CAEHBEODw8svX557pjB_EL-Os7KWtwi-9Uq=RuCkRKgHVZWw8Bw@mail.gmail.com>
In-Reply-To: <CAEHBEODw8svX557pjB_EL-Os7KWtwi-9Uq=RuCkRKgHVZWw8Bw@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 5 Mar 2025 10:21:27 -0500
Message-ID: <CAKAnmmLyu_tub6_OVhfFJeypxT4wAtVPs8x6kQfO6YPvmNDsaA@mail.gmail.com>
Subject: Re: Quesion about querying distributed databases
To: me nefcanto <sn.1361@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, Adrian Klaver <adrian.klaver@aklaver.com>, 
	pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009d7379062f99f3c1"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLyu_tub6_OVhfFJeypxT4wAtVPs8x6kQfO6YPvmNDsaA%40mail.gmail.com>
Precedence: bulk

--0000000000009d7379062f99f3c1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 5, 2025 at 7:15=E2=80=AFAM me nefcanto <sn.1361@gmail.com> wrot=
e:

> I think if we put all databases into one database, then we have blocked
> our growth in the future.
>

I think this is premature optimization. Your products table has 100,000
rows. That's very tiny for the year 2025. Try putting everything on one box
with good indexes and you might be surprised at the performance.


> A monolith database can be scaled only vertically.
>

Postgres scales well vertically. Plus, you can have streaming replicas to
distribute the read queries (like the one given here) across many machines.


> We have had huge headaches in the past with SQL Server on Windows and a
> single database.
> But when you divide bounded contexts into different databases, then you
> have the chance to deploy each database on a separate physical machine.
> That means a lot in terms of performance.
>

I get your concern, but if the data is inter-related, it really is best to
have them on the same server (and same database, and same schema). Then
Postgres can devise a really efficient plan. You can also use Citus to
start sharding things across multiple physical servers if your database
gets very large.

Let's put this physical restriction on ourselves that we have different
> databases. What options do we have?
>

Your main option is FDW, which will never perform as well as a single
server. Plus, you have the additional headache of trying to coordinate data
updates atomically across different servers. The other option is to have
the application do the work, e.g. pull a list of things from one server,
use that to build a query against another one, etc. Definitely not ideal.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

--0000000000009d7379062f99f3c1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Mar 5, 2025 at 7:15=E2=80=AFAM me=
 nefcanto &lt;<a href=3D"mailto:sn.1361@gmail.com">sn.1361@gmail.com</a>&gt=
; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div style=3D"font-f=
amily:tahoma,sans-serif">I think if we put all databases into one database,=
 then we have blocked our growth in the future.</div><div style=3D"font-fam=
ily:tahoma,sans-serif"></div></div></blockquote><div><br></div><div>I think=
 this is premature optimization. Your products table has 100,000 rows. That=
&#39;s very tiny for the year 2025. Try putting everything on one box with =
good indexes and you might be surprised at the performance.</div><div>=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"=
><div style=3D"font-family:tahoma,sans-serif">A monolith database can be sc=
aled only vertically.</div></div></blockquote><div><br></div><div>Postgres =
scales well vertically. Plus, you can have streaming replicas to distribute=
 the read queries (like the one given here) across many machines.</div><div=
>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"=
ltr"><div style=3D"font-family:tahoma,sans-serif">We have had huge headache=
s in the past with SQL Server on Windows and a single database.</div><div s=
tyle=3D"font-family:tahoma,sans-serif">But when you divide bounded contexts=
 into different databases, then you have the chance to deploy each database=
 on a separate physical machine. That means a lot in terms of performance.<=
/div></div></blockquote><div><br></div><div>I get your concern, but if the =
data is inter-related, it really is best to have them on the same server (a=
nd same database, and same schema). Then Postgres can devise a really effic=
ient plan. You can also use Citus to start sharding things across multiple =
physical=C2=A0servers if your database gets very large.</div><div><br></div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div st=
yle=3D"font-family:tahoma,sans-serif">Let&#39;s put this physical restricti=
on on ourselves that we have different databases. What options do we have?<=
/div></div></blockquote><div><br></div><div>Your main option is FDW, which =
will never perform as well as a single server. Plus, you have the additiona=
l headache of trying to coordinate data updates atomically across different=
 servers. The other option is to have the application do the work, e.g. pul=
l a list of things from one server, use that to build a query against anoth=
er one, etc. Definitely not ideal.</div><div><br></div></div><div dir=3D"lt=
r" class=3D"gmail_signature"><div dir=3D"ltr"><div>Cheers,</div><div>Greg</=
div><div><br></div><div>--</div><div>Crunchy Data - <a href=3D"https://www.=
crunchydata.com" target=3D"_blank">https://www.crunchydata.com</a></div><di=
v>Enterprise Postgres Software Products &amp; Tech Support</div><div><br></=
div></div></div></div>

--0000000000009d7379062f99f3c1--