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 1tq5uP-00GuMO-0k for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 07:44:45 +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 1tq5uN-001uEV-OT for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 07:44:43 +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 1tq5uN-001uE3-As for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 07:44:43 +0000 Received: from mail-pj1-x102a.google.com ([2607:f8b0:4864:20::102a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tq5uJ-001FyE-2Z for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 07:44:42 +0000 Received: by mail-pj1-x102a.google.com with SMTP id 98e67ed59e1d1-2fec13a4067so570115a91.2 for ; Wed, 05 Mar 2025 23:44:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741247079; x=1741851879; 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=Pb5meH3QrXNaB9/QQHz1shol+7jcAJZVcl/5bFvBszw=; b=BjTpqbdWZNJ0CUEI+WGu8QtkA9P9l5Wrxx+rMbCGqngdXvlMTOPeYn1e1GGcdzaCma 2H+WMIMOhuhh1y1SOtrnGwUQbflwwmG65OSsAgGfmCl/L7xiTcECT9m42FsmxqA4mNEg hvzlZkS+cms/ZHkUwbsAezIMTnAAbkfPqG0gDLEoyypvs1fLKSaum5N/EYpMxcki6qYl 9WI/1OkUqPRHxaUiID9/q5xQxBqzVnhPZFTj2SF+KYapNEMxnaKR3fKrsrulxwxq+jPt LSOF6s0824kUSVYP3GmVUa18dD2iLqPasc8U1MeZUi5tNOEb0Ckfk/nfLcl36ffSmWgP 2GNQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741247079; x=1741851879; 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=Pb5meH3QrXNaB9/QQHz1shol+7jcAJZVcl/5bFvBszw=; b=K0m/zELfTTS1tsAUu4V6gV2b7B5idMMbdkB1XpmO5amt4CsxPnKO8NWfw6jH84YwkD tJoezHIER8UvymiwG3wXo+uYjTj1vkKpIXEn/AaJyBD6F96gUO+L21uAWHgj1oBF29Qe r9S0WqbGxaxy45/IVKoN458iQIu2a6NuAZ2HuA2Z2LjvpLbe5EDGnVE4/XtN6QmTtslX mYZrienyfJcpECDFw7xzg7M33Qcr37KbfA7o2KnJtd+Buc/p5aP1vLva80sAHav3LN4i zcZdCMbp7AyAz+rBLqI+m6bDU3Tn+fVh/OW6c9s923pZq8fBwBk/LvWItyFLwFwqU+hF k/oA== X-Forwarded-Encrypted: i=1; AJvYcCUOZSIHDf0ZG432vZnCJKfddSbA9p4nc5rIkBRCuTjJp4jtrpQh1/50Rs7bCgJk6n4yFEH3mW2Cfq3vyhy2@lists.postgresql.org X-Gm-Message-State: AOJu0YzPT3pEHQnUYVGmqy+rxHocUWgmcDCpMRJkoQ7B2R4qlbWACxMN YJ2x9T/RSHKXWbsdbMRJJlZ65P2FJFB0yTICc4pZvrwsSgl8KxNkZmD5e/RF8ish2H7MfLxYqfa woL1UNqk23m3dwGP+CMfPpRdq9aI= X-Gm-Gg: ASbGnctgKlv/jaO13I2gQxkLMcL1+ppZJGAAMsMu/WQBBg3J9rE+07ZVzjI0f5H9owU gSScg7BqQAuIrENx/VSmCUYQhzGvAJPu7sKMpPNmkNmc6wY5B7XDggyztvQ66vYuE+rZiIt0iIn jRpMTzsnh4qZVVndXpvUtP/+4HKg== X-Google-Smtp-Source: AGHT+IGa71bkU0acpl8zcZuvjdYadDsYHTYTmubYrf3StJWdwQnOq8lfWqYC2JchiamzvDHYyK+zRoQULwkBcCnUQxI= X-Received: by 2002:a17:90b:1ccd:b0:2fe:955d:cdb1 with SMTP id 98e67ed59e1d1-2ff4979d905mr8286947a91.23.1741247078541; Wed, 05 Mar 2025 23:44:38 -0800 (PST) MIME-Version: 1.0 References: <025C57B4-F1C3-4533-86FD-D7C85EDCF143@gmail.com> In-Reply-To: <025C57B4-F1C3-4533-86FD-D7C85EDCF143@gmail.com> From: me nefcanto Date: Thu, 6 Mar 2025 11:14:25 +0330 X-Gm-Features: AQ5f1JrOSwN7kZ3V-EkLej0YZMZ0OxrNBCqddApkg7z81Qt6CRJg5GcsMPjZKpk Message-ID: Subject: Re: Quesion about querying distributed databases To: Rob Sargent Cc: Igor Korot , Adrian Klaver , Laurenz Albe , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000095348a062fa7adae" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000095348a062fa7adae Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 number of tables is not our criteria to break them. The business semantics is our 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, create 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 about - 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 that 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-database 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 own servers, and we start implementing table partitinong on them. But we have experienced some amazing improvements too in our initial tests. For example, creating all databases and tables and database objects 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 this 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 ca= n > 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. 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 Serv= er >> 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 you > come to choose postgres? The numbers you present are impressive but not > unheard of on this list. > > --00000000000095348a062fa7adae Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I appreciate your time guys. Thank you very much.

>=C2=A0<= span style=3D"font-family:Arial,Helvetica,sans-serif">Having 1 table per da= tabase per server is too ugly.

Our databases are not one table per database. T= hey are mapped to DDD's bounded contexts and usually by one table per d= omain entity.
For example, we have these databases:

  • Contacts
    • =
  • Courses
  • Seo
  • Payment
  • Forms
  • Geo
  • S= ales
  • 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
  • Re= lations
  • RelationTypes
  • SocialNetworks
  • SocialProfiles=
  • Titles
And, these are the tables we have in th= e Geo database:

  • AdministrativeDivisions
  • AdministrativeDivisionTypes
  • Cities
  • CityDivisions
    • =
  • Countries
  • Locations
  • SpatialDataItems
  • TelephoneP= refixes
  • TimeZones
But we also do have databases that = only have one table in them. The number of tables is not our criteria to br= eak them. The business semantics is our criteria.

>=C2=A0Cros= s-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 conce= pt of schemas. In other words, both SQL Server and Postgres let you create = databases, create schemas inside them, and create tables inside schemas. So= SQL Server's cross-schema query equals Postgres's cross-schema que= ry.

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

We cho= se 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 about
  • Supporting expres= sions in unique constraints
  • It's usually considered one of the = best when it comes to performance, especially in GIS we intend to develop m= ore upon
  • As it claims on its website, it's the most advanced op= en-source database engine (but to be honest, we saw many serious drawbacks = to that statement)
But here's the deal. We don't have= one project only. We don't need cross-server queries=C2=A0for a= ll of our projects. But we tend to keep our architecture the same across pr= ojects as much as we can. We chose Postgres because we had experience with = SQL Server and MariaDB and assumed that cross-database query on the same se= rver is something natural. Both of them support that. And both are very per= formant on that. On MariaDB all you have to do is to use `db_name.table_nam= e` and on SQL Server all you have to do is to use `database_name.schema_nam= e.table_name`. So we thought, for projects that do not need more than one s= erver, we keep databases on the same server. When it needed more resources,= we start by taking heavy databases onto their own servers, and we start im= plementing table partitinong on them.

But we= have experienced some amazing improvements too in our initial tests. For e= xample, creating all databases and tables and database objects on MariaDB t= akes more than 400 seconds, while the same took 80 seconds on Postgres. So = amazing performance on DDL.
Also, 1 million records in bulk inser= tion take almost one-sixth to on-fourth of the time on MariaDB. These are v= aluable numbers. They warmed our hearts to keep digging as much as we can t= o see if we can perform this migration.

Regards
Saeed

On Thu, Mar 6, 2025 at 7:14= =E2=80=AFAM Rob Sargent <robjsa= rgent@gmail.com> wrote:

On Mar 5, 2025, at 8= :03=E2=80=AFPM, Igor Korot jn= it worked great for SQL Server. If you're small, we host them all on on= e server. If you get bigger, we can put heavy databases on separate machine= s.

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 a= sked you guys. However, encouraging me to go back to monolith without givin= g 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 wa= y (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 i= dentical to cross schema on postgres. If you truly need cross server suppor= t (versus say beefier hardware) how did you come to choose postgres?=C2=A0 = The numbers you present are impressive but not unheard of on this list.=C2= =A0

--00000000000095348a062fa7adae--