Question 1

PostgreSQL vs MySQL, which should I use?

Accepted Answer

PostgreSQL: complex queries, JSONB, geospatial (PostGIS), full-text search, window functions, CTEs, advanced indexing, extensible. MySQL: simpler, faster reads on OLTP workloads, better replication. Use PostgreSQL for startups/complex apps (Airbnb, GitHub, Slack); MySQL for WordPress and high-read-volume LAMP stacks. PostgreSQL is the safer default for engineers learning databases.

Question 2

What is JSONB and how does it replace MongoDB?

Accepted Answer

JSONB = binary-encoded JSON stored natively in PostgreSQL, indexed and queryable. You get SQL joins + JSON flexibility. Outperforms MongoDB on indexing, querying, and transactions. Use JSONB for semi-structured data while keeping relational guarantees. Example: store user profile as JSONB, still JOIN on user_id. Schema validation via CHECK constraints or PL/pgSQL triggers.

Question 3

What is MVCC and why does it matter?

Accepted Answer

MVCC = Multi-Version Concurrency Control. Every transaction sees a consistent snapshot of data from its start time. Allows reads while writes happen (no locks) and vice versa. Cost: old rows must be vacuumed (VACUUM command). Side effect: SELECT count(*) is expensive (must scan all rows). Benefit: high concurrency, no blocking.

Question 4

Partitioning vs sharding, when do I use each?

Accepted Answer

Partitioning: split single table across PostgreSQL itself (range/list/hash), queries still use normal SQL. Scale up to ~50GB per partition. Sharding: split data across multiple Postgres servers, requires application logic (Citus extension automates). Partition first; shard only if single server saturates. Sharding adds consistency risk, network latency, join complexity.

Question 5

How do I find slow queries and optimize them?

Accepted Answer

Enable pg_stat_statements extension (track query execution times), then query: SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC. Use EXPLAIN ANALYZE to see actual row counts and plan. Index missing columns (look for Seq Scan on large tables), add JOINable columns, rewrite N+1 queries. Check autovacuum keeping up (look at bloat in pg_stat_user_tables).

Question 6

What's the difference between Postgres replication, backup, and disaster recovery?

Accepted Answer

Backup: point-in-time copy (pg_dump snapshot). Replication (streaming): replica polls primary via WAL (write-ahead log), stays seconds behind. Use for read scaling. HA (Patroni): automated failover if primary dies, replica becomes primary. Use both: WAL archiving for recovery, replication for standby. Test failover regularly; untested HA is broken HA.

Question 7

When should I use TimescaleDB or pgvector?

Accepted Answer

TimescaleDB: time-series data (metrics, logs, telemetry). Stores billions of rows efficiently with compression, automatic partitioning, time-bucket aggregations. pgvector: AI/ML embeddings (OpenAI embeddings, LLM similarity search). Stores 1536-dim vectors, fast cosine similarity queries. Both are PostgreSQL extensions (drop-in), no new database needed.

地區	初級	中級	高級
USA	$125,000	$165,000	$230,000
UK	£80,000	£110,000	£160,000
EU	€85,000	€115,000	€165,000
CANADA	CA$130,000	CA$170,000	CA$240,000

PostgreSQL

什麼是 PostgreSQL

📋 開始之前

💰 各地區薪資

🎓 認證

🎯 使用 PostgreSQL 的職業

⚖ 對比

❓ 常見問題

不確定這項技能適不適合你？

找到理想的職業方向