3 ms·

Not so much a reason to not use it, but something to keep in mind: queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to M

Not so much a reason to not use it, but something to keep in mind: queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB. This doesn't necessarily mean they're always slower, but it's something you should take into account. Another thing to take into account is that updating between minor versions (major versions per 10.x) is a bit tricky since IIRC the WAL format can change. This means that upgrading from e.g. 10.x to 11.0 requires you to either take your cluster offline, or use something like pg_logical. This is really my only complaint, but again it's not really a reason to _not_ use PostgreSQL.

ZitchDog 9y ago
With logical replication in PG 10 the wal issue shouldn't be a factor going forward :)
- snuxoll 9y ago
  Here's hoping someone writes an alternative to pg_upgrade to handle this automatically. Hell, I'd be willing to throw some money in.
ngrilly 9y ago
> queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB As far as I know, that's true when you use the MyISAM storage engine (which is non transactional), but not when you use InnoDB (which has been the default for years now).
wolf550e 9y ago
`select count(star)` is fast only on non-transactional MyISAM storage engine which locks the whole table for a single writer and has no ACID support. It's not what people usually mean when they say they want an RDBMS. When ACID support is required, you use InnoDB, and that has the same `select count(star)` performance as other database engines. What workload do you have that you need exact count of rows in a big table? Because if the table is not big or if inexact count would suffice, there are solutions (e.g. HyperLogLog).
aaronbwebber 9y ago
Since 9.2 Postgres has had index-only scans which can substantially improve the performance of `SELECT COUNT(* )` queries. If you need to get exact counts, and your query plan is not showing an index-only scan, you should try adding an index on the columns you are filtering on to see if you can get the query to run on an index-only scan.* * <all the usual caveats about attempting to tune query performance>

ZitchDog 9y ago

With logical replication in PG 10 the wal issue shouldn't be a factor going forward :)

snuxoll 9y ago
Here's hoping someone writes an alternative to pg_upgrade to handle this automatically. Hell, I'd be willing to throw some money in.

ngrilly 9y ago

> queries such as `SELECT COUNT(*)` tend to be a bit more expensive in PostgreSQL compared to MySQL/MariaDB As far as I know, that's true when you use the MyISAM storage engine (which is non transactional), but not when you use InnoDB (which has been the default for years now).

wolf550e 9y ago

`select count(star)` is fast only on non-transactional MyISAM storage engine which locks the whole table for a single writer and has no ACID support. It's not what people usually mean when they say they want an RDBMS. When ACID support is required, you use InnoDB, and that has the same `select count(star)` performance as other database engines. What workload do you have that you need exact count of rows in a big table? Because if the table is not big or if inexact count would suffice, there are solutions (e.g. HyperLogLog).

aaronbwebber 9y ago

Since 9.2 Postgres has had index-only scans which can substantially improve the performance of `SELECT COUNT(* )` queries. If you need to get exact counts, and your query plan is not showing an index-only scan, you should try adding an index on the columns you are filtering on to see if you can get the query to run on an index-only scan.* * <all the usual caveats about attempting to tune query performance>