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 1tq7NP-00HI92-HU for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 09:18:47 +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 1tq7NN-004ULw-EE for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 09:18:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tq7NM-004UIP-Vx for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 09:18:45 +0000 Received: from mail-yw1-x1134.google.com ([2607:f8b0:4864:20::1134]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tq7NJ-001Gfh-19 for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 09:18:44 +0000 Received: by mail-yw1-x1134.google.com with SMTP id 00721157ae682-6fead015247so3661237b3.2 for ; Thu, 06 Mar 2025 01:18:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741252721; x=1741857521; 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=HUaBEUt5EiPJiwbZwd2nXUJG6fXyjHGI938AkOIUtRQ=; b=L1d3uM5GyxMzUD6Q71aLwrqaor2U4OWepkEZR4WFHqcIG6lJSzQAIBQBajKacOwV/j zYG+9mxodETQZO4grBMk5g6lUdSWzZaxsiHTAj0qG8pX289nTuy6KZy4mEVAV7GUV2AR NFrge+3km/NkFK2ZoMk0Ihx3sXCnBKNxCb+kU+MIPrqByIrfanWGG/IBtnXHfIzyyp76 te4ce+RiDwgecf6h5bMQfc78+5nZo0w/INh2wdQ2y6BGVIOFM1lQSeIKso5DSUllrqmk pbhrAq3fIKPlnrJ8rfLAH7dsOfaYBuoUj6C1AVzOlRQybHcN/OudxlLYc89GrOYOmecS Pcyw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741252721; x=1741857521; 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=HUaBEUt5EiPJiwbZwd2nXUJG6fXyjHGI938AkOIUtRQ=; b=wG3kSriM+6KoiRTnzxD+hUrT2ZJIsZyZ/HJlHtO0i2ZBwP7GV2BuamA3/iRJWWRsGr hvQeN+mfFZ5cXXU6bwLq1+TkJ3RRrtd5EryWFj8ndwN/FJMjUcOvqXp/2vYonAUgXK1D VC+BbuReU+dTYwjTZ876QeMl6wILA/K/VZ8pJqzls/oHqVQ9jB8P04LPNPayoVuCsRhZ ypwoeGnHvSt9kuH90z1g0BdSwBnE7iabm1MoQaNL2Tw2B7n2FjY5yvLj1+gKTgtU2C0r 4UgXgPL6t7iL7oR72Mr6wXj01iPPcN/30wP7kHixBWMNfEovCMnVqkgaoMSziIqeMMl0 upGA== X-Forwarded-Encrypted: i=1; AJvYcCVvsp5MCQki3a+x3KmCgXkII6q2KGjiiySSTxC2NaTPdCpl4l/LEw6a0r7CVNEjMbwRd5UJzfElaayw6Xys@lists.postgresql.org X-Gm-Message-State: AOJu0YwWMeo45kwuLZFxbs7q2YWeYV8uD6Kv590HObTsTfOhnv06BG7c c9LM051IhlyIdyGorOi0QKyWJqJ4CA0dPZS0YqZu1IACwoXAmbpP2P1x2dtREHRSr2XrfX78dvT RNqmzkiR1a7XjRPTZm5aDA3jHkCc= X-Gm-Gg: ASbGncutEW01NeYQ17FAtXDPieovIzbrK5n5lyHM15z+JF67G2h4qb/c5qyCjBFF99X uIYqvN7mbXB9NmKhm2kZqsWLEIyBeunOe+s5KYxzTXZvsgBHxgli22jGueD+cmzgnkGM1n/A5Bt DPu9WWSDDZKGrZcmzDB1yPXr7zpkAEzwzEeg4GWw+OCjREU/1tG6d4lmp0x2rf X-Google-Smtp-Source: AGHT+IEYIyHNlIieFCAw61bh4Hlq8PTLbRLeoMkW3PIWXfaC3YFT62vWY6wMn5Y84Pr7xUo6DNln/jANdYQiGpU3Kfo= X-Received: by 2002:a05:690c:6086:b0:6fd:4718:9bde with SMTP id 00721157ae682-6fda300d8c7mr83067997b3.9.1741252720675; Thu, 06 Mar 2025 01:18:40 -0800 (PST) MIME-Version: 1.0 References: <025C57B4-F1C3-4533-86FD-D7C85EDCF143@gmail.com> In-Reply-To: From: Igor Korot Date: Thu, 6 Mar 2025 03:18:29 -0600 X-Gm-Features: AQ5f1JpM3n9uOdvVXmju7jBG27KaJLvvmsSih_3doRm0yNZODhmIDrtaayXZ0-E Message-ID: Subject: Re: Quesion about querying distributed databases To: me nefcanto Cc: Rob Sargent , Adrian Klaver , Laurenz Albe , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000e152ee062fa8fdbb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e152ee062fa8fdbb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, On Thu, Mar 6, 2025, 1:44=E2=80=AFAM me nefcanto wrote: > I appreciate your time guys. Thank you very much. > > > Having 1 table per database per server is too ugly. > > Our databases are not one table per database. They are mapped to DDD's > bounded contexts and usually by one table per domain entity. > For example, we have these databases: > > > - Contacts > - Courses > - Seo > - Payment > - Forms > - Geo > - Sales > - Media > - Taxonomy > - ... > > These are the tables we have in the Contacts database: > > > - Addresses > - AddressTypes > - Attributes > - BankAccounts > - ContactContents > - Contacts > - Emails > - Genders > - JobTitles > - JuridicalPersons > - NaturalPersonRelations > - NaturalPersons > - Persons > - Phones > - PhoneTypes > - Relations > - RelationTypes > - SocialNetworks > - SocialProfiles > - Titles > > And, these are the tables we have in the Geo database: > > > - AdministrativeDivisions > - AdministrativeDivisionTypes > - Cities > - CityDivisions > - Countries > - Locations > - SpatialDataItems > - TelephonePrefixes > - TimeZones > > But we also do have databases that only have one table in them. The numbe= r > of tables is not our criteria to break them. The business semantics is ou= r > criteria. > > > Cross-database on MSSQL is identical to the cross schema on Postgres. > > Cross-database query in SQL Server is not equivalent to cross-schema > queries in Postgres. Because SQL Server also has the concept of schemas. = In > other words, both SQL Server and Postgres let you create databases, creat= e > schemas inside them, and create tables inside schemas. So SQL Server's > cross-schema query equals Postgres's cross-schema query. > > > If you truly need cross server support (versus say beefier hardware) > how did you come to choose postgres? > > We chose Postgres for these reasons that we did R&D about: > > > - Native array per column support > - Not having multiple storage engines like MariaDB to be confused abou= t > - Supporting expressions in unique constraints > - It's usually considered one of the best when it comes to > performance, especially in GIS we intend to develop more upon > - As it claims on its website, it's the most advanced open-source > database engine (but to be honest, we saw many serious drawbacks to th= at > statement) > > But here's the deal. We don't have one project only. We don't need *cross= -server > queries* for all of our projects. But we tend to keep our architecture > the same across projects as much as we can. We chose Postgres because we > had experience with SQL Server and MariaDB and assumed that cross-databas= e > query on the same server is something natural. Both of them support that. > And both are very performant on that. On MariaDB all you have to do is to > use `db_name.table_name` and on SQL Server all you have to do is to use > `database_name.schema_name.table_name`. So we thought, for projects that = do > not need more than one server, we keep databases on the same server. When > it needed more resources, we start by taking heavy databases onto their o= wn > servers, and we start implementing table partitinong on them. > But why? Remember - multiple servers means more traffic which might be performance wise. And especially if thise servers are located on different hardware as your OP implied. Thank you. > But we have experienced some amazing improvements too in our initial > tests. For example, creating all databases and tables and database object= s > on MariaDB takes more than 400 seconds, while the same took 80 seconds on > Postgres. So amazing performance on DDL. > Also, 1 million records in bulk insertion take almost one-sixth to > on-fourth of the time on MariaDB. These are valuable numbers. They warmed > our hearts to keep digging as much as we can to see if we can perform thi= s > migration. > > Regards > Saeed > > On Thu, Mar 6, 2025 at 7:14=E2=80=AFAM Rob Sargent wrote: > >> >> >> On Mar 5, 2025, at 8:03=E2=80=AFPM, Igor Korot jnit worked great for SQL= Server. >> If you're small, we host them all on one server. If you get bigger, we c= an >> put heavy databases on separate machines. >> >> >>> However, I don't have experience working with other types of database >>> scaling. I have used table partitioning, but I have never used 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. T= o be >>> honest, I'm somehow disappointed by how the most advanced open source >>> database does not support cross-database querying just like how SQL Ser= ver >>> 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. >>> >>> >> Cross-database on MSSQL is identical to cross schema on postgres. If you >> truly need cross server support (versus say beefier hardware) how did yo= u >> come to choose postgres? The numbers you present are impressive but not >> unheard of on this list. >> >> --000000000000e152ee062fa8fdbb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

On Thu, Mar 6, 2025, 1:44=E2= =80=AFAM me nefcanto <sn.1361@gmail= .com> wrote:
I ap= preciate your time guys. Thank you very much.

>=C2=A0Having 1 table per database per serv= er is too ugly.

Our databases are not one table per database. They are mapped = to DDD's bounded contexts and usually by one table per domain entity.
Fo= r example, we have these databases:

  • Contacts
  • Courses
  • Seo
  • Payment
  • Forms
  • Geo
  • Sales
  • Me= dia
  • Taxonomy
  • ...
These are the tables we have= in the Contacts database:

  • Addresses
    • <= li>AddressTypes
  • Attributes
  • BankAccounts
  • ContactCont= ents
  • Contacts
  • Emails
  • Genders
  • JobTitles
    • =
  • JuridicalPersons
  • NaturalPersonRelations
  • NaturalPersons<= /li>
  • Persons
  • Phones
  • PhoneTypes
  • Relations
    • RelationTypes
  • SocialNetworks
  • SocialProfiles
  • Titles=
And, these are the tables we have in the Geo database:=

  • AdministrativeDivisions
  • Administr= ativeDivisionTypes
  • Cities
  • CityDivisions
  • Countries
  • Locations
  • SpatialDataItems
  • TelephonePrefixes
    • TimeZones
But we also do have databases that only have one t= able in them. The number of tables is not our criteria to break them. The b= usiness semantics is our criteria.

>=C2= =A0Cross-database on= MSSQL is identical to the cross schema on Postgres.

<= /div>
Cross-database query in SQL Server is not equivalent to cross-sch= ema queries in Postgres. Because SQL Server also has the concept of schemas= . In other words, both SQL Server and Postgres let you create databases, cr= eate schemas inside them, and create tables inside schemas. So SQL Server&#= 39;s cross-schema query equals Postgres's cross-schema query.

>=C2=A0If you truly need cross server support (versus say beefier hardware) = how did you come to choose postgres?

We chose Postgres f= or these reasons that we did R&D about:

    Native array per column support
  • Not having multiple storage engin= es like MariaDB to be confused about
  • Supporting expressions in uniq= ue constraints
  • It's usually considered one of the best when it = comes to performance, especially in GIS we intend to develop more upon
    • =
  • As it claims on its website, it's the most advanced open-source dat= abase engine (but to be honest, we saw many serious drawbacks to that state= ment)
But here's the deal. We don't have one project = only. We don't need cross-server queries=C2=A0for all of our pro= jects. But we tend to keep our architecture the same across projects as muc= h as we can. We chose Postgres because we had experience with SQL Server an= d MariaDB and assumed that cross-database query on the same server is somet= hing natural. Both of them support that. And both are very performant on th= at. On MariaDB all you have to do is to use `db_name.table_name` and on SQL= Server all you have to do is to use `database_name.schema_name.table_name`= . So we thought, for projects that do not need more than one server, we kee= p databases on the same server. When it needed more resources, we start by = taking heavy databases onto their own servers, and we start implementing ta= ble partitinong on them.

But why?
Remember - multiple servers means more traffic which might be performance = wise.
And especially if thise servers are located on= different hardware as your OP implied.

Thank you.=C2=A0


But we have experienced some = amazing improvements too in our initial tests. For example, creating all da= tabases and tables and database objects on MariaDB takes more than 400 seco= nds, while the same took 80 seconds on Postgres. So amazing performance on = DDL.
Also, 1 million records in bulk insertion take almost one-si= xth to on-fourth of the time on MariaDB. These are valuable numbers. They w= armed our hearts to keep digging as much as we can to see if we can perform= this migration.

Regards
Saeed

On Thu, Mar 6, 2025 at 7:14=E2=80=AFAM Rob Sargent <robjsargent@gma= il.com> wrote:

<= div dir=3D"ltr">
On Mar 5, 2025, at 8:03=E2=80= =AFPM, Igor Korot jnit 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.=

However, I don't have experienc= e working with other types of database scaling. I have used table partition= ing, but I have never used sharding.

Anyway, that's why I asked you = guys. However, encouraging me to go back to monolith without giving solutio= ns on how to scale, is not helping. To be honest, I'm somehow disappoin= ted by how the most advanced open source database does not support cross-da= tabase querying just like how SQL Server does. But if it doesn't, it do= esn't. Our team should either drop it as a choice or find a way (by ask= ing the experts who built it or use it) how to design based on its features= . That's why I'm asking.


Cross-database on MSSQL is identical = to cross schema on postgres. If you truly need cross server support (versus= say beefier hardware) how did you come to choose postgres?=C2=A0 The numbe= rs you present are impressive but not unheard of on this list.=C2=A0
--000000000000e152ee062fa8fdbb--