1

I have very starnge behavior on Azure PostgreSQL server.

I try to read DB size using the next query:

SELECT CAST(pg_database_size(current_database()) * 1.0/(1024 * 1024) AS DECIMAL(12, 5))

But this query runs very long and I have timeout exception in my C# application:

Exception while reading from stream; Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

I have no idea why pg_database_size takes a lot of time...

To resolve my problem I see two options:

  1. Increase query performance using Azure PostgreSQL Server settings
  2. Replace the current query to another query with some result

I'm open for any suggestions. Please give me any ideas about how I can read DB size fast and correctly.

Alexander I.
  • 2,380
  • 3
  • 17
  • 42
  • How much data you have in the server? May be the size is not computed looking at the catalog tables. Try to find a query that relies on catalog tables – Sirisha Chamarthi Feb 23 '19 at 13:08

1 Answers1

0

Here is very much the same sql, runs fine directly on postgresql

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database; --- as option #2 ?

Slumdog
  • 470
  • 2
  • 4
  • Yes. It is the same query... But I have a troubel with **pg_database_size**. It works very slow. Can I read DB size without **pg_database_size**? – Alexander I. Oct 01 '18 at 11:51
  • Before we leave pg_database_size how many rows do you have in that? There must be a lot to make a difference. Also there should be an index on datname. Maybe there is an index problem but that would be a surprise. I'm not sure from your description if you have the problem when running SQL directly on Postgresql, is it only from your C# application or from PG directly as well? – Slumdog Oct 01 '18 at 19:26
  • As per above meaning run same sql above using PGadmin, and also " SELECT * FROM pg_database; " – Slumdog Oct 01 '18 at 23:04