4 ms·

This was mostly well done and really makes me want to take a closer look at Postgres. But a cheap shot came on the last one, "Readers and writers block each oth

by Lendal 13y ago

This was mostly well done and really makes me want to take a closer look at Postgres. But a cheap shot came on the last one, "Readers and writers block each other". Both locking and snapshots are implemented in SQL Server, and you can pick your poison. Is Postgres or Oracle better? Is either method more efficiently or intelligently implemented in either of those systems than in SQL Server?

thristian 13y ago
PostgreSQL is 100% pure MVCC, a little bit like the "SNAPSHOT" method described in the article: updates and deletes always create new copy of a row, and every row has a visibility associated with it: the oldest transaction and the newest transaction that can see that row (if the row hasn't been deleted by any currently active transactions, the 'newest transaction' ID is empty). There are definitely some drawbacks to MVCC. For example, many people are surprised how long "select count(*) from X" takes to run, because it has to do a full table-scan to check which rows the current transaction can actually see. You need to periodically 'vaccuum' tables, which updates a cached list of which table rows are deleted, so they can be reused by future update (modern Postgres will auto-vacuum, but it's another set of knobs for the DBA to tweak). Plus, the storage overhead mentioned by the article (although that really depends on the amount of churn in your particular application, and regular vacuuming keeps it to a minimum).
- gmac 13y ago
  In fact, the latest stable Postgres supports fast, index-only scans for count queries: https://wiki.postgresql.org/wiki/Index-only_scans https://wiki.postgresql.org/wiki/Index-only_scans.
j-kidd 13y ago
I think the last one is actually the most important one. The READ COMMITTED isolation level is a bad default. I have encountered many SQL Server databases that are unknowingly stuck with this default, with "WITH (NOLOCK)" added all over the place. It is kinda funny that you used the phrase "pick your poison". That's exactly my feeling when working with SQL Server. I have never had a "pick your poison" moment with PostgreSQL :)

by Lendal 13y ago

thristian 13y ago

PostgreSQL is 100% pure MVCC, a little bit like the "SNAPSHOT" method described in the article: updates and deletes always create new copy of a row, and every row has a visibility associated with it: the oldest transaction and the newest transaction that can see that row (if the row hasn't been deleted by any currently active transactions, the 'newest transaction' ID is empty). There are definitely some drawbacks to MVCC. For example, many people are surprised how long "select count(*) from X" takes to run, because it has to do a full table-scan to check which rows the current transaction can actually see. You need to periodically 'vaccuum' tables, which updates a cached list of which table rows are deleted, so they can be reused by future update (modern Postgres will auto-vacuum, but it's another set of knobs for the DBA to tweak). Plus, the storage overhead mentioned by the article (although that really depends on the amount of churn in your particular application, and regular vacuuming keeps it to a minimum).

gmac 13y ago
In fact, the latest stable Postgres supports fast, index-only scans for count queries: https://wiki.postgresql.org/wiki/Index-only_scans https://wiki.postgresql.org/wiki/Index-only_scans.

j-kidd 13y ago

I think the last one is actually the most important one. The READ COMMITTED isolation level is a bad default. I have encountered many SQL Server databases that are unknowingly stuck with this default, with "WITH (NOLOCK)" added all over the place. It is kinda funny that you used the phrase "pick your poison". That's exactly my feeling when working with SQL Server. I have never had a "pick your poison" moment with PostgreSQL :)