5

I work with postgresql (and Postgis) for years now on a VM with dockers, and I start to be used to tweak servers parameters and optimize request, and I never had the kind of problems I have with Azure Postgresql.

The problem is the following: writing is slow (usually around X2 compared to normal PG) but vacuum and index are EXTREMELY slow. At one point we need to create a PostGIS index on 500 millions row, on another server it takes around 30 minutes, on Azure instance it takes MORE THAN A DAY.

I tried to modify server parameters, checked the requests and finally went back to the basics to check the performance of the server, and I still don't understand if there is something I missed or if Azure Postgres has a big problem.

Here what I did to compare:

  • Servers:
    • PG TEST:
      • basic docker postgres 11 hosted on a VM (SSD, enough ram and cores but it's not really relevant)
      • command to launch: docker run --rm -e POSTGRES_PASSWORD=pass -d postgres:11.14-stretch
    • PG AZURE:
      • azure database for postgresql, single server, memory optimized 8 cores, 1028Gb of storage
      • default server parameters
  • Test from local PC (I don't really care about response time):
    • use pgbench and create a table with factor 50 (cmd: pgbench -i -s 50)
    • look at the time to create the table, vacuum and index

This is the most simple and reproductible I could find. The exact result can vary a little, but it's around the same idea:

Test results that show that Azure postgres is 70 times less performant to create an index

Read performance seems to be pretty good, so it's really a writing problem, and the more problematic one for us is index creation, and I don't see what can explain that.

This is a big problem for us, and I doubt that this is the kind of difference that can change with a few tweaks in the parameters, except if there is something specific to Azure ?

Did I miss something big ? Am I the only who have this kind of performance ? Or is it something limited to the system itself ? (I read that size of disk impact IOPS but looking at the graphs there doesn't seem to be a problem here, and we tried to add disk and it didn't change much) Maybe flexible servers doesn't have this issue ?

EDIT:

We tested to create new Azure postgres services with 3 different types of services (of the 4 possibles) that we just create and didn't modify, to be sure. I did the same test as before, 2 times for each service, and took the mean. I added the reference (called above PG TEST) and the one we have in production (called above PG AZURE). There was a few change with the previous results, and flexible server seems to be a bit better but the problem is still the same:

Full comparison

8
  • Hi, and welcome to dba.se! Did you modify any postgresql.conf parameters? Have you tried different versions of PostgreSQL?
    – Vérace
    Commented Jan 14, 2022 at 22:27
  • Hello, I've done tests by changing work_mem, shared_buffer, deactivate auto-vacuum, deactivates a maximum of logs, ... It didn't really change much. And for the postgres version, 11 is the max disponible with this kind of azure server, so no. But the version is the same in both server so I don't think that's the problem here. Commented Jan 16, 2022 at 18:26
  • Hmmm, (s)'étrange! Indeed, I see what you're saying about versions being the same - but I think you are bang on the money in saying that there should not be a difference of this order of magnitude! I mean, why waste your money paying Microsoft (or anyone else) for this sort of performance? Could you spin up an AWS server (and/or GCP) and do some quick tests? À+ (disponible = available en anglais! :-) ). Oh, BTW, images are discouraged here for the reasons outlined in this link: put in the queries you ran and the output of \d+ for any tables
    – Vérace
    Commented Jan 16, 2022 at 19:32
  • Yes, sorry for my english. I'm stuck on managed Azure postgres due to political reasons, and I'm trying my best to make it work. The server I use to compare performance is a docker on a basic Azure VM and it works well, so I don't need to test other options. The image is just the result, the actual command is pgbench (for what concern this test it's basic create indexand vacuum). I got the same kind of performance when I do manual commands, I just though that pgbench should be simpler as reprex Commented Jan 17, 2022 at 10:53
  • What is the disk configuration of your Azure instance? It's the only thing that I can think of?
    – Vérace
    Commented Jan 17, 2022 at 16:49

2 Answers 2

1

Performance of disk on PostgreSQL Azure is tied to the size of the disk allocated. You get max 3 IOPS per GB, which for your size is 128G x 3 = 384 IOPS. Your SSD probably has 5,000 IOPS and low latency.

To match that you would need to allocate ~ 1.7TB of disk. Give it a try.

4
  • I've got 1028Gb of disk, not 128, so that should be around 3000 IOPS, so I don't think that can explain a lot. Also, when inserting data there is not much difference, only when working on it (index, vacuum, ...) Commented Feb 13, 2022 at 13:56
  • Ah, your screenshot show 128G.
    – Bards
    Commented Feb 14, 2022 at 0:53
  • You're right, that's a mistake. In the description above I've put the correct description of the differents servers. Commented Feb 14, 2022 at 9:09
  • @robinloche how it reading performance on Azure Flexible vs VM?
    – Theo F
    Commented Aug 30, 2022 at 20:27
-2

PostgreSQL Single Server in Azure is being deprecated. You should be using PostgreSQL Flexible Server which has much improved performance and will be the long term PG service going forward in Azure.

1
  • 1
    As you can see on the edit that I did long time ago, neither the flexible server nor the citus cluster changed anything. Commented Nov 3, 2022 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.