Тема: postgresql
-
Managing PostgreSQL extensions: installing, versioning, updating and dumping them
An extension packages SQL objects and often a shared library under one name with a control file and versioned scripts; CREATE EXTENSION installs it per database, ALTER EXTENSION UPDATE applies the author's update scripts, and pg_dump emits only the CREATE EXTENSION line. Keep the installed files, the catalog version and the loaded library in step, especially across package upgrades and pg_upgrade.
-
Проектирование таблицы временных рядов в PostgreSQL только для добавления данных
Храните измерения в таблице с секционированием по диапазонам времени, timestamptz, составным ключом из ряда и времени и индексами под запросы: B-tree для отдельного ряда, BRIN для сканирования времени по всей таблице. Срок хранения обеспечивайте отсоединением и удалением секций вместо DELETE. Такой выбор следует из поступления строк по времени и удаления целых временных интервалов.
-
Хранение производных данных в PostgreSQL: генерируемые столбцы и материализованные представления
Генерируемый столбец выводит одно значение для каждой строки только из неё самой: виртуально (при чтении) или с хранением (при записи), используя лишь неизменяемые выражения. Материализованное представление хранит результат любого запроса и актуально лишь на момент последнего REFRESH. Для индексируемой нормализации строк используйте хранимые генерируемые столбцы, для дорогих агрегатов с допустимым отставанием — материализованные представления, а если оба варианта не подходят — поддерживаемую сводную таблицу.
-
VACUUM, autovacuum и разрастание таблиц
После обновлений и удалений механизм MVCC в PostgreSQL оставляет мёртвые версии строк. VACUUM освобождает их место для повторного использования, поддерживает статистику и защиту от переполнения идентификаторов транзакций. Autovacuum следует оставлять включённым и настраивать для активно используемых таблиц.
-
Одноразовые базы данных в контейнерах для интеграционных тестов
Для каждого прогона тестов запускайте реальную СУБД в одноразовом контейнере, храните данные в памяти, один раз применяйте миграции к шаблонной базе и копируйте её для каждого тестового файла. Тогда тесты проверяют настоящий планировщик, ограничения и поведение изоляции вместо замены, работающей в памяти.
-
Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server
PostgreSQL's ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQLite's PostgreSQL-style ON CONFLICT and SQL Server's MERGE are three families of upsert syntax, LIMIT/OFFSET has at least three shapes, and a quoted identifier means double quotes, backticks or square brackets depending on the engine.
-
Уровни изоляции транзакций на практике
Read committed, repeatable read и serializable задают компромисс между параллелизмом и согласованностью; знание допускаемых аномалий помогает понять, когда нужны явные блокировки или повторные попытки.
-
Datenbankänderungen ohne Ausfall: Expand und Contract
Ein Schema in drei einzeln auslieferbaren Schritten ändern: erweitern (neue Spalte oder Tabelle anlegen, alte behalten), migrieren (doppelt schreiben und in Häppchen nachfüllen), zusammenziehen (Altes entfernen, sobald aller Code das Neue nutzt); lange Sperren vermeiden, indem keine Tabelle in einem Statement umgeschrieben wird und lock_timeout jede Wartezeit begrenzt.
-
Общие табличные выражения и рекурсивные запросы с WITH
WITH задаёт имя подзапросу для остальной части оператора. WITH RECURSIVE сначала вычисляет нерекурсивную часть, затем повторяет рекурсивную, пока та не перестанет возвращать новые строки, обходя деревья и графы любой глубины одним запросом. Однократно используемые CTE без побочных эффектов встраиваются во внешний запрос, если не задано MATERIALIZED; SEARCH и CYCLE управляют порядком и циклами.
-
JSONB columns: what they are good for and when a column is better
jsonb stores parsed JSON in a binary form that can be indexed with GIN and queried with containment and path operators; it suits sparse, externally defined or genuinely variable attributes. Data with a fixed shape, data that needs constraints, foreign keys or per-field updates, and large frequently changed documents belong in ordinary columns.
-
Пул соединений с базой данных и его ограничения
Каждое соединение PostgreSQL — это процесс, расходующий память. Приложению нужен небольшой пул по реальному параллелизму, таймаут получения соединения и запрет на произвольное открытие соединений обработчиками запросов.
-
Measured PostgreSQL SKIP LOCKED claims with four concurrent queue consumers
Four concurrent transactions each claimed 25 synthetic jobs in PostgreSQL 16.15. The returned 100 IDs were unique and no jobs remained unclaimed; this verifies one bounded claim phase, not exactly-once processing or broker replacement.
-
Статистика планировщика PostgreSQL: целевые объёмы, зависимые столбцы и ошибки оценок
Планировщик оценивает число строк по статистике столбцов, которую ANALYZE собирает выборкой: частые значения, гистограммы и число различных значений. Он предполагает независимость столбцов. Ошибки возникают из-за устаревшей статистики, недостаточной статистики неравномерных распределений, зависимых столбцов и выражений. Повышайте целевой объём для нужных столбцов, создавайте расширенную статистику для зависимых столбцов или выражений, повторяйте ANALYZE и сравнивайте оценки с фактическими строками.
-
NULL in SQL: dreiwertige Logik und ihre Fallen
NULL bedeutet unbekannt, darum ergibt jeder Vergleich mit NULL weder wahr noch falsch, sondern unbekannt; WHERE verwirft unbekannte Zeilen, NOT IN mit einem NULL trifft nichts, Aggregate überspringen NULL, und ein Unique-Constraint lässt mehrere NULL zu. IS NULL, IS DISTINCT FROM und COALESCE bewusst einsetzen.
-
Schema migrations run with a short lock_timeout and automatic retry cause fewer deploy-time incidents than migrations without one
Hypothesis: because most ALTER TABLE forms take an ACCESS EXCLUSIVE lock that queues behind any long transaction while blocking every later query, migrations executed with a lock_timeout of a few seconds and a bounded retry loop produce fewer and shorter deploy-time outages than the same migrations run with the default unlimited wait, at the cost of a few migrations that need a manual rerun.
-
Row-level security policies reduce cross-tenant data leaks compared with application-side filtering
Hypothesis: multi-tenant services that enforce tenant isolation with PostgreSQL row-level security policies (a per-connection tenant setting checked by the database) have fewer cross-tenant exposure bugs than services that add a tenant_id predicate to every query, because a query that forgets the tenant predicate still sees only the current tenant's rows, and a request that never sets the tenant gets nothing or an error instead of every tenant's rows.
-
Savepoints and the aborted-transaction state in PostgreSQL
After any error inside a transaction block PostgreSQL rejects every further command until the block is rolled back; a savepoint set before a risky statement lets ROLLBACK TO SAVEPOINT discard only that part and continue. Use savepoints deliberately and sparingly, and make error handlers roll back instead of retrying on the same connection.
-
Logical replication in PostgreSQL: publications, subscriptions and how it differs from streaming replication
Streaming (physical) replication ships write-ahead log to a byte-identical standby of the same major version and architecture; logical replication publishes row changes from selected tables to a subscriber that may run a different major version and can hold its own data. Logical replication needs a replica identity, carries neither DDL nor sequence values, and its slot retains WAL on the publisher while the subscriber is behind.
-
Read replicas and replication lag: what stale reads look like and how to bound them
A streaming replica applies the primary's log with some delay, so a read right after a write may not see the write. Route reads by how much staleness each caller tolerates, use synchronous replication modes only where their latency is acceptable, and understand that replaying the log can cancel long queries on the replica.
-
Long-running and idle-in-transaction sessions in PostgreSQL: what they block and how to bound them
An open transaction holds its locks and pins the xmin horizon, so VACUUM cannot remove rows deleted after it began and DDL queues behind it; the worst case is a session idle in transaction because a client never committed. Find them in pg_stat_activity by xact_start and state, and bound them per role with idle_in_transaction_session_timeout, transaction_timeout and statement_timeout.
Машиночитаемо: JSON