Re: Quesion about querying distributed databases

Dear Laurenz

> I hear you, and I agree with that.

Thank you. Such a relief.

> If that worked well, then it should also work well with PostgreSQL and
foreign data wrappers.

You're right. 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. It seems that for
cross-database queries, Postgres returns the entire result set from the
other database to this database and then performs joins locally. It seems
that for Postgres it's not different if the foreign database is on the same
machine, or it's on another machine. I just say so by seeing the queries
and asking questions about them. I have not performed a test yet.

> Well, if you split the data into several databases, that *was* sharding.

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 the
base of sharding. Never have I seen an example that stores Orders on one
database and Customers on another database and call it sharding. I don't
know, but we might call it distributed databases.

> Consider using other, better databases than PostgreSQL (if you can find
them).

That's the point here. If we can't design a good thing on Postgres, then we
stick back to MariaDB. That's why we're researching and testing. As I
mentioned above, Postgres is amazing at some points but lacks some simple
things that other engines expose out of the box.

> Perhaps you should get a consultant; the mailing list does not seem to be
the right format for that request.

We have done that over the last decade. For SQL Server and then for
MariaDB. We have come up with some very practical and useful designs.
Separating CLOBs from main tables, storing UUID only as the name of files
to match the cloud storage, storing date-times as UTC, using bigint
everywhere even for small tables for consistency, denormalizing enum
storage (storing text instead of numeric value) even in large tables, etc.
etc.

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.

> Don't ever store arrays in the database. It will be a nightmare.

This is a very interesting claim. May I ask you to share its problems and
your experience?

On Thu, Mar 6, 2025 at 11:34 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Thu, 2025-03-06 at 06:13 +0330, me nefcanto wrote:
> > I once worked with a monolithic SQL Server database with more than 10
> billion
> > records and about 8 Terabytes of data. A single backup took us more than
> 21 days.
> > It was a nightmare. Almost everybody knows that scaling up has a
> ceiling, but
> > scaling out has no boundaries.
>
> I hear you, and I agree with that.
>
>
> > We initially chose to break the database into smaller databases, because
> it
> > seemed natural for our modularized monolith architecture. And it worked
> great
> > for SQL Server. If you're small, we host them all on one server. If you
> get
> > bigger, we can put heavy databases on separate machines.
>
> So you mean that you had those databases on different servers?
> How would a cross-database query work in that case? It must be something
> akin to foreign data in PostgreSQL.
>
> If that worked well, then it should also work well with PostgreSQL and
> foreign data wrappers. Look at the execution plan you got on SQL Server
> and see where PostgreSQL chooses a different plan. Then try to improve
> that.
> We can try to help if we see actual plans.
>
> > However, I don't have experience working with other types of database
> > scaling. I have used table partitioning, but I have never used sharding.
>
> Well, if you split the data into several databases, that *was* sharding.
>
> > Anyway, that's why I asked you guys. However, encouraging me to go back
> to
> > monolith without giving solutions on how to scale, is not helping. To be
> > honest, I'm somehow disappointed by how the most advanced open source
> > database does not support cross-database querying just like how SQL
> Server
> > does. But if it doesn't, it doesn't. Our team should either drop it as a
> > choice or find a way (by asking the experts who built it or use it) how
> > to design based on its features. That's why I'm asking.
>
> Excluding options from the start is limiting yourself. Consider using
> other, better databases than PostgreSQL (if you can find them).
>
> It is difficult to come up with a concrete design based on the information
> you provided. Perhaps you should get a consultant; the mailing list does
> not seem to be the right format for that request.
>
> Typically, you split the data in a ways that they have few
> interconnections,
> for example per customer, so that you don't regularly end up joining data
> from different databases (shards).
>
> > One thing that comes to my mind, is to use custom types. Instead of
> storing
> > data in ItemCategories and ItemAttributes, store them as arrays in the
> > relevant tables in the same database.
>
> Don't ever store arrays in the database. It will be a nightmare.
> You seem to be drawn to questionable data design...
>
> Yours,
> Laurenz Albe
>