स्टार्टअप्स के लिए Postgres सर्वाइवल गाइड

 (hatchet.run)
3 पॉइंट द्वारा GN⁺ 4 시간 전 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • Hatchet ने 2 साल के प्रोडक्शन अनुभव में झेली समस्याओं के आधार पर, शुरुआती schema·query design से लेकर high-volume writes और table migration तक चरणबद्ध operational principles को व्यवस्थित किया है
  • तेज़ reads के लिए index और ORDER BY को मेल में रखना चाहिए, लेकिन query planner statistics और cost के आधार पर sequential scan चुन सकता है, इसलिए EXPLAIN ANALYZE से estimates और actual execution की तुलना करनी चाहिए
  • write performance और stability छोटे transactions, केवल ज़रूरी rows को lock करना, CREATE INDEX CONCURRENTLY, और connection pooling पर निर्भर करती है; batch processing ने Hatchet के माप में throughput को लगभग 10 गुना बढ़ाया
  • high-frequency write environments में default autovacuum settings dead tuples और transaction IDs को समय पर reclaim नहीं कर पातीं, और transaction ID wraparound आने पर भारी downtime हो सकता है
  • scale बढ़ने पर FOR UPDATE SKIP LOCKED आधारित job queue, partitioning, trigger और batch backfill का उपयोग करें, लेकिन ORM abstraction के बाहर SQL को सीधे नियंत्रित कर पाना ज़रूरी है

लक्षित पाठक और ORM की सीमाएँ

  • यह गाइड उन developers के लिए है जो SQL, row, table, index की बुनियादी अवधारणाएँ जानते हैं और production Postgres समस्याओं से निपटना चाहते हैं
  • Postgres manual व्यापक है, लेकिन outage जैसी स्थिति में उसे जल्दी संदर्भित करना कठिन होता है, इसलिए Hatchet के 2 साल के operational अनुभव को केंद्र में रखकर इसे संक्षेपित किया गया है
  • ORM इस्तेमाल करने पर भी ये सिद्धांत लागू होते हैं, लेकिन scale बढ़ने पर abstraction layer से बाहर निकलकर सीधे SQL लिखना पड़ता है ताकि कई optimizations संभव हो सकें
    • Prisma TypedSQL जैसी सुविधाओं से ORM और direct SQL साथ में इस्तेमाल किए जा सकते हैं
    • Go-आधारित Hatchet इसी तरह के व्यवहार के लिए sqlc का उपयोग करता है
    • Claude से query लिखवाने वाले environment में supabase/agent-skills की सिफारिश की गई है

बदलना मुश्किल schema design

  • deploy के बाद schema change सबसे कठिन होता है, इसलिए table और primary key का draft बनाने के बाद application को चाहिए queries लिखते हुए design को बार-बार सुधारना चाहिए
  • design प्रक्रिया में इन सवालों से table के उपयोग का आकलन किया जाता है
    • read और write में किसकी frequency ज़्यादा है
    • read करते समय सबसे अधिक उपयोग होने वाले filters कौन से हैं
    • सबसे अधिक update होने वाले columns कौन से हैं
  • database normalization के 1NF·2NF·3NF लागू किए जा सकते हैं, लेकिन कभी-कभी normal forms query efficiency या तेज़ development के लिए ज़रूरी usability से टकरा जाते हैं
    • कुछ स्थितियों में data को jsonb column में रखना ज़्यादा सरल होता है
  • schema design पर लागू किए गए thumb rules इस प्रकार हैं
    • primary key के लिए identity column वाला auto-increment integer या Postgres built-in UUID इस्तेमाल करें
    • identity columns bigserial से थोड़ा तेज़ होते हैं
    • time के लिए हमेशा timestamptz का उपयोग करें
    • हर table में primary key रखें
    • consistency और accuracy महत्वपूर्ण होने वाली low-volume tables में cascade delete सहित foreign keys का उपयोग करें, लेकिन high-volume environments में सावधानी रखें

read queries और indexes

  • तेज़ SELECT को समझने के लिए एक सरल मॉडल यह है कि Postgres index से किसी row को तेज़ी से ढूंढता है, या sequential scan (seq scan) से table की सभी rows पढ़ता है
  • तेज़ single-row lookup के लिए ये संरचनाएँ उपयोग होती हैं
    • explicit index
    • unique constraint, जो index का एक विशेष रूप है
    • primary key, जिसे Postgres अपने-आप index करता है
  • default index btree होता है, और इसे ऐसे समझा जा सकता है जैसे query के लिए optimized data रखने वाली एक अलग table हो
    • row lookup time लगभग log(n) होता है, जहाँ n table की row count है
  • अगर index उपयोग नहीं हो सकता तो sequential scan चलता है, लेकिन modern databases rows को memory में बहुत तेज़ी से लाती हैं, इसलिए 20,000 rows से कम वाली tables में यह लगभग तुरंत पूरा हो सकता है

join और composite index

  • inner join के target में सामान्यतः primary key का उपयोग होना चाहिए; नहीं तो schema design या normalization में समस्या हो सकती है
  • ON clause को भी WHERE clause की तरह समझना चाहिए, और join conditions पर उचित index होने चाहिए
  • बड़ी tables की list queries अक्सर application में सबसे पहले धीमी पड़ने वाली queries बनती हैं
    • अगर organization और creation time को साथ filter और sort करना है, तो composite index इस्तेमाल किया जा सकता है
CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);
  • complex queries में thumb rule यह है कि ORDER BY column को index के अंत में रखें और sort direction भी मेल में रखें
    • Postgres btree को दोनों दिशाओं में scan कर सकता है, इसलिए single column में DESC बेअर्थ हो सकता है, लेकिन composite index में इसे मेल में रखना बेहतर है
    • descending index के detailed behavior के लिए संबंधित सामग्री देखें

writes, locks, और migrations

  • सफल writes की पहली शर्त है transactions को छोटा रखना
    • कोई विशेष कारण न हो तो transaction के बीच external service को query न करें
  • दूसरी शर्त है केवल ज़रूरी rows को lock करना
    • किसी row को update करने पर transaction commit होने तक उस row पर lock रहता है
    • system load बढ़ने पर lock का असर और स्पष्ट दिखता है
  • किसी मौजूदा बड़ी table पर सामान्य CREATE INDEX चलाने से table lock हो जाती है और insert/update रुक जाते हैं, इसलिए हमेशा CREATE INDEX CONCURRENTLY का उपयोग करें
  • अच्छी schema migration क्षमता iteration की speed बढ़ाती है और uptime बेहतर करती है
    • जहाँ तक संभव हो column delete या removal से बचें और additive changes करें
    • जहाँ संभव हो transaction के अंदर execute करें ताकि rollback और partial apply की स्थिति संभाली जा सके
    • और उन्नत तरीके के रूप में expand and contract migration का उपयोग किया जा सकता है
  • migration में पहले यह जाँचना चाहिए कि क्या वह सभी writes को block करेगी
    • CONCURRENTLY के बिना index creation सभी writes रोककर downtime ला सकता है
    • ALTER TABLE operations की दोबारा समीक्षा करनी चाहिए, और बड़ी tables में check constraint जोड़ना भी writes block कर सकता है
    • check constraint को NOT VALID के साथ जोड़ने पर यह block टाला जा सकता है

connection management

  • हर query और transaction database connection का उपयोग करता है, और connections CPU तथा memory की दृष्टि से महंगे होते हैं, इसलिए उन्हें लंबे समय तक बनाए रखना चाहिए
  • बार-बार connections बनाना और हटाना resources की बर्बादी है
    • एक साथ बहुत सारे नए connections बनने वाली connection storm, Postgres internal locks से जुड़ी कठिन debugging समस्याएँ पैदा कर सकती है
  • पहले external connection pooler pgbouncer पर विचार करें; अगर वह उपलब्ध न हो तो in-memory connection pool विकल्प हो सकता है
    • Hatchet pgxpool for Go का उपयोग करता है क्योंकि वह यह मान नहीं सकता कि users का database external pooler इस्तेमाल कर रहा होगा

query planner और statistics

  • बहुत सारे joins वाली या कई join methods मिलाने वाली complex queries केवल index जोड़ देने से हल नहीं होतीं
    • index का अपना overhead भी होता है, इसलिए उन्हें असीमित रूप से नहीं जोड़ना चाहिए
  • query planner SQL को internal database operations में बदलता है और तय करता है कि index इस्तेमाल होगा या नहीं, लेकिन सीमित जानकारी के कारण वह हमेशा optimal plan नहीं चुन पाता
  • planner जिस जानकारी का उपयोग करता है वह table statistics है, जिसे pg_stats में देखा जा सकता है
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
  • statistics ANALYZE के समय इकट्ठी होती हैं और autovacuum चलने पर भी update होती हैं
    • autovacuum frequency बढ़ाने से query statistics भी अधिक up-to-date रहती हैं
    • query के गलत तरीके से चलने का एक सामान्य कारण analysis frequency का कम होना है
  • query को sequential scan हुआ या नहीं, इस सरल दृष्टिकोण से देखने पर micro-optimization के कारण planner की unpredictability बढ़ाने से बचा जा सकता है
    • primary key और index-केंद्रित lookups planner के लिए plan चुनना आसान बनाते हैं

execution plan analysis और sequential scan

  • कुछ providers, जैसे Google CloudSQL, query sampling करके slow queries store करते हैं, लेकिन हर service यह सुविधा नहीं देती
  • EXPLAIN ANALYZE query को वास्तव में चलाता है और table statistics के आधार पर expected rows तथा actual scanned rows की तुलना करता है
    • production में यह query सचमुच चलती है, इसलिए सावधानी ज़रूरी है
    • execution के बिना केवल plan देखना हो तो ANALYZE हटाकर EXPLAIN इस्तेमाल करें
  • detailed plan को JSON में save करके explain.dalibo.com पर visualize किया जा सकता है
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
  • अगर statistics और indexes सही होने के बावजूद sequential scan हो रहा है, तो planner ने संभवतः sequential scan की cost को कम आंका होगा
    • index वास्तविक table data वाले heap से अलग store होता है, इसलिए index से मिली कई rows को heap से फिर पढ़ने की लागत लगती है
    • अगर query को बड़े स्तर पर restructure नहीं किया जा सकता, तो sequential scan स्वीकार करना या partitioning पर विचार करना चाहिए

bulk writes और batch processing

  • हर query में database round-trip time, application connection pool से connection लेने का समय, और Postgres processing time जैसा overhead होता है
    • Postgres internal locks भी high-throughput environments में bottleneck बन सकते हैं
  • एक query में कई rows को बाँधने से ये लागतें कम की जा सकती हैं
    • सबसे सरल तरीका है implicit transaction के रूप में कई queries को server पर एक साथ भेजना
    • Go में pgx का SendBatch इस्तेमाल किया जा सकता है
  • Hatchet में batch processing से throughput लगभग 10 गुना बढ़ा, और insert optimization पर अतिरिक्त जानकारी तेज़ Postgres inserts गाइड में है

autovacuum और transaction ID wraparound

  • autovacuum dead tuples की सफाई और transaction ID management संभालता है, और high-frequency write environments में इसकी settings को adjust करना पड़ सकता है
  • tuple file system में stored row का एक version होता है
    • किसी row को update या delete करने पर भी पुराने versions तब तक बने रहते हैं जब तक उससे पहले शुरू हुए सभी transactions commit या rollback न हो जाएँ
    • वह version जिसे अब कोई transaction पढ़ नहीं सकता, dead tuple कहलाता है
  • अगर write speed बहुत अधिक हो, तो autovacuum dead tuples बनने की speed के साथ नहीं चल पाता और database की हालत तेज़ी से बिगड़ सकती है
  • pg_stat_activity में active processes देखते समय अगर autovacuum query लगभग 1 घंटे या उससे अधिक समय से चल रही हो, तो settings बदलने पर विचार करें
  • autovacuum reclaim करने से पहले अगर सभी transaction IDs समाप्त हो जाएँ, तो transaction ID wraparound होता है और भारी downtime आ सकती है

table और index bloat

  • Postgres disk के 8KB pages में rows store करता है, और अगर मौजूदा page में नई row नहीं आ सकती तो नया page बनाया जाता है
  • dead tuples reclaim होने के बाद page आंशिक रूप से खाली रह जाए तो table bloat हो सकता है, जिससे disk usage बहुत बढ़ जाती है
    • सबसे अच्छा बचाव यह है कि bloat होने से पहले autovacuum को tune किया जाए
    • जो tables पहले से bloated हों, उनमें pg_repack जैसी extension इस्तेमाल की जा सकती है
    • built-in VACUUM FULL लगभग कभी अच्छा विकल्प नहीं होता
    • Postgres 19 में concurrent table repacking के लिए REPACK...CONCURRENTLY आने की उम्मीद है, लेकिन Hatchet ने अभी इसका परीक्षण नहीं किया है
  • index bloat भी table bloat का एक विशेष रूप है, और उचित autovacuum settings से इसे कम किया जा सकता है
    • जो indexes पहले से bloated हों, उनके लिए built-in command REINDEX INDEX CONCURRENTLY उपयोग की जा सकती है

FOR UPDATE SKIP LOCKED आधारित concurrent processing

  • FOR UPDATE SKIP LOCKED चुनी हुई rows को वर्तमान transaction के लिए reserve करता है, जबकि दूसरी queries को बाधित नहीं करता
  • Hatchet इसे job queue में उपयोग करता है, जहाँ एक query में waiting tasks को lock करके उनका status RUNNING किया जा सकता है
WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;
  • यह उन स्थितियों में भी उपयोगी है जहाँ स्वतंत्र rows को एक साथ update करना हो या कई application instances किसी object की lease manage कर रहे हों
    • Hatchet इसका उपयोग कई engines में tenant lease बाँटने के लिए करता है

partitioning

  • Postgres की built-in partitioning table को timestamp या hash जैसे row values के आधार पर विभाजित करती है
  • time-series data और Hatchet के historical task data में इसके ये लाभ हैं
    • हर partition पर autovacuum स्वतंत्र रूप से चल सकता है, जिससे table की autovacuum processing capacity बढ़ती है
    • पुराने data को row-by-row delete करने के बजाय partition table को drop करके लगभग तुरंत हटाया जा सकता है
  • अगर planning stage में Postgres अनावश्यक partitions को हटा नहीं पाता, तो read queries में overhead आ सकता है
    • हाल की Postgres releases में partition pruning बेहतर हुई है
    • Hatchet का operational अनुभव Postgres partitioning लेख में संकलित है

बड़ी tables के बीच data move करना

  • यहाँ large table migration का अर्थ schema change नहीं, बल्कि एक table से दूसरी table में large-scale data move करना है
  • बहुत बड़ी table को एक single transaction में copy करने पर घंटों लग सकते हैं
    • लंबे transactions autovacuum के सामान्य कामकाज को रोकते हैं और dead tuple bloat बढ़ाते हैं
    • अगर पुरानी table में लगातार writes होती रहें, तो नई table में वह data प्रतिबिंबित नहीं होगा
  • Hatchet transaction के बाहर बड़े batch backfill चलाता है, और migration शुरू होने के बाद आने वाली नई writes को Postgres triggers से नई table में copy करता है
    • primary key के unique constraint का उपयोग duplicate writes रोकने के लिए किया जाता है

संबंधित पढ़ाई

1 टिप्पणियां

 
GN⁺ 4 시간 전
Hacker News की राय

  • अगर यह production database है, तो सबसे पहले backup और recovery plan बनाना चाहिए। High availability शुरुआत में वैकल्पिक हो सकती है, लेकिन survival guide में backup और recovery का न होना अजीब लगता है
    PostgreSQL backup के लिए क्या आज भी Barman(https://pgbarman.org/) का काफी इस्तेमाल होता है, यह जानने की उत्सुकता है

    • अगर आप PostgreSQL expert नहीं हैं, तो खुद इसे operate करने के बजाय RDS जैसी managed database इस्तेमाल करना बेहतर है। खुद host करके जो बचत होती है, वह proven high availability, backup·recovery, point-in-time recovery, और read replicas पाने की लागत की तुलना में बहुत कम है
    • मैं pgBackRest इस्तेमाल कर रहा हूँ। यह मेरे पुराने nightly backup self-built solution से बेहतर point-in-time recovery देता है, Backblaze B2 पर backup के लिए इसे अपेक्षाकृत आसानी से configure किया, और कोई खास समस्या भी नहीं हुई
    • ज़्यादातर मामलों में cron से pg_dump_all चलाकर उसे zstd से compress करने के बाद S3 या FTP जैसी जगह पर copy कर देना काफी होता है। डेटा बड़ा होने पर full backup का समय और लागत बोझ बनते हैं, लेकिन यह सरल तरीका भी काफी लंबे समय तक काम कर सकता है
    • अगर database power failure के दौरान भी durability की गारंटी देता है, तो atomic volume snapshots से backup लिया जा सकता है। Recovery time कम करने के लिए पहले checkpoint बनाना चाहिए, और data corruption रोकने के लिए snapshot की atomicity का पक्का होना ज़रूरी है
      AWS पर कई TB आकार के MongoDB को EBS snapshots से backup करके तेज incremental backup और recovery लागू की गई थी। Point-in-time recovery नहीं मिलती, लेकिन इसे घंटों के अंतराल पर बार-बार लिया जा सकता है, इसलिए PostgreSQL-specific tools के साथ चलाने लायक एक सहायक रणनीति है
    • अगर आप पहले से Kubernetes चला रहे हैं, तो CloudNativePG इस्तेमाल कर सकते हैं

  • कुछ बातें और जोड़ी जा सकती हैं। सामान्य UUIDv4 की जगह UUIDv7 इस्तेमाल करें, और deadlock से बचने के लिए सिर्फ lock की जाने वाली rows की संख्या ही नहीं, बल्कि सभी queries में lock order को id ASC जैसे deterministic तरीके से एकसमान रखना चाहिए
    EXPLAIN (GENERIC_PLAN) इस्तेमाल करने पर parameter placeholders को बनाए रखते हुए query copy की जा सकती है, और PostgreSQL वास्तविक values न जानता हो तब का optimization plan भी देखा जा सकता है। खाली या छोटी tables में SET enable_seqscan = off से index इस्तेमाल होने की संभावना जांची जा सकती है
    सबके द्वारा default में इस्तेमाल होने वाले B-tree indexes भारी होते हैं और आसानी से bloated हो सकते हैं, इसलिए अगर sorting या range search के बिना केवल simple lookups करने हैं, तो hash index पर भी विचार किया जा सकता है। Unique hash index नहीं बनाया जा सकता, लेकिन hash exclusion constraint से मिलता-जुलता असर लाया जा सकता है, और multi-column unique index इसमें supported नहीं है
    GIN·GiST indexes भी सीख लेना अच्छा है। MySQL users के लिए यह चौंकाने वाला हो सकता है, लेकिन full-text search में बदले बिना भी साधारण LIKE '%foo%' query को तेज किया जा सकता है

    • Deadlock सिर्फ तब नहीं होता जब lock की जाने वाली rows के set पर consistent ORDER BY न हो, बल्कि table lock order अलग होने पर भी होता है। अगर एक transaction table_a, table_b क्रम में lock करे और दूसरा transaction उल्टे क्रम में, तो हर table के भीतर ORDER BY और FOR UPDATE इस्तेमाल करने पर भी deadlock हो सकता है
      सिद्धांत में यह साफ है, लेकिन व्यवहार में debugging बहुत कठिन हो जाती है क्योंकि globally यह समझना पड़ता है कि हर write किन tables को छू रही है। किसी खास extension में मैं यह झेल चुका हूँ। JSONB key-value lookup के लिए GIN आज़मा रहा हूँ, और performance improvement बहुत बड़ा था; AND और OR के बीच performance का अंतर भी काफी था
    • किसी भी UUID को primary key की तरह इस्तेमाल करने पर primary key joins बहुत बार होते हैं, इसलिए इसकी लागत अधिक होती है और अक्सर फायदा कम मिलता है। Default के रूप में sequentially increasing primary key इस्तेमाल करना, और बाहरी exposure की जरूरत हो तो secondary index वाली UUIDv4 column जोड़ना ज़्यादा सुरक्षित है। यह जानने की जिज्ञासा है कि UUIDv7 की B-tree performance वास्तव में UUIDv4 से बेहतर है या नहीं
    • Sequential scan बंद करने पर, अगर कोई भी index मौजूद हो, तो क्या PostgreSQL उसी index को ज़बरदस्ती इस्तेमाल नहीं करेगा? इसलिए शायद इससे यह नहीं पता चलेगा कि सही index कौन-सा है
    • UUIDv7 और UUIDv4 conversion tools के रूप में https://github.com/ali-master/uuidv47 और https://github.com/stateless-me/uuidv47 कई बार साझा किए गए हैं

  • यह सलाह भी अच्छी है, लेकिन जिन startup के साथ काम किया, उनमें scalability से पहले ही संगठनात्मक समस्याएँ सामने आ गईं। ORM न इस्तेमाल करना, अर्थपूर्ण fields की जगह sequentially increasing primary keys का उपयोग करना, और JSONB को सिर्फ़ जब सच में ज़रूरत हो तब सीमित रूप से इस्तेमाल करना बेहतर है
    source data को insert-only append-only रखना चाहिए और उसे modify या delete नहीं करना चाहिए। performance और सुविधा के लिए denormalized सहायक tables बदली जा सकती हैं, लेकिन उन्हें source of truth नहीं बनाना चाहिए
    connection pool का उपयोग करें, लेकिन connections की संख्या पर ध्यान दें; अगर कोई समस्या नहीं है, तो शायद PgBouncer तक की ज़रूरत न पड़े। स्पष्ट कारण न हो तो explicit transactions से बचें, और उन्हें खोलकर RPC जैसे लंबे काम न चलाएँ; SERIALIZABLE भी लगभग कभी इस्तेमाल न करना बेहतर है
    अगर SELECT FOR UPDATE जैसी explicit locking की ज़रूरत पड़ रही है, तो संभव है कि design गलत हो। type int value के आधार पर एक table की rows को कई अर्थ देने जैसा type system फिर से न बनाएँ, और self-referencing node·edge tables से graph database की नकल भी न करें। ज़्यादातर चीज़ें सामान्य normalized tables से हल हो सकती हैं

    • जिस PHP backend पर काम कर रहा हूँ, उसमें permission checks आदि के लिए objects instantiate करने पड़ते हैं, इसलिए ORM बहुत उपयोगी है। ORM के बिना implement करने पर काफ़ी ज़्यादा काम लगता है; यह बुरा विकल्प क्यों माना जाता है, जानना चाहता हूँ
    • अगर developer salary सबसे बड़ा खर्च है, तो ORM मत इस्तेमाल करो वाला सिद्धांत विवादास्पद है। tables की business requirements, customer pressure, और तंग budget के बीच DBA और सही design पर लंबी चर्चा चलती रहे तब भी लागत बढ़ती रहती है, इसलिए type column या graph-जैसी संरचना से बचने वाले सिद्धांत भी कहना जितना आसान है उतना करना नहीं
    • जो startup जल्दी product launch करना चाहते हैं, उनके लिए ORM काफ़ी अच्छा विकल्प है। अगर N+1 query और lazy loading जैसे pitfalls समझते हों, तो query management और parameterization फिर से ख़ुद बनाने की तुलना में यह बेहतर समझौता है
      project के शुरुआती चरण में database schema पर ज़रूरत से ज़्यादा सोचने और जल्दबाज़ी में optimization करने के बजाय मैं product development पर समय देना पसंद करूँगा
    • मैंने SELECT FOR UPDATE को कई जगह उपयोगी पाया है; समस्या क्या है, यह जानना चाहता हूँ। यह भी जानना है कि append-only source of truth इस्तेमाल करने पर क्या ऐसी locking की ज़रूरत नहीं रहती
    • append-only source data आकर्षक है, लेकिन जिन कई systems पर काम किया, उनमें संदिग्ध फ़ायदे के लिए काफ़ी tables का storage बहुत बढ़ गया होता। यह उपयोगी technique है, लेकिन क्या इसे हर जगह लागू करने वाला सिद्धांत बनाना चाहिए, इस पर संदेह है
      इसके उलट, traditional mutable relational tables को source of truth रखकर triggers से change log लिखने का तरीका कैसा रहेगा, यह जानना चाहता हूँ

  • cascade delete पसंद नहीं है। ज़्यादातर developers database की बजाय Python, Node, Go जैसी application layer में रहते हैं, इसलिए table A की row हटाने पर table B का data भी गायब हो जाना जादू जैसा लग सकता है। गलत configuration होने पर यह और ख़तरनाक है, इसलिए long-term maintenance के लिए explicit delete statements बेहतर हैं; foreign keys सही तरह इस्तेमाल करने भर से भी consistency रखी जा सकती है
    बड़े tables की migration के pitfalls और workarounds सही हैं, लेकिन pg-osc जैसे tools पहले से मौजूद हैं। यह इतना सरल होना चाहिए कि एक command चलाएँ और फिर 24 घंटे तक data copy होते समय तनाव में नज़र रखें
    application और database deployment को शुरुआत से अलग करना चाहिए। schema और application changes को पूरी तरह एक साथ transaction में deploy नहीं किया जा सकता, इसलिए production में जाने के बाद नई columns को nullable बनाना या default value देना, और table/column names न बदलना जैसे backward-compatible schema changes ही करने की आदत ज़रूरी है
    schema management strategy भी जल्दी तय करनी चाहिए। ऐसा deployment process टालना चाहिए जिसमें senior developer अपने कंप्यूटर से production DB पर DDL manually चलाए; चाहें तो Liquibase या Flyway जैसे परिचित tools इस्तेमाल कर सकते हैं

    • declarative schema management tool pgschema बनाया है

  • query planner औसत स्थिति को optimize करता है, लेकिन application के लिए कभी-कभी worst case को optimize करना ज़्यादा उपयोगी होता है। औसत user के लिए rows कम थीं और एक खास index से 10ms में result आ जाता था, लेकिन heavy users के लिए वही query parameters के अनुसार 1 सेकंड से ज़्यादा लेती थी
    ज़्यादा complex query लिखकर अलग index path force किया; average performance थोड़ी धीमी हुई, लेकिन worst case भी 100ms से कम हो गया। कंपनी के लिए average में 10ms बचाने से timeout रोकना कहीं ज़्यादा महत्वपूर्ण था

  • SKIP LOCKED उस interactive transaction-based work queue में उपयोगी है जहाँ application काम करते समय transaction खुला रखकर rows lock करती है। high-performance applications में ऐसे transactions से ही बचना चाहिए और row को तुरंत pending में update कर देना चाहिए, इसलिए SKIP LOCKED की ज़रूरत नहीं पड़ती
    scale बढ़ने के साथ database memory में रखी जाने वाली state कम करनी चाहिए, और interactive transactions भी ऐसी ही state हैं। distributed environment में idempotency, atomicity से ज़्यादा फ़ायदेमंद है

  • long-running transactions database की स्थिति को नुकसान पहुँचा सकती हैं, इसलिए इन्हें सिर्फ़ मज़बूत कारण होने पर ही इस्तेमाल करना चाहिए। idle_in_transaction_session_timeout सेट करके idle transactions को लंबे समय तक locks या tuples पकड़े रखने से रोकना चाहिए, और migrations में lock_timeout सेट करना चाहिए ताकि एक DDL पूरे system को न रोक दे
    statement_timeout भी सेट करना चाहिए ताकि एक महँगी query पूरे system को पंगु न बना दे

  • startup के शुरुआती दौर में PostgreSQL चलाने के अनुभव से लगा कि यह लेख monitoring और alerts पर पर्याप्त ज़ोर नहीं देता। PostgreSQL में कुछ मुख्य failure types हैं जिनसे हर हाल में बचना चाहिए, और alerts से जोखिम जल्दी पकड़ा जा सकता है
    AWS अगर email भेजे कि transaction ID wraparound नज़दीक है, तो startup में, खासकर Boxing Day जैसे दिन, यह आसानी से छूट सकता है। AWS जिन signals को देखता है, उन्हें email नहीं बल्कि pager से जोड़ना चाहिए

  • connection pool implementations में एक बड़ा, कम-ज्ञात अंतर होता है। ज़्यादातर application connection pools FIFO से low latency और connection availability optimize करते हैं, लेकिन connections को लगातार warm रखते हैं, इसलिए अनावश्यक connections कम करना मुश्किल होता है
    PgBouncer और कुछ external poolers LIFO का उपयोग करते हैं, जिससे PostgreSQL तक पहुँचने वाले connections की संख्या और throughput optimize होते हैं। सबसे हाल का connection पहले reuse करने पर बचे हुए connections स्वाभाविक रूप से ठंडे होकर बंद हो जाते हैं
    नए applications के लिए FIFO काफ़ी है, लेकिन scale बढ़ने पर PgBouncer जैसे tools से सैकड़ों connections को लगभग 90% तक घटाना बेहतर है। PostgreSQL की प्रति-connection process बनाने वाली संरचना में connections कम हों तो वह बेहतर काम करता है

  • बहुत ही खास स्थितियों में application memory में join करके अच्छे परिणाम मिले। कई बार database round trip कम करने की कोशिश में JOIN, UNION, CASE से उलझी हुई एक single query बना दी जाती है
    इसके बजाय कई simple queries को स्वतंत्र रूप से चलाकर, फिर results को iterate करते हुए map से संबंधित rows को जोड़ें, तो round trip और iteration cost बढ़ने पर भी query plan ज़्यादा predictable हो सकता है और उल्टा यह अधिक फायदेमंद हो सकता है। इसे केवल सीमित रूप से इस्तेमाल करना चाहिए; सिर्फ इसलिए कि कुछ ORM अंदरूनी तौर पर ऐसा करते हैं, इसे बिना शर्त recommend नहीं किया जाता

    • इस तरीके का असर स्थिति पर बहुत निर्भर करता है। अगर join से मूल data से कहीं बड़ा cartesian product बनता है, तो सिर्फ मूल sets लाकर local में combine करना DB load और network traffic को कम कर सकता है
      लेकिन selective inner join मूल data से बहुत छोटा result बनाता है, इसलिए सभी records लाकर local में intersection और filtering करना कहीं ज़्यादा महंगा पड़ता है। Index join में query planner index का उपयोग करके अंधाधुंध table scan, sort, और filtering से बच सकता है
    • जटिल single query की जगह दो views बनाकर फिर join करने का तरीका भी इस्तेमाल होता है, ऐसा मुझे पता है