Very slow syncing performance with Postgres

[ajscilingo]

Hi, we've been developing a proof of concept using DotMim.Sync to keep our databases in sync. For smaller tables and schemas it has worked as expected within somewhat reasonable time constraints but for larger tables and schemas we've seen a massive performance hit. Case in point, we're trying to sync just one table which has two foreign keys on look up tables. The look up tables do not have any foreign keys and have 61 rows of data and 6 rows of data respectively, each have a primary key which is a UUID. The table we're trying to sync has 17 columns, one of which is a UUID primary key and 10,056 rows of data. For the purpose of this test we've set the SyncOptions property DisableConstraintsOnApplyChanges to true to avoid issues with foreign key constraints. On average synching only this one table takes about 7 minutes! We've played around with the BatchSize property as well but this has resulted in only about a 30 sec performance gain at best. We've also tried inserting the data manually into the table using the export feature in pgadmin to produce a SQL script and all 10,056 rows are inserted in microseconds. Is this expected performance from DotMim.Sync?

[Mimetis]

PostgreSQL is really performant.
That's not normal at all.
PostgreSQL performance should be better or at least equals to SQL Server

Can you share something with me to be able to reproduce your scenario ?

[Mimetis]

I understand.
I may be able to help you figure it out, but without a clear example, it will be more complicated to troubleshoot.
Maybe reproducing this behavior using a PostgreSQL table that mimic the schema and data ?

[ajscilingo]

Okay I can provide you with the basic structure of the tables, it's part of a larger schema, 67 tables in total, but being that we're just trying to sync this one table and possibly the two look up tables, would it be sufficient to provide you with the structure of those three tables?

[Mimetis]

Give me a script that creates the tables and fill some data I can use to reproduce your behavior

[ajscilingo]

Okay, let me work on putting something together, thank you.

[ajscilingo]

Hi. I'm attaching a script that creates the data and table structure we're using, I had to rename it .txt from .sql . We've also tried syncing with the random data produced by this script and we're still seeing slow performance. It takes about 7 to 8 minutes to sync the 10,000+ rows from the main table.
[TEST] Create Schema And Generate Data.txt

[ajscilingo]

Hi, we just did an additional test. Our previous test was going through our VPN, which appears to have created quite a dramatic bottleneck, when testing DotMim.Sync with our test data set inside a LAN, this same data takes only 10 seconds to sync rather than 8 minutes!

[Mimetis]

Nice to read :)
Well, even 10 seconds for the data text your provided is too much, I think :)
Of course depends on a lot of things.
If I have some times, I will make a quick test.
Anyway, glad you found the bottleneck !!!