۱۰ افزونه پرکاربرد پایگاه داده PostgreSQL

در این مستند می خواهیم ۱۰ افزونه پرکاربرد در پایگاه داده Postgresql را معرفی و بررسی کنیم.

1. POSTGIS

PostGIS یک افزونه منبع باز قدرتمند است که PostgreSQL را قادر می سازد اشیاء جغرافیایی و داده های مکانی را مدیریت کند. این امر نیاز به سیستم های تخصصی مجزا را از بین می برد و امکان اجرای پرس و جوهای مکان را در SQL فراهم می کند.

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

در اینجا سه مثال از نحوه استفاده از پسوند PostGIS آورده شده است:

ایجاد یک جدول با قابلیت spatially:

CREATE TABLE spatial_data (

    id SERIAL PRIMARY KEY,

    name VARCHAR,

    location GEOMETRY(Point, 4326) — 4326 is the SRID (Spatial Reference Identifier) for WGS 84

);

پرس و جو از داده های مکانی به عنوان مثال، برای پیدا کردن تمام نقاط در ۱۰۰۰ متر از یک نقطه معین:

 

SELECT * FROM spatial_dataWHERE ST_DWithin(location, ST_GeomFromText(‘POINT(-73.975972 40.782865)’, 4326), 1000);

 

Spatial joins، اگر می خواهید نقاطی را در یک چند ضلعی پیدا کنید:

SELECT p.name, pg.name AS polygon_nameFROM points pJOIN polygons pgON ST_Within(p.location, pg.location);

این نمونه ها تنها قسمتی از قابلیت های PostGIS را نشان می دهند. این یک برنامه افزودنی همه کاره است که پتانسیل ساخت برنامه‌های کاربردی آگاه از مکان، مدیریت داده‌های مکانی در پروژه‌های GIS و انجام تحلیل‌های پیچیده در پایگاه داده PostgreSQL را باز می‌کند.

مستند معرفی پایگاه داده Postgresql

2. HSTORE

ماژول hstore یک افزونه Postgres است که به شما امکان می دهد مجموعه هایی از جفت های کلید-مقدار را به عنوان یک مقدار واحد در جدول PostgreSQL ذخیره و دستکاری کنید.

پسوند hstore به گونه ای طراحی شده است که سبک و کارآمد باشد. این به تیم های داده اجازه می دهد تا داده های نیمه ساختار یافته را در یک پایگاه داده رابطه ای ذخیره کنند.

 

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

ایجاد جدول با ستون hstore:

CREATE TABLE products (    product_id SERIAL PRIMARY KEY,    product_name VARCHAR,    properties hstore);

 

پرس و جو داده ها از ستون hstore. فرض کنید می‌خواهید همه محصولات با ویژگی «رنگ» را به‌عنوان «قرمز» بازیابی کنید:

SELECT * FROM productsWHERE properties -> ‘color’ = ‘red’;

حذف یک جفت کلید-مقدار از ستون hstore. بنابراین، برای حذف ویژگی “وزن” از یک محصول خاص:

UPDATE productsSET properties = delete(properties, ‘weight’)WHERE product_id = 1;

 

3. PG_STAT_STATEMENTS

pg_stat_statements یک افزونه داخلی PostgreSQL است که راهی برای جمع آوری و ردیابی آمار در مورد دستورات SQL اجرا شده در پایگاه داده ارائه می دهد.

اطلاعاتی مانند کل زمان اجرا، تعداد تماس‌ها و تعداد ردیف‌های برگردانده شده برای هر دستور SQL را ثبت می‌کند.

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

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

برای مشاهده آمار استعلام:

SELECT query, total_time, calls, rowsFROM pg_stat_statementsORDER BY total_time DESCLIMIT 10;

این پرس‌وجو ۱۰ عبارت SQL زمان‌بر را از نظر کل زمان اجرا و تعداد تماس‌ها و ردیف‌های برگشتی نشان می‌دهد.

اگر می خواهید آمار جمع آوری شده را بازنشانی کنید:

SELECT pg_stat_statements_reset();

 

با این کار تمام آمار جمع آوری شده به صفر می رسد.

گزینه های پیکربندی:

pg_stat_statements برخی از گزینه های پیکربندی را ارائه می دهد که می توانید آنها را در فایل postgresql.conf یا در یک جلسه تنظیم کنید. به عنوان مثال، می توانید تعداد پرس و جوها را برای ردیابی تنظیم کنید یا مشخص کنید که متن پرس و جو شامل یا حذف شود.

برای پیگیری همه statements:

ALTER SYSTEM SET pg_stat_statements.max ON;

برای گنجاندن متن پرس و جو در آمار:

ALTER SYSTEM SET pg_stat_statements.track = all;

 

برای حذف متن پرس و جو در آمار:

ALTER SYSTEM SET pg_stat_statements.track = none;

مستند مدیریت افزونه ها در Postgresql

 

4. PGCRYPTO

pgcrypto یک افزونه Postgres است که توابع رمزنگاری و قابلیت های رمزگذاری داده ها را در پایگاه داده فعال می کند.

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

توسعه دهندگان می توانند عملیاتی مانند هش کردن، رمزگذاری و رمزگشایی را مستقیماً در پرس و جوهای SQL یا توابع PL/pgSQL انجام دهند.

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

HASHکردن رمز عبور و ذخیره آن در جدول «کاربران»:

INSERT INTO users (username, password_hash)VALUES (‘user123’, crypt(‘password123’, gen_salt(‘bf’)));

رمزگذاری داده های حساس:

INSERT INTO sensitive_data (id, encrypted_info)VALUES (1, pgp_sym_encrypt(‘sensitive_info’, ‘passphrase’));

ایجاد هش رمزنگاری، مانند HASH SHA-256 از یک رشته:

SELECT digest(‘Hello, world!’, ‘sha256’);

 

۵٫ CITEXT
citext مخفف «متن بدون حروف بزرگ» است. این به کاربران پایگاه داده اجازه می دهد تا داده های متنی را به روشی غیر حساس به حروف کوچک ذخیره و مقایسه کنند. با استفاده از نوع داده citext، آنها می توانند متن را بدون در نظر گرفتن حروف مورد مقایسه کنند.
این به ویژه برای احراز هویت کاربر (مقایسه نام کاربری و رمز عبور)، جستجوی سوابق بر اساس نام یا عناوین، و انجام پرس و جوهایی که به حروف بزرگ و کوچک حساس هستند مفید است.
بدون این پسوند، توسعه‌دهندگان معمولاً باید از توابع LOWER() یا UPPER() برای تبدیل متن به یک مورد معمول برای مقایسه استفاده کنند، که می‌تواند زمان‌بر باشد و همچنین مانع عملکرد پرس و جو شود.
در اینجا سه مثال از نحوه استفاده از این پسوند آورده شده است:
ایجاد جدول با ستون citext:

CREATE TABLE users (

user_id SERIAL PRIMARY KEY,

username CITEXT,

email CITEXT

);

برای جستجوی کاربر با نام کاربری بدون نگرانی در مورد مورد:

SELECT * FROM users WHERE username = ‘jOhNDoE’;

درج داده های حساس به حروف کوچک و بزرگ:

INSERT INTO users (username, email)

VALUES (‘JohnDoe’, ‘john.doe@example.com’);

 

 

۶٫ PG_TRGM
pg_trgm پشتیبانی از جستجوی متن مبتنی بر تریگرام و رتبه بندی شباهت را اضافه می کند. تریگرام ها توالی های سه نویسه ای هستند که از کلمات استخراج می شوند. آنها به عنوان پایه ای برای مقایسه شباهت بین رشته ها عمل می کنند.
افزونه قابلیت جستجوی متن کامل PostgreSQL را افزایش می دهد. این مکانیسمی را برای برنامه‌ها فراهم می‌کند تا پرس‌و‌جوهای پیچیده را هوشمندانه‌تر مدیریت کنند، حتی زمانی که عبارات پرس و جو دارای خطاهای چاپی هستند یا مطابقت دقیقی با داده‌های ذخیره شده در سیستم ندارند.
روش‌های سنتی جستجوی متن دقیق ممکن است هنگام برخورد با ورودی کاربر، غلط املایی، یا تغییرات در فرم‌های کلمه کافی نباشد. pg_trgm در این مواقع راه حلی ارائه می دهد.
در اینجا سه مثال از نحوه استفاده از این پسوند Postgres آورده شده است:
ایجاد یک نمایه در یک ستون متنی برای جستجوی شباهت trigram-based:

CREATE INDEX trgm_index ON your_table USING gin (your_text_column gin_trgm_ops);

رشته های مشابه را بر اساس شباهت trigram پیدا کنید:

SELECT * FROM your_table

WHERE your_text_column % ‘search_term’;

در اینجا، عملگر % جستجوی شباهت را بر اساس تریگرام ها انجام می دهد. ردیف‌هایی را برمی‌گرداند که در آن ستون your_text مشابه عبارت «search_term» است.
رتبه بندی شباهت با استفاده از نمره شباهت سه گانه:

SELECT *, similarity(your_text_column, ‘search_term’) AS trigram_similarity

FROM your_table

WHERE your_text_column % ‘search_term’

ORDER BY trigram_similarity DESC;

در این مثال، تابع similarity()امتیاز شباهت سه‌گرام را بین “your_text_column” و “search_term” داده شده محاسبه می‌کند و نتایج بر اساس امتیاز شباهت به ترتیب نزولی مرتب می‌شوند.

۷٫ TABLEFUNC
tablefunc توابع جدول اضافی را فراهم می کند. اینها را می توان در پرس و جوها برای تولید نتایج crosstab، جداول محوری و انجام تبدیل داده ها اعمال کرد.
برنامه افزودنی به کاربران امکان می دهد داده های ردیف را به ستون تبدیل کنند، که به ارائه داده ها در قالب جدول محوری کمک می کند. همچنین می‌تواند داده‌های از دست رفته را در نتایج crosstab پر کند و به بهبود تصویرسازی و گزارش‌دهی داده‌ها کمک کند.
تحلیلگران داده می توانند از tablefunc برای چرخش داده ها بر اساس معیارهای خاص استفاده کنند و تجزیه و تحلیل و خلاصه کردن اطلاعات را آسان تر کنند. آنها همچنین می توانند پرس و جوهای متقاطع پویا را اجرا کنند، جایی که تعداد ستون های حاصل ممکن است بر اساس داده ها متفاوت باشد.
در اینجا دو مثال از نحوه استفاده از این پسوند آورده شده است:

پرسش های Crosstab:
فرض کنید جدولی به نام «فروش» با ستون‌هایی برای ماه، محصول و درآمد دارید. برای تبدیل داده ها به فرمت crosstab با ماه ها به عنوان ردیف و محصولات به عنوان ستون، می توانید از تابع crosstab از tablefunc استفاده کنید:

SELECT *

FROM crosstab(

‘SELECT month, product, revenue FROM sales ORDER BY 1, 2’,

‘VALUES (”January”), (”February”), (”March”), (”April”), (”May”), (”June”)’

) AS ct (month text, product1 revenue1 numeric, product2 revenue2 numeric, product3 revenue3 numeric);

می توانید از تابع crosstab با پر کردن NULL برای رسیدگی به داده های از دست رفته استفاده کنید. برای جایگزینی مقادیر NULL با ۰:

SELECT *

FROM crosstab(

‘SELECT month, product, revenue FROM sales ORDER BY 1, 2’,

‘VALUES (”January”), (”February”), (”March”), (”April”), (”May”), (”June”)’

) AS ct(month text, product1 revenue1 numeric, product2 revenue2 numeric, product3 revenue3 numeric)

WITH NULL AS 0;

       ۸٫ INTARRAY
intarray پشتیبانی از آرایه های یک بعدی اعداد صحیح را به پایگاه داده Postgres اضافه می کند. مهندسان داده را قادر می سازد تا آرایه های اعداد صحیح را در یک ستون پایگاه داده ذخیره و دستکاری کنند.
با intarray، کاربران می‌توانند آرایه‌های عدد صحیح را مستقیماً در PostgreSQL ذخیره و دستکاری کنند، مدیریت داده‌ها را ساده‌تر کرده و عملکرد جستجوی بهتری را برای عملیات‌های مرتبط با آرایه ممکن می‌سازد.
در اینجا سه مثال از نحوه استفاده از این پسوند آورده شده است:

ایجاد جدول با ستون آرایه عدد صحیح:

CREATE TABLE scores (

id SERIAL PRIMARY KEY,

player_name VARCHAR,

scores INT[]

);

پرس و جو داده ها از ستون آرایه عدد صحیح. اگر می خواهید همه بازیکنان با امتیاز بیشتر از ۹۰ را پیدا کنید:

SELECT * FROM scores

WHERE 90 = ANY(scores);

جمع آوری داده ها از ستون آرایه عدد صحیح. بنابراین، می توانید میانگین امتیاز هر بازیکن را با استفاده از موارد زیر محاسبه کنید:

SELECT player_name, AVG(score) AS average_score

FROM scores, unnest(scores) score

GROUP BY player_name;

9. EARTHDISTANCE

ماژول فاصله زمینی از محاسبات و پرس و جوهای مبتنی بر موقعیت جغرافیایی در پایگاه های داده PostgreSQL پشتیبانی می کند. این به کاربران امکان می دهد فاصله بین دو نقطه مشخص شده توسط مختصات طول و عرض جغرافیایی را محاسبه کنند.

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

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

ایجاد جدول با ستون های طول و عرض جغرافیایی:

CREATE TABLE locations (    location_id SERIAL PRIMARY KEY,    name VARCHAR,    latitude DOUBLE PRECISION,    longitude DOUBLE PRECISION);

پرس و جو از مکان ها در یک فاصله معین. فرض کنید می خواهید مکان هایی را در ۱۰۰ کیلومتری یک نقطه مشخص پیدا کنید:

SELECT name, latitude, longitudeFROM locationsWHERE earth_box(ll_to_earth(40.7128, -74.0060), 100000) @> ll_to_earth(latitude, longitude);

محاسبه فاصله بین مکان ها:

SELECT earth_distance(ll_to_earth(40.7128, -74.0060), ll_to_earth(34.0522, -118.2437)) AS distance_km;

۱۰- CUBE

cube یک نوع داده جدید به نام “مکعب” را معرفی می کند تا کاربران بتوانند به طور موثر نقاط چند بعدی را ذخیره و دستکاری کنند. این برنامه افزودنی همچنین پشتیبانی از نمایه سازی را ارائه می دهد و امکان جستجوها و عملیات سریع روی داده های چند بعدی را فراهم می کند.

با استفاده از ماژول مکعب، کاربران می توانند نقاط n بعدی را ذخیره و پردازش کنند، پرس و جوهای محدوده را انجام دهند، فواصل بین نقاط را محاسبه کنند و از نمایه سازی برای بازیابی سریعتر داده ها در فضاهای با ابعاد بالاتر استفاده کنند.

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

ایجاد جدول با ستون مکعب:

CREATE TABLE points (    point_id SERIAL PRIMARY KEY,    position CUBE);

جست و جوی داده ها با استفاده از عملگرهای مکعبی به عنوان مثال، برای پیدا کردن نقاط در یک محدوده خاص در یک فضای دو بعدی:

SELECT *FROM pointsWHERE position @ cube(array[1, 1], array[2, 4]);

برای پیدا کردن فاصله بین دو نقطه سه بعدی:

SELECT cube_distance(position, CUBE(array[1.0, 2.0, 3.0]))FROM pointsWHERE point_id = 1;

استفاده از نمایه سازی مکعبی برای ایجاد یک شاخص در ستون مکعب:

CREATE INDEX idx_points_position ON points USING gist (position);

برای انجام یک پرس و جو محدوده با استفاده از ایندکس:

SELECT *FROM pointsWHERE position @ cube(array[0, 0], array[2, 5]);