Тема: sql
-
Полнотекстовый поиск в PostgreSQL с помощью tsvector
PostgreSQL превращает текст в tsvector из нормализованных лексем с помощью языковой конфигурации, сопоставляет его с tsquery, ранжирует через ts_rank и индексирует через GIN; из коробки он умеет стемминг и стоп-слова, но не опечатки и не синонимы.
-
Хранение производных данных в PostgreSQL: генерируемые столбцы и материализованные представления
Генерируемый столбец выводит одно значение для каждой строки только из неё самой: виртуально (при чтении) или с хранением (при записи), используя лишь неизменяемые выражения. Материализованное представление хранит результат любого запроса и актуально лишь на момент последнего REFRESH. Для индексируемой нормализации строк используйте хранимые генерируемые столбцы, для дорогих агрегатов с допустимым отставанием — материализованные представления, а если оба варианта не подходят — поддерживаемую сводную таблицу.
-
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.
-
NULL in SQL: three-valued logic and its traps
NULL means unknown, so comparisons with NULL yield unknown rather than true or false; WHERE filters drop unknown rows, NOT IN with a NULL matches nothing, and aggregates skip NULLs. Use IS NULL, IS DISTINCT FROM and COALESCE deliberately.
-
Общие табличные выражения и рекурсивные запросы с WITH
WITH задаёт имя подзапросу для остальной части оператора. WITH RECURSIVE сначала вычисляет нерекурсивную часть, затем повторяет рекурсивную, пока та не перестанет возвращать новые строки, обходя деревья и графы любой глубины одним запросом. Однократно используемые CTE без побочных эффектов встраиваются во внешний запрос, если не задано MATERIALIZED; SEARCH и CYCLE управляют порядком и циклами.
-
Статистика планировщика PostgreSQL: целевые объёмы, зависимые столбцы и ошибки оценок
Планировщик оценивает число строк по статистике столбцов, которую ANALYZE собирает выборкой: частые значения, гистограммы и число различных значений. Он предполагает независимость столбцов. Ошибки возникают из-за устаревшей статистики, недостаточной статистики неравномерных распределений, зависимых столбцов и выражений. Повышайте целевой объём для нужных столбцов, создавайте расширенную статистику для зависимых столбцов или выражений, повторяйте ANALYZE и сравнивайте оценки с фактическими строками.
-
Reading a PostgreSQL query plan with EXPLAIN ANALYZE
EXPLAIN shows the planner's chosen tree with estimated costs; EXPLAIN ANALYZE runs the query and adds actual times and row counts. Compare estimated with actual rows, find the node with the largest actual time, and check for sequential scans on large tables and misestimated joins.
-
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.
-
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.
-
Cursor-Pagination statt Offsets: Seiten, die bei Änderungen stabil bleiben
Offset-Pagination lässt die Datenbank alle übersprungenen Zeilen trotzdem berechnen und verschiebt Seiten, sobald dazwischen eingefügt oder gelöscht wird; Cursor- oder Keyset-Pagination fragt «die nächsten 20 nach Schlüssel X», nutzt den Index und liefert jede Zeile genau einmal. Voraussetzung ist eine eindeutige Sortierung, der Preis ist der Verzicht auf Seitenzahlen.
-
Schema conventions for a new PostgreSQL database: names, identifiers, timestamps and text
Decide a handful of conventions before the first migration: lower-case snake_case names that never need quoting, one id strategy applied everywhere, timestamptz for every point in time with created_at on every table, text instead of varchar(n), and explicit NOT NULL and foreign keys; write them down so every later migration follows them.
-
Bulk operations instead of per-row loops: round trips, transactions and COPY
A loop that sends one statement per row pays a round trip, a parse and often a commit per row; a bulk operation sends the set in one statement, one stream or one transaction. PostgreSQL's own guidance is to commit once, use COPY for loads and build indexes after loading; client libraries offer executemany and copy for the same reason.
-
Datenqualitätsprüfungen: Aktualität, Menge, Nullwerte und Eindeutigkeit als Mindestsatz
Vier billige Prüfungen fangen die meisten kaputten Ladeläufe: Ist die Quelle frisch genug, kam eine plausible Zeilenzahl, sind Schlüssel und Kennzahlen gefüllt, ist die erklärte Körnung eindeutig? Jede Prüfung als Abfrage formulieren, die fehlerhafte Zeilen liefert, nach dem Laden und vor dem Veröffentlichen ausführen, Warnung und Blockade trennen.
-
What connection-pool size relative to CPU cores have teams settled on for a PostgreSQL server, and which measurement made them change it?
Open question: the PostgreSQL wiki offers a sizing formula for active connections built around core count and effective spindle count, and the server's max_connections default is typically 100; what pool sizes have teams actually ended up with after tuning, how far from the formula were they, and what evidence (lock waits, queueing at the pooler, CPU saturation, latency) drove each change?
-
Soft deletes versus archive tables
A soft delete keeps the row with a deleted_at marker, so every query must filter it out and every unique constraint must become a partial index; an archive table moves the row out of the live table, so live queries stay simple and history lives in one place. Choose by who reads deleted data and how often, and put the choice into constraints and views rather than into every query.
-
Writing an upsert with INSERT ... ON CONFLICT
INSERT ... ON CONFLICT (columns) DO UPDATE SET ... turns insert-or-update into one atomic statement driven by a unique index: name the conflict target, update only the columns that should change, use EXCLUDED for the proposed row, and add a WHERE that skips no-op updates. MERGE covers the multi-branch cases ON CONFLICT cannot express.
-
Запросы N+1: обнаружение подсчётом и исправление пакетной загрузкой
Загрузка N родительских строк с последующим ленивым обращением к связи каждой строки создаёт N+1 запросов. Затраты растут с данными, а не с кодом, поэтому маленькие фикстуры не выявляют проблему. Проверяйте число запросов, превращайте нежелательные ленивые загрузки в ошибки и исправляйте связи «к одному» соединениями, а коллекции — вторым пакетным запросом IN.
-
Normalising to third normal form and choosing when to denormalise
Normal forms remove repeating groups and facts stored in more than one place; third normal form means every non-key column depends on the key and nothing else. Normalise by default for transactional data and denormalise only in named, derived columns whose source of truth stays normalised.
-
Декларативные ограничения PostgreSQL: CHECK, UNIQUE и внешние ключи с ON DELETE
Ограничения заставляют базу отклонять недопустимые состояния для всех пишущих клиентов, а не только приложения: CHECK для правил строки, NOT NULL для обязательных значений, UNIQUE для уникальности и внешние ключи с явным ON DELETE. Чаще всего упускают выбор ссылочного действия и индексирование ссылающегося столбца.
-
Поиск самых затратных SQL-операторов с помощью pg_stat_statements
pg_stat_statements агрегирует число вызовов, время выполнения и количество блоков по нормализованным операторам для всего сервера. Загрузите модуль через shared_preload_libraries, ранжируйте по total_exec_time и отдельно по calls, сравните среднее с максимумом, изучите столбцы буферов, затем исследуйте лидирующие операторы через EXPLAIN ANALYZE и сравните результаты до и после по queryid.
Машиночитаемо: JSON