Here are sensible defaults I reach for when spinning up a Postgres‑backed service.
Schema & types
Use UUID primary keys (uuid_generate_v4() or app‑side ULIDs).
Add created_at and updated_at with DEFAULT now().
Prefer TEXT over under‑sized VARCHAR(n) unless you truly need the limit.
Indexes
Unique indexes for external identifiers (email, username, slug).
Partial indexes for soft‑deleted rows (e.g., WHERE deleted_at IS NULL).
Composite indexes in the query order you filter/sort.
Migrations
One feature = one migration file; never edit history, always add.
Wrap destructive changes with safe rollouts (create new column, backfill, swap).
-- Example: safe rename via new columnALTER TABLE users ADD COLUMN full_name TEXT;UPDATE users SET full_name = first_name || ' ' || last_name WHERE full_name IS NULL;-- Switch reads/writes in app, then drop old columns in a later migration
Connections
Use a pool (PGbouncer or library pool). Close idle connections.
Keep transactions short; do work in app memory before/after the TX.
Observability
Enable slow query logging; sample queries over N ms.
Track connection counts and queue times.
Simple, boring defaults reduce 90% of “why is prod slow?” incidents.
2:["$","section",null,{"id":"blog","children":[["$","script",null,{"type":"application/ld+json","suppressHydrationWarning":true,"dangerouslySetInnerHTML":{"__html":"{\"@context\":\"https://schema.org\",\"@type\":\"BlogPosting\",\"headline\":\"Postgres in practice: a tiny production playbook\",\"datePublished\":\"2025-07-05\",\"dateModified\":\"2025-07-05\",\"description\":\"Defaults that work: schema design, indexes, migrations, and connection hygiene.\",\"image\":\"https://github.com/Syxless/og?title=Postgres in practice: a tiny production playbook\",\"url\":\"https://github.com/Syxless/blog/backend-postgres-playbook\",\"author\":{\"@type\":\"Person\",\"name\":\"Syxles\"}}"}}],["$","h1",null,{"className":"title font-medium text-2xl tracking-tighter max-w-[650px]","children":"Postgres in practice: a tiny production playbook"}],["$","div",null,{"className":"flex justify-between items-center mt-2 mb-8 text-sm max-w-[650px]","children":["$","$12",null,{"fallback":["$","p",null,{"className":"h-5"}],"children":["$","p",null,{"className":"text-sm text-neutral-600 dark:text-neutral-400","children":"July 5, 2025 (2mo ago)"}]}]}],["$","article",null,{"className":"prose dark:prose-invert","dangerouslySetInnerHTML":{"__html":"$13"}}]]}]
11:[["$","meta","0",{"name":"viewport","content":"width=device-width, initial-scale=1"}],["$","meta","1",{"charSet":"utf-8"}],["$","title","2",{"children":"Postgres in practice: a tiny production playbook | Syxles"}],["$","meta","3",{"name":"description","content":"Defaults that work: schema design, indexes, migrations, and connection hygiene."}],["$","meta","4",{"name":"robots","content":"index, follow"}],["$","meta","5",{"name":"googlebot","content":"index, follow, max-video-preview:-1, max-image-preview:large, max-snippet:-1"}],["$","meta","6",{"property":"og:title","content":"Postgres in practice: a tiny production playbook"}],["$","meta","7",{"property":"og:description","content":"Defaults that work: schema design, indexes, migrations, and connection hygiene."}],["$","meta","8",{"property":"og:url","content":"https://github.com/Syxless/blog/backend-postgres-playbook"}],["$","meta","9",{"property":"og:image","content":"https://github.com/Syxless/og?title=Postgres%20in%20practice:%20a%20tiny%20production%20playbook"}],["$","meta","10",{"property":"og:type","content":"article"}],["$","meta","11",{"property":"article:published_time","content":"2025-07-05"}],["$","meta","12",{"name":"twitter:card","content":"summary_large_image"}],["$","meta","13",{"name":"twitter:title","content":"Postgres in practice: a tiny production playbook"}],["$","meta","14",{"name":"twitter:description","content":"Defaults that work: schema design, indexes, migrations, and connection hygiene."}],["$","meta","15",{"name":"twitter:image","content":"https://github.com/Syxless/og?title=Postgres%20in%20practice:%20a%20tiny%20production%20playbook"}],["$","link","16",{"rel":"icon","href":"/favicon.ico","type":"image/x-icon","sizes":"16x16"}]]
1:null