Message-ID: <0a6ee022deb198be152670742e20eae4099ae9ec.camel@cybertec.at>
Subject: Re: Quesion about querying distributed databases
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: me nefcanto <sn.1361@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	pgsql-general@lists.postgresql.org
Date: Thu, 06 Mar 2025 13:01:32 +0100
In-Reply-To: <CAEHBEODn+-SF5nTArF9_mf968UAKbs0ZPbDpzJK=ZU2vs2DKNA@mail.gmail.com>
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>
	 <6d7e1022-6404-4dab-8467-8d1f6e8b63cb@aklaver.com>
	 <CAEHBEOCpxASoNn=u21kaqOn1A-4YPy_mVfgkEjT3wRT5G4ycbg@mail.gmail.com>
	 <e9769f673c78bbeeabd82eb8b3054cee4fbd662f.camel@cybertec.at>
	 <CAEHBEODn+-SF5nTArF9_mf968UAKbs0ZPbDpzJK=ZU2vs2DKNA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/0a6ee022deb198be152670742e20eae4099ae9ec.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-03-06 at 12:15 +0330, me nefcanto wrote:
> We had problems with cross-server queries on SQL Server and MariaDB too.
> It seems that cross-server queries are not solved by any engine. But we
> had no problem with cross-database queries. That's where it worked well
> both on SQL Server and MariaDB.

But then you always worked with a monolithic system.  Splitting over severa=
l
"databases" (we call them schemas) does not allow you to scale horizontally=
.

> > Well, if you split the data into several databases, that *was* sharding=
.
>=20
> The way I understood it, sharding is when you split the database by rows,
> not by tables. Examples choose a column like Tenant or User or Date as th=
e
> base of sharding. Never have I seen an example that stores Orders on one
> database and Customers on another database and call it sharding.

Right.  And I don't think that your data model is good.  It won't scale
well, because you don't get more tables as you get more data.


> But to choose a technology, we do have enough literacy and experience.
> It's just some simple questions and answers. If I know that FDW works
> differently for same-server databases, then I know that we will migrate.

It doesn't work any differently.

> > Don't ever store arrays in the database.=C2=A0 It will be a nightmare.
>=20
> This is a very interesting claim. May I ask you to share its problems
> and your experience?

If you store foreign keys in arrays, you have no referential integrity,
and any join over the array will be complicated and slow.

If you store many values in an array and you want to update one of these
values, you have to retrieve the whole array, construct a new array and
store that.  You cannot modify individual array elements, only the whole
thing.

Of course there are ways to use arrays that are not harmful.  It's just
that almost always when I see someone use an array as table column, it
is an abuse of technology.

Yours,
Laurenz Albe