PostgreSQL Q&A: Use Cases, Oracle Comparison, Features, Migration and Best Practices

1. What scenarios are recommended for PostgreSQL?

OLTP workloads, complex SQL queries, SQL‑layer data analysis (e.g., Greenplum), GIS requirements, Oracle migration projects, and integration with multiple external data sources are typical use cases.

2. How does PostgreSQL differ from Oracle?

Design-wise they share concepts such as complex SQL and point‑in‑time recovery (PITR). Deployment-wise Oracle is heavyweight, while PostgreSQL is lightweight (tens of MB) and easier to automate. Functionally, Oracle is a comprehensive suite, whereas PostgreSQL offers a concise core with rich extensions. PostgreSQL also has higher compliance with SQL standards, especially JSON and JSONPath support.

3. Does PostgreSQL provide online hot‑backup similar to Oracle RMAN, and can it restore to any point in time?

Yes. PITR supports time‑based, transaction‑based, and restore‑point based recovery. Tools such as pg_rman and pg_probackup enable single‑node or remote backup and allow database‑level restoration.

4. Does PostgreSQL support RAC‑style shared storage?

It requires software‑level support rather than built‑in shared storage.

5. What are the use cases for PostGIS and how to use it?

PostGIS is suited for location‑based queries and calculations, such as vehicle trajectory analysis in public transport or intelligent video monitoring. Users can refer to the official PostGIS website for documentation and case studies.

6. How powerful are stored procedures in PostgreSQL compared to Oracle?

PostgreSQL originally supported only functions, but recent versions also support stored procedures with manual commit/rollback, offering capabilities comparable to Oracle and supporting multiple programming languages (C, Java, Python, Perl, etc.).

7. Are there many business‑logic implementations using PostgreSQL functions and procedures?

Yes; the author has implemented complex logic such as card issuance, recharge, and loss handling for a public‑transport IC‑card system using PostgreSQL functions.

8. When migrating from Oracle to PostgreSQL, do stored procedures need to be rewritten?

The migration is generally smooth because PostgreSQL and Oracle share similar design philosophies and syntax, though some Oracle‑specific constructs require rewriting.

9. Recommended introductory books?

"PostgreSQL: From Apprentice to Expert", "PostgreSQL Server Programming", and "PostgreSQL in Action".

10. Where is PostgreSQL mainly applied?

It is used in enterprise‑developed products across industries such as public transport and banking, with many case studies available from conferences and online resources.

11. How does PostgreSQL differ from MySQL?

While the author lacks direct MySQL production experience, PostgreSQL was chosen over MySQL in a case where complex SQL support, procedural language, and non‑relational data structures were decisive factors.

12. Comparison of PostgreSQL, Oracle, and MySQL strengths and weaknesses?

PostgreSQL is an open, extensible database platform akin to building with LEGO blocks, requiring users to integrate tools themselves. Oracle is a mature commercial product with a comprehensive feature set but is less lightweight. The author has limited experience with MySQL.

13. Does PostgreSQL support clustering?

PostgreSQL provides built‑in backup and primary‑standby failover interfaces, WAL streaming, and can be clustered using tools like pgpool, keepalived + VIP, repmgr, or Patroni, though these require manual integration.

14. Experience with PostgreSQL 12 versus 10 performance?

Benchmarks with pgbench show modest overall speed gains, but improvements in parallelism, partitioned tables, and recovery mechanisms are noticeable in version 12.

15. Recommended production version?

Version 10 is considered stable for production; EnterpriseDB provides minor‑version updates for it, while versions 11+ lack official support.

16. How long does it take to get started?

For those with a background, a couple of days are enough to become productive; deeper mastery requires ongoing learning.

17. Experience with Oracle‑to‑PostgreSQL migrations?

The author has migrated Oracle systems in the public‑transport sector to PostgreSQL and continues to work on similar projects.

18. Time required from beginner to expert?

Getting started is relatively easy, but achieving expertise is challenging.

19. Why are many enterprises moving from Oracle to PostgreSQL?

Cost savings and the desire for a controllable, self‑developed solution are primary motivations.

20. Can PostgreSQL handle large‑scale business systems?

Yes; the author has deployed PostgreSQL in a quasi‑financial public‑transport system since 2013, with stable operation after upgrading versions.

21. How many customers use PostgreSQL in China?

Over 100, including major tech companies like Alibaba and Tencent.

22. PostgreSQL optimization tips?

Focus on OS‑level parameter tuning, postgresql.conf settings, and application‑level optimizations such as query and index tuning.

23. Improving performance for spatial data queries?

PostgreSQL offers GIS‑specific indexes that boost spatial query performance.

24. When are WAL files on a standby cleared, and have you encountered WAL overflow?

If the primary’s replication slot is configured and the standby is disconnected, WAL can fill up on the primary, even if size limits are set.

25. Is PostgreSQL’s market small?

Contrary to perception, the market is growing steadily.

26. Current mainstream PostgreSQL versions and learning recommendations?

Versions 9 and 10 dominate production; version 10 is recommended for deployment, while learning should focus on version 12 and newer.

27. How stable is pgpool?

pgpool‑II is widely used and provides stable connection pooling, load balancing, and other features.

28. Using PostgreSQL for data warehousing?

It can be achieved via foreign data wrappers (FDW).

— End of Q&A —