[Перевод] Несколько советов по использованию PostgreSQL

• Оригинальный адрес:wiki.PostgreSQL.org/wiki/Don%27…
• Адрес перевода:GitHub.com/вода Мел О/…
• Переводчик: хаки хаки
• Уровень переводчика ограничен, если есть ошибка в переводе или понимании, помогите указать

1. Кодирование базы данных

1.1 Не используйте кодировку SQL_ASCII

1.1.1 Почему

Хотя кажется, что имяASCIIСвязь есть, но нет. Вместо этого он просто запрещает использование пустых байтов.

важнее,SQL_ASCIIОзначает «не преобразовывать» для всех функций преобразования кодировки. То есть сырая байтовая кодировка такая, какая она есть. Если не принять крайнюю осторожность,SQL_ASCIIВ закодированных базах данных могут храниться смешанные данные в разных кодировках, и они не смогут надежно восстановить исходные символы.

1.1.2 Когда можно использовать

Если ваши входные данные уже закодированы как смешанные данные, такие как журналы IRC или электронные письма, не совместимые с MIME, тоSQL_ASCIIВероятно, последнее средство - следует рассмотреть в первую очередьbyteaКодирование или может определить, является ли это кодировкой UTF8, если можно предположить, что кодировка не-UTF8, такая как данные, закодированные WIN1252, является кодировкой UTF8.

2. Инструменты

2.1 Не используйте psql -W или psql --password

Не используйте psql -W или psql --password

2.1.1 Почему

Если вы подключаетесь к сервису с помощью флага --password или -W,psqlВам будет предложено для пароля - поэтому вы будете предложены для пароля, даже если сервер не требует одного.

Эта опция необязательна, она создаст ложное впечатление, что сервер требует пароль. Если у пользователя, под которым вы входите, не установлен пароль или вы вводите неправильный пароль при появлении запроса, вы все равно успешно войдете в систему и будете думать, что это правильный пароль, но вы не сможете использовать этот пароль. от других клиентов (подключение через локальный хост) или с Войти как другой пользователь.

2.1.2 Когда можно использовать

Не используй.

2.2 Не используйте ПРАВИЛО

Не используйRULE, (Примечание переводчика: CREATE RULE определяет новое правило, которое применяется к определенной таблице или представлению). Если вы хотите его использовать, используйтекурокальтернатива.

2.2.1 Почему

ПРАВИЛО очень мощное, но не простое для понимания. Это выглядит как какая-то условная логика, но на самом деле она переписывает запрос или добавляет к запросу другой запрос.

это означаетВсе нетривиальные правила неверны. (Примечание переводчика: обратитесь к справочной статье для определения нетривиального)

У Депеша есть этоБольше слов.

2.2.2 Когда можно использовать

Не используй.

2.3 Не используйте наследование таблиц

Не используйнаследование таблицЕсли вы действительно используете, вы можете использовать вместо этого внешние ключи.

2.3.1 Почему

Наследование таблиц — модная концепция, в которой база данных тесно связана с объектно-ориентированным кодом. Оказывается, эти связанные вещи на самом деле не дают ожидаемых результатов.

2.3.2 Когда можно использовать

Почти не использовался... почти. Теперь, когда секционирование таблиц выполняется изначально, обычный сценарий наследования таблиц был заменен некоторыми функциями.

3. Оператор SQL

3.1 Не используйте NOT IN

Не используйте NOT IN, IN и любую комбинацию или NOT, как NOT (x IN (выберите ...)).

(Если вы думаете, что хотите NOT IN (выбрать...), то вам следует вместо этого использовать NOT EXISTS.)

3.1.1 Почему

Две причины:

1. NOT IN ведет себя неожиданным образом, если есть значения NULL:

select * from foo where col not in (1,null);
  -- always returns 0 rows

select * from foo where col not in (select x from bar);
  -- returns 0 rows if any value of bar.x is null

Это происходит потому, что col IN(1,null) возвращает TRUE, если col = 1, и NULL в противном случае (т. е. никогда не возвращает FALSE). Поскольку NOT(TRUE) равно FALSE, но NOT(NULL) по-прежнему равно NULL, NOT(столбец IN(1, null)) (то же, что и столбец NOT IN(1, null)) не может возвращать TRUE, что означает NOT IN ( 1, NULL) Эта форма никогда не возвращает данные.

2. Из-за пункта 1 выше, NOT IN (SELECT...) плохо оптимизирован. В частности, планировщик (планировщик, ответственный за создание плана запроса) не может превратить его в антисоединение, поэтому он становится хеш-подпланом или обычным подпланом. Подпланы хэширования выполняются быстро, но планировщик разрешает план только для небольших наборов результатов; обычные подпланы очень медленные (на самом деле временная сложность O(N²)). Это означает, что производительность выглядит хорошо на небольших тестах, но замедляется на 5 или более порядков при больших объемах данных; мы не хотим, чтобы это произошло.

3.1.2 Когда можно использовать

NOT IN (список, из, значения,...) является проблемой только в том случае, если список имеет нулевые значения (параметры или иное). Таким образом, его можно использовать для исключения, когда нет нулевого значения.

3.2 Не используйте заглавные буквы

Не используйNamesLikeThis, вместо этого используйтеnames_like_thisметод наименования.

3.2.1 Почему

PostgreSQL преобразует таблицу, столбец, функцию и т. Д. Имена в нижнем регистре, если они не прилагаются к «двойным кадинатам».

такcreate table Foo()создаст таблицу с именемfooстол, выполнитьcreate table "Bar"()создаст таблицу с именемBarТаблица.

Эти операторы запроса будут выполняться нормально:select * from Foo, select * from foo, select * from "Bar"

Эти запросы сообщит об ошибке «Нет такой таблицы»:select * from "Foo", select * from Bar, select * from bar

Это означает, что при запросе, используются ли заглавные буквы в именах таблиц или столбцов, необходимо использовать двойные кавычки. Это раздражает, но когда вы используете другие инструменты для доступа к базе данных, некоторые имена используют двойные кавычки, а другие нет, это сбивает с толку.

Придерживайтесь az, 0-9 и символов подчеркивания для имен, и больше не беспокойтесь об этом.

3.2.2 Когда можно использовать

Если важно, чтобы имена в выводе были красивыми, вы можете использовать заглавные буквы. Но вы также можете использовать псевдонимы столбцов, а также выводить красивые имена в запросе:select character_name as "Character Name" from foo.

3.3 Не используйте BETWEEN (особенно метки времени)

3.3.1 Почему

BETWEEN использует сравнение с закрытым диапазоном: в результат включаются значения на обоих концах диапазона.

Это запрос-вопрос

SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

Это будет включать результаты с отметками времени ровно 2018-06-08 00:00:00.000000 . Запрос работает, но поскольку это закрытый интервал, следующий запрос может включать значение в этот момент (например, «2018-06-08» И «2018-06-09» будут включать значение в этот момент).

Замените следующим утверждением

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

3.3.2 Когда можно использовать

BETWEEN безопасен для дискретных данных, таких как целые числа или даты. Помните, что BETWEEN — это закрытый интервал. Но использование BETWEEN может стать плохой привычкой.

4. дата/время сохранены

(Примечание переводчика:Дата/время Документация на китайском языке)

4.1 Не используйте отметку времени (без часового пояса)

Не используйtimestampвведите для хранения временных меток, вместо этого используйтеtimestamptz(Также известен как временное помещение с часовым поясом) для хранения.

4.1.1 Почему?

timestamptzЗаписывает количество микросекунд в формате UTC, и вы можете вставлять значения для любого часового пояса. По умолчанию отображается время в текущем часовом поясе, но вы можете преобразовать его в другие часовые пояса.

Поскольку он хранит информацию о метках времени, ее можно алгоритмически преобразовать в метки времени в разных часовых поясах.

timestamp(он же временная метка без часового пояса) ничего не делает, он просто сохраняет дату и время, которые вы указываете. Вы можете думать об этом как об изображении календаря и часов, а не момента времени, без информации о часовом поясе. Поэтому метки времени без часовых поясов не могут преобразовывать часовые пояса.

Поэтому, если вы хотите сохранить момент времени, а не изображение часов, используйтеtimestamptz.

Подробнее о метках времени

4.1.2 Когда он может быть использован

Если вы имеете дело с отметками времени абстрактным образом или просто для сохранения и поиска приложений без их синхронизации, тоtimestampможет быть уместно.

4.2 Не используйте отметку времени (без часового пояса) для хранения времени UTC

Хранить значения UTC без часового поясаtimestamp, как правило, практика наследования данных из других баз данных, в которых отсутствует доступная поддержка часовых поясов.

использовать вместо этогоtimestamp with time zoneкоторыйtimestamptz.

4.2.1 Почему

Потому что база данных не может узнать, находится ли она в часовом поясе UTC.

Это усложняет расчет времени. Например, оператор расчета для «последней полуночи в заданном часовом поясе u.timezone» выглядит следующим образом:

date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

И оператор расчета для «полночи до даты x.datecol в u.timezone»:

date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone)
  AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

4.2.2 Когда можно использовать

Если совместимость с базами данных, не поддерживающими часовой пояс, важнее всех других соображений.

4.3 Не используйте timetz

Не используйtimetzтип, вы можете использоватьtimestamptzзаменять.

4.3.1 Почему

Даже руководство также сообщает вам, что он просто соответствует стандарту SQL.

Тип time с часовым поясом определяется стандартом SQL, а атрибут people определяет отображение сомнения в его наличии. В большинстве случаев дата, время, комбинации без часового пояса, отметка времени, отметка времени и часовой пояс должны быть предоставлены с любой желаемой датой приложения/функции времени.

4.3.2 Когда можно использовать

Никогда не используйте.

4.4 Не используйте CURRENT_TIME

Не используйте функцию CURRENT_TIME. Уместно использовать следующее:

• CURRENT_TIMESTAMP или now(), если хотитеtimestamp with time zone
• Localtimestamp, если вы хотитеtimestamp without time zone
• CURRENT_DATE, если хотитеdate
• МЕСТНОЕ ВРЕМЯ, если хотитеtime

4.4.1 Почему

он возвращаетсяtimetzзначение типа, оtimetzСм. предыдущее объяснение.

4.4.2 Когда я могу использовать?

Никогда не используйте.

4.5 Не используйте метку времени (0) или метку времени (0)

Не используйте timestamp() или timestamptz() для преобразования временных меток (особенно 0).

использоватьdate_trunc('second', blah)заменять.

4.5.1 Почему

Потому что он будет усекать дробную часть округленного числа. Это может вызвать непредвиденные проблемы; если принять во вниманиеnow()При хранении в такой колонне вы можете хранить дробное второе значение в будущем.

4.5.2 Когда можно использовать

Никогда не используйте.

5. Хранение текста

5.1 Не используйте char(n)

Не используйchar(n),использоватьtextЭто может быть более подходящим.

5.1.1 Почему

использоватьchar(n)Поля типа, если они недостаточно длинные, будут дополнены пробелами до объявленной длины. Это, вероятно, не то, что вы хотите.

название	описывать
character varying(n), varchar(n)	Переменная длина, есть ограничение по длине
character(n), char(n)	Фиксированная длина, неадекватное заполнение промежутков
text	Переменная длина, без ограничения длины

В руководстве сказано:

Числовые значения типа char физически дополняются пробелами до указанной длины n, сохраняются и отображаются таким образом. Однако при сравнении двух значений типа char конечные пробелы не имеют значения и не должны игнорироваться. В сопоставлениях, где важны пробелы, такое поведение может привести к неожиданным результатам, напримерSELECT 'a '::CHAR(2) collate "C" < 'a\n'::CHAR(2)Возвращает истину. При преобразовании значения char в другие типы строк завершающие пробелы удаляются. Обратите внимание, что конечные пробелы являются семантическими в varchar и текстовых значениях. А при использовании сопоставления с образцом, например LIKE, используйте регулярные выражения.

Заполнение пробелами тратит место впустую и не ускоряет работу; на самом деле, во многих случаях мы собираемся удалить пробелы.

Совет. Между этими тремя типами нет разницы в производительности, за исключением увеличения объема памяти при использовании пустого заполнения и некоторых дополнительных циклов ЦП для проверки длины хранилища при сохранении столбцов с ограничениями по длине. Хотя char(n) имеет некоторые преимущества в производительности в некоторых других системах баз данных, в PostgreSQL этого нет. Фактически, char(n) обычно является самым медленным из трех из-за дополнительных затрат на хранение. В большинстве случаев следует использовать text или varchar.

5.1.2 Когда можно использовать

Когда вы портируете очень, очень старое программное обеспечение, которое использует поля фиксированной ширины. Или его можно использовать, когда вы читаете фрагмент из руководства выше и думаете: «Да, это совершенно разумно и делает то, что я хочу».

5.2 Не используйте char(n) даже для идентификаторов фиксированной длины

Иногда на вопрос «почему бы не использовать char(n)» люди отвечают: «Мое значение — ровно N символов» (например, коды стран, хэши или идентификаторы из других систем). На самом деле, даже в этих сценариях с использованиемchar(n)Тоже не лучшая идея.

5.2.1 Почему

Для слишком коротких значенийchar(n)заполнит их пробелами. Поэтому при определенной длинеchar(n)сравниватьtextРеальной пользы нет.

5.2.2 Когда можно использовать

Никогда не используйте.

5.3 Не используйте varchar(n) по умолчанию

Не использовать по умолчаниюvarchar(n)тип. рассмотрите возможность использованияvarchar(без ограничения длины) илиtextальтернатива.

5.3.1 Почему

varchar(n)представляет собой текстовое поле с длиной, при попытке вставить в него строку длиннее n символов (не байтов) будет выдана ошибка.

varchar (без (n)) илиtextаналогичны и не имеют ограничений по длине. Если вы вставите одну и ту же строку в три типа полей, они будут занимать одинаковое место, и разницы в производительности практически нет.

Если вы хотите, чтобы текстовое поле ограничилось длиной, тоvarchar(n)Очень хорошо, но если вы определите в качестве поля фамилииvarchar(20), то при регистрации Hubert Blaine Wolfeschlegelsteinhausenbergerdorff в вашей службе будет сообщено об ошибке.

Некоторые базы данных не длинныеtextтипы, или они не имеют одинаковыхvarchar(n)Это хорошо поддерживается. Пользователи этих баз данных обычно используют что-то вродеvarchar(255)представительство, но на самом деле они хотятtext.

Если вам нужно ограничить значение в поле, скажем, ограничить максимальную длину — или минимальную длину, или ограниченный набор строк —Проверить ограниченияЭто можно сделать.

5.3.2 Когда можно использовать

Если вам нужно текстовое поле, а вставка слишком длинной строки должна вызвать ошибку и вы не хотите использовать явное ограничение проверки, тогдаvarchar(n)очень хороший тип. Просто нужно думать об этом при использовании.

6. Другие типы данных

6.1 Не используйте деньги

moneyТипы данных не совсем подходят для хранения денежных значений. Числа или целые числа могут быть лучше.

6.1.1 Почему

множество причин

Тип money хранит денежные суммы с фиксированной десятичной точностью.lc_monetaryИспользуется для установки форматированных чисел. Но его поведение округления, вероятно, не то, что вам нужно.

название	место хранения	описывать	Сфера
money	8 байт	сумма в валюте	-92233720368547758,08 до +92233720368547758,07

если ты изменишьсяlc_monetaryустановлен, все денежные столбцы будут содержать неверные значения. Это означает, что если вы вставите «$10,00» иlc_monetaryУстановить какen_US.UTF-8, значение, которое вы получите, может быть «10,00 Lei» или «1000 ¥».

6.1.2 Когда можно использовать

Если вы просто работаете с одной валютой, не имеете дело с дробными центами и выполняете только сложение и вычитание, тоmoneyТип может быть правильным.

6.2 Не используйтеserial

Для новых приложений следует использоватьidentity.

6.2.1 Почему

serialтипы имеют некоторыестранное поведение, что делает управление структурой, зависимостями и разрешениями более громоздким.

6.2.2 Когда можно использовать

• Если вам нужна поддержка PostgreSQL до версии 10.0.
• в некоторой комбинации наследования таблиц
• В более общем случае, если вы каким-то образом используете одну и ту же последовательность из нескольких таблиц, хотя в этих случаях явное объявление может быть предпочтительнее, чемserialтип.