Connecting Azure PostgreSQL to Oracle Autonomous with ORACLE_FDW
When migrating databases to PostgreSQL, not all data transitions happen immediately. Applications often need to keep accessing legacy databases for reading or writing, whether during a phased migration, for reporting, or because certain data still lives in another system. PostgreSQL implements parts of the SQL/MED standard via Foreign Data Wrappers (FDW), enabling queries against tables stored in external systems as if they were local. Instead of building ETL pipelines or copying data periodically, PostgreSQL can access remote data directly and let the query planner decide what to execute remotely. In this article, I'll connect Azure Database for PostgreSQL Flexible Server to an Oracle Autonomous Database using oracle_fdw, a Foreign Data Wrapper maintained by Laurenz Albe. I'll show how to: connect PostgreSQL to Oracle over TLS (Transport Layer Security) discover the outbound IP address used by Azure Database for PostgreSQL import Oracle tables as PostgreSQL foreign tables examine how filters and join operations are pushed down to Oracle perform INSERT, UPDATE, and DELETE operations on Oracle tables from PostgreSQL migrate from Oracle to PostgreSQL with a Create Table As Select over FDW run hybrid queries to compare remote and local tables after migration The goal is not to build a distributed database or a distributed transaction system. The goal is to access Oracle data from PostgreSQL with minimal setup while letting each database do the work it can do most efficiently. Enable the Foreign Data Wrapper To enable ORACLE_FDW, I selected it in the server parameters allowed extensions: Another option is to include it in the ServerParameter entry of an ARM template. In both cases, it is easy to check: postgres=> \dconfig azure.extensions List of configuration parameters Parameter | Value ------------------+------------------------------------------------- azure.extensions | ORACLE_FDW (1 row) Open the firewall and get the connection string I will connect to the Oracle Autonomous Database over the public internet. The security measures include user-password authentication, an encryption certificate, and a firewall with an IP whitelist. Since I am unsure which IP address Azure Database for PostgreSQL will use when connecting, I currently permit connections from any IP to my Oracle Autonomous Database: I didn’t select “Secure access from everywhere” because it requires mutual TLS (mTLS) with client certificates stored in a wallet on the client device. Since the client is an Azure PostgreSQL managed service and I can’t install a custom wallet, I chose one-way TLS (encryption without a client wallet), which is a practical option in this setup, as Oracle server certificates chain to public CAs trusted by Azure/PostgreSQL trust stores. This approach requires whitelisting IP addresses or a CIDR range. For testing, I temporarily allowed 0.0.0.0/0 to connect and capture the real source IP address for future adjustments. I did this only to identify the source IP used by Azure Database for PostgreSQL. In sensitive database environments, avoid allowing 0.0.0.0/0 even for a short time. Instead, use an ephemeral Oracle Autonomous test instance to identify the PostgreSQL server’s source IP address. You will need a username and password to connect, along with the connection string shown in the database connection details: I recommend using the TP or LOW services because MEDIUM and HIGH are intended for data warehouse workloads, which can cause unexpected locking behavior or resource usage. In my case, the connection string is: (description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-madrid-1.oraclecloud.com))(connect_data=(service_name=g230b6bb64a62e6_mad_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes))) That's everything I need on the Oracle side. I have all the information needed to connect from PostgreSQL. Declare the Foreign Data Wrapper server I connect to the Azure PostgreSQL server with psql and enable the ORACLE_FDW extension: postgres=> create extension oracle_fdw; CREATE EXTENSION I declare the connection string for the foreign data wrapper server: postgres=> create server oracle_autonomous foreign data wrapper oracle_fdw options (dbserver '(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-madrid-1.oraclecloud.com))(connect_data=(service_name=g230b6bb64a62e6_mad_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'); CREATE SERVER I declare the Oracle username and password to be used by my current user: postgres=> create user mapping for current_user server oracle_autonomous options (user 'ADMIN', password '<password on Oracle Autonomous>'); CREATE USER MAPPING To check if the connection is correct, I call oracle_diag(), which shows the client-side versions as well as the major version of the server: postgres=> select oracle_diag('oracle_autonomous') ; oracle_diag ---------------------------------------------------------------------------------------- oracle_fdw 2.8.0, PostgreSQL 18.4, Oracle client 23.26.0.0.0, Oracle server 23.0.0.0.0 (1 row) As the server version is returned, I know that I'm connected. If it cannot connect, you must verify the connection string, the IP allow list, and the credentials. Discover the public outbound IP address Since I don't want to keep the firewall open to 0.0.0.0/0 indefinitely, and rely solely on password-based protection, I first identify the IP address I'm connected from to narrow down the allowed IP list: postgres=> select oracle_execute('oracle_autonomous',$$ begin -- PL/SQL block to raise an exception that carries the IP address information raise_application_error( -20999, 'Hello ' || user||'@'||sys_context('userenv','ip_address') ); end; $$); ERROR: error executing statement: OCIStmtExecute failed to execute query DETAIL: ORA-20999: Hello ADMIN@4.203.152.223 ORA-06512: at line 3 The error message is expected because I used raise_application_error() to return a message to oracle_execute(), which does not accept statements that return a result. Now I can replace CIDR 0.0.0.0/0 with the IP address of the Azure PostgreSQL server I'm connecting from: Note that Azure does not guarantee that this public address remains static, so this rule may need updating after maintenance or failover. For production where such FDW access is long-term and highly available, use an architecture that provides controlled egress IPs (for example via NAT) that you can allowlist, or private connectivity where available. Since ORACLE_FDW allows me to connect and perform DML on the Oracle side, I have no other tasks on the Oracle side. PostgreSQL is my Oracle client. Import Oracle table metadata Back at my psql prompt, I can import the metadata for the Oracle schema "SH" into the PostgreSQL schema "fdw_sh": postgres=> create schema fdw_sh ; CREATE SCHEMA postgres=> import foreign schema "SH" from server oracle_autonomous into fdw_sh ; IMPORT FOREIGN SCHEMA I can describe what is imported: postgres=> set search_path TO fdw_sh, public ; postgres=> \d List of relations Schema | Name | Type | Owner --------+----------------------------+---------------+-------- fdw_sh | channels | foreign table | franck fdw_sh | costs | foreign table | franck fdw_sh | countries | foreign table | franck fdw_sh | customers | foreign table | franck fdw_sh | products | foreign table | franck fdw_sh | promotions | foreign table | franck fdw_sh | sales | foreign table | franck fdw_sh | supplementary_demographics | foreign table | franck fdw_sh | times | foreign table | franck (9 rows) Those are the tables imported from the "SH" schema of the Oracle database. ORACLE_FDW has automatically mapped the data types to PostgreSQL data types: postgres=> \d fdw_sh.countries Foreign table "fdw_sh.countries" Column | Type | Collation | Nullable | Default | FDW options ----------------------+-----------------------+-----------+----------+---------+-------------- country_id | numeric | | not null | | (key 'true') country_iso_code | character(2) | | not null | | country_name | character varying(40) | | not null | | country_subregion | character varying(30) | | not null | | country_subregion_id | numeric | | not null | | country_region | character varying(20) | | not null | | country_region_id | numeric | | not null | | country_total | character varying(11) | | not null | | country_total_id | numeric | | not null | | country_name_hist | character varying(40) | | | | Server: oracle_autonomous FDW options: (schema 'SH', "table" 'COUNTRIES') It is recommended to run ANALYZE to ensure the PostgreSQL query planner knows the cardinalities. Note that foreign tables are not automatically analyzed by auto-analyze. postgres=> select format('analyze verbose %I.%I;', table_schema, table_name) from information_schema.tables where table_schema = 'fdw_sh' \gexec PostgreSQL doesn't automatically collect statistics on foreign tables. Without running ANALYZE, the optimizer might misjudge row counts, resulting in suboptimal join plans and fewer pushdown opportunities. By default, ANALYZE reads 100% of the table but it can be lowered for large table by setting the sample percentage beforehand: postgres=> alter foreign table fdw_sh.sales options (set sample_percent '5') ; ALTER FOREIGN TABLE Query the foreign tables I can query foreign tables just like local ones. For example, to find the total customer credit exposure by country for certain regions, I run the following: postgres=> -- explain (analyze, verbose, buffers) select co.country_name, sum(cu.cust_credit_limit) as total_credit from fdw_sh.customers cu join fdw_sh.countries co on co.country_id = cu.country_id where co.country_region in ('Europe') group by co.country_name having sum(cu.cust_credit_limit) > 1e7 order by total_credit desc ; country_name | total_credit ----------------+-------------- Germany | 49579000 United Kingdom | 45205500 Italy | 44844500 France | 23987000 Spain | 12170000 (5 rows) The execution plan indicates the operations that have been delegated to the foreign database: Sort (cost=253237.64..253237.64 rows=3 width=41) (actual time=10348.498..10348.500 rows=5.00 loops=1) Output: co.country_name, (sum(cu.cust_credit_limit)) Sort Key: (sum(cu.cust_credit_limit)) DESC Sort Method: quicksort Memory: 25kB -> GroupAggregate (cost=253056.49..253237.61 rows=3 width=41) (actual time=10343.320..10348.489 rows=5.00 loops=1) Output: co.country_name, sum(cu.cust_credit_limit) Group Key: co.country_name Filter: (sum(cu.cust_credit_limit) > '10000000'::numeric) Rows Removed by Filter: 3 -> Sort (cost=253056.49..253116.81 rows=24130 width=14) (actual time=10342.473..10343.918 rows=30564.00 loops=1) Output: co.country_name, cu.cust_credit_limit Sort Key: co.country_name Sort Method: quicksort Memory: 1783kB -> Foreign Scan (cost=10000.00..251300.00 rows=24130 width=14) (actual time=51.461..10333.852 rows=30564.00 loops=1) Output: co.country_name, cu.cust_credit_limit Oracle query: SELECT /*47caada6fd16dcb0*/ r2."COUNTRY_NAME", r1."CUST_CREDIT_LIMIT" FROM ("SH"."CUSTOMERS" r1 INNER JOIN "SH"."COUNTRIES" r2 ON (r1."COUNTRY_ID" = r2."COUNTRY_ID") AND (r2."COUNTRY_REGION" = 'Europe')) Oracle plan: SELECT STATEMENT Oracle plan: HASH JOIN (condition "R1"."COUNTRY_ID"="R2"."COUNTRY_ID") Oracle plan: TABLE ACCESS FULL COUNTRIES (filter "R2"."COUNTRY_REGION"='Europe') Oracle plan: TABLE ACCESS FULL CUSTOMERS Query Identifier: -2654414372183047898 Planning Time: 152.202 ms Execution Time: 10348.595 ms The most important line in the execution plan is the generated Oracle query. It shows exactly which operations PostgreSQL delegated to Oracle and how many rows were returned across the network. In this example, the join and filter were pushed down: it executed a hash join with the COUNTRIES table as the build table and the CUSTOMERS table as the probe table, returning 30564 rows. The aggregation happened in PostgreSQL. Here is the visualization in the VS Code extension for PostgreSQL: Checking the execution plan is essential because remote calls introduce latency. We should minimize roundtrips and avoid reading excessive rows that will be discarded later. Execute DML (read and write) Unlike many federation technologies, oracle_fdw supports direct INSERT, UPDATE, and DELETE operations on Oracle tables from PostgreSQL. I use oracle_execute() to create a new table on the remote Oracle Database: postgres=> select oracle_execute( 'oracle_autonomous', $$ create table "REGIONS" ( ID number primary key, NAME varchar2(100) unique ) $$ ); oracle_execute ---------------- (1 row) postgres=> select oracle_close_connections() ; oracle_close_connections -------------------------- (1 row) After executing DDL through oracle_execute(), I close the cached Oracle connection because, in my tests, Oracle’s implicit DDL commit left oracle_fdw’s transaction state out of sync, causing subsequent queries on the same remote session to fail with ORA-08177 ("can't serialize access for this transaction"). I am able to declare the foreign table and insert rows through it: postgres=> create foreign table regions ( id numeric options (key 'true'), name text ) server oracle_autonomous options (schema 'ADMIN', table 'REGIONS') ; CREATE FOREIGN TABLE postgres=> insert into regions (name, id) select distinct country_region, country_region_id from fdw_sh.countries ; INSERT 0 6 To demonstrate that DML occurs on the Oracle Database, I attempt to insert a duplicate, which results in an Oracle error: postgres=> insert into regions values (1,'Europe') ; ERROR: error executing query: OCIStmtExecute failed to execute remote query DETAIL: ORA-00001: unique constraint (ADMIN.SYS_C0035974) violated on table ADMIN.REGIONS columns (NAME) ORA-03301: (ORA-00001 details) row with column values (NAME:'Europe') already exists Help: https://docs.oracle.com/error-help/db/ora-0000 Remote queries are supported for transactions: postgres=> begin; BEGIN postgres=*> delete from regions; DELETE 6 postgres=*> select * from regions; id | name ----+------ (0 rows) postgres=*> rollback; ROLLBACK postgres=> select * from regions; id | name -------+------------- 52800 | Africa 52801 | Americas 52802 | Asia 52803 | Europe 52804 | Middle East 52805 | Oceania (6 rows) The remote delete was executed but later reversed through a rollback in the local transaction. You can check when the remote transaction starts and ends by setting client_min_messages to debug. You can query both local and remote tables within a single local transaction (without two-phase commit or distributed transaction guarantees). However, this does not provide consistent guarantees for distributed transactions. Hybrid queries An SQL statement can involve local and remote tables. Here is an easy way to import data from Oracle to PostgreSQL: postgres=> create table local_customers as select * from fdw_sh.customers ; CREATE TABLE postgres=> alter table local_customers add primary key (cust_id) ; ALTER TABLE postgres=> vacuum analyze local_customers ; VACUUM A SQL statement can join local and remote tables. Here is an easy way to compare two tables: postgres=> select l.cust_id, r.cust_id -- full outer join to read all rows from both tables from local_customers l -- push down order by to favor merge join full outer join ( select * from fdw_sh.customers order by cust_id ) r using (cust_id) -- eliminate the same rows where -- one cust_id row doesn't exist in the other l.cust_id is null or r.cust_id is null -- or it exists in both but with difference values or l is distinct from r ; To compare them, all rows must be read, but this execution plan is efficient, using a sort-merge join that compares rows without buffering them into a temporary table. This comparison is long and may produce false positives if concurrent DML operations occur while logical replication runs during the migration. Nevertheless, since both databases utilize multi-version concurrency control snapshots and their transactions started nearly simultaneously when using autocommit or serializable transactions, the likelihood of false positives is low. To deal with transient differences, you can quiesce writes, compare only a known past time window, or recheck reported rows after replication catches up. Limitations The foreign data wrapper is not a distributed query engine. It pushes only certain operations when it improves performance. For example, in the previous case, Oracle performed the join and filter, but PostgreSQL executed the GROUP BY. Operation Pushdown WHERE ✅ Yes (only expressions that can be safely translated) JOIN ✔️ Yes, between two foreign tables on the same foreign server when the join conditions and filters can be translated ORDER BY ✔️ Yes, except for string-based sort and when a join is pushed down GROUP BY ❌ (no aggregation pushdown in oracle_fdw 2.8.0) INSERT/UPDATE/DELETE ✅ Joins over 3 foreign tables ❌ No PostgreSQL functions ❌ No, except now(), transaction_timestamp(), current_timestamp, current_date, localtimestamp which are translated and pushed down Queries continue to be transmitted over the network. If pushdown isn't feasible, large result sets can slow down performance. Foreign tables are not automatically analyzed. Cross-database transactions are not managed as distributed transactions. oracle_fdw is ideal for access, reporting, and migration, but it does not substitute for physically transferring heavily used data into PostgreSQL. Conclusion ORACLE_FDW is simple to enable on Azure Database for PostgreSQL Flexible Server and provides a simple way to access Oracle data from PostgreSQL without introducing a separate replication or ETL layer. Oracle tables appear as PostgreSQL foreign tables, can join with local tables, and support read-write operations. The key feature is visibility. PostgreSQL's execution plan shows not only local operations but also the SQL sent to Oracle, along with the Oracle execution plan. This helps users easily see which parts are executed remotely and which stay on PostgreSQL. As with all Foreign Data Wrappers, performance depends on how much work you delegate to the remote database. Pushdown candidates typically include filters, joins, and sorting. PostgreSQL executes aggregation locally rather than delegating it to Oracle. Network latency and data transfer costs also play a significant role. For migrations, reporting, data validation, or gradual application modernization, oracle_fdw provides an effective solution to connect PostgreSQL and Oracle while using standard SQL on both platforms. It does not try to treat multiple databases as a single distributed system. If you used the Oracle Foreign Data Wrapper, please share your questions, comments, and feedback in the PostgreSQL Hub Developer Forum.
More Performance, Same Price: Azure Postgres V3 & V5 Compute Compared
Azure customers can select newer compute options and scale resources in real time with minimal disruption to business operations. This flexibility allows you to scale capacity as demand changes, match compute and memory profiles to each workload, and test new hardware configurations before moving production workloads. Using this opportunity to improve workload performance and cost efficiency over time results in real improvements to both price and performance. New Compute Can Change Workload Economics A newer compute generation is not merely a different SKU name. Changes in processor architecture, clock speed, memory bandwidth, storage throughput, and virtualization can materially affect application performance. Azure Database for PostgreSQL offers multiple options across General Purpose and Memory Optimized tiers. Customers can change the compute size and move between hardware generations without rebuilding the database platform. Reviewing these options regularly ensures that workloads are optimized and able to maximize performance and cost investments. A workload that was appropriately sized when deployed may no longer be running on the most cost-effective infrastructure. Periodic evaluation of newer compute generations can reveal opportunities to improve throughput, latency, or capacity without increasing spend. The opportunity to upgrade Azure Postgres workloads while maintaining the same operating costs presents a valuable option for anyone currently consuming V3 family compute. Take advantage of these capabilities by scaling your Azure Postgres workloads today: Scale Compute in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft Learn Benchmarking V3 and V5 PostgreSQL Compute To measure the potential impact, we compared two Azure Database for PostgreSQL servers. Each server was provisioned with 4 General Purpose vCores, 16 GiB memory, and SSD Storage with 7500 IOPS. We ran the same CPU-intensive workload under identical test conditions with increasingly concurrent client workloads. Across repeated test runs, the V5 server processed approximately 40% more transactions than the comparable V3 configuration at effectively the same price. Benchmark resources and provisioning steps are included in the appendix. (Higher is better) The result represents approximately 40% more transaction throughput for the same spend in this specific benchmark. (Lower is better) The V5 configuration completed the workload in less time, indicating lower overall execution latency in this benchmark. For CPU-intensive workloads, this improvement translates to higher transaction volumes, reduced processing backlogs and latency, and provides additional capacity for future growth at approximately the same cost. Database performance also depends on memory, storage, I/O latency, concurrency, query design, indexing, PostgreSQL configuration, and application behavior. This result should therefore be treated as a workload-specific reference benchmark rather than a universal performance claim. The most meaningful comparison is one performed with a representative version of your own workload. Infrastructure should be reviewed continuously Cloud optimization is not a one-time sizing exercise. A server selected several years ago may continue to operate reliably while missing newer price-performance improvements. Regular infrastructure reviews help teams identify opportunities before older choices become unnecessary cost or capacity constraints. Teams should periodically review: Available compute family options in their Azure regions CPU, memory, storage, and I/O utilization Current and projected workload demands Transactions or queries completed per unit of cost Performance under representative load Migration requirements and expected downtime For additional guidance on optimizing Azure Database for PostgreSQL workloads, see Plan Azure Database for PostgreSQL flexible server deployments for operational performance on Microsoft Learn. Azure’s continued investment in regions, datacenters, and compute infrastructure gives customers new ways to improve their workloads. Realizing that value requires regularly reviewing what has become available, measuring it against real application behavior, and adopting it where the business case makes sense. The combination of continued platform investment from Microsoft and your active optimization becomes an ongoing partnership focused on helping your businesses perform, scale, and succeed. Appendix This appendix provides the resources and provisioning steps used for the benchmark. Benchmark Resources The benchmark was deployed using the following bicep file definition, named “postgres-flex-compute-benchmarks.bicep”: param administratorLogin string = 'benchAdmin' @secure() param administratorLoginPassword string = '' param serverEdition string = 'GeneralPurpose' type serverConfiguration = { serverName: string skuName: string } param storageSizeGB int = 32 param storageTier string = 'P40' //7500 IOPS param location string = 'canadacentral' param haMode string = 'Disabled' param availabilityZone string = '2' param serverConfigs serverConfiguration[] = [ { serverName: 'bench-standard-d4s-v3' skuName: 'Standard_D4s_v3' // 4 vCores, 16 GiB memory, 6400 Max IOPS } { serverName: 'bench-standard-d4s-v5' skuName: 'Standard_D4s_v5' // 4 vCores, 16 GiB memory, 6400 Max IOPS } ] resource servers 'Microsoft.DBforPostgreSQL/flexibleServers@2025-08-01' = [for serverConfig in serverConfigs: { location: location name: serverConfig.serverName properties: { createMode: 'Default' version: '18' administratorLogin: administratorLogin administratorLoginPassword: administratorLoginPassword availabilityZone: availabilityZone storage: { storageSizeGB: storageSizeGB autoGrow: 'Disabled' type: 'Premium' tier: storageTier } network: { publicNetworkAccess: 'Enabled' } backup: { backupRetentionDays: 7 geoRedundantBackup: 'Disabled' } highAvailability: { mode: haMode } } sku: { name: serverConfig.skuName tier: serverEdition } }] // Create the firewall rule on every server. resource serverFirewallRules 'Microsoft.DBforPostgreSQL/flexibleServers/firewallRules@2025-08-01' = [ for (serverConfig, i) in serverConfigs: { name: 'AllowAll' parent: servers[i] properties: { startIpAddress: '0.0.0.0' endIpAddress: '255.255.255.255' } } ] The following plpgsql function was created to prioritize CPU operations: CREATE OR REPLACE FUNCTION leibniz_pi(iterations integer) RETURNS double precision LANGUAGE plpgsql AS $$ DECLARE i integer; result double precision := 0; sign double precision := 1; BEGIN FOR i IN 0..iterations - 1 LOOP result := result + sign / (2 * i + 1); sign := -sign; END LOOP; RETURN 4 * result; END; $$; Provisioning Steps Provision two Azure Database for PostgreSQL flexible servers using comparable V3 and V5 compute configurations using the following CLI command: $password = Read-Host "Password" -MaskInput az deployment group create ` --resource-group <your_resource_group_name> ` --template-file ./postgres-flex-compute-benchmarks.bicep ` --parameters administratorLoginPassword="$password" Once the servers have been provisioned, create the “leibniz_pi” function on each server. Use containerized environments to execute a pgbench while passing in the custom plpgsql function: 'SELECT leibniz_pi(10000000);' | docker run --rm -i ` -e PGPASSWORD="<YOUR_PG_PASSWORD>" ` postgres:18 ` pgbench -n -c 8 -j 8 -T 300 -f - ` "host=<V3_OR_V5_SERVER_NAME>.postgres.database.azure.com port=5432 dbname=postgres user=benchAdmin sslmode=require" Repeat the test runs and record transaction throughput, execution time, and relevant resource metrics. Compare the results while accounting for workload variability and any differences in the underlying compute architecture.July 2026 Recap: Azure Database for PostgreSQL
Features PgBouncer: Update to 𝘃𝗲𝗿𝘀𝗶𝗼𝗻 𝟭.𝟮𝟱.𝟮 Azure Database for PostgreSQL Flexible Server now supports PgBouncer 1.25.2, keeping the built-in connection pooler aligned with the latest community release. PgBouncer helps applications efficiently manage large numbers of idle and short-lived connections with low overhead. This update includes the latest community security and stability fixes, including fixes for multiple CVEs affecting network packet parsing, SCRAM authentication, error handling, and admin command authorization - strengthening the reliability and security of the managed connection pooling experience. Documentation: PgBouncer in Azure Database for PostgreSQL Maintenance Events: Programmatic control through Rest APIs and Azure CLI Azure Database for PostgreSQL Flexible Server now supports new Maintenance Events REST APIs and Azure CLI commands, giving you more ways to programmatically view and manage planned maintenance. You can view upcoming maintenance, review maintenance history, reschedule eligible maintenance for up to 14 days, and apply maintenance on demand through REST APIs and Azure CLI. These capabilities make it easier to into automation integrate maintenance management, scripts, internal tooling, and operational workflows. The REST APIs are available starting with the 2026-04-01-preview API version, while the same maintenance capabilities are available in Azure CLI version 2.88.0 and later. Documentation: Maintenance Events REST API Documentation: Azure CLI Maintenance Event Commands India South Central now generally available We’re excited to announce that Azure Database for PostgreSQL Flexible Server is now generally available in the India South Central region. You can now build and run production-ready cloud applications closer to your users, with the flexibility and control of a fully managed PostgreSQL service. Expanding PostgreSQL Extensibility with pgPointCloud, RDKit, and plpgsql_check Azure Database for PostgreSQL Flexible Server now supports three additional PostgreSQL extensions, expanding the range of specialized workloads you can run on a fully managed PostgreSQL service. pgPointCloud: Enables you to store, compress, and query large-scale LiDAR and 3D point-cloud data directly in PostgreSQL, supporting spatial workloads across areas such as geospatial analytics, autonomous systems, agriculture, research, and other data-intensive scenarios. RDKit: Brings cheminformatics capabilities to PostgreSQL, helping pharmaceutical, chemical, and research teams work with molecular data, fingerprints, similarity searches, and indexing support. plpgsql_check: Helps developers validate PL/pgSQL code and improve code quality, especially for teams modernizing database applications or migrating procedural SQL workloads to Azure Database for PostgreSQL Flexible Server. Together, these extensions make it easier to bring advanced data types, domain-specific analytics, and developer tooling closer to your PostgreSQL applications on Azure. See the full list of all available extensions in Azure Database for PostgreSQL in our learn documentation. Azure PostgreSQL Learning Bytes Take Control of PostgreSQL Maintenance Azure Database for PostgreSQL Flexible Server now gives you more control over planned maintenance events. With self-service maintenance controls in the Azure portal, you can view upcoming maintenance, reschedule eligible maintenance to a more convenient time, apply updates when you're ready, and review maintenance history after completion. These capabilities help reduce operational risk and make it easier to align maintenance with your business schedule. Whether you're managing production workloads, preparing for a major release, or avoiding peak business periods, these controls provide greater flexibility and visibility so you can plan maintenance with confidence. Learn more: Read the full blog post, Take Control of Your PostgreSQL Maintenance, for details on the maintenance experience and how to use it.Oracle to PostgreSQL in VS Code: Assessment, Conversion, and Validation
This article explores the possibilities opened by the schema conversion workflow. It is an early evaluation: I used HorizonDB as a target, which is in preview and not a supported target yet, and Oracle Database 26ai as a source which has not been validated yet. Early tests and feedbacks are welcome. If you encounter any issue, please open an issue (https://github.com/microsoft/vscode-pgsql/issues) or a discussion (https://github.com/Azure-Samples/postgres-hub/discussions) The goal is to evaluate how AI assistance changes the first phases of a migration: assessing the effort, creating a first working prototype, and making the remaining technical gaps visible. This is where an integrated development environment is particularly effective. Source artifacts, generated PostgreSQL code, reports, database connections, tests, findings, and repairs can remain in one reproducible VS Code workspace. Migrating a database is not finished when the tables compile. A useful test must also answer three harder questions: Was the reference data copied correctly? Were the application-facing stored routines actually deployed? Do those routines produce the same business effects under a realistic load? This is particularly important when business logic is embedded in proprietary database constructs such as PL/SQL packages, which are in scope for this migration. Swingbench Order Entry is a good example. It has two versions: one uses pure JDBC, with the business logic in Java, and the other places the logic in a PL/SQL package that uses advanced Oracle Database features. The server-side version illustrates the migration challenges found in many critical legacy applications. In this article, I walk through those questions using Swingbench's Sales Order Entry (SOE) schema. I ran Oracle Database and Swingbench in Docker containers and used the PostgreSQL extension for Visual Studio Code to convert the schema against an Azure HorizonDB (preview) scratch database. I then used GitHub Copilot to build the data-copy, workload-emulation, and validation programs needed to test PostgreSQL. This article follows the migration end to end. Readers can use individual sections independently, but I keep the complete sequence because each stage exposed issues that schema conversion alone could not reveal. Throughout this project, I made the architectural decisions, directed the investigation, and validated results. The migration extension performed the model-assisted schema and PL/SQL conversion, while GitHub Copilot generated supporting code and tests under my direction. PostgreSQL compilation and the executable tests provided the evidence. Instead of replacing deterministic extraction, dependency analysis, and type mappings, AI participated in the iterations that traditionally follow them: contextual translation, review, diagnosis, repair, and validation. GitHub Copilot also produced the first draft of this article, which I reviewed and refined so it accurately describes what I ran, observed, and concluded. What was migrated Swingbench SOE is a compact but realistic migration target. It includes: customers, addresses, cards, products, inventory, orders, and order items primary keys, foreign keys, checks, indexes, and sequences views and analytical queries the Oracle SOE.ORDERENTRY PL/SQL package used by Swingbench, with all the application logic, leaving only the presentation layer to the frontend package state, collection types, helper routines, random choices, sleeps, transaction control, and business transactions The test environment was: Component Version or image Oracle Database 26ai gvenzl/oracle-free:slim (23.26.2) Swingbench domgiles/swingbench:latest (2.6.1118) PostgreSQL Azure HorizonDB (PostgreSQL 17.9) PostgreSQL extension for Visual Studio Code 1.27.3 Foundry model gpt-5.2 1. Build the Oracle source environment I used the following docker-compose.yaml to start Oracle and keep a Swingbench container available for schema creation and testing: services: oracle: image: gvenzl/oracle-free:slim environment: ORACLE_PASSWORD: "<oracle-system-password>" ports: - "1521:1521" healthcheck: test: ["CMD", "healthcheck.sh"] interval: 10s timeout: 5s retries: 30 swingbench: image: domgiles/swingbench:latest platform: linux/amd64 depends_on: oracle: condition: service_healthy entrypoint: ["sleep", "infinity"] I started both containers with: docker compose up -d Once Oracle was healthy, I created a small SOE database with Swingbench's command-line wizard. I chose scale 0.1 : large enough to exercise the relationships while remaining convenient for repeated migrations. docker compose exec swingbench oewizard -cl -create \ -cs //oracle:1521/FREEPDB1 \ -dba "sys as sysdba" -dbap '<oracle-system-password>' \ -df /opt/oracle/oradata/FREE/FREEPDB1/soe01.dbf \ -u soe -p '"<soe-password>"' \ -scale 0.1 -tc 1 -v Swingbench substitutes the SOE password into CREATE USER ... IDENTIFIED BY . A password containing characters that require Oracle's quoted-password syntax must be passed with the literal double quotes expected by that SQL statement. Before migrating anything, I compiled the source package and checked for Oracle errors: docker compose exec -T oracle \ sqlplus -s / as sysdba <<'SQL' alter session set container=FREEPDB1; alter package SOE.ORDERENTRY compile; alter package SOE.ORDERENTRY compile body; show errors package body SOE.ORDERENTRY SQL I then tested the environment to be migrated by running the original Swingbench application for one minute: docker compose exec -it swingbench charbench \ -cs //oracle:1521/FREEPDB1 \ -dbau system -dbap '<oracle-system-password>' \ -u soe -p '<soe-password>' \ -rt 00:01:00 -c ../configs/SOE_Server_Side_V2.xml 2. Prepare VS Code, HorizonDB, and Foundry I installed VS Code and the PostgreSQL extension for Visual Studio Code, authored by Microsoft. The migration workflow is exposed under Migrations (Preview) in the PostgreSQL view: I opened Migrations (Preview) and created a project. The first step was to name the migration project. I generated all screenshots while reviewing this article and replaying the walkthrough, so the project identifier for this run was walkthrough-validation : The second step was to connect to the source Oracle Database as a user with the DBA role and select the schema: The third step was to define the destination PostgreSQL database: The migration tool executes generated DDL there, so this must not be an application or production database. At the time of this test, the migration workflow expected an Azure PostgreSQL connection for compilation, either Flexible Server or HorizonDB. I used an Azure HorizonDB (preview) database as a dedicated scratch target. This was an exploratory use of the preview workflow, not a statement of official HorizonDB support. The generated code targets PostgreSQL rather than HorizonDB-specific APIs; portability to another PostgreSQL deployment still depends on its PostgreSQL version, available features, and managed-service restrictions. I created the database service beforehand from VS Code: The conversion also needed a Microsoft Foundry resource and a deployed model. I created the resource, opened Foundry, deployed gpt-5.2 , and recorded the resource endpoint and deployment name. I created the resource from the Azure portal: I noted its endpoint and key: From VS Code, I opened the Foundry portal: I selected GPT-5.2: I chose to use the selected model: I deployed the model (I used the model name as the deployment name here): The fourth configuration step was to select the model deployment using its name, endpoint, and API key: The migration project was created: All files, configuration, logs, and reports were stored under .github/postgres-migrations. 3. Run the migration project The migration workflow extracted Oracle metadata, created a dependency graph, divided the objects into dependency-aware chunks, converted each chunk, reviewed the generated SQL, compiled it against PostgreSQL, and assembled deployment artifacts. The process resembles a compiler pipeline more than a text translator: It started by extracting the metadata into artifacts/oracle/SOE/extract/ddl: A report provides all details when completed: It then started the conversion, one chunk at a time: The conversion log shows the cycle of conversion, compilation, review, test, and validation using the LLM. Here is an example showing the level of detail the log provides: 16:50:16 [INFO] ossdbtoolsservice.conversion_v2.pipeline.chunk_converter.package_converter: Package ORDERENTRY: member processOrders -> converted in 80.0s (tokens=5232, notes=Oracle ROWNUM < 10 translated to LIMIT 9 (no ORDER BY in source, so row choice remains arbitrary).; Oracle (+) outer joi) This is a good example because it goes beyond syntax, where ROWNUM < 10 translates to LIMIT 9 . It warns that a LIMIT without ORDER BY has a nondeterministic result that can differ between Oracle and PostgreSQL. Applications should not rely on physical row order, but a migration must still consider behavior validated by years of production use, even when that behavior originated as a side effect of an application bug. The PostgreSQL DDL goes into artifacts/oracle/SOE/convert/sessions. A report provides all details: Simple objects can follow deterministic paths, while complex PL/SQL receives model-assisted conversion and review. PostgreSQL compilation, rather than the model, is intended to be the final syntax and dependency check. Once the migration is complete, it lists the review tasks. The counts and classifications shown here are observations from version 1.27.3 of an evolving preview workflow. Future versions may present fewer review tasks as their focus evolves in response to feedback, including a stronger emphasis on syntax and conversion completeness: The summary also lists extensions that should be installed in the target database: The orafce extension implements many functions familiar to Oracle users, including functions from Oracle DBMS packages. It can reduce the amount of code that must change during a migration. Extensions such as orafce and plpgsql_check can improve conversion and validation when they are available. For this experiment, however, I deliberately used a vanilla PostgreSQL target without them. I wanted to see what the workflow could produce without tying the converted application to extensions that may not be available from every managed PostgreSQL service. This makes portability a design goal of the prototype, not a claim that every PostgreSQL deployment is identical or that omitting extensions always produces the most accurate conversion. Before proceeding to the manual review, I inspected the fresh conversion log to separate what the pipeline had repaired automatically from what it had only flagged for attention. 4. What the migration workflow corrected, and what it could not prove I used GitHub Copilot to parse the conversion log and distinguish initial conversion, model review, compiler-driven repair, and warnings left for application review. The analysis found 37 automatic correction events: 33 during the second-pass review and four after PostgreSQL rejected generated SQL during compilation. The run finished with zero failed objects and zero fallback objects. The second-pass reviewer corrected 20 of the 35 generated package artifacts. The interesting changes included: guarding current_setting() results against missing or empty values before casting them changing the DML counter helpers from transaction-local to session-level GUC writes so their state can survive a commit on the same connection replacing invalid Oracle %TYPE references in PostgreSQL signatures and expressions replacing invalid PERFORM * FROM and Oracle cursor syntax schema-qualifying table references and limiting a SELECT INTO that could otherwise return multiple rows repairing an incomplete generated type-creation block These were code changes, not only warnings. This is where model-assisted conversion goes beyond a fixed syntax rulebook: it reviews generated code in context and revises interactions between types, state, queries, and control flow. PostgreSQL compilation then found four remaining defects. The repair loop removed unsupported DEFERRABLE clauses from two check constraints and repaired two routines before recompiling them. This is an important distinction: model review can anticipate defects, while the target database provides the exact SQLSTATE and failing statement. The pipeline then fixes and retries, much like a developer working until the code compiles. Chunking did not leave dependencies to the user. When a referenced table, index, or routine was produced by another chunk, the pipeline deferred the dependent operation and retried it after the required object existed. All such operations completed automatically in this run. Structural translations The conversion itself also made deliberate structural changes that were not compiler repairs: Oracle behavior PostgreSQL translation Package members Schema routines named orderentry$<member> (PostgreSQL has no packages) Package records and collections Composite types, array domains, and set-returning functions Package variables Custom settings accessed through set_config() and current_setting() DBMS_RANDOM.VALUE random() with range arithmetic (without the DBMS_RANDOM emulation provided by orafce ) DBMS_LOCK.SLEEP pg_sleep() DBMS_APPLICATION_INFO application_name or custom settings BULK COLLECT RETURNS TABLE , SETOF , or RETURN QUERY ROWNUM < n LIMIT n - 1 or row_number() CONNECT BY row generation Recursive CTE FORALL inventory updates Row-by-row PL/pgSQL loop (PostgreSQL runs PL and SQL in the same engine) SYSDATE and SYSTIMESTAMP CURRENT_TIMESTAMP , clock_timestamp() , and date_trunc() Package procedures Functions returning void Package overloads PostgreSQL overloads with the same flattened name What still requires attention Successful compilation proves syntax and dependency consistency, not behavioral equivalence. The fresh review report classifies 34 of 78 objects as auto-approved and leaves 44 non-blocking behavioral divergences for application review. The most important are: Oracle ENABLE NOVALIDATE primary keys do not validate existing rows, while PostgreSQL primary-key creation does. Eight tables therefore require clean source data before deployment. Five Oracle reverse-key indexes became normal PostgreSQL btree indexes, which can change insertion hot spots and access behavior. Package state is stored as text in custom GUCs. Although the reviewer changed the counter helpers to session-level writes, the emitted routines still use different key prefixes and a mixture of session-level and transaction-local settings. Initialization, pooling, commits, and casts need runtime tests. PostgreSQL functions cannot reproduce Oracle package-side COMMIT . The caller must own transaction boundaries. ROWNUM conversions without ORDER BY preserve nondeterministic selection, but they do not guarantee that Oracle and PostgreSQL choose the same rows. BULK COLLECT collections became row sets, and FORALL became a loop. Empty collection behavior, ordering, locking, and performance can differ. Random-number boundaries, numeric casts, exception behavior, and SYSDATE / SYSTIMESTAMP timing and timezone semantics remain database-specific. None of these 44 findings blocked deployment in this run. They define the work for the runtime validation in the following sections rather than automatic fixes that the migration report can prove. 5. Examples from the generated ORDERENTRY package The package conversion shows why this is more than syntax replacement. The pipeline emitted all 34 executable package-body members, including private helpers, and represented Oracle procedures as PostgreSQL functions returning void . Because PostgreSQL has no equivalent of package visibility, every member became a schema routine named orderentry$<member> . Applications should keep calling only the former public API, with privileges used to preserve the old visibility boundary. The harder transformations preserved the shape of the business logic without pretending that Oracle and PostgreSQL use the same programming model. For example, BULK COLLECT collections became set-returning functions. Indexed collection access became operations over those row sets. The FORALL inventory update in orderentry$neworder became a PL/pgSQL loop, and the CONNECT BY row generator used for warehouse activity became a recursive CTE. Package helpers using DBMS_RANDOM , DBMS_LOCK.SLEEP , and DBMS_APPLICATION_INFO became calls to random() , pg_sleep() , and PostgreSQL settings. A small conversion with a large consequence I verified that from_mills_to_secs was already a private Oracle function rather than a helper invented during migration: function from_mills_to_secs(value integer) return float is real_value float := 0; begin real_value := value/1000; return real_value; exception when zero_divide then real_value := 0; return real_value; end from_mills_to_secs; Despite the shortened name, “mills” means milliseconds here. The ZERO_DIVIDE branch is defensive but cannot normally be reached because the divisor is the constant 1000. I compared it with the generated PostgreSQL function, which keeps the operation but explicitly converts to floating point to avoid integer division: CREATE OR REPLACE FUNCTION soe.orderentry$from_mills_to_secs(value integer) RETURNS double precision LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE real_value double precision := 0; BEGIN real_value := value::double precision / 1000.0; RETURN real_value; EXCEPTION WHEN division_by_zero THEN real_value := 0; RETURN real_value; END; $$; orderentry$sleep calls this helper for the fixed or randomly selected delay, maps DBMS_LOCK.SLEEP to pg_sleep , and updates a package counter. Here the review found a subtle semantic issue: the generated helper calculates elapsed milliseconds but adds the selected delay in seconds to the counter. The supplied workload uses zero delays, so it did not affect this run. With nonzero delays, the code could compile and execute while returning the wrong value. This is the kind of small conversion detail that is easy to miss and appears much later as an application error. Transaction control changes ownership The largest architectural difference is transaction control. In Oracle Database, oecommit can issue COMMIT from inside the package when the session-level PLSQLCOMMIT flag is true. The package therefore decides when its changes become durable and increments its own commit counter at that point. The converter represented the package procedures as PostgreSQL functions to preserve their callable API, but a PostgreSQL function cannot commit or roll back its surrounding transaction. The generated oecommit can preserve the instrumentation, not the transaction boundary. PostgreSQL procedures invoked with CALL can perform transaction control in specific contexts, but changing these package entry points into procedures would also change how the application calls them and how return values are handled. For this migration, I chose to disable package-side commits. The Python driver calls orderentry$setplsqlcommit('false') , commits each successful mutating call, and rolls back a failed call. Transaction ownership therefore moved from the stored package to the application. That is an intentional adaptation, not an equivalent syntax translation. Multi-call atomicity, retries, error handling, and connection-pool behavior must be reviewed with that new boundary in mind. 6. Copy the reference data separately Without leaving VS Code, I first checked the migrated schema, including its tables, views, and converted ORDERENTRY routines: The migration extension converted the schema and code but did not copy the Swingbench data. For this small, specific task, it was faster to guide GitHub Copilot to generate a Python program than to switch to another migration tool. The script stayed in the repository with the connection setup and commands, making the copy reproducible and documented rather than a one-time manual operation. I installed its dependencies (Python 3.10 or later required) and exposed connection information through environment variables rather than embedding credentials: python3 -m pip install -r scripts-postgres/requirements.txt export ORACLE_DSN='localhost:1521/FREEPDB1' export ORACLE_USER='soe' export ORACLE_PASSWORD='<soe-password>' export PG_DSN='host=<host> port=5432 dbname=<db> user=<user> sslmode=require' export PG_SCHEMA='_mig_scratch_soe' The PG_SCHEMA variable names the PostgreSQL schema created by the migration extension during validation. The scripts set search_path to this schema automatically before executing any SQL. I ran a clean copy with: bash scripts-postgres/copy_soe_data.sh --truncate The program uses dependency order and PostgreSQL binary COPY . It also handles Oracle intervals and numeric values, preserves the intent of ENABLE NOVALIDATE checks, and advances the PostgreSQL sequences after loading. I then verified that the Oracle and PostgreSQL row counts matched: The complete implementation is available in copy_soe_data.py. 7. Recover and reproduce the Swingbench workload Swingbench is a free load generator, but it is not open source. It is distributed as compiled Java, so porting its client to PostgreSQL was neither practical nor the goal. For this server-side workload, the business logic is in the SOE.ORDERENTRY package. The Java client primarily chooses transactions, generates parameters, and calls the package. Calling the migrated routines with arbitrary values would test invocation, but not the real workload. I guided GitHub Copilot to inspect the files in the Swingbench container. The XML configuration provided the enabled transactions, weights, user count, delays, and timeout. I used javap to confirm the externally observable call signatures and runtime behavior needed to build a compatible validation workload. GitHub Copilot documented those findings under my direction in scripts-postgres/swingbench_calls.md. I then guided GitHub Copilot to create run_swingbench_workload.py. It follows the same weighted transaction selection with four persistent user connections, a start barrier, copied customer data, and the configured timeout. It reports throughput, latency, and errors without trying to reproduce the Swingbench GUI or Oracle-specific connection pool. As described in Section 5, transaction ownership had to change. The driver disables package-side commits, commits successful mutating calls through Psycopg, and rolls back failed calls. I ran it with: export PG_DSN='host=<host> port=5432 dbname=<db> user=<user> sslmode=require' export PG_SCHEMA='_mig_scratch_soe' bash scripts-postgres/run_swingbench_workload.sh --duration 300 Before creating load, the program verifies that the public workload API is complete. It exits early rather than report misleading performance results for an incomplete deployment. 8. Validate in layers Validation is part of the migration, not a final smoke test. Compilation proves that PostgreSQL accepts the generated objects, but not that they return the right results or preserve business effects. I directed GitHub Copilot to build four validation layers so each failure could be isolated before adding more workload complexity. Layer 1: catalog and query validation The rollback-safe test harness begins with a preflight that checks the PostgreSQL version, required tables, five sequences, and all public workload routines. It also submits the two analytical queries to PostgreSQL with EXPLAIN before proceeding to routine calls. I separately ran those two analytical queries with EXPLAIN ANALYZE against 106,583 customers, 160,684 orders, and 482,752 order items. Both executed successfully. The top-customer query completed in 424 ms. Its sequential scans were reasonable because it consumed almost all orders and items, although the aggregate spilled to temporary disk: Sort (actual time=423.274..423.277 rows=20 loops=1) Sort Key: (rank() OVER (?)) Sort Method: quicksort Memory: 26kB Buffers: shared hit=11220, temp read=1792 written=1953 -> WindowAgg (actual time=423.242..423.255 rows=20 loops=1) Run Condition: (rank() OVER (?) <= 20) -> Sort (actual time=423.236..423.240 rows=21 loops=1) Sort Key: sum(oi.quantity * oi.unit_price) DESC Sort Method: quicksort Memory: 1929kB -> HashAggregate (actual time=402.740..415.533 rows=24968 loops=1) Group Key: c.customer_id Batches: 9 Memory Usage: 8273kB Disk Usage: 1480kB -> Hash Join (actual time=60.363..279.582 rows=482718 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Hash Join (actual time=34.192..176.799 rows=482752 loops=1) Hash Cond: (oi.order_id = o.order_id) -> Seq Scan on order_items oi (rows=482752 loops=1) -> Hash -> Seq Scan on orders o (rows=160684 loops=1) -> Hash -> Seq Scan on customers c (rows=106583 loops=1) Planning Time: 0.742 ms Execution Time: 423.895 ms VS Code with the PostgreSQL extension can visualize the plan, including buffers read by each node and the flow of rows between nodes: The monthly revenue query completed in 627 ms. Its aggregate spilled into 33 batches before two in-memory window sorts, making work_mem the first tuning candidate rather than an index: WindowAgg (actual time=615.702..626.031 rows=27472 loops=1) Buffers: shared hit=11211, temp read=1965 written=2208 -> Sort (actual time=615.698..617.253 rows=27472 loops=1) Sort Key: customer_sales.customer_id, customer_sales.month_start Sort Method: quicksort Memory: 2352kB -> WindowAgg (actual time=600.739..610.404 rows=27472 loops=1) -> Sort (actual time=600.708..602.149 rows=27472 loops=1) Sort Key: customer_sales.month_start, customer_sales.revenue Sort Method: quicksort Memory: 2137kB -> Subquery Scan on customer_sales (actual time=570.611..587.861 rows=27472 loops=1) -> HashAggregate (actual time=570.610..586.090 rows=27472 loops=1) Group Key: c.customer_id, date_trunc('month', o.order_date) Batches: 33 Memory Usage: 8209kB Disk Usage: 2040kB -> Hash Join (actual time=54.823..392.870 rows=482718 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Hash Join (actual time=31.672..208.840 rows=482752 loops=1) Hash Cond: (oi.order_id = o.order_id) -> Seq Scan on order_items oi (rows=482752) -> Hash -> Seq Scan on orders o (rows=160684) -> Hash -> Seq Scan on customers c (rows=106583) Planning Time: 0.266 ms Execution Time: 627.065 ms These execution plans establish a PostgreSQL baseline and identify possible tuning work, such as aggregate spills and work_mem pressure. I kept those observations for the physical-design review after correctness and concurrent workload validation rather than tuning from isolated queries too early. Layer 2: call every public workload routine After the preflight, the same harness discovers viable values and calls all nine workload transactions plus setPLSQLCOMMIT : bash scripts-postgres/run_orderentry_tests.sh Each call is isolated and reports its SQLSTATE on failure. The first run exposed defects that compilation could not: composite return types did not match some rows, internal and public customer-ID types differed, package settings lacked defaults, and newOrder retained an Oracle %TYPE cast. GitHub Copilot generated schema-relative repairs, which I reviewed and applied. The implementation is available in apply_orderentry_runtime_fixes.py: bash scripts-postgres/apply_orderentry_runtime_fixes.sh I then ran the harness again, and all ten checks passed. Layer 3: verify business effects A successful call does not prove the correct business effect. GitHub Copilot generated a rollback-safe order workflow: bash scripts-postgres/run_swingbench_workload.sh --verify-only It creates two orders for a copied customer, verifies their fields and item rows, then checks that browseandupdateorders changes one item quantity and the matching order total together. It rolls back, reconnects, and proves that both orders are absent. Only sequence values may advance because PostgreSQL sequences are nontransactional. Layer 4: weighted concurrent load Only after the first three layers passed did I run concurrent load. Four users executed 287 calls across all nine transaction types with zero errors. This ordering separated migration defects from performance observations and avoided assuming that copied customer IDs were contiguous. After validation: review physical design After validating the migration and ensuring data accuracy, it is time to look at the physical layer, using the execution plans captured in Layer 1 together with observations from the concurrent workload. Oracle and PostgreSQL handle data patterns differently, so blindly copying Oracle optimizations can degrade performance. PostgreSQL's MVCC architecture adds write overhead to updates, so the table FILLFACTOR strategy needs a fresh look compared with Oracle's PCTFREE . PostgreSQL also provides different tuning options, including partial indexes, specialized index types, and incremental sorting. Reproduce the complete validation At the end of my run, the test setup contained: all ten workload tables populated all five required sequences deployed and advanced two analytical queries accepted by PostgreSQL under EXPLAIN a complete invocation harness a weighted Swingbench-like concurrent driver a rollback-safe semantic order test After deploying the converted schema and its ORDERENTRY routines, I applied the reviewed runtime fixes and ran the checks in increasing order of scope: bash scripts-postgres/apply_orderentry_runtime_fixes.sh bash scripts-postgres/run_orderentry_tests.sh bash scripts-postgres/run_swingbench_workload.sh --verify-only bash scripts-postgres/run_swingbench_workload.sh --duration 300 Conclusion AI did not make this migration a one-click operation. The Oracle application combined packages, private helpers, collections, package state, bulk operations, transaction control, and behavior that only appears under realistic calls. A credible migration had to understand those interactions rather than only produce PostgreSQL syntax. The useful change is that the migration can be approached as an integrated project from one place. In VS Code, the migration extension converted and compiled the schema. I inspected the generated code and reports, directed GitHub Copilot to generate the supporting data-copy and workload programs, reviewed and applied runtime fixes, and validated data, API calls, business effects, and concurrent load. The model accelerated code conversion and the creation of focused tools, while PostgreSQL, executable tests, and my knowledge of the application provided the evidence. This is the method I would carry from assessment into a real migration: use AI to drive more of the conversion, diagnosis, repair, and validation iterations, not to hide complexity. Keep source behavior, converted code, data movement, test programs, findings, and fixes together in a reproducible workspace. This makes the first prototype useful evidence for estimating the remaining effort. A migration report measures one pipeline stage; the application is migrated only when its data, transactions, business effects, and workload work together on PostgreSQL. This validation is necessary but not sufficient for production. A real go-live also requires review of edge cases, collation, NULL handling, error paths, security, and sustained performance under representative data volumes. Because this walkthrough exercises preview functionality, experience reports are especially valuable. Readers trying it on other Oracle applications or PostgreSQL targets can share what converts well, what requires intervention, and which findings would make the assessment more useful in the PostgreSQL Hub Developer ForumAI-assisted Oracle-to-PostgreSQL schema conversion in Visual Studio Code
By AI Omar Rajawat, Pranay Lohia, Gautam Juneja, Vikas Nimmagadda, Anil Dogra, Aditya Duvuri We’re seeing significant interest in migrating database workloads from Oracle to Azure Database for PostgreSQL. Historically, schema conversion has been one of the most technically challenging and expensive steps in that journey, demanding specialist knowledge of both engines and stretching migration timelines before a single row of data moves. Recent advances in AI-assisted schema conversion are changing that, turning what used to be a long, manual effort into a faster, more accessible, and lower-cost proposition. This post looks at what that shift means in practice for teams moving to Azure Database for PostgreSQL flexible server. Oracle schema conversion is where the complexity of translating schema and code objects to PostgreSQL becomes visible. Packages, procedures, triggers, custom types, and dependencies built up over years must be mapped to PostgreSQL-compatible definitions while preserving the relationships that make the schema work. Generally available since May 2026, the feature is built into the PostgreSQL extension for Visual Studio Code, published by Microsoft. It helps teams convert Oracle schema and code objects — tables, views, constraints, packages, procedures, functions, and triggers — into PostgreSQL-compatible definitions for Azure Database for PostgreSQL flexible server, with no separate conversion utility to install and no disconnected workflow to manage. It brings schema discovery, conversion, compile validation, and review into one project-based experience. Teams can connect to Oracle, select schemas, and configure a Microsoft Foundry connection in the same project. The extension then translates Oracle-specific constructs, compiles and syntax-checks converted DDL into scratch schemas on Azure Database for PostgreSQL flexible server and surfaces unresolved items as review tasks that teams can work through with GitHub Copilot agent mode. Why schema conversion deserves a better workflow Traditional conversion tools can produce a useful first pass, but the long tail of the process is rarely solved by generating replacement DDL alone. Teams still need clear answers to practical questions: What converted successfully? What needs attention? Which Oracle constructs require a PostgreSQL design decision? Which items should be reviewed first? We designed the schema conversion experience around those questions. The goal is not to hide complexity behind a single score. It is to help teams make steady progress while keeping the work visible and reviewable. What the schema conversion experience provides The experience guides teams through a schema conversion project rather than a collection of separate scripts. It discovers the selected Oracle schemas and converts both the relational model and the code that runs on it: tables, indexes, sequences, primary key, unique, check and foreign key constraints, views and materialized views, synonyms, and Oracle object types — along with the PL/SQL that is usually the hardest part of the migration. Packages and package bodies, package-level state, standalone procedures and functions, and triggers are translated into PostgreSQL functions, procedures, and trigger functions. Oracle-specific constructs are mapped to PostgreSQL equivalents rather than dropped or stubbed out. REF CURSOR and SYS_REFCURSOR become PostgreSQL refcursor; CLOB and BLOB columns become text and bytea ; and NUMBER and VARCHAR2 are mapped by precision and length to their closest PostgreSQL types. Oracle date functions such as ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, and TRUNC are resolved through the orafce extension, which the project detects and flags for you before deployment. Every converted definition is compiled against scratch schemas on Azure Database for PostgreSQL flexible server, so the deployment script you end up with is an organized, dependency-ordered set of PostgreSQL SQL artifacts that has already been proven to build. Objects that still require human judgment are surfaced as review tasks. Teams can inspect the source and converted definitions side by side, work through the remaining items, and use GitHub Copilot agent mode for guided assistance — keeping automation and human review in the same workflow. How it works: the system architecture Under the hood, the conversion engine follows one principle — the language model is a single, bounded stage; it never has the first or the last word. Deterministic steps decide what the model sees and what it is allowed to produce. Deterministic in. Rule-based extraction reads the Oracle DDL and metadata, then a dependency-graph decomposition splits the estate into bounded, dependency-ordered chunks — so every object is converted in the context that keeps it correct. Bounded conversion. A tiered model strategy through the Microsoft Foundry connection translates each chunk with structured, contract-wrapped input and output. Even very large PL/SQL packages are split and converted member by member, so nothing is trusted as a monolith. Deterministic out. Converted objects pass through review, then compile-and-verify against scratch schemas on Azure Database for PostgreSQL flexible server, and finally dependency-ordered deploy assembly. Unresolved items become review tasks, and every object carries a per-object audit trail. A continuous-improvement loop closes the system: the engineering team maintains a versioned regression suite of supported conversion patterns, and an executable benchmark tracks regressions as the pipeline evolves. Proven in production The approach has been exercised on real enterprise estate. Across representative production runs totaling more than 60,000 schema objects; conversion reached roughly 98% overall — with several schemas converting at a full 100%. The hardest tail, PL/SQL package members, now compiles at 96% across more than 20,000 members thanks to targeted coverage and a resilient compile stage. Conversion outcome and review status are separate measures. Objects that convert and compile cleanly are safe to deploy as they are; the rest are deliberately routed into a prioritized review queue rather than silently accepted. In a representative single-schema run, no object ended in a hard conversion failure, and a cleanly generated object can still involve a PostgreSQL design decision. That is the workflow operating as intended: automation absorbs the volume, and review tasks to keep the remaining judgment calls visible, ordered, and auditable. Measured, not asserted: the SchemaBench eval Quality is verified by running it. SchemaBench, the evaluation framework, deploys each converted schema to a live PostgreSQL database and probes real behavior — whether constraints still fire and whether objects still resolve — rather than comparing DDL text. It scores seven weighted dimensions: semantic fidelity, structure, constraints, completeness, performance, target idioms, and maintainability, behind hard gates. On the e-commerce benchmark, the strongest model scored 96.1 overall with 100% semantic fidelity and a ~98% behavioral-probe pass rate. Every failure a migration hits becomes a permanent regression test the next run has to pass. Learn more: Oracle to Azure Database for PostgreSQL schema conversion overviewFaster, Safer Version Upgrades for Databases with Large Objects
By Varun Dhawan, Ilan Benschikovski, and Alexander Kukushkin - Azure PostgreSQL, Microsoft Faster, Safer Upgrades for Databases with Large Objects TL;DR: We improved major version upgrades for PostgreSQL databases with very high large-object counts. For upgrades targeting PostgreSQL 15 and later, large-object metadata is now handled more efficiently, reducing memory/temp-space pressure and helping previously risky upgrades complete more reliably. Why this matters Some PostgreSQL workloads store documents, images, PDFs, scanned files, or attachments as large objects (LOBs). In normal operations this is fine. But during a major version upgrade, very high LOB counts could make the schema dump step slow, memory-heavy, or fail. This improvement is about making that upgrade path safer and more predictable for Azure Database for PostgreSQL flexible server customers. What changed? During a major version upgrade, PostgreSQL uses pg_upgrade , which internally runs pg_dump to move schema and metadata into the new version. For databases with millions of large objects, the older upgrade path handled large-object metadata one object at a time. That created high memory and temporary-space pressure during the schema dump phase. This fix changes the upgrade path. Instead of processing large-object metadata one object at a time, PostgreSQL now transfers that metadata in bulk. The actual large-object data is not changed; only the upgrade metadata handling is improved. Why this is different This improvement builds on upstream PostgreSQL work that makes large-object metadata handling more efficient during upgrades. We brought that benefit into Azure Database for PostgreSQL flexible server for supported PostgreSQL 15+ upgrade targets, so customers with large-object-heavy workloads can benefit without waiting for a future PostgreSQL major version. Before vs after Area Before After Metadata handling One operation per large object Bulk metadata transfer Memory/temp pressure Grew heavily with LOB count Much flatter and more predictable High LOB counts Risk of OOM or temp-space failure Completes more reliably for PostgreSQL 15+ targets Customer workaround vacuumlo + scale-up often needed Less reliance on LOB-specific workarounds for PostgreSQL 15+ targets In plain English: the upgrade no longer has to carry paperwork for every large object one by one. It moves the metadata in bulk, which makes the upgrade faster, safer, and less likely to fail at very high LOB counts. The numbers We tested upgrades from PostgreSQL 13 with large-object counts ranging from 10M to 500M. The older path is represented by PostgreSQL 13 → 14. The improved path is represented by PostgreSQL 13 → 15. Note: These figures come from a multi-database test where large objects were spread across 100 databases. Because pg_dump runs per database, single-database workloads with the same total large-object count may see different runtimes. Cap Outcome summary Large objects Older path Improved path What changed 10M 48 min 16 min 3x faster 20M 1h 02m 18 min 3.4x faster 30M 2h 41m 21 min 7.6x faster 50M Failed at higher scale 26-30 min Now completes 100M Failed 54 min Now completes 500M Failed 4h 01m Now completes Key takeaway: this is not just faster. At higher LOB counts, the improvement changes the outcome from upgrade fails to upgrade completes. Who benefits from this? You should care if your database stores large binary content using PostgreSQL large objects. Workload pattern Why it matters Document management PDFs, contracts, scans, and archived files Attachment-heavy apps Files stored inside PostgreSQL instead of external storage Legacy apps using lo APIs LOBs may have accumulated for years Image/archive systems Millions of binary objects can build up quietly Previous upgrade failures Failures during schema dump may map to this scenario Copy/paste: check your large-object count Run these checks in each database you plan to upgrade. 1. Count large objects in the current database -- Count PostgreSQL large objects in the current database SELECT current_database() AS database_name, count(*) AS large_object_count FROM pg_largeobject_metadata; 2. Check large-object storage footprint -- Estimate large-object data and metadata size SELECT pg_size_pretty(pg_total_relation_size('pg_largeobject'::regclass)) AS large_object_data_size, pg_size_pretty(pg_total_relation_size('pg_largeobject_metadata'::regclass)) AS large_object_metadata_size; 3. Understand ownership and ACL shape -- Inspect large-object metadata shape SELECT count(*) AS total_large_objects, count(lomacl) AS large_objects_with_custom_acl, count(DISTINCT lomowner) AS distinct_large_object_owners FROM pg_largeobject_metadata; 4. Find top large-object owners -- Top large-object owners SELECT lomowner::regrole AS owner, count(*) AS large_object_count FROM pg_largeobject_metadata GROUP BY lomowner ORDER BY large_object_count DESC LIMIT 10; What should I do before my next major version upgrade? If your situation is... Recommended action Target is PostgreSQL 15 or later Target PostgreSQL 15 or later to benefit from improved large-object metadata handling. Target is PostgreSQL 14 or earlier Prefer PostgreSQL 15+ where possible; very high LOB counts may still hit older-path limitations. Very large or unusual database Restore a copy and rehearse the upgrade before production. Suspected orphan LOBs Consider vacuumlo only after testing. It can delete valid LOBs if your app uses custom references. Any major version upgrade Keep healthy free space and leverage pre-upgrade validation checks to validate extension/schema compatibility first. Bottom line If large objects were making your PostgreSQL upgrade risky, this improvement makes the upgrade path safer and more predictable. For large-object-heavy databases, upgrades targeting PostgreSQL 15 and later now show faster runtime, lower memory/temp-space pressure, and successful validation up to 500M large objects. Learn more Major version upgrades in Azure Database for PostgreSQL flexible server How to perform a major version upgrade PostgreSQL vacuumlo documentationTop 10 Performance Optimization Techniques for Azure Database for PostgreSQL Flexible Server
Introduction Performance optimization is one of the most common challenges faced by organizations running business-critical workloads on Azure Database for PostgreSQL flexible server. As your workloads grow it’s common to encounter high CPU utilization, storage bottlenecks, autovacuum issues, excessive temporary file generation, and connection saturation. The good news is that Azure PostgreSQL flexible server provides several built-in capabilities to help optimize performance, improve scalability, and reduce operational overhead. This article explores ten practical techniques that can significantly improve database performance and reliability. 1. Choose the Right Compute SKU Performance starts with selecting the appropriate compute tier. Azure PostgreSQL flexible server offers: Pricing tier Target workloads Burstable Designed for workloads that don't require full CPU performance continuously. Best suited for proof-of-concept environments, and development builds. Not recommended for production workloads. General Purpose Provides a balance between CPU and memory with scalable I/O throughput, making it suitable for most production workloads. Examples include servers for hosting web applications, mobile apps, and enterprise applications. Memory Optimized Suitable for high-performance database workloads that require in-memory performance for larger buffer cache sets, and higher concurrency. Examples include servers for processing real-time data and high-performance transactional or analytical apps. Learn more about Compute Tiers here. 2.Enable and Use Query Store Query Store is one of the most powerful performance tools available. Query Store automatically captures the following and keeps them available for review: Query execution statistics Runtime metrics Wait event information Historical execution trends It organizes the data into time windows, so you can spot database usage patterns. Data for all users, databases, and queries is stored in a database named azure_sys in the Azure Database for PostgreSQL instance. It’s generally recommended to monitor query store from Azure tools, KQL, etc. Learn more about Query store here. You can also view some useful scenario for query store and some Best Practices for Query store 3.Leverage Built-In PgBouncer Connection Pooling PostgreSQL uses a process-per-connection model, which means every connection consumes memory and CPU resources. Azure PostgreSQL flexible server provides built-in PgBouncer support for eligible SKUs . PgBouncer allows multiple application sessions to reuse open backend connections and significantly reduces overhead. Benefits include: Lower memory consumption Faster connection handling Improved application scalability Reduced CPU overhead Learn more about PgBouncer here 4.Use Azure Troubleshooting Guides One underutilized feature is the built-in troubleshooting experience available directly in the Azure portal. Guides are available for: CPU troubleshooting Memory troubleshooting IOPS analysis Temporary files Autovacuum monitoring Autovacuum blockers These tools provide actionable recommendations and visualizations without requiring external monitoring solutions. Learn more about Troubleshooting Guides here. 5. Monitor and Tune Autovacuum Autovacuum is critical for maintaining PostgreSQL performance. Without proper vacuuming: Dead tuples accumulate Table bloat increases Statistics are not refreshed regularly Query performance degrades Transaction ID wraparound risks emerge Use Azure's built-in Autovacuum Monitoring TroubleshootingGuides to identify: Vacuum lag Blocked autovacuums Table bloat Inefficient cleanup operations Azure now also offers adaptive tuning capabilities to optimize maintenance behavior. Learn more about Autovacuum tuning here 6.Optimize Storage and IOPS Planning Many performance incidents originate from insufficient storage planning rather than inefficient SQL. In Azure PostgreSQL flexible server: Storage and baseline IOPS are closely related. Learn more here. Larger storage allocations provide higher baseline IOPS. Auto-grow prevents storage-related outages Note: Storage can only be scaled up and will always be double in size. SSDv2 auto-grow will allow customized growth settings in future release. For write-heavy workloads, monitoring storage utilization and IOPS is essential. Best practice: Enable Storage Auto-Grow Monitor Read/Write IOPS regularly Scale storage proactively 7.Investigate Temporary File Generation Large sorts and hash operations that exceed available memory spill to disk and generate temporary files. Symptoms include: Sudden Latency Spikes Increased IOPS Slower query execution Azure TroubleshootingGuides provide dedicated temporary-file analysis capabilities that help identify offending queries. Frequent temp file generation often indicates: Missing indexes Undersized work_mem Large sorting operations 8.Use Intelligent Tuning Azure PostgreSQL flexible server includes Intelligent Tuning capabilities. The service continuously observes workload behavior and automatically optimizes parameters related to write operations. Examples of tuning include: checkpoint_completion_target max_wal_size min_wal_size bgwriter settings This reduces administrative effort while helping maintain consistent performance. Learn more about Intelligent Tuning here. 9.Optimize Checkpoints and Write Workloads Checkpoint spikes frequently appear in escalations involving high IOPS and latency. Aggressive checkpoint activity can: Generate excessive disk writes Increase latency Consume IOPS capacity Monitoring checkpoint behavior and ensuring WAL parameters are properly configured can significantly improve write-intensive workloads. Azure intelligent tuning can assist in this area as well. 10.Metric Monitoring Optimization should always be data-driven. You should track the following: CPU utilization Memory pressure Active Connections Oldest Query IOPS consumption Combining Azure Metrics, Query Store, and PostgreSQL statistic views allows teams to distinguish between normal workload spikes and true performance degradation. PostgreSQL statistics views provide valuable workload insights. For example, pg_stat_activity can be used to identify long-running or blocking queries, pg_stat_user_tables helps track dead tuples, vacuum activity, and statistics refreshes, while pg_stat_statements (if enabled) help identify the most resource-intensive queries by execution time and frequency. Learn more about Metric here Conclusion Performance optimization in Azure Database for PostgreSQL flexible server is not just about changing a few parameters and hoping for better results. It requires a structured approach that combines workload understanding, proactive monitoring, proper sizing, query optimization, and platform-native capabilities. By leveraging Query Store, PgBouncer, Intelligent Tuning, Autovacuum Monitoring, Azure Metrics, and Troubleshooting Guides, you can significantly improve database efficiency while reducing operational effort. References Compute Options - Azure Database for PostgreSQL | Microsoft Learn Query Store in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft Learn PgBouncer in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft Learn Autovacuum Tuning - Azure Database for PostgreSQL | Microsoft Learn Intelligent Tuning in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft LearnTLS Certificate Pinning and Best Practices in Azure Database for PostgreSQL
TLS certificate pinning in Azure Database for PostgreSQL Transport Layer Security (TLS) encrypts data in transit between client applications and the server and authenticates the service endpoint in client-server authentication. Azure Database server certificates are issued by well-known trusted public Certificate Authorities (CAs), including Microsoft-issued certificates, and are validated by clients during the TLS handshake. Customers do not manage certificates on the server side. Certificate pinning is a client-side security technique where an application restricts trust to a specific certificate, for example by thumbprint, public key, or CA, rather than relying solely on the default OS or platform trust store. The trust store contains pre-installed root CAs and may also include additional certificates configured by the client. During standard TLS validation, the client will trust any server certificate that chains to one of those root CAs. Why detecting TLS certificate pinning is not possible by design Certificate pinning is entirely client-side logic. The server has no visibility into whether pinning is configured on the client. From the server’s perspective, the client either completes the TLS handshake or aborts it. The server never sees: Which certificate(s) the client trusts Whether the client is comparing root CA, intermediate CA, leaf certificate or SPKI hash Whether the trust decision was static or dynamic What the server can see is TLS handshake failure patterns, TLS protocol, and cipher negotiation. Why certificate pinning is risky While certificate pinning was historically used to reduce the risk of man-in-the-middle attacks, it introduces significant operational fragility in cloud environments, particularly during certificate rotations. Server certificates and certificate authorities (CAs) must be rotated periodically to maintain security and compliance. In Azure Database for PostgreSQL, when certificate pinning is used, clients bind trust to a specific certificate or CA. As a result, any change to the server certificate chain—including CA updates—can cause connection failures, even when the new certificates are fully valid and secure. One of the most common complications during certificate rotations is certificate pinning. Recommended TLS certificate trust model for Azure PostgreSQL Instead of pinning, adopt a CA‑based trust model that allows certificates to change safely. Trust root CAs, not individual certificates. Configure clients to use standard TLS validation against Azure-documented root CAs, rather than restricting trust to specific certificates or a narrowly scoped set of certificate authorities. Avoid configurations that effectively implement certificate pinning—such as trusting only a single certificate, public key, or limited CA set—unless explicitly required. Maintain a flexible and up-to-date trust store Clients rely on a trust store, key store, or equivalent certificate bundle to validate server certificates during TLS negotiation. Include the appropriate root and intermediate certificate authorities (CAs) required to validate the server certificate chain Ensure that trust stores are periodically reviewed and updated in line with provider guidance and announced certificate authority changes For the current TLS certificates visit the Azure Database for PostgreSQL documentation. Use certificate validation modes that rely on standard CA-based trust rather than pinning For PostgreSQL client configurations, prefer: sslmode=verify-ca Validates the server certificate chain against trusted CAs sslmode=verify-full Verifies CA and hostname match These modes ensure that clients validate the server certificate chain against trusted CAs, and in stricter modes, verify hostname identity. They do not imply certificate pinning by themselves. They rely on standard CA-based trust. Configurations only become rigid when trust is narrowly restricted, such as to a single certificate or limited CA set, often through custom or overly constrained trust stores. This effectively introduces certificate pinning. When properly configured, these modes authenticate the service endpoint and protect against spoofing, while remaining resilient to certificate rotations. Maintain a combined CA during certificate rotations Azure may rotate root or intermediate CAs over time. When Azure announces a CA rotation: Add newly required root CAs to the client trust store before the rotation begins. Retain existing trusted root CAs until the transition is fully complete. Avoid removing older root certificates prematurely. If specific rotation guidance includes updates related to intermediate CAs, follow the service-specific instructions provided for that rotation. This combined CA approach, using both the current and upcoming certificate authorities during the transition window, allows clients to continue validating the server certificate chain without interruption. As you review your current client configurations, ensure your applications rely on CA-based trust, avoid overly restrictive certificate configurations such as certificate pinning, and are prepared to handle routine certificate rotations without disruption. For a deeper dive, see the full article: TLS Certificate Pinning in PostgreSQL and MySQL: Risks, Rotations, and Best Practices.Multitude builds resilient banking platform with PostgreSQL and MySQL on Azure
Expanding into new markets is usually a sign that things are going well. For digital banking platforms, however, growth brings a different kind of challenge - more customers, more data, and stricter expectations around availability, security, and regulatory compliance. At Multitude, we operate across 17 countries and deliver digital banking, credit services, payment processing, and regulatory reporting through a platform composed of more than 400 microservices. Each service encapsulates a defined business capability, including onboarding, risk assessment, collections, and compliance workflows. Historically, our services relied on on-premises PostgreSQL and MySQL environments deployed within our own data centers, where capacity scaled vertically on shared compute and storage resources. This model created contention between unrelated workloads and limited their ability to scale independently. Expanding capacity required adding or upgrading physical hardware, which involved demand forecasting, procurement, delivery coordination, and installation within the data center. Over time, continued growth amplified these architectural constraints. The database engines themselves remained reliable, but the surrounding infrastructure limited elasticity and domain-level isolation. As a result, sustained growth began to expose structural limits in the underlying infrastructure. "In a regulated financial environment, those constraints carried broader implications. Frameworks such as DORA and GDPR require predictable availability, controlled recovery procedures, and governed access to sensitive data. As workload demands increased, sustaining both growth and compliance required structural changes at the database layer. We decided that redesigning our data architecture was necessary to improve workload isolation, scalability, and governance alignment. Rearchitecting data boundaries with Azure Databases We initiated our architectural redesign by migrating database workloads to Microsoft Azure and standardizing on Azure Database for PostgreSQL and Azure Database for MySQL for core application services. Central to this redesign was the adoption of bounded contexts. Each bounded context represents a logical business domain and encapsulates the services and schemas required to support that capability. Each domain is owned and managed by a single team, aligning technical boundaries with team responsibility and accountability. Rather than maintaining a small number of large, shared database instances, we provisioned dedicated database instances aligned to defined business domains, establishing domain-level isolation at the database layer. Today, approximately 35 database instances support more than 400 microservices across the platform. Each instance may host multiple schemas serving related services within the same domain, while cross-domain database dependencies are intentionally avoided. This structure limits the blast radius of configuration changes or workload spikes and allows scaling adjustments to be applied within clearly defined domain boundaries. While the bounded context model was a strategic architectural decision, leveraging managed database services helped us implement it by drastically reducing the operational overhead of provisioning, scaling, and maintaining independent instances across domains. Azure Database for PostgreSQL and Azure Database for MySQL provide the managed capabilities required to sustain this model. Instances are provisioned according to the performance and storage requirements of each domain and can be adjusted as workload characteristics evolve. Compute and storage resources are scaled at the instance level, allowing capacity changes to be applied to a specific bounded context without affecting unrelated domains. Altogether, these architectural decisions balance domain-level isolation with operational manageability. A database-per-microservice pattern would significantly increase provisioning, monitoring, and lifecycle overhead without materially improving data ownership boundaries. By grouping related services within bounded contexts, we maintain clear domain alignment while keeping the number of database instances practical to operate. As a result, data boundaries, scaling behavior, and operational controls remain consistent with business domain structures across the platform. Operationalizing high availability and backup strategy To support availability, we deploy Azure Database for PostgreSQL and Azure Database for MySQL with zone-redundant high availability, placing primary and standby replicas in separate availability zones within the same Azure region. Replication preserves transactional consistency, and zone separation reduces exposure to localized infrastructure failures. We periodically exercise failover procedures as part of operational validation to confirm recovery behavior under defined conditions. Availability controls are complemented by a layered backup strategy. Azure Database for PostgreSQL and Azure Database for MySQL provide automated backups with a retention window of up to 35 days and point-in-time restore capabilities. These features allow us to restore a database to a specific timestamp within the retention window, supporting recovery from application-level errors or unintended data modifications without custom snapshot orchestration. Together, operational backups and governed archival retention address both short-term recovery and long-term compliance obligations. Restore operations require documented justification and follow established approval workflows, ensuring that recovery actions remain controlled, traceable, and auditable. We also enforce consistency through lifecycle management. Azure’s managed service model standardizes engine patching and version updates across environments, reducing configuration drift and minimizing manual coordination. By operating within the managed service boundary, the database team can focus on workload analysis, performance tuning, and capacity planning. For migration and synchronization scenarios, we use Azure Data Migration Service to orchestrate controlled cutovers between database environments. Engineers validate configuration and readiness before initiating synchronization, after which Azure-managed replication then maintains data alignment until final switchover. Provisioning decisions and structural modifications remain subject to internal governance approvals to preserve change control and oversight. By combining zone-redundant availability, structured recovery workflows, governed retention policies, and standardized lifecycle management, we operate a database layer engineered for resilience, auditability, and regulatory alignment at scale. Compliance as an architectural property For us, governance is embedded directly into how the platform operates, beginning at the identity layer. Access to Azure Database for PostgreSQL and Azure Database for MySQL integrates with Microsoft Entra ID, aligning database authentication with centrally managed corporate identities. Role-based access control is enforced through enterprise identity policies, providing centralized visibility into access assignments and authentication events across environments. These controls extend into production access management. Privileged access is approval-based and time-bound, and administrative roles are not permanently assigned. Access requests follow defined workflows, and all privileged actions are logged for review under established oversight procedures, ensuring traceability of operational interventions. Database isolation reinforces these identity controls. By aligning database instances with bounded contexts, each business domain maintains a discrete data boundary at the database layer. This structure limits lateral access across domains and confines sensitive data to clearly defined ownership scopes, simplifying monitoring and audit review. In a regulated financial environment, these architectural controls also support compliance requirements under frameworks such as DORA and GDPR. By embedding identity integration, domain isolation, and lifecycle controls directly into the platform architecture, governance becomes an operational property of the system rather than a separate procedural layer. The simplicity of this architecture is a strong driver for both auditability and security of the whole platform. Measurable impact across engineering teams and business outcomes Beyond improved stability, our ability to respond to growth has changed significantly since moving to Azure. In the past, expanding database capacity meant procuring hardware and planning installation in the data center. Now, capacity adjustments happen directly within Azure and can be applied to individual databases instances, allowing us to scale in near real time as workload demands change. Maintenance effort has also decreased. Managed patching, version alignment, and automated backups have reduced the need for manual coordination and reactive capacity management. Infrastructure-level tasks that once required continuous oversight are now handled within the managed service boundary. Our DBAs are now focused on improving performance and stability. We spend far less time maintaining the basics. Resilience by design The structural changes behind these results reflect a deliberate long-term strategy. Our database architecture now aligns with the operating model we expect to sustain over the next five years and beyond. Bounded contexts define discrete data domains, while Azure Database for PostgreSQL and Azure Database for MySQL provide managed high availability, scaling controls, and standardized lifecycle management across those domains. Identity integration and governed recovery procedures operate consistently across environments. With this architecture in place, Multitude scales responsibly in regulated markets while maintaining strict governance and availability standards. Expanding into new markets still means more customers and more data - but now our platform is designed to handle that success.Building an Azure architecture that’s ready for every signature
At Exclaimer, we help organizations manage email signatures at scale, so every message can carry a consistent, compliant, on-brand signature without IT teams manually updating thousands of mailboxes. This is more difficult than it may seem, especially when you're doing it for more than 80,000 customers, around 9.6 million seats, and more than 21 billion emails a year. Every signature must show up in the right place, with the right details, for the right sender, recipient, device, and business rule. Behind that are constantly changing employee records, customer-specific policies, email chains, recipient lists, regional disclaimers, and brand requirements. Because our platform sits directly in the email flow, availability is critical. And because many of our customers operate in regulated industries, they also need confidence that data stays in-region and configured signatures are applied consistently. To support that level of scale and reliability, we’ve spent the last several years evolving our architecture on Microsoft Azure. Today, Azure Kubernetes Service (AKS), Azure SQL Database, Azure Database for PostgreSQL, Azure Cosmos DB, Azure Data Explorer, and Azure Databricks help us run a global platform that’s more responsive, more resilient, and more cost-efficient. Reading the signs that our architecture needed to change In the beginning, our cloud product ran more like a multi-server, on-premises product hosted on Azure Virtual Machines (VMs). The platform was split into a smaller number of core services, and the team relied heavily on VM-based infrastructure to keep those services running. As Exclaimer grew, our architecture had to keep pace with higher volumes, more regions, and more complex customer requirements. Regional demand shifted throughout the day, but scaling infrastructure up and down still relied on scripts, pre-baked VMs, and operational coordination. That created more risk during maintenance and failover. We run parallel data centers in regional pairs so we can move traffic away from one site when needed. But when traffic moves, the receiving environment has to be ready to handle the full load. In the VM world, that meant someone or something had to remember to scale up standby resources at the right moment. At the same time, our product was becoming more service-oriented. We were moving away from a smaller set of larger services toward well over 100 microservices. Every new service created more conversations about VM sizing, images, patching, and operational overhead. It was time for a model that could scale faster, run more efficiently, and reduce the amount of infrastructure work required to ship and operate the product. Signing on to AKS for faster, more efficient scaling By moving many workloads to Linux containers on AKS, we gained a smaller footprint, faster startup times, and a more consistent way to package and deploy services. AKS also gave us a managed Kubernetes foundation for running those containers at global scale, with autoscaling capabilities that better matched our traffic patterns. With Horizontal Pod Autoscaler, services can react to load in seconds rather than minutes. With Cluster Autoscaler, we can add or remove node capacity based on what the platform actually needs. That means we can pack workloads onto nodes more efficiently, scale down during quiet periods, and scale up quickly when demand returns. The operational difference is just as important. During an incident, maintenance event, or regional failover, our teams have fewer manual steps to think about. If traffic shifts, the platform can scale with it. That takes away one more thing for engineers to worry about when they should be focused on keeping the customer experience steady. The move to containers and a more streamlined CI/CD workflow also improved our deployment cadence by making it easier to build, test, and deploy changes across the platform. In 2021, we deployed 285 changes, features, and fixes to production over the course of the entire year. Today, we deploy that many every few days. Cost has improved, too. Since 2024, when the bulk of our migration to containerized services took place, we’ve reduced our average cost per user by about 39 percent, even as the product has grown more complex and we’ve added more capabilities for customers. We achieved that through a combination of containerized architecture, AKS autoscaling, and expanded reservations across compute and storage technologies. Choosing the right database for the right kind of data We started with a strong Microsoft SQL Server foundation, and Azure SQL Database remains core to our platform today. It stores critical customer configuration data and continues to give us the reliability, replication, resizing flexibility, and regional scale we need. But not every workload belongs in the same database. Customer configuration, relational service data, key-value storage, usage events, and business intelligence (BI) all have different access patterns. That principle led us to Azure Database for PostgreSQL flexible server for one of our most important migrations. We had used Azure Table storage for a core service that needed to retrieve customer data quickly. It was cost-effective and stable for a long time, but as the product evolved, the data became more relational, and we found ourselves adding complexity in application code that a relational database could handle more naturally. Azure Database for PostgreSQL gave us that relational model with low management overhead, fast read replicas, reserved instances for predictable workloads, and a path to future scale. After the migration, average request time for a critical service dropped from 18.6 milliseconds to 1.79 milliseconds. That’s a 90 percent improvement across a service that handles around 9 billion requests each month. Azure Cosmos DB plays a different role, supporting key-value and document storage where we need scale, availability, low latency, encryption at rest, and straightforward dev/test support. Optimized for unstructured data and high-performance reads and writes, it gives us a highly scalable foundation for workloads that don't fit a traditional relational model. We use it to store customer assets for signatures and video branding, high-volume metadata for internal message-processing operations, audit events that help customers track account changes, and tokens used to collect data from third-party systems on behalf of customers. It also gives us a clean way to keep data and services aligned. Azure Data Explorer solved another scaling challenge: usage and billing data. We need to be able to audit the number of messages we process for our customers so we can bill accurately, and at more than 20 billion emails a year, our previous SQL-based usage pipeline became difficult to manage. With Azure Data Explorer, we can ingest massive volumes of event data at low storage cost, connect to Azure Event Hubs, and avoid maintaining custom plumbing. That move reduced the cost of the system by around 70 percent. Azure Databricks rounds out the picture as our BI and data platform, giving our teams a shared foundation for transformations, analysis, and reporting across product and business data. Keeping every region ready for business Our customers are everywhere, so our platform has to be, too. Exclaimer runs in seven distinct geographic locations: Australia, Canada, Europe, Germany, the United Arab Emirates, the United Kingdom, and the United States. That global footprint helps us meet customer expectations around availability and data residency. Many organizations want their data to stay in-region, and Azure gives us the coverage we need to support that. Availability is especially important because our platform is part of a live communication flow. When someone sends an email, they expect it to keep moving. Our Azure architecture helps us support that expectation across the stack. AKS lets compute scale with regional demand. Azure SQL and Azure Database for PostgreSQL support critical relational workloads. Azure Cosmos DB gives us scalable, low-latency storage for document and key-value patterns. Azure Data Explorer handles very high-volume usage ingestion without the complexity of our former custom pipeline. Across the board, these managed Azure services reduce the amount of operational work our engineers have to carry. We can spend less time maintaining the basics and more time tuning performance, improving stability, and building the capabilities our customers need next. Building for the future on a stronger foundation The biggest sign that our architecture is working may be how little we have to reinvent when we build something new. As we develop upcoming product capabilities, we already have many of the foundational pieces in place: AKS for compute, Azure Cosmos DB for state, and Azure Service Bus for messaging. We also have Azure SQL for core data, Azure Database for PostgreSQL where relational service data needs room to scale, Azure Data Explorer for high-volume event analysis, and Azure Databricks for BI tooling. Together, these services make our platform faster, more efficient, and more resilient. Email signatures may look simple on the surface. Behind every one, there’s a set of decisions about performance, scale, data, availability, and trust. With Azure, we’ve built an architecture that helps us keep every signature moving, wherever our customers do business. About the authors Phil Vetter started in engineering at Exclaimer as a developer at the start of 2013, and now sits at the helm as VP of Engineering. Lee Jones started at Exclaimer in 2013 in the IT department, and now serves as Director of Platform Engineering, managing the infrastructure and resilience of Exclaimer Cloud.
