Tips, Security and Q&A

Security Best Practices

• Force SSL on remote connections.
• Store the master password in a password manager.
• Create role-level users: least privilege per app.
• Prefer pg_hba.conf restrictions over firewall alone.
• Never run pgAdmin on a public port without a reverse proxy and auth.

Productivity Tips

• Use Explain/Explain Analyze on every slow query.
• Save frequent queries from the query tool history.
• Export grids as CSV for reports.
• Review the auto-generated DDL to learn SQL.
• Keep maintenance (VACUUM/ANALYZE) on a schedule.

Interview Q&A

Q: How do you find what is blocking a table?

A: Open the Dashboard > Locks tab; the blocking PID chain shows who holds the lock, then decide to cancel or wait for the transaction.

Q: How do you back up only a schema?

A: In Backup set options to schema-only; for selective tables use the Table options or filter in pg_dump.

Q: Why is EXPLAIN ANALYZE important?

A: It executes the query, revealing row estimates vs actuals, and exposes seq scans, missing indexes and join problems for tuning.

Key Points

• Least-privilege roles and SSL keep databases safe.
• Locks, activity and VACUUM fix most operational pain.
• EXPLAIN ANALYZE is your query-performance ally.
• Automate dumps; keep a practice restore procedure.