Newer versions of PostgreSQL have improved in terms of speed (when it
comes to disk access, sorting, and so on). In certain situations, such as periods
of heavy simultaneous access, PostgreSQL can be significantly faster than
MySQL, as you will see in the next section. However, MySQL is still plenty
fast compared to many other databases.
Data Locking
To prevent data corruption, a database needs to put a lock on data while it is
being accessed. As long as the lock is on, no other process can access the data
until the first process has released the lock. This means that any other
processes trying to access the data have to wait until the current process
completes. The next process in line then locks the data until it is finished, and
the remaining processes have to wait their turn, and so on.
Of course, operations on a database generally complete quickly, so in
environments with a small number of users simultaneously accessing the
database, the locks are usually of such short duration that they do not cause
any significant delays. However, in environments in which many people are
accessing the database simultaneously, locking can create performance
problems as people wait their turn to access the database.
Older versions of MySQL lock data at the table level, which can be a
bottleneck for updates during periods of heavy access. This means that when
someone writes a row of data in the table, the entire table is locked so no one
else can enter data. If your table has 500,000 rows (or records) in it, all
500,000 rows are locked any time one row is accessed. Once again, in
environments with a relatively small number of simultaneous users, this
doesn’t cause serious performance problems because most operations
complete so quickly that the lock time is extremely short. However, in
environments in which many people are accessing the data simultaneously,
MySQL’s table-level locking can be a significant performance bottleneck.
PostgreSQL, in contrast, locks data at the row level. In PostgreSQL, only the
row currently being accessed is locked. Other users can access the rest of the
table. This row-level locking significantly reduces the performance effect of
locking in environments that have a large number of simultaneous users.
Therefore, as a general rule, PostgreSQL is better suited for high-load
environments than MySQL.
The MySQL release bundled with Ubuntu gives you the choice of using tables
with table-level or row-level locking. In MySQL terminology, MyISAM
tables use table-level locking and InnoDB tables use row-level locking.