Hi, On Thu, Mar 6, 2025, 1:44 AM me nefcanto <sn.1...@gmail.com> 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 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 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 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 AM Rob Sargent <robjsarg...@gmail.com> wrote: > >> >> >> On Mar 5, 2025, at 8:03 PM, 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 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 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. >>> >>> >> 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. >> >>
