PostgreSQL

Never forgets a constraint. Very elephant of it.

Connecting

Field	Default	Notes
Host	localhost
Port	5432
Database		Optional; pick one after connecting
User	postgres
Password		Kept in the Keychain

• Use TLS / SSL encrypts the connection. Verify server certificate also rejects untrusted and self-signed certificates, like sslmode=verify-full.
• URLs: postgres:// and postgresql://, with sslmode.
• SSH tunnels are supported.

Lily speaks the PostgreSQL protocol directly; nothing else needs to be installed. Services built on PostgreSQL, like Supabase, connect the same way.

Databases and schemas

The sidebar's Database menu lists every database and has New database…, Refresh database list and Drop database… (for databases other than the one you are on). The Schema menu picks the schema; the schema's … menu adds Rename schema… and Create enum type….

PostgreSQL-only tools

• Objects: functions, procedures, triggers, sequences and enum types in the sidebar, with their definitions.
• Enums: create types, add and rename values. Enum columns get a menu of values when you edit.
• Roles & LISTEN/NOTIFY (in Tools): browse pg_roles, listen on a channel and send NOTIFY messages.
• Table size in the Structure tab.

Performance

• Trace: Live, recording, Blocks and Top from pg_stat_statements (Lily can enable the extension; it must also be in shared_preload_libraries).
• Visual plan from EXPLAIN (FORMAT JSON).
• Speed up this query and Investigate create indexes CONCURRENTLY.

Health Check

On top of the common rules, PostgreSQL gets Row-Level Security (critical when an auth schema suggests Supabase or PostgREST), PUBLIC grants and unused indexes.

Migrating from PostgreSQL

PostgreSQL is the richest source for migrations: keys, unique constraints, indexes, enums, arrays, identity columns and defaults all carry over.

Playground

postgres:16 on port 15432, user lily, password lily123, database lily_store. See Docker Playground.