Author Topic: postgresql vs mysql ดีต่างกันยังไง  (Read 3927 times)

golfreeze

  • Administrator
  • Hero Member
  • *****
  • Posts: 2031
    • View Profile
    • นั่งสมาธิ สติปัฏฐานสี่ พาเที่ยววัด แนะนำวัด แจกcd ธรรมะฟรี
    • Email
postgresql vs mysql ดีต่างกันยังไง
« on: มีนาคม 07, 2019, 11:39:22 AM »
Special Thank: https://blog.panoply.io/postgresql-vs.-mysql

Who uses these databases?
Here are a few examples of companies that use these databases:

PostgreSQL: Apple, BioPharm, Etsy, IMDB, Macworld, Debian, Fujitsu, Red Hat, Sun Microsystem, Cisco, Skype. See the full list here.

MySQL: GitHub, US Navy, NASA, Tesla, Netflix, WeChat, Facebook, Zendesk, Twitter, Zappos, YouTube, Spotify. See the full list here.

What about database structure?
PostgreSQL: PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. PostgreSQL is ACID-compliant, transactional, has updatable and materialized views, triggers, and foreign keys. It also supports functions and stored procedures.

PostgreSQL uses tables, constraints, triggers, roles, stored procedures and views as the core components that you work with. A table consists of rows, and each row contains a same set of columns. PostgreSQL uses primary keys to uniquely identify each row (a.k.a. record) in a table, and foreign keys to assure the referential integrity between two related tables.

It's worth mentioning that PostgreSQL also supports many NoSQL features as well.

MySQL: MySQL is an open-source relational database management system (RDBMS). Just like PostgreSQL, and all other relational databases for that matter, MySQL uses tables as a core component and has more-less the same feature set as PostgreSQL.

To be fair and to not make this PostgreSQL vs. MySQL post all about how PostgreSQL is great, it’s worth mentioning that newer versions of MySQL (5.7+) also support some NoSQL features as well.

Are indexes needed?
Indexes enhance database performance, as they allow the database server to find and retrieve specific rows much faster than without an index. But, indexes add a certain overhead to the database system as a whole, so they should be used sensibly.

Without an index, the database server must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more costly the operation.

PostgreSQL: PostgreSQL includes built-in support for regular B-tree and hash indexes. Indexes in PostgreSQL also support the following features: 

Expression indexes: can be created with an index of the result of an expression or function, instead of simply the value of a column.
Partial indexes: index only a part of a table.