LLM rules for PostgreSQL
Postgres is incredibly versatile: from storing vectors to acting as a task queue. Its simplicity and extensibility make it a reliable tool.
Versatility, however, comes at a cost. As your schema grows and your team expands, the database becomes harder to manage.
Today, large language models help engineers quickly generate SQL and ORM code. Yet spotting Postgres performance issues, before code reaches production, remains a challenge.
Traditional linters can fill that gap, but writing and maintaining linter rules is hard. Teams often rely on one engineer who knows the database inside out to review every change.
This is where LLMs shine. They are:
- Customizable – can check for anything you ask.
- Quick to implement – in many cases, a single well-crafted prompt is enough.
Despite their power, LLMs need well-defined rules to guide them. These rules improve both code generation and automated reviews.
The rules below reflect what we learned while building wispbit, our code reviewer. You can explore the full rules collection on the wispbit rules page.
Preventing table locks by enforcing concurrent indexes
Table locks cause downtime and affect your bottom line. This rule makes concurrent indexes mandatory.
When creating indexes in PostgreSQL, always use the `CONCURRENTLY` option to prevent blocking writes during index creation.
Bad:
```sql
CREATE INDEX idx_users_email ON users(email);
```
Good:
```sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
```
Preventing downtime from missing columns
In most setups, removing a column causes brief downtime because your ORM uses that column before the code is deployed.
The following is an example using the Prisma ORM but you can apply this to any ORM that supports ignoring columns:
When creating a migration to remove a column from the database, ensure that the schema has the `@ignore` attribute on that column in the Prisma schema. Search for the .prisma schema in the codebase to verify this.
```sql
ALTER TABLE "User" DROP COLUMN "createdAt";
ALTER TABLE "User" DROP COLUMN "updatedAt";
```
```prisma
model User {
id Int @id @default(autoincrement())
email String @unique
password String
remarks String?
createdAt DateTime @default(now()) @ignore
updatedAt DateTime @updatedAt @ignore
}
```
Ensure critical indexes always exist
By default, foreign key constraints do not add indexes. With large tables, this can be problematic. This rule enforces adding an index for each foreign key.
When adding a foreign key constraint in PostgreSQL, always add a corresponding index.
Bad:
```sql
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id);
```
Good:
```sql
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id);
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
```
Ensure table column consistency
A consistent data model makes it easier for engineers to jump in and contribute to your code.
All CREATE TABLE statements must include `id`, `created_at`, and `updated_at` columns.
Bad:
```sql
CREATE TABLE users (
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL
);
```
Good:
```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);
```
Ensure indexes are not already covered
A composite index like email_username_idx covers both the email and email, username query patterns. A single index for email is redundant.
This rule is specific to SQLAlchemy but you can ask an LLM to adapt it to your favorite ORM.
Ensure that individual column indexes in SQLAlchemy are not covered by existing composite indexes.
Bad:
```python
class User(Base):
__tablename__ = "users"
__table_args__ = (
Index("email_username_idx", "email", "username"),
)
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(255), index=True) # covered by email_username_idx
username: Mapped[str] = mapped_column(String(100))
status: Mapped[str] = mapped_column(String(20))
```
Good:
```python
class User(Base):
__tablename__ = "users"
# Only composite index needed - covers both email and email+username queries
__table_args__ = (
Index("email_username_idx", "email", "username"),
)
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(255))
username: Mapped[str] = mapped_column(String(100))
status: Mapped[str] = mapped_column(String(20))
```
Ensure queries are covered by indexes
If you write a query, it should be covered by an index.
Again, this is written in SQLAlchemy but you can ask an LLM to adapt it to your ORM or the wispbit interface.
Ensure that SQLAlchemy query patterns are covered by appropriate database indexes.
Bad:
```python
# queries.py
def get_user_by_email(session: Session, email: str):
# Will perform full table scan - no index on email
return session.scalar(select(User).where(User.email == email))
# models.py
class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(255)) # No index
status: Mapped[str] = mapped_column(String(50))
created_at: Mapped[datetime] = mapped_column(DateTime)
```
Good:
```python
# queries.py
def get_user_by_email(session: Session, email: str):
# Is covered by an index
return session.scalar(select(User).where(User.email == email))
# models.py
class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(255), index=True)
status: Mapped[str] = mapped_column(String(50))
created_at: Mapped[datetime] = mapped_column(DateTime)
```
Coming up with your own rules
Start by recalling the last time you experienced:
- Downtime triggered by a code change
- A blocking comment that held up a pull request
Each incident or postmortem points to a gap that a focused rule can close. Write the rule in plain language, and agree on it with the team.
Postgres remains versatile and largely unopinionated. That flexibility is its strength, but as more engineers start working on the same database, you need shared standards. Capture them as rules and let the LLM enforce them so everyone ships with confidence.
This blog post was originally published in the Neon Community