PostgreSQL इस्तेमाल करते समय काम आने वाले पैटर्न

• Postgres को ज़्यादा productive और सुरक्षित तरीके से इस्तेमाल करने में मदद करने वाले practical patterns का संकलन
• हर pattern छोटा है, लेकिन मिलकर बड़ा फर्क पैदा करता है

UUID primary key का उपयोग

• UUID random होता है, इसलिए sorting या index performance के लिहाज़ से इसके कुछ नुकसान हैं
• यह numeric ID की तुलना में ज़्यादा space लेता है
• लेकिन इसके ये फायदे हैं
  • DB से connect किए बिना भी UUID generate किया जा सकता है
  • इसे बाहर सुरक्षित रूप से expose किया जा सकता है
• gen_random_uuid() का उपयोग करके primary key के रूप में UUID को अपने-आप generate किया जा सकता है

created_at और updated_at field हमेशा जोड़ें

• debugging के दौरान यह जानना बहुत उपयोगी होता है कि record कब बना और कब बदला गया
• updated_at को trigger के ज़रिए अपने-आप update होने के लिए configure किया जा सकता है
• function एक बार बनानी होती है, और trigger हर table पर लागू करना पड़ता है

Foreign key पर on update restrict / on delete restrict सेट करें

• foreign key constraint सेट करते समय on update restrict on delete restrict का ज़रूर उपयोग करें
• इससे data delete करते समय गलती से cascading delete होने से बचाव होता है
• storage सस्ता है, लेकिन data recovery बहुत मुश्किल होती है, इसलिए conservative approach बेहतर है

Schema के उपयोग की सिफारिश

• default schema public होता है, लेकिन application बड़ा होने पर उसे अलग schema में बाँटना बेहतर है
• schema namespace की तरह काम करता है, और अलग-अलग schema के बीच भी join किया जा सकता है
• table की संख्या बढ़ने पर schema का उपयोग readability और maintainability के लिए फायदेमंद होता है

Enum table pattern का उपयोग

• PostgreSQL के enum type या check constraint की बजाय enum table का उपयोग ज़्यादा flexible होता है
• enum values को अलग table में manage करने पर metadata जोड़ना या enum values को आसानी से expand करना संभव होता है
• enum table की values को foreign key से refer करके constraint बनाए रखे जाते हैं

Table name singular रखें

• table name को plural की बजाय singular रखना बेहतर है
• query लिखते समय singular ज़्यादा स्पष्ट होता है, जबकि plural possessive या अर्थगत confusion पैदा कर सकता है

Join table का नाम mechanical तरीके से रखें

• many-to-many relation के लिए join table का नाम दोनों table नामों को जोड़कर रखना सुरक्षित और स्पष्ट होता है
• उदाहरण: person_pet
• combination पर unique index जोड़कर duplication रोकी जा सकती है

Delete की जगह soft delete का उपयोग

• data को वास्तव में delete करने की बजाय, revoked_at जैसे timestamp field का उपयोग करना बेहतर है जो delete का समय दिखाए
• इससे सिर्फ delete हुआ या नहीं, इतना ही नहीं बल्कि कब delete हुआ यह भी track किया जा सकता है
• Boolean value की तुलना में timestamp ज़्यादा जानकारी देता है

Status को log table से व्यक्त करें

• status को एक single column से दिखाने की बजाय, status change history को अलग table में store करें
• status कब हुआ, इसे valid_at column से स्पष्ट करें
• latest status को जल्दी query करने के लिए latest flag और unique index + trigger सेट करें
• यह async event processing या ऐसी स्थिति में फायदेमंद है जहाँ order गड़बड़ा सकता है

खास rows के लिए system_id जोड़ें

• enum table के अलावा भी कुछ खास "system rows" की ज़रूरत पड़ सकती है
• nullable system_id text field जोड़ें और unique index सेट करें
• system_id के ज़रिए किसी खास row को स्पष्ट रूप से query किया जा सकता है

View का न्यूनतम उपयोग करें

• view जटिल query को abstract करने में उपयोगी है, लेकिन इसका maintenance कठिन होता है
  • column हटाने पर view को फिर से बनाना पड़ता है
  • view के ऊपर view बनाने से performance और readability की समस्या हो सकती है
• इसलिए जितनी ज़रूरत हो उतना ही, सावधानी से इस्तेमाल करें

JSON query का सक्रिय उपयोग

• Postgres सिर्फ JSON store करने में ही नहीं, JSON return करने वाली queries में भी बहुत शक्तिशाली है
• nested relations को एक ही query में JSON रूप में return किया जा सकता है
• N+1 समस्या के बिना ज़रूरी सारा data एक बार में लाया जा सकता है
• नुकसान: type information का loss, और पूरे data को एक बार memory में लोड करना पड़ता है
• फिर भी performance और structure के लिहाज़ से इसके फायदे ज़्यादा बड़े हैं

[jhj0517]

> join table के नाम मशीनी तरीके से रखें

मुझे लगता है कि नाम रखते समय ऐसा कोई नियम होना अपने-आप में अच्छी बात है~

[halfenif]

अगर UUID7 पर विचार करें, तो क्या time-based sorting संभव नहीं होगी?

[winterjung]

PostgreSQL में UUID को primary key के रूप में इस्तेमाल करने के बारे में वाला लेख भी देखना उपयोगी रहेगा।

[t7vonn]

soft delete करते समय timestamp डालने का तरीका अच्छा है।
अगर primary key के रूप में UUID डालें तो time order में sort नहीं होता, इसलिए snowflake id या ulid का इस्तेमाल करना भी अच्छा रहेगा। हालांकि इस मामले में हर server के पास sequence number होना चाहिए।