کارهایی که نباید در PostgreSQL انجام داد!

PostgreSQL یکی از پایگاه های داده رابطه ای و بسیار محبوب در سراسر دنیا است. این پایگاه داده مانند MySQL با زبان SQL کار می کند اما ویژگی های بسیار بیشتری نسبت به MySQL دارد. این ویژگی های بیشتر طبیعتا پیچیدگی ها و گزینه های بیشتری را به همراه دارند بنابراین امکان اشتباه در آن بالاتر می رود. در این مقاله می خواهیم به اشتباهات رایج کاربران در هنگام استفاده از PostgreSQL اشاره کنیم. برخی از این اشتباهات بسیار مهم هستند اما برخی از آن ها بیشتر پیشنهاد و قرارداد را دارند. بسیاری از کاربران حرفه ای پس از سال ها تجربه کار با PostgreSQL نتیجه کارهایشان را در این مقاله قرار داده اند بنابراین دنبال کردن آن را شدیدا پیشنهاد می کنم.

سطح مقاله: این مقاله برای افرادی نوشته شده است که تا حدی با PostgreSQL آشنایی دارند و برای افراد مبتدی پیشنهاد نمی شود.

۱. از SQL_ASCII استفاده نکنید

در پایگاه داده PostgreSQL مفهومی به نام Character Sets وجود دارد که کدبندی متون نوشته شده در پایگاه داده را مشخص می کند. یکی از این Character Set ها SQL_ASCII نام دارد و به سادگی به معنی عدم تبدیل کاراکتر ها است. در حالت عادی اگر UTF-8 را انتخاب کنید، کاراکتر ها به UTF-8 تبدیل شده و سپس ذخیره می شوند اما با SQL_ASCII کاراکتر ها مستقیما ذخیره می شوند. در این حالت بایت های داده به عنوان encoding (کدبندی) جدید شناخته می شوند بنابراین در اکثر مواقع باعث می شود که چندین کدبندی مختلف در پایگاه داده شما ذخیره شوند و مشکلات زیادی را برایتان ایجاد کنند. بهترین گزینه، مخصوصا برای کاربران فارسی زبان، UTF-8 است.

۲. از psql -W استفاده نکنید

psql محیط ترمینال PostgreSQL است و از طریق آن می توانید پایگاه داده خود را مدیریت کنید. مشکل اینجاست که psql گزینه های مختلفی دارد که یکی از آن ها W- یا password-- است:

psql -W

psql --password

هر دو دستور بالا یک کار را انجام می دهند و آن پرسیدن رمز عبور قبل از اتصال به سرور است. به عبارتی حتی اگر سرور اصلا رمز عبوری نداشته باشد، این فلگ از شما می خواهد که رمز عبوری را وارد کنید.

هیچگاه نیازی به استفاده از این فلگ نیست. چرا؟ به دلیل اینکه اگر سرور رمز عبور داشته باشد خودش از شما می خواهد که رمز را وارد کنید و اصلا نیازی به فلگ W- ندارد. اگر هم رمز عبوری نداشته باشد مستقیما به پایگاه داده متصل می شوید. استفاده از این دستور باعث سردرگمی های مختلفی می شود و هیچ مزیتی ندارد بنابراین هیچ وقت از آن استفاده نکنید.

۳. از rules استفاده نکنید

همانطور که می دانید rule ها در PostgreSQL به شما اجازه می دهند قانون خاصی را برای داده هایتان بنویسید تا در هنگام اجرای دستورات INSERT یا UPDATE یا DELETE اعمال شده و رفتار پیش فرض را تغییر دهند. مثلا اگر بخواهیم داده های مربوط به کاربری با نام John و فامیلی Smith هیچ گاه ویرایش نشوند می توانیم چنین rule ای را برایش بنویسیم:

CREATE OR REPLACE RULE PROTECT_JOHN_SMITH

AS ON UPDATE TO EMPLOYEE

WHERE old.FIRST_NAME = 'JOHN'

AND old.last_name = 'SMITH'

DO INSTEAD NOTHING;

حالا اگر کوئری به شکل زیر را اجرا کنیم هیچ چیزی تغییر نمی کند:

UPDATE EMPLOYEE SET FIRST_NAME = 'ALIA' WHERE FIRST_NAME = 'JOHN';

چرا؟ به دلیل اینکه در rule خود گفته بودیم ردیفی که JOHN را به عنوان نام خود دارد نباید تغییر کند.

rule ها بسیار قدرتمند هستند اما در بسیاری از مواقع آن کاری را که فکر می کنید انجام نمی دهند. آیا متوجه منظورم می شوید؟ بسیاری از کاربران تصور می کنند rule ها مانند دستور CHECK در SQL هستند، یعنی تصور می کنند rule ها یک شرط می باشند که روی پایگاه داده قرار می گیرد اما در اصل کوئری اصلی شما را ویرایش کرده و کوئری های دیگری را به آن اضافه می کنند!

شما می توانید مقالات تخصصی را در این باره مطالعه کنید اما به طور کل پیشنهاد می شود از rule ها استفاده نکنید. در صورتی که نیاز به rule ها را در برنامه خود حس کردید، پیشنهاد می کنم به جای آن ها از trigger ها استفاده کنید.

۴. از ارث بری جداول استفاده نکنید

table inheritance یا ارث بری جداول یک مکانیسم پیشرفته و شیء گرا برای پایگاه داده است که به جداول اجازه می دهد خصوصیاتی را از هم به ارث ببرند و رابطه ای پدر و فرزندی ایجاد کنند (دقیقا مانند برنامه نویسی شیء گرا). این قابلیت معرفی شد تا کد های شیء گرا به پایگاه داده نزدیک شوند اما در طولانی مدت معلوم شد که این روش به شکل مورد انتظار کار نمی کند.

استفاده از ارث بری در جداول به جز ایجاد پیچیدگی اضافه در پروژه هایتان هیچ مزیت دیگری ندارد. من پیشنهاد می کنم به جای آن ها از روش استاندارد SQL به نام FOREIGN KEYS استفاده کنید که بسیار راحت تر است.

۵. از NOT IN استفاده نکنید

همانطور که می دانید زبان SQL دو دستور خاص به نام های NOT و IN را دارد و در بسیاری از مواقع این دو دستور با هم استفاده می شوند تا NOT IN را تشکیل بدهند. استفاده از این دستور دو مشکل اصلی را دارد.

مشکل اول رفتار غیر منتظره NOT IN هنگام کار با مقادیر null است. به مثال زیر توجه کنید:

select * from foo where col not in (1,null);

این دستور همیشه صفر ردیف را برمی گرداند چرا که با مقدار null سر و کار داریم. یک مثال دیگر:

select * from foo where col not in (select x from bar);

اگر یکی از مقادیر bar.x برابر null باشد هیچ ردیفی برایمان برگردانده نمی شود.

مشکل بعدی اینجاست که دستور NOT IN (SELECT ...) دیگر بهینه سازی نمی شوند بنابراین در پس زمینه PostgreSQL تبدیل به hashed Subplan یا plain Subplan می شوند که از نظر سرعت اصلا بهینه نیست. در واقع hashed subplan سرعت خوبی دارد اما PostgreSQL فقط اجازه می دهد مجموعه داده های کوچک از طریق آن منتقل شوند، بنابراین برای داده های بزرگتر به plain subplan منتقل می شویم که به طرز وحشتناکی کُند است. با این حساب داده ها در حجم کوچک مشکلی نخواهند داشت اما در حجم های بزرگ تر ۵ برابر یا بیشتر کند خواهند بود.

هرگاه خواستید از NOT IN استفاده کنید به جایش به سراغ NOT EXISTS بروید چرا که همان کار را انجام می دهد. تنها استفاده مناسب از NOT IN هنگامی است که می خواهید داده ها را از یک مجموعه نتایج کوئری خارج کرده و دور بیندازید (مخصوصا زمانی که null در داده ها باشد) بنابراین دستوری مانند دستور زیر مشکلی ایجاد نمی کند:

NOT IN (list,of,values,...)

۶. از حروف بزرگ در نام جداول و ستون ها استفاده نکنید

پایگاه داده PostgreSQL نام جداول، ستون ها، توابع و همه چیز را به حروف کوچک تبدیل می کند مگر آنکه آن را در علامت double quotation (علامت ") قرار داده باشید. با این حساب کدی مثل create table Foo یک جدول به نام foo را ایجاد می کند در حالی که "create table "Bar یک جدول به نام Bar را ایجاد خواهد کرد. در این حالت تمام دستورات زیر صحیح بوده و کار می کنند:

select * from Foo

select * from foo

select * from "Bar"

اما دستورات زیر اجرا نشده و به شما خطا می دهند که جدول مورد نظر وجود ندارد:

select * from "Foo"

select * from Bar

select * from bar

با این حساب با قرار دادن حروف بزرگ در نام جدول ها و ستون ها فقط برای خودتان دردسر درست می کنید. در چنین حالتی مجبور هستید همیشه نام آن ها را درون double quotation قرار بدهید که بسیار گیج کننده است. مشکل اصلی از آنجایی شروع می شود که شروع به استفاده از ORM ها یا ابزارهای دیگر اتصال به پایگاه داده کنید. این ابزارها نمی دانند که باید نام ستون شما را درون double quotation قرار بدهند و طبیعتا در این حالت برنامه شما crash می شود.

پیشنهاد می کنم همیشه از حروف a-z و اعداد 0-9 استفاده کنید و از چیز دیگری در نام گذاری هایتان استفاده نفرمایید. همچنین در صورتی که نیاز به نمایش ستون ها با حروف بزرگ در گزارشات خود دارید می توانید از alias ها استفاده کنید. به طور مثال:

select character_name as "Character Name" from foo

۷. از BETWEEN استفاده نکنید

دستور BETWEEN از یک مقایسه بسته بازه ای استفاده می کند. یعنی چه؟ یعنی مقادیر ابتدا و انتهای آن بازه شامل عملیات مقایسه می شوند. به این مثال توجه کنید:

SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

من در کوئری بالا گفته ام از جدول blah تمام ردیف هایی را دریافت کن که در آن ها ستون timestampcol بین روز اول از ماه ششم سال ۲۰۱۸ و روز هشتم از همین ماه باشد. فرمت ذخیره زمان در ستون timestampcol از نوع timestamp است (تعداد ثانیه های سپری شده از سال ۱۹۷۰).

این مقایسه دقیقا تا پایان 2018-06-08 00:00:00.000000 (ساعت ۱۲ شب روز هشتم) اعمال می شود اما در ساعات بعدی همان روز نخواهد بود. این در حالی است که ما گفتیم از روز اول تا روز هشتم را شامل بشود و این به معنی این است که تا آخر روز هشتم نخواهد بود بلکه فقط تا اولین لحظه آن روز می باشد. با این حساب ممکن است تصور کنید کوئری هایتان کار می کنند اما به محض آنکه یکی از ردیف هایتان دقیقا در نیمه شب (ساعت ۱۲) ثبت شده باشد، دو بار آن را می شمارید!

برای حل این مشکل از اپراتورهای مقایسه ای و کلیدواژه AND استفاده کنید:

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

۸. از timestamp بدون time zone استفاده نکنید

هیچ گاه از نوع داده timestamp برای ذخیره زمان استفاده نکنید، بلکه به جای آن از timestamptz استفاده کنید. timestamptz علاوه بر timestamp منطقه زمانی یا timezone را نیز مشخص می کند. این مسئله باعث می شود که زمان را بر اساس منطقه زمانی مورد نظر شما نمایش دهد و حتی می توانید آن را به سلیقه خودتان تغییر دهید تا در منطقه زمانی دیگری نمایش داده شود. این مسئله باعث می شود تا زمان ذخیره شده در آن قابل تبدیل به مناطق زمانی مختلف باشد.

این در حالی است که نوع داده timestamp (بدون منطقه زمانی) هیچ کدام از این کار ها را انجام نمی دهد. استفاده از timestamp مانند این است که از تقویم و ساعت خانه خودتان عکس گرفته و برای دوستتان در کشور دیگری ارسال کرده باشید. او نمی تواند ساعت را به زمان خودش ببیند. این مسئله باعث می شود زمان ذخیره شده در تابستان و زمستان یا زمان ذخیره شده در مناطق زمانی مختلف در عملیات های تبدیل با مشکل مواجه شوند.

با این حساب می توان گفت که اگر قصد شما فقط ذخیره timestamp در پایگاه داده و سپس دریافت و نمایش آن است، استفاده از timestamp مناسب شما است و هیچ مشکلی ندارد اما اگر قصد تبدیل زمان و اجرای عملیات محاسباتی روی آن را دارید باید از نوع داده timestamptz استفاده کنید.

۹. از timestamp برای ذخیره زمان UTC استفاده نکنید

این نکته شباهت بسیار زیادی به نکته قبلی دارد. در صورتی که می خواهید زمانی را در قالب UTC ذخیره کنید حتما از timestamptz استفاده نمایید تا timezone نیز مشخص شود. چرا؟ به دلیل اینکه اگر از timestamp خالی استفاده کنید، پایگاه داده نمی تواند بفهمد فرمت زمان ذخیره شده در قالب UTC است.

همانطور که می دانید PostgreSQL برخلاف بسیاری از پایگاه های داده می تواند با timezone ها کار کند. در صورتی که زمان را فقط با timestamp خالی ذخیره کنید، توابع کاربری ما برای تبدیل زمان کاربرد نخواهند داشت و مجبور می شوید ازکوئری های پیچیده مانند کوئری زیر استفاده کنید. من در کوئری زیر گفته ام «نیمه شب گذشته در یک منطقه زمانی خاص در u.timezone» که به شکل پیچیده زیر درآمده است:

date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

همچنین اگر بخواهیم بگوییم «نیمه شب قبل از x.datecol در u.timezone» با یک کوئری پیچیده و عجیب روبرو می شویم:

date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

۱۰. از timetz استفاده نکنید

نوع داده timetz استفاده نکنید، بلکه به جایش از timestamptz استفاده نمایید. حتی documentation رسمی PostgreSQL نیز این موضوع را توضیح داده است:

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

پاراگراف بالا توضیح می دهد که پیاده سازی نوع داده timetz فقط به دلیل پیروی از استاندارد های SQL بوده است و از نظر عملی کارایی زیادی ندارد. در این پاراگراف توضیح داده شده است که ترکیب date و time و timestamp و timestamptz ابزار کاملی برای کار با تاریخ را در اختیار شما قرار می دهد و هیچ نیازی به timez وجود ندارد.

۱۱. از تابع CURRENT_TIME استفاده نکنید

تابع CURRENT_TIME زمان فعلی را در قالب timez برمی گرداند و من در مورد قبلی توضیح دادم که چرا timez نوع داده جالبی نیست. ما انواع توابع کاربردی و مناسب دیگر را داریم که جایگزین مناسبی برای CURRENT_TIME هستند:

• اگر می خواهید timestamp ای را به همراه منطقه زمانی داشته باشید از تابع ()now استفاده کنید.
• اگر می خواهید timestamp ای را بدون منطقه زمانی داشته باشید از LOCALTIMESTAMP استفاده کنید.
• اگر نوع داده Date را می خواهید از CURRENT_DATE استفاده کنید.
• اگر نوع داده time را می خواهید از LOCALTIME استفاده کنید.

همانطور که در چهار مورد بالا می بینید ما انواع توابع را برای دریافت انواع تاریخ و زمان داریم بنابراین هیچ نیازی به استفاده از CURRENT_TIME نیست.

۱۲. از (0)timestamp یا (0)timestamptz استفاده نکنید

از معیار دقت در توابع timestamp و timestamptz استفاده نکنید، مخصوصا اگر عدد صفر مد نظرتان است. بسیاری از افراد تصور می کنند اضافه کردن معیار دقت به این دو تابع باعث حذف بخش انتهایی و اعشاری timestamp می شود و از آن برای حذف میلی ثانیه استفاده می کنند اما اتفاقی که در پس زمینه می افتد این است که timestamp شما گِرد می شود نه اینکه بخش انتهایی آن حذف شود. این موضوع می تواند باعث ایجاد مشکلات مختلفی در برنامه شما شود. به طور مثال اگر از تابع ()now برای ثبت زمان در پایگاه داده استفاده کنید زمان ثبت شده چیزی حدود نیم ثانیه بعد خواهد بود. در برنامه هایی که نسبت به زمان حساس هستند این موضوع می تواند تمام برنامه را بهم بریزد.

بهتر است به جای آن از روشی مثل روش زیر استفاده کنید:

date_trunc('second', blah)

تابع date_trunc این کار را به راحتی برایتان انجام می دهد بنابراین هیچ نیازی به اضافه کردن معیار دقت به timestamp و timestamptz نیست.

۱۳. از (char(n استفاده نکنید

بهتر است همیشه به جای نوع داده (char(n از نوع داده text استفاده کنید. چرا؟ هر رشته ای که در قالب (char(n ثبت شود باید دقیقا به طول n باشد بنابراین اگر n برابر ۱۰ باشد اما رشته شما ۵ حرف داشته باشد، PostgreSQL به صورت خودکار ۵ اسپیس دیگر را به رشته شما اضافه می کند تا حتما ۱۰ کاراکتر داشته باشیم. به زبان ساده تر رشته ای مانند "Hello" به صورت "Hello     " ذخیره می شود.

از طرف دیگر در هنگام مقایسه دو رشته در PostgreSQL، اسپیس های انتهایی یک رشته در عملیات مقایسه نادیده گرفته می شوند. حالا اگر collation پایگاه داده شما نسبت به اسپیس حساس باشد، عملیات های مختلف را بهم خواهد ریخت. مثلا به کوئری زیر توجه کنید:

SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)

نتیجه این کوئری true خواهد بود! همچنین زمانی که از دستوراتی مانند LIKE یا از عبارات باقاعده (regular expressions) استفاده می کنید نیز شاهد بروز رفتار های بسیار عجیبی خواهید بود. با این حساب استفاده از char و انتخاب طول رشته برای آن فضای پایگاه داده را با اسپیس های اضافی اشغال می کند.

در بسیاری از اوقات افراد می گویند ما نیاز به فیلدی داریم که باید طول ثابت داشته باشد. مثلا کد کشور ها یا شماره تلفن و الی آخر. در این حالت باز هم بهتر است از text یا نوع داده های شبیه به آن استفاده کرده و به جای تعیین طول رشته، سعی کنید یک قانون برای چک کردن آن فیلد تعیین کنید. به طور مثال:

CHECK(length(VALUE)=3)

CHECK(VALUE ~ '^[[:alpha:]]{3}$')

هر دو چک بالا بررسی می کنند که طول رشته VAVLUE حتما سه کاراکتر باشد. اولین چک با تابع length و CHECK انجام شده است و دومین چک با عبارات با قاعده انجام می شود.

۱۴. از (varchar(n به صورت پیش فرض استفاده نکنید

بهتر است به صورت پیش فرض از (varchar(n استفاده نکنید بلکه از varchar عادی (بدون محدودیت تعداد کاراکتر) یا از text استفاده کنید. (varchar(n به ما اجازه می دهد رشته ای به طول n کاراکتر را وارد پایگاه داده کنیم اما اگر تعداد کاراکتر های رشته از n بیشتر باشد یک خطا برایمان برگردانده می شود.

نوع داده varchar (بدون محدودیت) و text از نظر سرعت با (varchar(n یکی هستند. اگر می خواهید فیلد شما در پایگاه داده محدودیت کاراکتر داشته باشد، (varchar(n انتخاب خوبی است اما باید خوب به این موضوع فکر کنید. مثلا اگر به صورت پیش فرض تصور می کنید (varchar(n بهتر است و از آن برای ذخیره کردن username با محدودیت ۲۰ کاراکتری استفاده می کنید دچار مشکل می شوید. من از شما سوالی دارم: عدد ۲۰ کاراکتر را از کجا به دست آورده اید؟ اگر کاربری بخواهد username ای بیشتر از ۲۰ کاراکتر داشته باشد چطور؟

استفاده از (varchar(n لزوما بد نیست اما باید به عواقب کارتان فکر کنید. مثلا اگر می خواهید حداکثر طول username شما ۲۰ کاراکتر باشد مشکلی نیست اما یادتان باشد که این موضوع را در همه جای برنامه خود اعمال کنید. مثلا قبل از ارسال username به سمت پایگاه داده حتما مطمئن شوید که مقدار آن کمتر از ۲۰ کاراکتر است در غیر این صورت با ارسال آن مقدار به پایگاه داده باعث ایجاد خطا می شوید.

اگر تنها نگرانی شما محدودیت کاراکتر است می توانید از همان CHECK استفاده کنید که بالاتر توضیح دادم.

۱۵. از serial استفاده نکنید

نوع داده serial از قدیم در PostgreSQL برای ایجاد ستون های auto increment استفاده می شد یعنی ستون هایی مانند id که باید به صورت خودکار با اضافه شدن یک ردیف جدید افزایش پیدا کنند. مشکل اینجاست که serial رفتارهای عجیبی را از خودش نشان می دهد که می تواند باعث ایجاد مشکلاتی در برنامه های شما شود. به طور مثال serial باعث سخت تر شدن تنظیم schema و وابستگی ها و مدیریت سطح دسترسی (permission) می شود. شما می توانید این مشکلات را به صورت مفصل در این مقاله مطالعه کنید.

بهترین راه حل استفاده از ستون های identity است که در نسخه ۱۰ از PostgreSQL معرفی شدند و یکی از استاندارد های SQL نیز هستند. با این حساب فقط زمانی از serial استفاده کنید که:

• مجبور هستید از نسخه های قبل از نسخه ۱۰ پایگاه داده PostgreSQL استفاده کنید.
• مجبور هستید از ارث بری جداول استفاده کنید.
• اگر از یک توالی خاص برای چندین جدول و به صورت همزمان استفاده می کنید.

۱۶. از trust برای احراز هویت در TCP/IP استفاده نکنید

از سیستم احراز هویت trust در متدهای TCP/IP (مانند host یا hostssl) استفاده نکنید. مخصوصا از نوشتن چنین دستوری در فایل pg_hba.conf پرهیز کنید:

host all all 0.0.0.0/0 trust

انجام این کار به هر کسی در اینترنت اجازه می دهد سعی در احراز هویت در پایگاه داده شما بکند. documentation رسمی PostgreSQL لیستی از متدهای احراز هویت از راه دور (remote connection) را در اختیار شما قرار داده است که از trust بهتر هستند. شما می توانید به راحتی یک سیستم احراز هویت با رمز عبور را برای پایگاه داده خود فعال کنید.

documentation رسمی PostgreSQL می گوید:

trust authentication is only suitable for TCP/IP connections if you trust every user on every machine that is allowed to connect to the server by the pg_hba.conf lines that specify trust. It is seldom reasonable to use trust for any TCP/IP connections other than those from localhost (127.0.0.1).

یعنی سیستم احراز هویت trust فقط برای زمانی مناسب است که مطمئن هستید تمام افراد مجاز به اتصال به پایگاه داده (مشخص شده در فایل pg_hba.conf) صادق هستند. تقریبا هیچ شرایطی وجود ندارد که بخواهیم به غیر از اتصالات از طریق localhost از سیستم trust استفاده کنیم.

زمانی که از سیستم trust استفاده می کنید، هر کاربری می تواند خودش را به عنوان یک حساب کاربری در PostgreSQL معرفی کند و اگر داده های مناسب را داشته باشد می تواند وارد آن حساب شود.

امیدوارم این نکات در مدیریت پایگاه داده PostgreSQL به شما کمک کرده باشد.

منبع: وب سایت postgresql