Miisaamidda PostgreSQL si uu u taageero 800 milyan oo isticmaaleyaasha ChatGPT ah

Muddo sannado ah, PostgreSQL wuxuu ahaa mid ka mid ah nidaamyada xogta ee ugu muhiimka badan ee gadaal ka shaqeeya, kuwaas oo awood siiya alaabooyinka aasaasiga ah sida ChatGPT iyo API-ga OpenAI. Maaddaama saldhigga isticmaaleyaasheenna uu si degdeg ah u korayo, baahida saaran keydadka xogtayaduna si xawli ah ayay u kordheen. Sannadkii la soo dhaafay, culeyska PostgreSQL-keennu wuxuu kordhay in ka badan 10 jeer, welina si dhakhso ah ayuu u sii kacayaa.

Dadaalladayada aan ku hormarinaynay kaabeyaashayada wax-soo-saarka si ay u xamili karto koritaankan waxay noo muujiyeen aragti cusub: PostgreSQL waa la miisaami karaa si uu si lagu kalsoonaan karo u taageero culaysyo akhris-badan oo aad uga waaweyn intii dad badani hore u malaynayeen. Nidaamkan (oo markii hore ay sameeyeen koox saynisyahanno ah oo ka socday Jaamacadda California, Berkeley) wuxuu noo suurtogeliyay inaan ku taageerno taraafikada caalamiga ah ee aadka u weyn hal primary ah oo Azure PostgreSQL flexible server instance⁠(ku furmaa daaqad cusub) ah iyo ku dhowaad 50 nuqul oo akhris ah oo ku kala baahsan gobollo badan oo dunida ah. Tani waa sheekada sida aan OpenAI uga miisaannay PostgreSQL si uu u taageero malaayiin weydiin ilbiriqsikii oo loogu adeego 800 milyan oo isticmaale, annagoo adeegsanayna hagaajin adag iyo injineernimo sugan; sidoo kale waxaan ka hadli doonnaa casharrada muhiimka ah ee aan jidka ku barannay.

Ka dib bilaabistii ChatGPT, taraafikadu waxay ku kortay xawaare aan horay loo arag. Si aan taas u taageerno, waxaan si degdeg ah uga hirgelinnay hagaajin ballaaran labadaba heerka codsiga iyo heerka kaydka xogta ee PostgreSQL, waxaanan kor ugu qaadnay awoodda annagoo weynaynayna cabbirka instance-ka, sidoo kalena ballaarinayna annagoo ku darnay nuqullo akhris oo badan. Dhismahan qaab-dhismeedka ahi muddo dheer si fiican buu noogu shaqeeyay. Horumarro joogto ah awgood, wali wuxuu bixiyaa fursad ku filan oo loogu diyaar garoobo koritaanka mustaqbalka.

Waxa laga yaabaa inay la yaab noqoto in qaab-dhismeed hal-primary ahi dabooli karo baahiyaha miisaanka OpenAI; hase yeeshee, in tani si dhab ah u shaqeyso ma aha wax fudud. Waxaan aragnay dhowr SEV oo ka dhashay culays xad-dhaaf ah oo ku dhacay Postgres, badanaana waxay raacaan isla qaabkan: arrin ka timaadda dhinaca kore ayaa keenta koror lama filaan ah oo culeyska kaydka xogta ah, sida cache miss badan oo ka dhasha fashilka lakabka kaydinta, koror ku yimaadda isku-xirro qaali ah oo badan oo CPU-ga buuxiya, ama duufaan qorid ah oo ka dhalata soo saarista muuqaal cusub. Marka isticmaalka kheyraadku kordho, daahitaanka weydiimuhu wuu sii kacaa, codsiyaduna waxay bilaabaan inay wakhtigu ka dhammaado. Retry-yadu markaas waxay sii xoojiyaan culeyska, iyagoo kicinaya wareeg xun oo halis u leh inuu dhaawaco dhammaan adeegyada ChatGPT iyo API-ga.

Inkastoo PostgreSQL si wanaagsan ugu miisaamo culaysyadeenna akhris-badan, haddana weli waxaan la kulannaa caqabado xilliyada taraafikada qorista badani jirto. Tani waxay badanaa ka timaaddaa hirgelinta xakamaynta isbarbar-socodka noocyo-badan (MVCC) ee PostgreSQL, taas oo ka dhigta inuu waxtar yar u yeesho culaysyada qorista badan. Tusaale ahaan, marka weydiin ay cusboonaysiiso tuple ama xitaa hal field, safka oo dhan ayaa la koobiyeeyaa si nooc cusub loo abuuro. Marka culayska qoristu culus yahay, tani waxay keentaa write amplification weyn. Waxay kaloo kordhisaa read amplification, maadaama weydiimuhu ay mariyaan noocyo badan oo tuple ah (dead tuples) si ay u helaan kii ugu dambeeyay. MVCC waxay keentaa caqabado kale sida buurnaan ku timaadda tables iyo indexes, koror ku yimaadda kharashka dayactirka index-yada, iyo hagaajin adag oo autovacuum ah. (Faahfaahin qoto dheer oo ku saabsan arrimahan waxaad ka heli kartaa blog aan la qoray Prof. Andy Pavlo oo ka tirsan Carnegie Mellon University, oo la yiraahdo Qaybta PostgreSQL ee aan ugu necbahay⁠(ku furmaa daaqad cusub), taas oo lagu xusay⁠(ku furmaa daaqad cusub) bogga Wikipedia ee PostgreSQL.)

Si loo yareeyo xaddidaadahaan oo loo dhimo cadaadiska qorista, waxaan u wareejinnay, welina waan u wareejinaynaa, culaysyada la shard-gareyn karo (taas oo ah, culaysyada si jiif ah loo qaybin karo) ee qorista badan nidaamyo shardaysan sida Azure Cosmos DB, annagoo hagaajinayna macquulka codsiga si loo yareeyo qoritaannada aan loo baahnayn. Sidoo kale mar dambe ma oggolin in miisas cusub lagu daro geynta PostgreSQL ee hadda jirta. Culaysyada cusub waxay si toos ah ugu dhacaan nidaamyada shardaysan.

Xitaa iyadoo kaabeyaasheenna ay is beddeshay, PostgreSQL wuxuu weli yahay mid aan la shard-gareyn, iyadoo hal primary instance ahi u adeegayo dhammaan qoritaannada. Sababta ugu weyn ayaa ah in shard-gareynta culaysyada codsiyada jira ay noqon lahayd mid aad u adag oo wakhti badan qaadata, una baahan wax-ka-beddel lagu sameeyo boqolaal endpoint oo codsi ah, isla markaana laga yaabo inay qaadato bilo ama xitaa sannado. Maaddaama culaysyadeennu badankoodu yihiin akhris-badan, isla markaana aan hirgelinnay hagaajin aad u ballaaran, qaab-dhismeedka hadda jira weli wuxuu bixiyaa awood dheeraad ah oo ku filan si loo taageero koritaanka taraafikada ee sii socda. In kasta oo aynaan mustaqbalka ka saarin shard-gareynta PostgreSQL, haddana mudnaan dhow ma aha marka la eego waqtiga ku filan ee aan u haysanno koritaanka hadda iyo kan mustaqbalka.

Qaybaha soo socda, waxaan si qoto dheer ugu gelaynaa caqabadihii aan wajahnay iyo hagaajintii ballaarneyd ee aan hirgelinnay si aan wax uga qabanno ugana hortagno hakadyo mustaqbalka, annagoo PostgreSQL ku riixayna xadkiisa oo ku miisaamayna ilaa malaayiin weydiin ilbiriqsikii (QPS).

Caqabadda: Iyadoo uu jiro hal qore oo keliya, qaabeynta hal-primary ma miisaami karto qorista. Koror degdeg ah oo qorid culus ahi si dhaqso ah ayuu u culaysin karaa primary-ga, wuxuuna saameyn karaa adeegyada sida ChatGPT iyo API-gayaga.

Xalka: Waxaan yareynaa culeyska saaran primary-ga intii suurtagal ah—akhris iyo qoridba—si loo hubiyo inuu leeyahay awood ku filan oo uu ku xamili karo kororka qorista. Taraafikada akhriska waxaa loo weeciya nuqullada meel kasta oo ay suurtagal tahay. Hase yeeshee, qaar ka mid ah weydiimaha akhrisku waa inay ku sii jiraan primary-ga sababtoo ah waxay qayb ka yihiin write transactions. Kuwaas, waxaan diiradda saarnaa inaan hubinno inay hufan yihiin oo ay ka fogaadaan weydiimo gaabis ah. Dhanka taraafikada qorista, waxaan u wareejinnay culaysyada la shard-gareyn karo ee qorista-badan nidaamyo shardaysan sida Azure CosmosDB. Culaysyada adag in la shard-gareeyo balse wali soo saara mug qorid oo sare leh waxay qaataan waqti dheer in la raro, howshaasna weli way socotaa. Waxaan sidoo kale si adag u hagaajinnay codsiyadeenna si loo yareeyo culeyska qorista; tusaale ahaan, waxaan saxnay cilado codsi oo sababay qoritaanno soo noqnoqda, waxaanan soo kordhinnay lazy writes, halka ay ku habboon tahay, si loo simo kororka taraafikada. Intaa waxaa dheer, marka dib loo buuxinayo fields-ka miisaska, waxaan dhaqan gelinnaa xaddidaad xawaare oo adag si looga hortago cadaadis qorid oo xad-dhaaf ah.

Caqabadda: Waxaan aqoonsannay dhowr weydiin oo qaali ah gudaha PostgreSQL. Waagii hore, koror degdeg ah oo ku yimaadda mugga weydiimahan wuxuu cuni jiray CPU badan, isagoo gaabiyay codsiyada ChatGPT iyo API-ga labadaba.

Xalka: Dhowr weydiin oo qaali ah, sida kuwa isku xira miisas badan, waxay si weyn u wiiqi karaan ama xitaa u dumin karaan adeegga oo dhan. Waxaan u baahanahay inaan si joogto ah u hagaajinno weydiimaha PostgreSQL si loo hubiyo inay hufan yihiin ugana fogaadaan anti-pattern-yada caadiga ah ee Online Transaction Processing (OLTP). Tusaale ahaan, mar waxaan aqoonsannay weydiin aad qaali u ah oo isku xirtay 12 miis, taas oo kororkeedu masuul ka ahaa SEV-yo aad u daran oo hore u dhacay. Waa inaan ka fogaano isku-xirro adag oo miisas badan ah markasta oo ay suurtagal tahay. Haddii joins loo baahdo, waxaan barannay inaan ka fiirsanno kala jebinta weydiinta oo aan macquulka join-ka adag u wareejinno lakabka codsiga. Qaar badan oo ka mid ah weydiimahan dhibka leh waxaa soo saara qaab-dhismeedyada Object-Relational Mapping (ORMs), sidaas darteed waa muhiim in si taxaddar leh loo eego SQL-ka ay soo saaraan oo la hubiyo inuu u dhaqmo sida la filayo. Sidoo kale waa wax caadi ah in PostgreSQL laga helo weydiimo muddo dheer aan shaqo qaban oo furan. Dejinta timeouts sida idle_in_transaction_session_timeout waa lama huraan si looga hortago inay xannibaan autovacuum.

Caqabadda: Haddii nuqul akhris ahi hoos u dhaco, taraafikada weli waxaa loo diri karaa nuqullo kale. Hase yeeshee, ku tiirsanaanta hal qore waxay ka dhigan tahay hal dhibic oo fashil ah—haddii uu hoos u dhaco, adeegga oo dhan ayaa saameynmaya.

Xalka: Inta badan codsiyada ugu muhiimka ah waxay ku lug leeyihiin oo keliya weydiimo akhris ah. Si loo yareeyo halista hal dhibic ee fashilka ee primary-ga, waxaan akhrisyadaas ka wareejinnay writer-ka una wareejinnay replicas, si loo hubiyo in codsiyadaasi ay sii adeegaan xitaa haddii primary-gu hoos u dhaco. Inkastoo hawlgallada qoristu ay weli fashilmi lahaayeen, saameyntu way yaraataa; mar dambe ma noqoneyso SEV0 maadaama akhrisyadu weli diyaar yihiin.

Si loo yareeyo fashillada primary-ga, waxaan primary-ga ku wadnaa qaabka High-Availability (HA) oo leh hot standby, oo ah nuqul si joogto ah ula jaanqaadaya kaas oo mar kasta diyaar u ah inuu la wareego adeegga taraafikada. Haddii primary-gu hoos u dhaco ama loo baahdo in dayactir darteed offline loo dhigo, waxaan si dhaqso ah u dallacin karnaa standby-ga si loo yareeyo hakadka. Kooxda Azure PostgreSQL waxay qabteen shaqo badan si ay u hubiyaan in failover-radani ay ahaadaan kuwo ammaan ah oo lagu kalsoonaan karo xitaa marka culeysku aad u sarreeyo. Si loo maareeyo fashillada nuqullada akhriska, waxaan gobol kasta ku geynaa nuqullo badan oo leh awood dheeraad ah oo ku filan, si fashilka hal nuqul uusan u horseedin hakad goboleed.

Caqabadda: Marar badan waxaan la kulannaa xaalado ay codsiyo gaar ahi ku cunaan qayb aan u dhigmin oo ka mid ah kheyraadka instance-yada PostgreSQL. Tani waxay keeni kartaa waxqabad liita culaysyo kale oo ku wada socda isla instance-yadaas. Tusaale ahaan, soo saarista muuqaal cusub waxay keeni kartaa weydiimo aan hufnayn oo si culus u cuna CPU-ga PostgreSQL, taas oo gaabisa codsiyada astaamo kale oo muhiim ah.

Solution: To mitigate the “noisy neighbor” problem, we isolate workloads onto dedicated instances to ensure that sudden spikes in resource-intensive requests don’t impact other traffic. Specifically, we split requests into low-priority and high-priority tiers and route them to separate instances. This way, even if a low-priority workload becomes resource-intensive, it won’t degrade the performance of high-priority requests. We apply the same strategy across different products and services as well, so that activity from one product does not affect the performance or reliability of another.

Challenge: Each instance has a maximum connection limit (5,000 in Azure PostgreSQL). It’s easy to run out of connections or accumulate too many idle ones. We’ve previously had incidents caused by connection storms that exhausted all available connections.

Solution: We deployed PgBouncer as a proxy layer to pool database connections. Running it in statement or transaction pooling mode allows us to efficiently reuse connections, greatly reducing the number of active client connections. This also cuts connection setup latency: in our benchmarks, the average connection time dropped from 50 milliseconds (ms) to 5 ms. Inter-region connections and requests can be expensive, so we co-locate the proxy, clients, and replicas in the same region to minimize network overhead and connection use time. Moreover, PgBouncer must be configured carefully. Settings like idle timeouts are critical to prevent connection exhaustion.

Challenge: A sudden spike in cache misses can trigger a surge of reads on the PostgreSQL database, saturating CPU and slowing user requests.

Solution: To reduce read pressure on PostgreSQL, we use a caching layer to serve most of the read traffic. However, when cache hit rates drop unexpectedly, the burst of cache misses can push a large volume of requests directly to PostgreSQL. This sudden increase in database reads consumes significant resources, slowing down the service. To prevent overload during cache-miss storms, we implement a cache locking (and leasing) mechanism so that only a single reader that misses on a particular key fetches the data from PostgreSQL. When multiple requests miss on the same cache key, only one request acquires the lock and proceeds to retrieve the data and repopulate the cache. All other requests wait for the cache to be updated rather than all hitting PostgreSQL at once. This significantly reduces redundant database reads and protects the system from cascading load spikes.

Challenge: The primary streams Write Ahead Log (WAL) data to every read replica. As the number of replicas increases, the primary must ship WAL to more instances, increasing pressure on both network bandwidth and CPU. This causes higher and more unstable replica lag, which makes the system harder to scale reliably.

Solution: We operate nearly 50 read replicas across multiple geographic regions to minimize latency. However, with the current architecture, the primary must stream WAL to every replica. Although it currently scales well with very large instance types and high-network bandwidth, we can’t keep adding replicas indefinitely without eventually overloading the primary. To address this, we’re collaborating with the Azure PostgreSQL team on cascading replication⁠(ku furmaa daaqad cusub), where intermediate replicas relay WAL to downstream replicas. This approach allows us to scale to potentially over a hundred replicas without overwhelming the primary. However, it also introduces additional operational complexity, particularly around failover management. The feature is still in testing; we’ll ensure it’s robust and can fail over safely before rolling it out to production.

Challenge: A sudden traffic spike on specific endpoints, a surge of expensive queries, or a retry storm can quickly exhaust critical resources such as CPU, I/O, and connections, which causes widespread service degradation.

Solution: We implemented rate-limiting across multiple layers—application, connection pooler, proxy, and query—to prevent sudden traffic spikes from overwhelming database instances and triggering cascading failures. It’s also crucial to avoid overly short retry intervals, which can trigger retry storms. We also enhanced the ORM layer to support rate limiting and when necessary, fully block specific query digests. This targeted form of load shedding enables rapid recovery from sudden surges of expensive queries.

Challenge: Even a small schema change, such as altering a column type, can trigger a full table rewrite⁠(ku furmaa daaqad cusub). We therefore apply schema changes cautiously—limiting them to lightweight operations and avoiding any that rewrite entire tables.

Solution: Only lightweight schema changes are permitted, such as adding or removing certain columns that do not trigger a full table rewrite. We enforce a strict 5-second timeout on schema changes. Creating and dropping indexes concurrently is allowed. Schema changes are restricted to existing tables. If a new feature requires additional tables, they must be in alternative sharded systems such as Azure CosmosDB rather than PostgreSQL. When backfilling a table field, we apply strict rate limits to prevent write spikes. Although this process can sometimes take over a week, it ensures stability and avoids any production impact.

This effort demonstrates that with the right design and optimizations, Azure PostgreSQL can be scaled to handle the largest production workloads. PostgreSQL handles millions of QPS for read-heavy workloads, powering OpenAI’s most critical products like ChatGPT and the API platform. We added nearly 50 read replicas, while keeping replication lag near zero, maintained low-latency reads across geo-distributed regions, and built sufficient capacity headroom to support future growth.

This scaling works while still minimizing latency and improving reliability. We consistently deliver low double-digit millisecond p99 client-side latency and five-nines availability in production. And over the past 12 months, we’ve had only one SEV-0 PostgreSQL incident (it occurred during the viral launch⁠(ku furmaa daaqad cusub) of ChatGPT ImageGen, when write traffic suddenly surged by more than 10x as over 100 million new users signed up within a week.)

While we’re happy with how far PostgreSQL has taken us, we continue to push its limits to ensure we have sufficient runway for future growth. We’ve already migrated the shardable write-heavy workloads to our sharded systems like CosmosDB. The remaining write-heavy workloads are more challenging to shard—we’re actively migrating those as well to further offload writes from the PostgreSQL primary. We’re also working with Azure to enable cascading replication so we can safely scale to significantly more read replicas.

Looking ahead, we’ll continue to explore additional approaches to further scale, including sharded PostgreSQL or alternative distributed systems, as our infrastructure demands continue to grow.