Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tq6rv-00HACx-3T for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 08:46:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tq6rt-003bMW-6A for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 08:46:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tq6rs-003bGJ-IU for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 08:46:12 +0000 Received: from mail-pj1-x102e.google.com ([2607:f8b0:4864:20::102e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tq6ro-001GWG-0W for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 08:46:11 +0000 Received: by mail-pj1-x102e.google.com with SMTP id 98e67ed59e1d1-2feae794508so665372a91.0 for ; Thu, 06 Mar 2025 00:46:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741250767; x=1741855567; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=KFRPu7z+pdH04T4REEXDEABtmngdpjWpr9Yez3ymSKg=; b=D+cs8suJULX3zptAC7Kk685e13smThDejkyUEO44y3t4m4sKMms0eiA+2qjDKHIJFx l3pcgFcpFTm7kgXPNwXecXl51ckSf4GKHpgR7UiG0BDUulNYa0dI7LgF7uw6giCYfQU5 4BaQEKzrIVzcuuGpWU/LLZVtl93obs+h1Mqv+QJGQkw3LCjHSIK61RuBMdvN6OqzjGC5 HWUAAQRRTf9B0gOoFwG/ixs/eeDUp3YFluX5nIaG7lNpzLOEVDtWRSzmdzZvfQySAkU8 r2mSRTI7S1efVzKlckJUf6K+KreNEla7e/jnv1qqtkIniDyyUlBYSs1TuXdAXK5qLKIy rbyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741250767; x=1741855567; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=KFRPu7z+pdH04T4REEXDEABtmngdpjWpr9Yez3ymSKg=; b=sLhKjmPN1fHS7hv8u1VCC9uFSwAThJ6lyYtWUHFdq5LFaFYs4QR9TriDFecB3e4VyR 6/wunqP5IzUFBUVyY1lc++XHDnD0W6jbkQ/wg0HiF+ni3i2kFwh4vReJBJ88LEyaCq6o dSTfITFrIJk66a/vNNMv4vtB4c91Y4PNMjaKbTOU1JSs7jMFy2XBF7/BjmfsfHeQqYt4 2YErgura+ri0+21ETfy4QHVvxP+avcxxilfnNLpxe41Wup7JHzJMmdkihm0tiGEBOJcg uFVzuJERJR1naXsbCGqm3HQFVTeFE9CNHmXkPc530XZhxT9JVQfQkcjlmZtcMlxm7AOy yKZg== X-Forwarded-Encrypted: i=1; AJvYcCUnisIyJy+qW6XB7E8UHORp/SGhF7ifawfI4oySJ3/K10985PuDjonubO+Kmy9BCaVMnOYMikKyfv8woDSk@lists.postgresql.org X-Gm-Message-State: AOJu0Yzbs2oMUSk7sfoO/PqeVny35pdjaiHSLHVlrn4dLKJAM7roDpzP /iW+BWQcUXyUQbQdpU2lRE73nN+I5qtwEFObueUBEskWd7qWdLJbeC6L8LBSqHMSIbqC+vQtfpz 2vdT8zMYQCBT1luPrFQODp2567j8= X-Gm-Gg: ASbGncsXPmq1O1HI7+Qj5vccUumqoq5GDoaaoi7NCj7pnXQNPJh7/xlFbpbY6YX9w0Z ss98YvK2DgiyUNbOUrUmfdE48G2e3aKfvI/z70sNCsj9h6Y7O/s7H3LFJw9fQA0mVylspBzzaq6 NWR2kCS+WwCs3meEc5P03caeFqfUw= X-Google-Smtp-Source: AGHT+IGzkOx8lwOctVvTn+4RbRvzfIW5sJ4/CEzw7DpkUH0Adi3C55ISwewtzZeRBxj1BT1WaS0E0yyKXDSbzRXWrF8= X-Received: by 2002:a17:90b:4b45:b0:2fa:15ab:4de7 with SMTP id 98e67ed59e1d1-2ff497ac236mr12186957a91.12.1741250767206; Thu, 06 Mar 2025 00:46:07 -0800 (PST) MIME-Version: 1.0 References: <099b49ebae94e23f19afdad3f8c9c6e702a3a2d5.camel@cybertec.at> <6d7e1022-6404-4dab-8467-8d1f6e8b63cb@aklaver.com> In-Reply-To: From: me nefcanto Date: Thu, 6 Mar 2025 12:15:55 +0330 X-Gm-Features: AQ5f1Jpu5S_ptkc1c24bQPFG7lyHy0f_kstaMGnUva2VNHMsZG8ix1xFttJUwKc Message-ID: Subject: Re: Quesion about querying distributed databases To: Laurenz Albe Cc: Adrian Klaver , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000071c20d062fa88920" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000071c20d062fa88920 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Laurenz Albe 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 tha= n > 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, becaus= e > 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 b= e > > 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 informatio= n > 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 > --00000000000071c20d062fa88920 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear Laurenz

>=C2=A0I hear you, and I agree with that.

Thank you. Such a relief.

>=C2=A0If 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 cr= oss-database queries, Postgres returns the entire result set from the other= database to this database and then performs joins locally. It seems that f= or Postgres it's not different if the foreign database is on the same m= achine, 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.

>= =C2=A0Well, if you s= plit the data into several databases, that *was* sharding.

The way I understood it, s= harding is when you split the database by rows, not by tables. Examples cho= ose a column like Tenant or User or Date as the base of sharding. Never hav= e I seen an example that stores Orders on one database and Customers on ano= ther database and call it sharding. I don't know, but we might call it = distributed databases.

=C2=A0>=C2=A0Consider using=C2=A0other, better databases than Po= stgreSQL (if you can find them).

That's the point he= re. 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 a= bove, Postgres is amazing at some points but lacks some simple things that = other engines expose out of the box.

>=C2=A0Perhaps you should get a consultan= t; the mailing list does=C2=A0not seem to be the right format for that request.

We have done that over th= e last decade. For SQL Server and then for MariaDB. We have come up with so= me very practical and useful designs. Separating CLOBs from main tables, st= oring UUID only as the name of files to match the cloud storage, storing da= te-times as UTC, using bigint everywhere even for small tables for consiste= ncy, denormalizing enum storage (storing text instead of numeric value) eve= n in large tables, etc. etc.

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

>=C2=A0Don't ever store arrays in the databas= e.=C2=A0 It will be a nightmare.

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

<= br>
On Thu, Mar 6, 2025 at 11:34=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec.at> = wrote:
On Thu, 2= 025-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=C2=A0of data. A single backup took us mo= re than 21 days.
> It was a nightmare. Almost everybody knows that scaling up has a ceili= ng, but
> scaling out has no boundaries.

I hear you, and I agree with that.


> We initially chose to break the database into smaller databases, becau= se it
> seemed natural for our modularized monolith architecture. And it worke= d great
> for SQL Server. If you're small, we host them all on one server. I= f 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?=C2=A0 It must be someth= ing
akin to foreign data in PostgreSQL.

If that worked well, then it should also work well with PostgreSQL and
foreign data wrappers.=C2=A0 Look at the execution plan you got on SQL Serv= er
and see where PostgreSQL chooses a different plan.=C2=A0 Then try to improv= e that.
We can try to help if we see actual plans.

> However, I don't have experience working with other types of datab= ase
> scaling. I have used table partitioning, but I have never used shardin= g.

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 sou= rce
> database does not support cross-database querying just like how SQL Se= rver
> does. But if it doesn't, it doesn't. Our team should either dr= op it as a
> choice or find a way (by asking the experts who built it or use it) ho= w
> to design based on its features. That's why I'm asking.

Excluding options from the start is limiting yourself.=C2=A0 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<= br> you provided.=C2=A0 Perhaps you should get a consultant; the mailing list d= oes
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 da= ta
from different databases (shards).

> One thing that comes to my mind, is to use custom types. Instead of st= oring
> 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.=C2=A0 It will be a nightmare.<= br> You seem to be drawn to questionable data design...

Yours,
Laurenz Albe
--00000000000071c20d062fa88920--