This Blog is about some findings on Microsoft\u2019s new Flexible Server offering for PostgreSQL which is in Customer Preview at the moment.<\/p>\n
The Microsoft Flexible Server for PostgreSQL is using Ubuntu as OS infrastructure, I think it is worth to be noticed that Microsoft’s new offering is switching from Windows to Linux.<\/p>\n
postgres=> select version();\nversion\n-----------------------------------------------------------------------------------------------------------------\nPostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit\n<\/pre>\nUbuntu upgrade ist planned as i know, Ubuntu 16.04 is end of support 04\/2021.<\/p>\n
Findings about performance<\/h3>\n
We have tested the performance which is more stable than on the old Single Server offering.<\/p>\n
Differences<\/h3>\n
The new setup is one virtual machine per instance, instead of one big machine with many instances on. On the old setup there is in any case a gateway in between, which causes security and in some points also performance issues. On the old setup, with many instances, this gateway moves the connection for any instance to Port 5432 which is PostgreSQL default, with show port; <\/strong>we have seen many instances running originally in the 20000 port region.<\/p>\n
The mapping from ports in the 20000 region to 5432 is done by this gateway and this is gone with the new offering.<\/p>\n
Collation<\/h3>\n
The default collation on Azure Flexible Server Offering is UTF-8 which is PostgreSQL default.<\/p>\n
postgres=> show server_encoding;\nserver_encoding\n-----------------\nUTF8\n\npostgres=> l\nList of databases\nName | Owner | Encoding | Collate | Ctype | Access privileges\n-------------------+----------------+----------+------------+------------+-----------------------------------\nazure_maintenance | azuresu | UTF8 | en_US.utf8 | en_US.utf8 |\npostgres | azure_pg_admin | UTF8 | en_US.utf8 | en_US.utf8 |\ntemplate0 | azuresu | UTF8 | en_US.utf8 | en_US.utf8 | =c\/azuresu +\n| | | | | azuresu=CTc\/azuresu\ntemplate1 | azure_pg_admin | UTF8 | en_US.utf8 | en_US.utf8 | =c\/azure_pg_admin +\n| | | | | azure_pg_admin=CTc\/azure_pg_admin\n<\/pre>\nSSL<\/strong><\/p>\n
is stable:<\/p>\n
postgres=> select * from pg_stat_ssl;\npid | ssl | version | cipher | bits | compression | client_dn | client_serial | issuer_dn\n-------+-----+---------+-----------------------------+------+-------------+---------------------------------------------+------------------------------------------------+-----------------------------------\n51 | f | | | | | | |\n55 | f | | | | | | |\n56 | f | | | | | | |\n57 | f | | | | | | |\n28669 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | \/CN=azuresu.b92f28e7b317.database.azure.com | 1293482480206780384144623282355891870454996725 | \/DC=GBL\/DC=AME\/CN=AME INFRA CA 01\n28013 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | | |\n49 | f | | | | | | |\n48 | f | | | | | | |\n50 | f |\n<\/pre>\nExtensions<\/h3>\n
Not all extensions provided by the contrib package are available on Azure, this may have some consequences if your application requires one of them:<\/p>\n
\n
- address_standardizer | 2.5.1 | | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.<\/li>\n
- address_standardizer_data_us | 2.5.1 | | Address Standardizer US dataset example<\/li>\n
- adminpack | 2.0 | | administrative functions for PostgreSQL<\/li>\n
- amcheck | 1.1 | | functions for verifying relation integrity<\/li>\n
- autoinc | 1.0 | | functions for autoincrementing fields<\/li>\n
- azure | 1.0 | 1.0 | azure extension for PostgreSQL service<\/li>\n
- bloom | 1.0 | | bloom access method – signature file based index<\/li>\n
- btree_gin | 1.3 | | support for indexing common datatypes in GIN<\/li>\n
- btree_gist | 1.5 | | support for indexing common datatypes in GiST<\/li>\n
- citext | 1.5 | | data type for case-insensitive character strings<\/li>\n
- cube | 1.4 | | data type for multidimensional cubes<\/li>\n
- dblink | 1.2 | | connect to other PostgreSQL databases from within a database<\/li>\n
- dict_int | 1.0 | | text search dictionary template for integers<\/li>\n
- dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing<\/li>\n
- earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth<\/li>\n
- file_fdw | 1.0 | | foreign-data wrapper for flat file access<\/li>\n
- fuzzystrmatch | 1.1 | | determine similarities and distance between strings<\/li>\n
- hstore | 1.5 | | data type for storing sets of (key, value) pairs<\/li>\n
- insert_username | 1.0 | | functions for tracking who changed a table<\/li>\n
- intagg | 1.1 | | integer aggregator and enumerator (obsolete)<\/li>\n
- intarray | 1.2 | | functions, operators, and index support for 1-D arrays of integers<\/li>\n
- isn | 1.2 | | data types for international product numbering standards<\/li>\n
- lo | 1.1 | | Large Object maintenance<\/li>\n
- ltree | 1.1 | | data type for hierarchical tree-like structures<\/li>\n
- moddatetime | 1.0 | | functions for tracking last modification time<\/li>\n
- pageinspect | 1.7 | | inspect the contents of database pages at a low level<\/li>\n
- pg_buffercache | 1.3 | | examine the shared buffer cache<\/li>\n
- pg_cron | 1.2 | | Job scheduler for PostgreSQL<\/li>\n
- pg_freespacemap | 1.2 | | examine the free space map (FSM)<\/li>\n
- pg_prewarm | 1.2 | | prewarm relation data<\/li>\n
- pg_stat_statements | 1.6 | | track execution statistics of all SQL statements executed<\/li>\n
- pg_trgm | 1.4 | | text similarity measurement and index searching based on trigrams<\/li>\n
- pg_visibility | 1.2 | | examine the visibility map (VM) and page-level visibility info<\/li>\n
- pgaudit | 1.3.1 | | provides auditing functionality<\/li>\n
- pgcrypto | 1.3 | | cryptographic functions<\/li>\n
- pglogical | 2.3.2 | | PostgreSQL Logical Replication<\/li>\n
- pglogical_origin | 1.0.0 | | Dummy extension for compatibility when upgrading from Postgres 9.4<\/li>\n
- pgrowlocks | 1.2 | | show row-level locking information<\/li>\n
- pgstattuple | 1.5 | | show tuple-level statistics<\/li>\n
- plpgsql | 1.0 | 1.0 | PL\/pgSQL procedural language<\/li>\n
- postgis | 2.5.1 | | PostGIS geometry, geography, and raster spatial types and functions<\/li>\n
- postgis_sfcgal | 2.5.1 | | PostGIS SFCGAL functions<\/li>\n
- postgis_tiger_geocoder | 2.5.1 | | PostGIS tiger geocoder and reverse geocoder<\/li>\n
- postgis_topology | 2.5.1 | | PostGIS topology spatial types and functions<\/li>\n
- postgres_fdw | 1.0 | | foreign-data wrapper for remote PostgreSQL servers<\/li>\n
- refint | 1.0 | | functions for implementing referential integrity (obsolete)<\/li>\n
- seg | 1.3 | | data type for representing line segments or floating-point intervals<\/li>\n
- sslinfo | 1.2 | | information about SSL certificates<\/li>\n
- tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab<\/li>\n
- tcn | 1.0 | | Triggered change notifications<\/li>\n
- timetravel | 1.0 | | functions for implementing time travel<\/li>\n
- tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit<\/li>\n
- tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit<\/li>\n
- unaccent | 1.1 | | text search dictionary that removes accents<\/li>\n
- uuid-ossp | 1.1 | | generate universally unique identifiers (UUIDs)<\/li>\n
- xml2 | 1.1 | | XPath querying and XSLT<\/li>\n<\/ul>\n
These extensions are missing compared to a default community setup with the contrib package:<\/p>\n