Skip to main content

Scaling PostgreSQL

Chalo mani laie k aapde je business ma 6ie ae bau vadhare successful thai gyo. roj na millions of orders aave 6e. aapda system ma anek products 6e and customers pan roj na vadhe 6e.

Aa bau sari vaat 6e pan, clients ne response time ma bau delay thava lagyu 6e. Client notice kare 6e k aa application sau bandh j hoi athva offline thai gay hoi aej rite chale 6e.

Tame proper monitoring set karelu 6e OTEL ni madad thi and tame notice karo 6o k main problem je 6e ae database side thi 6e.

To have, better rehse jo aapde ae check karie k aapde database ni aa performance issue ne solve kai rite kari sakie 6ie.

Database na aa problem ne 3 rite solve kari sakie.

1) Optimize the database​

i. query bau j vadhare kharab rite lakheli 6e. (Badly written queries) jene karne aapde server ne database mathi bau vadhare data aapie 6ie.

ii. jo indexes proper set na kari hoi athva bau o6i set kari hoi to pan aa issue aavi sake 6e.

iii. Configuration parameters properly tuned na hoi - shared_buffers, work_mem, effective_cache_size etc. ni values system ni capacity ane workload ne match na karta hoi.

iv. Connection pooling na hoi - har request mate new connection create thai ane aa overhead database ne slow kari de.

v. VACUUM ane ANALYZE regularly na chalata hoi jene karne table bloat vadhi jay ane statistics outdated thai jay.

vi. Unnecessary locks - long running transactions athva improper transaction isolation levels jene karne blocking thay.

vii. N+1 query problem - loop ma repeated queries execute thai instead of JOIN athva batch operations use karva.

viii. Large result sets without pagination - ek j query ma lakho records fetch karva ni try karta hoi.

ix. Missing or outdated statistics - query planner ne optimal execution plan choose karva mate accurate statistics na male.

x. Disk I/O bottleneck - slow storage, fragmented data, athva inadequate IOPS jene karne read/write operations slow thay.

xi. Inadequate VACUUM frequency - Auto-vacuum settings properly configured na hoi athva manual VACUUM rare j chalavi hoi. Aathi dead tuples accumulate thay, table bloat vadhto jay ane query performance degrade thay. Especially high-transaction tables mate critical che.

xii. Not leveraging materialized views - Complex aggregations ane joins ne repeatedly calculate karva instead of materialized views use karva. Particularly reporting queries mate aa bau useful che jyare data real-time update ni zarurat na hoi.

xiii. Inappropriate data types - VARCHAR(255) jyare CHAR(5) j enough hoi, TEXT jyare ENUM better fit hoi, INTEGER jyare SMALLINT kaam kari shake, athva timestamp jyare date j zaruri hoi. Wrong data types extra storage waste kare che ane index operations slow kare che.

xiv. Poor normalization or over-normalization - Denormalized tables ma data redundancy ane update anomalies, over-normalized tables ma excessive JOINs. Balance na hoi to read/write performance both j suffer kare che. Transaction-heavy tables mate proper normalization critical che.

aa badha issues ne aapde optimization and proper setup ni madad thi solve kari sakie, jem k bad query na case ma aapde query ne rewrite kari sakie. jo indexing proper set nathi to proper indexes set kari sakie, etc... pan ghani vakhat aa solve na thai sake jem k aapda hardware ni ek limit aavi gai 6e, have aena thi vadhare load aapdu hardware sahan kari sake aem nathi to have aapdi pase baki rahela bija 2 option j 6e.

2) Buy a more capable hardware (Vertical Scaling)​

jo optimize karva thi problem solve nathi thati to ae case ma aapde ek vadhare powerful server buy/rent karsu. aane kahevai 6e vertical scaling.

For example, aapde 16 GB, 4 core CPU thi 64 GB 16 core CPU ma shift thaie.

Cloud na case ma aapde simple host machine na configuration ma change karva ni jarur 6e, tyaar pa6i simply aapde postgresql.conf file ma shared_buffers & and bija parameters ne change karva na hoi 6e.

Aa scaling no advantage ae 6e k koi prakar na code ma changes required nathi.

aa vertical scaling ek limit sudhi sakya 6e pa6i ae possible 6e k aapde ek limit thi vadhare hardware configurations bhi vadhari nahi sakie.

aa prakar na server bhi khub costly hoi 6e.

to aa case ma solution 6e, horizontal scaling. means use multiple servers.

3) Use multiple servers (Horizontal Scaling)​

Horizontal scaling ma aapde 16 code CPU and 64 GB RAM ma stuck nai thai jaie. aapde pase vadhare better option 6e.

Jo aapde multiple servers ne ek network ma connect kari ne aa darek server ne same purpose mate use karie to aene kahi sakai horizontal scaling.

vertical ma jya aapde vadhare ne vadhare powerful server banavta jaie 6ie tya horizontal scaling ma aavu nahi thaai.

horizontal scaling ma aapde normal required hardware configurations wada multiple servers ne aapde ek bija sathe connect kari ne ek prakare cluster create karie 6ie.

horizontal scaling ma aapde bau sara level ma system ne scale kari sakie 6ie, pan aa facility sathe distributed system ni complexity pan aave 6e.

distributed system ma aapdi pase alag alag options hoi 6e jema thi aapde choose karvu pade 6e, jene karne aapde thodi side choose karvi pade 6e like, read bound workload handle kari sake aevu configuration karvu 6e k write bound workload handle kari sake avu configuration karvu 6e.