Explore

Compare columns against values of the same type to avoid silent casts that disable indexes.

Run EXPLAIN on slow or important queries to confirm indexes are used and scans are bounded.

Avoid wrapping indexed columns in functions so the optimizer can use the index.

Add indexes on columns that appear in join conditions, filters, and sort clauses.

Run with STRICT_TRANS_TABLES so invalid or out-of-range values error instead of silently changing.

Use InnoDB for transactions, foreign keys, and row-level locking instead of MyISAM.

Pick the smallest data type that fits the domain instead of defaulting to BIGINT or wide VARCHAR.

Declare FOREIGN KEY constraints so the database enforces valid references between tables.

Choose an isolation level (e.g. READ COMMITTED) that matches your concurrency and consistency needs.

Bind user input as parameters rather than concatenating it into SQL strings.

Issue per-service credentials from a secrets manager, rotate them on a schedule, and keep them out of source control.

Match instance class and storage to actual load, and alert on CPU, IOPS, and free-storage thresholds before they are hit.

Enforce sslmode=require (or stricter, with CA verification) so no client can connect to the managed database over plaintext.

Create scoped application roles with only the privileges they need; reserve superuser/owner accounts for migrations and admin.

Route read-heavy or analytics queries to read replicas, keeping the primary focused on writes and consistency-critical reads.

Configure statement_timeout and idle_in_transaction_session_timeout so stuck queries and orphaned transactions are killed automatically.

Track connection count against max_connections and capture slow queries (e.g. via pg_stat_statements) with alerting thresholds.

Use a multi-AZ/standby configuration and ensure the app reconnects cleanly on failover instead of caching a dead endpoint.

Use text (optionally with a CHECK) instead of varchar(n); the length cap adds no performance benefit.

Store points in time as timestamptz so values are unambiguous across time zones and DST.

Use GENERATED ... AS IDENTITY or uuid for surrogate keys instead of the legacy serial pseudo-type.

Compute derived values with GENERATED ALWAYS AS ... STORED instead of duplicating logic in every writer.

Set a lock_timeout, split steps, and add columns nullable-then-backfill to keep migration locks short.

Store monetary amounts as numeric/decimal so values are exact; binary floats introduce rounding errors.