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.
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.albe@xxxxxxxxxxx> 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
