Skip to content
Planet Argon Planet Argon Engineering Handbook

PostgreSQL

PostgreSQL, often simply Postgres, is an open-source relational database management system (RDBMS) that emphasizes extensibility, technical standards compliance, and robustness. PostgreSQL provides an object-relational database system, which allows it to support both relational (SQL) and non-relational (JSON, XML) querying. It supports a rich set of native data types and offers such features as complex queries, foreign keys, triggers, updatable views, transactional integrity, and multi-version concurrency control. It is highly scalable both in the number of users it can support and in the sheer volume of data it can manage.

Using PostgreSQL at Planet Argon?

Section titled “Using PostgreSQL at Planet Argon?”

At Planet Argon, we use PostgreSQL to store, retrieve, and manipulate data for certain client applications. Engineers on these projects can create schemas that define the data types, the relationships between the data, and the operations that can be performed on the data.

Installing PostgreSQL?

Section titled “Installing PostgreSQL?”
  1. Use homebrew to install PostgreSQL, then configure to autolaunch on start-up:
brew install postgres
  1. Use the following command to have postgresql immediately and restart at login using a background service:
  brew services start postgresql
  1. If you don’t want to use a background service, simply run:
  pg_ctl -D /usr/local/var/postgres start

Differences Between PostgreSQL and MySQL

Section titled “Differences Between PostgreSQL and MySQL”

MySQL and PostgreSQL are both powerful, open-source relational database management systems, but they have some key differences:

  1. ACID Compliance: PostgreSQL is fully ACID compliant (Atomicity, Consistency, Isolation, Durability), ensuring data integrity even in cases of system failure. MySQL only offers ACID compliance when used with InnoDB and NDB Cluster storage engines.

  2. Performance: Generally, MySQL is considered faster for read-heavy operations due to its default configuration and storage engines. On the other hand, PostgreSQL is often favored for systems with heavy write and complex query operations, thanks to its powerful query optimizer.

  3. Extensibility: PostgreSQL is highly extensible and allows for custom functions, operators, and complex data types. MySQL is less flexible in terms of extensions.

  4. Replication: MySQL has traditionally offered more options for replication, including primary-replica and primary-primary replication, making it a popular choice for scalability. However, PostgreSQL has significantly improved its replication capabilities in recent versions.

  5. SQL Compliance: PostgreSQL adheres more closely to SQL standards and supports many advanced features of the SQL language that MySQL does not, including some forms of window functions and recursive queries.

  6. GIS Features: PostgreSQL, with its PostGIS extension, offers superior GIS features compared to MySQL, making it a popular choice for spatial databases.

  7. Storage Engines: MySQL supports plug-in storage engines (such as InnoDB, MyISAM, Memory, etc.), so you can choose based on specific needs. PostgreSQL has a single storage engine but it’s robust and offers a wide array of features.

Additional PostgreSQL Resources:

Section titled “Additional PostgreSQL Resources:”