This content originally appeared on Level Up Coding – Medium and was authored by Juan Andrés Leiva
A comprehensive guide to avoiding common PostgreSQL pitfalls that can compromise performance, data integrity, and maintainability
Introduction
PostgreSQL is one of the most powerful and flexible relational databases available, but with great power comes the potential for misuse. After years of building production systems, I’ve seen countless hours wasted debugging issues that could have been prevented by following established best practices. Over the years, the PostgreSQL community has identified numerous anti-patterns — practices that may seem reasonable at first but lead to problems down the road.
This guide compiles the most important things you should avoid when working with PostgreSQL, drawn from the collective wisdom of the community and hard-learned production lessons. Whether you’re designing a new schema or maintaining an existing system, these guidelines will help you avoid common traps.
Key Takeaways
Before we dive deep, here are the critical rules that will save you the most pain:
For Data Types:
Always use timestamptz for timestamps, never timestamp- Use text instead of char(n) or arbitrary varchar(n) limits
- Use numeric for money, not the money type
- Use identity columns instead of serial (PostgreSQL 10+)
For Queries:
- Use NOT EXISTS instead of NOT IN with subqueries
- Avoid BETWEEN for timestamp ranges—use >= and < instead
- Use lowercase_with_underscores for all identifiers
For Schema Design:
- Use triggers instead of rules (always)
- Avoid table inheritance — use foreign keys or native partitioning
For Security:
- Never use trust authentication over TCP/IP
- Always use scram-sha-256 or better for password authentication
If you remember nothing else, remember these. Now let’s explore why each matters.
Database Encoding
Never Use SQL_ASCII
The Problem: SQL_ASCII is not actually ASCII. It means “no conversions” — the database performs no encoding validation or conversion whatsoever. When you use SQL_ASCII, PostgreSQL simply accepts whatever bytes you give it and treats them as belonging to whatever encoding you claim, without verification.
This creates a database that’s a ticking time bomb. You’ll end up with data in multiple encodings — UTF-8, Latin1, Windows-1252, and who knows what else — all mixed together with no way to reliably recover the original characters. When you try to retrieve data, you’ll get mojibake (garbled characters), and there’s no reliable way to fix it after the fact.
The Exception: The only legitimate use case is when you’re dealing with data that’s already a hopeless mixture of unlabeled encodings, such as old IRC logs or non-MIME-compliant emails. Even then, consider using the bytea type instead or auto-detecting UTF-8 and assuming non-UTF-8 data is in a specific encoding like Windows-1252.
What to Do Instead: Use UTF-8 (UTF8 encoding) for new databases. It supports all characters from all languages and is the internet standard.
Tool Usage
Don’t Use psql -W or –password
The Problem: The -W or –password flags tell psql to prompt for a password before attempting to connect to the server. This means you'll be prompted even if the server doesn't require a password, which creates confusion when setting up authentication.
If your server is configured for peer authentication (common on local development machines), using -W makes it appear that password authentication is required when it isn't. Even worse, if you enter the wrong password or the user has no password set, you might still be logged in via peer authentication—making you think you have the correct password when you don't. This leads to confusion when trying to connect from other clients or systems.
What to Do Instead: Just run psql without the flag. If the server requires a password, psql will prompt you automatically. You'll save a round trip to the server and avoid authentication confusion.
Query Rewriting and Schema Design
Never Use Rules
The Problem: Rules look like conditional logic, but they’re actually a query rewriter. They don’t execute in the way you’d intuitively expect — they rewrite your query to modify it or add additional queries to it. This makes all non-trivial rules incorrect or at least surprising in their behavior.
Rules were a clever implementation detail for views, but exposing them directly to users has proven to be a mistake. They’re confusing, error-prone, and have been the source of countless bugs and misunderstandings.
What to Do Instead: Use triggers. Triggers execute procedural code in response to events and behave exactly as you’d expect. If you think you need a rule, you almost certainly want a trigger instead.
Avoid Table Inheritance
The Problem: Table inheritance was part of a fad in database design where the database structure was tightly coupled to object-oriented code. In practice, this tight coupling didn’t produce the desired results.
Table inheritance has several problems: constraints (particularly foreign keys and unique constraints) don’t work intuitively across the inheritance hierarchy, and you can end up with data integrity issues that are difficult to reason about.
What to Do Instead: Use foreign keys to model relationships between tables. Since PostgreSQL 10, native table partitioning has replaced the one common legitimate use case for table inheritance (partitioning large tables).
The Rare Exception: The temporal_tables extension for row versioning is one of the few remaining use cases where table inheritance might be acceptable if you're in a pinch and SQL:2011 temporal support isn't available. Even then, be aware of the caveats when working with parent tables.
SQL Constructs
Don’t Use NOT IN with Subqueries
The Problem: NOT IN behaves unexpectedly when NULL values are present, and it has severe performance issues with subqueries.
Consider this query:
SELECT * FROM foo WHERE col NOT IN (1, NULL);
This always returns zero rows. Why? Because col IN (1, NULL) returns TRUE if col=1, and NULL otherwise (never FALSE). Since NOT(TRUE) is FALSE but NOT(NULL) is still NULL, there's no way for NOT IN (1, NULL) to return TRUE.
With subqueries, the situation is worse:
SELECT * FROM foo WHERE foo.col NOT IN (SELECT bar.x FROM bar);
If any value of bar.x is NULL, this returns zero rows—probably not what you intended.
Additionally, the query planner can’t optimize NOT IN (SELECT …) into an anti-join, so it becomes either a hashed subplan (fast but only used for small result sets) or a plain subplan (horrifically slow with O(N²) complexity). Performance might look fine in testing but collapse by five orders of magnitude in production when the data grows.
What to Do Instead: Use NOT EXISTS:
SELECT * FROM foo
WHERE NOT EXISTS (SELECT FROM bar WHERE foo.col = bar.x);
The Exception: NOT IN (list, of, values) is safe when you're excluding specific constant values, as long as you're certain none of them are NULL.
Don’t Use Upper Case Table or Column Names
The Problem: PostgreSQL folds all unquoted identifiers to lowercase. If you create a table with CREATE TABLE Foo(), it creates a table named foo. But if you use CREATE TABLE "Bar"(), it creates a table named Bar (with uppercase B).
This creates a quoting nightmare:
- SELECT * FROM Foo works (folded to foo)
- SELECT * FROM foo works
- SELECT * FROM "Bar" works
- SELECT * FROM "Foo" fails (no table named Foo with capital F)
- SELECT * FROM Bar fails (folded to bar, which doesn't exist)
- SELECT * FROM bar fails
Some tools always quote identifiers, others never do. When you mix tools, you’ll spend hours debugging why queries work in one tool but not another.
What to Do Instead: Use lowercase names with underscores: user_profiles, order_items, created_at. You'll never need to quote them, and everything will just work.
The Exception: If you need “pretty” names in report output, use column aliases: SELECT user_name AS "User Name" FROM users.
Be Careful with BETWEEN (Especially for Timestamps)
The Problem: BETWEEN uses closed-interval comparison—both endpoints are included. This is particularly problematic with timestamps:
SELECT * FROM events
WHERE event_time BETWEEN '2018-06-01' AND '2018-06-08';
This includes events at exactly 2018-06-08 00:00:00.000000 but excludes events later on that same day. You'll appear to have complete data for June 8th, but you're actually missing everything after midnight. When you get an event at exactly midnight, you might double-count it in overlapping queries.
What to Do Instead: Use explicit comparisons:
SELECT * FROM events
WHERE event_time >= '2018-06-01'
AND event_time < '2018-06-08';
The Exception: BETWEEN is safe for discrete quantities like integers or dates (not timestamps), as long as you remember that both endpoints are included.
Date and Time Storage
Use timestamptz, Not timestamp
The Problem: Despite its name, timestamptz (also known as timestamp with time zone) doesn't store a timezone. It stores a point in time as microseconds since January 1, 2000 UTC. You can insert values in any timezone, and PostgreSQL converts them to UTC for storage.
The timestamp type (also known as timestamp without time zone) stores only the date and time you give it—like a photograph of a calendar and clock. Without knowing the timezone, you don't actually know what moment in time it represents. Arithmetic between timestamps from different locations or across daylight saving time changes will give wrong answers.
What to Do Instead: Use timestamptz to store points in time. PostgreSQL will handle all timezone conversions for you, and arithmetic will work correctly across daylight saving time boundaries and between different timezones.
The Exception: If you’re storing timestamps abstractly — like “the meeting happens at 2:00 PM” without caring what timezone — then timestamp might be appropriate. But if you're recording when something actually happened, use timestamptz.
Don’t Store UTC in timestamp Columns
The Problem: Some developers coming from databases with poor timezone support use timestamp without time zone to store UTC values. This forces you to manually track that these are UTC values, and the database can't help you.
Simple time calculations become nightmarish. To get “last midnight in the user’s timezone,” you need:
date_trunc('day', now() AT TIME ZONE u.timezone)
AT TIME ZONE u.timezone AT TIME ZONE 'UTC'With timestamptz, this would just be:
date_trunc('day', now() AT TIME ZONE u.timezone)What to Do Instead: Use timestamptz and let PostgreSQL handle the timezone conversions.
Never Use timetz
The Problem: Even the PostgreSQL manual warns you about this type, saying it’s “only implemented for SQL compliance” and that its “definition exhibits properties which lead to questionable usefulness.”
A time with a timezone but no date is almost never what you actually need. What does “3:00 PM EST” mean without a date? It means different things on different sides of a daylight saving time change.
What to Do Instead: Use timestamptz for timestamps, time for times of day (when the timezone doesn't matter), and date for dates.
Don’t Use CURRENT_TIME
The Problem: CURRENT_TIME returns a value of type timetz, which as we just discussed, is problematic.
What to Do Instead:
- Use CURRENT_TIMESTAMP or now() for timestamptz
- Use LOCALTIMESTAMP for timestamp without time zone
- Use CURRENT_DATE for date
- Use LOCALTIME for time
Don’t Use timestamp(0) or timestamptz(0)
The Problem: Precision specifications on timestamps round rather than truncate. If you store now() into a timestamp(0) column, you might be storing a value up to half a second in the future. This can cause subtle bugs with time-based logic.
What to Do Instead: Use date_trunc('second', timestamp_value) to explicitly truncate to the second.
Don’t Use +/-HH:mm as Text Timezone Names
The Problem: PostgreSQL doesn’t accept fixed timezone offsets like +05:00 in place of timezone names. If you provide such a string, it's interpreted as a POSIX timezone specification with the unfortunate property that positive values shift west while negative values shift east—the opposite of the ISO convention.
What to Do Instead: For fixed offsets, use an interval:
timestamp_value AT TIME ZONE INTERVAL '04:00'
The Exception: String timestamptz literals in ISO format can use signed offsets with standard ISO interpretation:
SELECT '2024-01-31 17:16:25+04'::timestamptz; -- yields 1pm UTC
Text Storage
Never Use char(n)
The Problem: The char(n) type pads values with spaces to the specified width. This almost certainly isn't what you want. Those spaces are included in storage (so it doesn't save space), they're ignored in comparisons (which can cause unexpected behavior), and they need to be stripped in many contexts (which hurts performance).
Worse, char(n) isn't even a fixed-width type from a storage perspective. Since characters can take multiple bytes in UTF-8, the actual storage is variable-length anyway.
The manual specifically warns about this behavior with collations:
SELECT 'a '::CHAR(2) COLLATE "C" < E'a\n'::CHAR(2)
-- returns true, even though C locale considers
-- space greater than newline
What to Do Instead: Use text. Always.
The Exception: When porting ancient software that requires fixed-width fields. Or when you read the manual’s description and think “yes, that’s exactly what I need” rather than running away screaming.
Don’t Use char(n) Even for Fixed-Length Identifiers
The Problem: A common response to “don’t use char(n)" is "but my values must always be exactly N characters" (like country codes or hashes). Even in these cases, char(n) is wrong.
Why? Because char(n) doesn't reject values that are too short—it silently pads them with spaces. So it provides no validation benefit. Worse, comparing a char(n) column against a parameter typed as text or varchar can prevent index usage, and this is hard to debug because manual queries work fine.
What to Do Instead: Use text with a CHECK constraint:
CREATE TABLE countries (
code text CHECK (code ~ '^[A-Z]{3}$'),
...
);
Or create a domain:
CREATE DOMAIN country_code AS text
CHECK (VALUE ~ '^[A-Z]{3}$');
Think Before Using varchar(n)
The Problem: varchar(n) throws an error if you try to insert a string longer than N characters. This is useful when you genuinely need a length limit, but it's often used thoughtlessly as a default.
If you arbitrarily choose varchar(20) for a surname field, you're setting yourself up for a production error when Hubert Blaine Wolfeschlegelsteinhausenbergerdorff signs up for your service. Many developers use varchar(255) by habit, carried over from databases where unlimited text storage wasn't efficient.
From a storage and performance perspective, varchar(20), varchar, and text are identical when storing the same string. The only difference is that varchar(n) enforces a maximum length.
What to Do Instead: Use text as your default. If you need constraints on the value—minimum length, maximum length, character set restrictions—use a CHECK constraint, which can enforce all of these at once:
CREATE TABLE users (
username text CHECK (
length(username) BETWEEN 3 AND 20 AND
username ~ '^[a-z0-9_]+$'
),
...
);
The Exception: If you specifically want a maximum length and don’t need other validation, varchar(n) is perfectly fine. Also, varchar is in the SQL standard while text is not, so it might be better for maximum portability (though all major databases support text-like types).
Other Data Types
Don’t Use money
The Problem: The money type is a fixed-point type implemented as a machine integer. While arithmetic is fast, it has several serious problems:
- It doesn’t handle fractions of a cent
- Its rounding behavior is probably not what you want for financial calculations
- It doesn’t store a currency — it assumes all money columns use the currency specified by the database's lc_monetary locale setting
- If you change lc_monetary, all your values become wrong. Insert $10.00 with lc_monetary='en_US.UTF-8', change the setting, and you might retrieve ¥1,000 or 10,00 Lei
What to Do Instead: Use numeric for monetary values, possibly with an adjacent column storing the currency code:
CREATE TABLE transactions (
amount numeric(19,4) NOT NULL,
currency char(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
...
);
The Exception: If you’re working in a single currency forever, don’t need fractional cents, and only do addition and subtraction, money might work. But those are big ifs.
Use Identity Columns Instead of serial
The Problem: The serial types (serial, bigserial, smallserial) have weird behaviors that make schema, dependency, and permission management unnecessarily cumbersome. They're implemented as a sequence with special dependency tracking, but that tracking doesn't work quite right in all scenarios.
What to Do Instead: For PostgreSQL 10 and later, use identity columns
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY,
...
);
Or if you want to allow manual ID insertion:
CREATE TABLE users (
id integer GENERATED BY DEFAULT AS IDENTITY,
...
);
The Exception: If you need compatibility with PostgreSQL 9.6 or earlier, or if you’re using table inheritance (but see the warning about that), you might need serial.
Authentication
Never Use trust Authentication Over TCP/IP
The Problem: The trust authentication method allows anyone to connect as any user without providing a password. With a line like this in your pg_hba.conf:
host all all 0.0.0.0/0 trust
You’re allowing anyone on the internet to authenticate as any PostgreSQL user, including the superuser. They can claim to be postgres, and PostgreSQL will believe them.
Even on your local development machine, trust over TCP/IP is dangerous. Anyone with access to your machine can connect as any user.
What to Do Instead: Use proper authentication:
- For local development over TCP/IP: scram-sha-256 authentication with passwords, or restrict to localhost and use peer authentication over Unix sockets
- For production: Always use strong authentication like scram-sha-256 (PostgreSQL 10+), combined with SSL/TLS for the connection
- For CI/CD: Use a dedicated test database on an isolated network with restricted authentication
The Exception: There really isn’t one. Even for local development, peer authentication over Unix sockets is better than trust over TCP/IP.
Wrap up
PostgreSQL is extraordinarily powerful, but like any sophisticated tool, it rewards careful use and punishes carelessness. These anti-patterns have been identified through years of collective experience — each one represents real production issues that have bitten real developers.
The good news is that most of these pitfalls are easy to avoid once you know about them. Use text instead of varchar(n), use timestamptz instead of timestamp, use NOT EXISTS instead of NOT IN with subqueries, and always use proper authentication. Follow these guidelines, and you'll avoid the most common PostgreSQL mistakes.
Remember: when PostgreSQL seems to be behaving strangely, it’s often because we’re using a feature in a way that seemed reasonable but doesn’t match how PostgreSQL actually works. The answer usually isn’t to fight the database — it’s to learn the PostgreSQL way of doing things.
For more information and updates to these recommendations, visit the PostgreSQL Wiki’s “Don’t Do This” page and consult the official PostgreSQL documentation. You can also use tools like schemalint to automatically verify your database schema against these recommendations.
This article is based on the PostgreSQL community wiki’s collective wisdom. Special thanks to all the PostgreSQL developers and community members who have contributed to documenting these anti-patterns over the years.
Juan Andrés Leiva
I write about .NET architecture, DevOps, and modern development practices.
Follow for deep technical dives and production-ready strategies.
Share your thoughts or questions in the comments — I reply to all messages.
GitHub · LinkedIn · Medium
PostgreSQL Mistakes You’re Probably Making (And How to Fix Them) was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding – Medium and was authored by Juan Andrés Leiva