4 ms·

> Yes Postgres can be slower out of the box Do you have an up to date source for that?

leetrout 5y ago
Not on my phone and i'm headed to bed but I can go educate myself in the morning. Maybe its all better now?
Izkata 5y ago
IIRC it's because some of the default settings were chosen back when RAM was measured in MB and low GB and not really updated. work_mem, for example, can typically be safely set to several times its default of 4MB, which then allows individual operations to use more memory before spilling over into temporary files on disk. Of course, that particular example really only matters if you're actually hitting the 4MB limit in your queries, but I think there were some other settings with similar too-low defaults.
fuy 5y ago
It's because the default Postgres configuration is very basic and targeted at low-end hardware. Besides, in Postgres you have to tune some of the settings that other databases take care of automagically (work_mem being probably the trickiest one). So the first thing you do after setting up Postgres on a reasonable hardware is tune the config file. There's a lot of advice on the web, and there's pgtune service that makes it very easy to start in the right direction (https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua). Still, you need to know about it to do it, so there's definitely quite a lot of suboptimal Postgres installations out there.
- dspillett 5y ago
  That isn't unique to pg though: just look at the number of performance questions in dba.stackexchange and on web hosting forums that, once you take out those that turn out to be bad queries and/or non-existent/inappropriate indexes, come down to tuning certain settings in my.ini. I've not used either for a long time so my knowledge may be out of date, but mysql definitely used to be the easiest to get started out of the box which was part of the reason it won out in the shared hosting space. Of course the main reason it won out in that area was because it was faster than pg in many artificial benchmarks because at the time it didn't bother with silly things like transactions (as MyISAM was the default table type), and foreign key constraints, and other necessities…
throwdbaaway 5y ago
Not just out of the box. There is a good comment at https://news.ycombinator.com/item?id=7488498 https://news.ycombinator.com/item?id=7488498 that listed a few key features of InnoDB: 1. Change Buffering - If your benchmark result shows that MySQL is faster than PostgreSQL, it is likely due to change buffering. For random read/write workload that doesn't fit into the memory, this feature is fantastic. This is available since the very first commit of InnoDB, and of course also has been causing random corruption during crash recovery throughout the years: https://jira.mariadb.org/browse/MDEV-24449 https://jira.mariadb.org/browse/MDEV-24449 (fixed in MariaDB, still exists in MySQL) 2. Page-level Compression - PostgreSQL still doesn't support this, but I am fine with relying on ZFS filesystem compression when running either MySQL or PostgreSQL. Arguably it should be better for the database engine to do its own compression, but see this blocker level bug that impacted FreeBSD: https://jira.mariadb.org/browse/MDEV-26537 https://jira.mariadb.org/browse/MDEV-26537 (MariaDB only, but feel free to search bugs.mysql.com for other compression related bugs) 3. Clustered Index - The main topic in the Uber drama. I think InnoDB has the better design, since scaling write is harder for a RDBMS. I also think that InnoDB implementation causes a severe performance cliff, when it comes to optimistic update versus pessimistic update.
- dspillett 5y ago
  > Page-level Compression ... but I am fine with relying on ZFS filesystem compression There are compromises there. With page level compression the pages usually remain compressed in memory as well as on-disk and get unpacked on access (and repacked on change). This eats CPU time, but saves RAM as well as disk space which for some workloads is a beneficial trade-off as a larger common working set fits into the same amount of active memory (particularly when IO is slow, such as traditional spinning disks, cloud providers when you aren't paying a fortune, or when competing with a lot of IO or network contention on a busy LAN/SAN). With filesystem compression you don't get the same CPU hit every time the page is read, and depending on your data you may get better compression for a couple of reasons, but you don't get the RAM saving.
  - throwdbaaway 5y ago
    ZFS ARC actually works the same way, i.e. the records would remain compressed in memory. When running PostgreSQL, I would probably go with `primarycache=all` for the data dir, a largish ARC cache, and a smallish shared buffers, to take advantage of the filesystem compression. When running MySQL, I would probably go with `primarycache=metadata` for the data dir, a smallish ARC cache, and a largish buffer pool, to still benefit (slightly) from the filesystem compression.

Not on my phone and i'm headed to bed but I can go educate myself in the morning. Maybe its all better now?

IIRC it's because some of the default settings were chosen back when RAM was measured in MB and low GB and not really updated. work_mem, for example, can typically be safely set to several times its default of 4MB, which then allows individual operations to use more memory before spilling over into temporary files on disk. Of course, that particular example really only matters if you're actually hitting the 4MB limit in your queries, but I think there were some other settings with similar too-low defaults.

fuy 5y ago

It's because the default Postgres configuration is very basic and targeted at low-end hardware. Besides, in Postgres you have to tune some of the settings that other databases take care of automagically (work_mem being probably the trickiest one). So the first thing you do after setting up Postgres on a reasonable hardware is tune the config file. There's a lot of advice on the web, and there's pgtune service that makes it very easy to start in the right direction (https://pgtune.leopard.in.ua https://pgtune.leopard.in.ua). Still, you need to know about it to do it, so there's definitely quite a lot of suboptimal Postgres installations out there.

dspillett 5y ago
That isn't unique to pg though: just look at the number of performance questions in dba.stackexchange and on web hosting forums that, once you take out those that turn out to be bad queries and/or non-existent/inappropriate indexes, come down to tuning certain settings in my.ini. I've not used either for a long time so my knowledge may be out of date, but mysql definitely used to be the easiest to get started out of the box which was part of the reason it won out in the shared hosting space. Of course the main reason it won out in that area was because it was faster than pg in many artificial benchmarks because at the time it didn't bother with silly things like transactions (as MyISAM was the default table type), and foreign key constraints, and other necessities…

throwdbaaway 5y ago

Not just out of the box. There is a good comment at https://news.ycombinator.com/item?id=7488498 https://news.ycombinator.com/item?id=7488498 that listed a few key features of InnoDB: 1. Change Buffering - If your benchmark result shows that MySQL is faster than PostgreSQL, it is likely due to change buffering. For random read/write workload that doesn't fit into the memory, this feature is fantastic. This is available since the very first commit of InnoDB, and of course also has been causing random corruption during crash recovery throughout the years: https://jira.mariadb.org/browse/MDEV-24449 https://jira.mariadb.org/browse/MDEV-24449 (fixed in MariaDB, still exists in MySQL) 2. Page-level Compression - PostgreSQL still doesn't support this, but I am fine with relying on ZFS filesystem compression when running either MySQL or PostgreSQL. Arguably it should be better for the database engine to do its own compression, but see this blocker level bug that impacted FreeBSD: https://jira.mariadb.org/browse/MDEV-26537 https://jira.mariadb.org/browse/MDEV-26537 (MariaDB only, but feel free to search bugs.mysql.com for other compression related bugs) 3. Clustered Index - The main topic in the Uber drama. I think InnoDB has the better design, since scaling write is harder for a RDBMS. I also think that InnoDB implementation causes a severe performance cliff, when it comes to optimistic update versus pessimistic update.

dspillett 5y ago
> Page-level Compression ... but I am fine with relying on ZFS filesystem compression There are compromises there. With page level compression the pages usually remain compressed in memory as well as on-disk and get unpacked on access (and repacked on change). This eats CPU time, but saves RAM as well as disk space which for some workloads is a beneficial trade-off as a larger common working set fits into the same amount of active memory (particularly when IO is slow, such as traditional spinning disks, cloud providers when you aren't paying a fortune, or when competing with a lot of IO or network contention on a busy LAN/SAN). With filesystem compression you don't get the same CPU hit every time the page is read, and depending on your data you may get better compression for a couple of reasons, but you don't get the RAM saving.
- throwdbaaway 5y ago
  ZFS ARC actually works the same way, i.e. the records would remain compressed in memory. When running PostgreSQL, I would probably go with `primarycache=all` for the data dir, a largish ARC cache, and a smallish shared buffers, to take advantage of the filesystem compression. When running MySQL, I would probably go with `primarycache=metadata` for the data dir, a smallish ARC cache, and a largish buffer pool, to still benefit (slightly) from the filesystem compression.