8 پسوند Postgres شما باید بدانید

برای یک توضیح عمیق تر ، به مقاله کامل پرش کنید.

انعطاف پذیری PostgreSQL تا حد زیادی از اکوسیستم پسوند آن ناشی می شود.

بیایید هشت برنامه افزودنی قدرتمند را کشف کنیم که می توانند قابلیت های پایگاه داده شما را با حداقل تنظیم تغییر دهند.

1 پس از 🌎

چه کاری انجام می دهد: PostgreSQL را به یک سیستم پایگاه داده تبدیل می کند که می تواند به طور مؤثر داده های مکانی را کنترل کند. این انواع داده های اضافی مانند هندسه ، جغرافیا ، شطرنج و موارد دیگر را به همراه مجموعه ای از توابع ، اپراتورها و قابلیت های نمایه سازی متناسب با این انواع مکانی معرفی می کند.

ایده آل برای: برنامه های IoT ، خدمات مبتنی بر مکان و تجزیه و تحلیل جغرافیایی.

نصب:

CREATE EXTENSION postgis;

پرس و جو نمونه postgis:

ما می خواهیم بدانیم: “چند تاکسی در روز سال نو 2016 مسافر را در 400 متر میدان تایمز انتخاب کرد؟”

-- How many taxis picked up rides within 400m of Times Square on New Years Day?
-- Times Square coordinates: (40.7589, -73.9851)
SELECT time_bucket('30 minutes', pickup_datetime) AS thirty_min, COUNT(*) AS near_times_sq
FROM rides
WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400
AND pickup_datetime < '2016-01-01 14:00'
GROUP BY thirty_min ORDER BY thirty_min
LIMIT 5;

پرس و جو فوق نتایج زیر را تولید می کند:

-[ RECORD 1 ]-+--------------------
thirty_min    | 2016-01-01 00:00:00
near_times_sq | 74
-[ RECORD 2 ]-+--------------------
thirty_min    | 2016-01-01 00:30:00
near_times_sq | 102
-[ RECORD 3 ]-+--------------------
thirty_min    | 2016-01-01 01:00:00
near_times_sq | 120
-[ RECORD 4 ]-+--------------------
thirty_min    | 2016-01-01 01:30:00
near_times_sq | 98
-[ RECORD 5 ]-+--------------------
thirty_min    | 2016-01-01 02:00:00
near_times_sq | 112

2 pg_stat_statements 📊

چه کاری انجام می دهد: آمار اجرای را برای کلیه سؤالات اجرا شده توسط یک پایگاه داده Postgres دنبال می کند. این به شما کمک می کند تا پرس و جو را اشکال زدایی کنید ، نمایش داده شدگان آهسته را شناسایی کنید و به طور کلی اطلاعات عمیق تری در مورد نحوه اجرای نمایش داده های شما به شما می دهد.

ایده آل برای: تنظیم عملکرد و شناسایی نمایش داده های آهسته.

نصب:

CREATE EXTENSION pg_stat_statements;

پرس و جو نمونه PG_STAT_STATEMENS

ما علاقه مندیم: “5 نمایشگاه برتر انتخابی I/O چیست؟”

SELECT query, calls, total_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE 'SELECT%'
ORDER BY shared_blks_read DESC, calls DESC
LIMIT 5;

خروجی به این شکل خواهد بود:

-[ RECORD 1 ]--—+---------------------------------------------------
query             | SELECT * FROM customer_data WHERE created_at > $1
calls             | 500
total_time        | 23000
rows              | 500000
shared_blks_hit   | 100000
shared_blks_read  | 75000
-[ RECORD 2 ]-----+---------------------------------------------------
query             | SELECT name, address FROM orders WHERE status = $1
calls             | 450
total_time        | 15000
rows              | 450000
shared_blks_hit   | 95000
shared_blks_read  | 55000
-[ RECORD 3 ]-----+---------------------------------------------------
query             | SELECT COUNT(*) FROM transactions WHERE amount > $1
calls             | 300
total_time        | 12000
rows              | 300000
shared_blks_hit   | 85000
shared_blks_read  | 50000
-[ RECORD 4 ]-----+---------------------------------------------------
query             | SELECT product_id FROM inventory WHERE quantity < $1
calls             | 400
total_time        | 16000
rows              | 400000
shared_blks_hit   | 80000
shared_blks_read  | 45000
-[ RECORD 5 ]-----+---------------------------------------------------
query             | SELECT * FROM user_logs WHERE user_id = $1 AND activity_date > $2
calls             | 350
total_time        | 17500
rows              | 350000
shared_blks_hit   | 75000
shared_blks_read  | 40000

3 pgcrypto 🔒

چه کاری انجام می دهد: توابع رمزنگاری را به PostgreSQL برای رمزگذاری ، هش و موارد دیگر اضافه می کند.

ایده آل برای: ذخیره ایمن داده های حساس و مدیریت رمز عبور.

نصب:

CREATE EXTENSION pgcrypto;

پرس و جو نمونه pgcrypto

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

INSERT INTO users (username, password) VALUES ('john_doe', crypt('my_secure_password', gen_salt('bf')));

در این جمله ، crypt تابعی است که توسط pgcrypto ارائه شده است که رمز عبور را با استفاده از الگوریتم Blowfish رمزگذاری می کند ، که توسط آن نشان داده شده است gen_salt('bf')

در مرحله بعد ، برای تأیید هویت یک کاربر ، یک رمز عبور ذخیره شده را در برابر یک ارائه شده در هنگام ورود به سیستم مقایسه می کنید:

SELECT username FROM users WHERE username = 'john_doe' AND password = crypt('input_password'

4 PG_PARTMAN 📑

چه کاری انجام می دهد: ایجاد و مدیریت پارتیشن های جدول را ساده می کند. پارتیشن بندی یک تکنیک کلیدی بانک اطلاعاتی است که شامل تقسیم یک میز بزرگ به قطعات کوچکتر و قابل کنترل تر است در حالی که به شما امکان می دهد به داده ها دسترسی پیدا کنید که گویی یک جدول است.

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

نصب:

CREATE EXTENSION pg_partman;

پرس و جو نمونه PG_PARTMAN

سناریویی را در نظر بگیرید که در آن جدول بزرگی از داده های دستگاه IoT وجود دارد که می خواهید به روز پارتیشن کنید.

در اینجا نحوه تنظیم یک طرح پارتیشن روزانه برای یک جدول به نام دستگاه_داتا با استفاده از آن آورده شده است pg_partman:

-- Create a parent table
CREATE TABLE device_data (
    time timestamptz NOT NULL,
    device_id int NOT NULL,
    data jsonb NOT NULL
);

-- Set up pg_partman to manage daily partitions of the device_data table
SELECT partman.create_parent('public.device_data', 'time', 'partman', 'daily');

در این تنظیم ، Create_Parent تابعی است که توسط PG_PARTMAN ارائه می شود که نام جدول والدین و ستون را به پارتیشن در (زمان) می برد ، و همچنین طرحواره (Partman) و فاصله پارتیشن (روزانه).

5 postgres_fdw 🌉

چه کاری انجام می دهد: به شما امکان می دهد از یک بسته بندی داده خارجی برای دسترسی به جداول در سرورهای از راه دور Postgres استفاده کنید (از این رو نام “FDW”). یک بسته بندی داده های خارجی به شما امکان می دهد تا اطلاعاتی را برای داده های ذخیره شده در سایر پایگاه داده های Postgres ایجاد کنید تا از آنها پرس و جو کنید که گویی از یک جدول در پایگاه داده فعلی آمده اند.

ایده آل برای: بانکهای اطلاعاتی توزیع شده و تنظیمات چند سرور.

نصب:

CREATE EXTENSION postgres_fdw;

پرس و جو نمونه postgres_fdw

در اینجا نحوه ایجاد ارتباطی به سرور خارجی خود آورده شده است:

CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '123.45.67.8', dbname ‘postgres’, port '5432');

این پرس و جو به یک پایگاه داده میزبان آدرس IP 123.45.67.8 با نام وصل می شود postgres در port 5432بشر اکنون ، یک نقشه برداری کاربر ایجاد کنید تا کاربران در پایگاه داده شما بتوانند به سرور خارجی دسترسی پیدا کنند:

CREATE USER MAPPING FOR postgres 
SERVER myserver 
OPTIONS (user 'postgres', password 'password');

6 PGVector 🔍

چه کاری انجام می دهد: پشتیبانی از عملیات بردار در Postgres را اضافه می کند-امکان جستجوی شباهت ، جستجوی نزدیکترین همسایه و موارد دیگر.

ایده آل برای: برنامه هایی مانند سیستم های توصیه ، بازیابی تصویر و جستجوی معنایی.

نصب:

CREATE EXTENSION vector;

پرس و جو نمونه PGVECTOR
بگویید می خواهید مشابه ترین تصاویر با یک بردار ویژگی خاص را پیدا کنید. در اینجا نحوه استفاده از PGVECTOR برای انجام یک جستجوی نزدیکترین همسایه آورده شده است:

-- Assuming we have a table with image features stored as vectors
-- Table: image_features
-- Columns: id (integer), features (vector)

-- Given a query vector, find the 5 most similar images
SELECT id, features
FROM image_features
ORDER BY features <-> 'query_vector'::vector
LIMIT 5;

این پرس و جو نتایج را با فاصله بین query_veector و ستون ویژگی ها سفارش می دهد ، و به طور موثری نزدیکترین مسابقات را برمی گرداند

7. hstore

چه کاری انجام می دهد: یک فروشگاه با ارزش کلیدی در Postgres ، که مجموعه هایی از جفت های کلید/مقدار را در یک نوع داده Postgres واحد ذخیره می کند.

ایده آل برای: داده های نیمه ساختار یافته با ویژگی های مختلف که نیاز به نمایه سازی سریع و الزامات طرحواره ای انعطاف پذیر و بدون تغییر جدول دارند.

نصب:

CREATE EXTENSION hstore;

پرس و جو نمونه HSTORE
در اینجا نمونه ای از نحوه استفاده از HSTORE برای ذخیره و پرس و جو از داده های محصول با ویژگی های مختلف آورده شده است:

-- Create a table with an hstore column for storing product attributes
CREATE TABLE products (
    id serial PRIMARY KEY,
    name text NOT NULL,
    attributes hstore
);

-- Insert a product with attributes into the table
INSERT INTO products (name, attributes)
VALUES ('Smartphone', 'color => "black", storage => "64GB", battery => "3000mAh"');

-- Query to find products with a specific attribute
SELECT name
FROM products
WHERE attributes @> 'storage => "64GB"';

8. PGCRE 📝

چه کاری انجام می دهد: عبارات منظم سازگار با Perl (PCRE) را در PostgreSQL ادغام می کند ، و عملکرد پیشرفته تطبیق رشته را فراتر از قابلیت های داخلی REGEX داخلی PostgreSQL فراهم می کند.

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

نصب

CREATE EXTENSION pgpcre

پرس و جو نمونه PGPCRE
اگر می خواهید آدرس های ایمیل را در ستونی از متن بدون ساختار جستجو کنید ، ممکن است از یک الگوی PCRE برای تطبیق ایمیل ها به شرح زیر استفاده کنید:

-- Assuming we have a table named messages with a column named content
-- Table: messages
-- Column: content (text)
-- Use pgpcre to match email addresses within the content
SELECT content, pcre_match('^\S+@\S+$', content) AS email
FROM messages
WHERE pcre_match('^\S+@\S+$', content) IS NOT NULL;

آیا TimescaledB پسوند postgreSQL است؟

بله TimescaledB یکی دیگر از برنامه های افزودنی برتر است که می دانید. این یک پرس و جو و افزایش عملکرد را از طریق پارتیشن بندی اتوماتیک ، پیشرفت های برنامه ریز پرس و جو ، نماهای بهبود یافته ، فشرده سازی ستونی و موارد دیگر به Postgres اضافه می کند.

چه کاری انجام می دهد: Postgres را برای بار کاری سری زمانی با ویژگی هایی برای تجزیه و تحلیل در زمان واقعی و مدیریت داده های کارآمد تقویت می کند.

ایده آل برای: برنامه هایی که داده های سری زمانی را که به پرس و جوهای سریعتر ، راندمان ذخیره سازی بهتر و تجمع مداوم ساده نیاز دارند ، اداره می کنند.

پرس و جو مثال TimeScaledB
فرض کنید ما یک جدول داده سنسور داریم که هر دقیقه خوانش دما را از دستگاه های مختلف ثبت می کند. ما می خواهیم میانگین دما در ساعت را برای یک دستگاه خاص تجزیه و تحلیل کنیم. در اینجا نحوه ساخت چنین پرس و جو با استفاده از TimescaledB آورده شده است:

-- Assuming we have a hypertable named sensor_readings with columns time, device_id, and temperature
-- Table: sensor_readings
-- Columns: time (TIMESTAMP WITH TIME ZONE), device_id (INT), temperature (DOUBLE PRECISION)

-- Calculate the average temperature per hour for device with ID 1
SELECT time_bucket('1 hour', time) AS one_hour_bucket, 
       AVG(temperature) AS avg_temperature
FROM sensor_readings
WHERE device_id = 1
GROUP BY one_hour_bucket
ORDER BY one_hour_bucket;

پایان

پسوندهای PostgreSQL عملکرد فوق العاده ای را با حداقل تنظیم ارائه می دهند. بیشتر آنها فقط به یک دستور واحد نیاز دارند ، اما می توانند به طور چشمگیری آنچه را که پایگاه داده شما می تواند انجام دهد ، افزایش دهد.

پسوند PostgreSQL مورد علاقه شما چیست؟ آیا شما هیچ یک از این موارد را امتحان کرده اید؟ در نظرات به من اطلاع دهید!