Postgres extensions для аналитики на собеседовании Data Engineer

pg_stat_statements

Tracks query performance.

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Top-N slowest queries — для optimization.

pg_repack

Online table reorg.

После heavy UPDATE / DELETE — table bloated. VACUUM FULL locks table. pg_repack — без lock.

pg_repack -t my_table -d my_db

Rewrites table в новый file, swap atomic. Минимум downtime.

TimescaleDB

Postgres extension для time-series.

Hypertables. Auto-partitioning по time.

SELECT create_hypertable('events', 'event_time', chunk_time_interval => INTERVAL '1 day');

Continuous aggregates. Pre-computed materialized views, auto-updating.

Compression. Old chunks compressed на 90%+.

Подходит для metrics, IoT, financial ticks. Сравним с InfluxDB.

pgvector

Vector similarity search.

CREATE EXTENSION vector;
CREATE TABLE items (id BIGSERIAL, embedding VECTOR(768));
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

SELECT * FROM items ORDER BY embedding <=> '[...]' LIMIT 10;

Для small / medium RAG. Combine с regular SQL.

PostGIS

Geospatial extension.

CREATE EXTENSION postgis;

SELECT name FROM places
WHERE ST_Distance(location, ST_MakePoint(37.6, 55.7)) < 5000;

Spatial indexes (GiST). Industry standard для geo.

hll

HyperLogLog для approximate distinct count.

CREATE EXTENSION hll;

CREATE TABLE daily_active (day DATE, users HLL);
INSERT INTO daily_active VALUES (...);

SELECT day, hll_cardinality(users) AS unique_users FROM daily_active;

Memory-efficient distinct counts на billions.

Связанные темы

• EXPLAIN и план запроса для DE
• Vector databases для DS
• Cumulative DISTINCT для DE
• Транзакции и MVCC для DE
• Подготовка к собесу Data Engineer

FAQ

Это официальная информация?

Нет. Статья основана на документации Postgres extensions.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.