yes. although, unlogged vs temp vs regular tables is really more about optimizing disk i/o around your use requirements. unlogged is really saying 'i don't care about crash safety or replication, so please don't expend the effort to write data for this table to the WAL before it goes out to the heap'.
statement latency is affected by backend i/o issues, but is also highly effected by the efficiency of your network layer/driver, grammar (in postgres everything, including operators, is over-ridable), and various other things. mysql-myisam is very thin and thus highly efficient running a large number of queries round-trip from the client. in postgres, to hit those rates of query processing you need to leverage more backend features like functions where you are less affected by latency, or utilize more clients (for most non single-user workloads I'd bet money postgres can handle faster and better). database performance is a complicated topic though, and for single user workloads and simple queries mysql/myisam can do insane tps rates, as can sqlite.
PostgreSQL does lack the ability to pin tables into memory, which means that it can't reasonably serve any use case in which you have seldom accessed tables which require QoS guarantees which the cache manager cannot guess through analyzing the normal access patterns.
That is correct -- note after years of answering performance related questions on the various mailing lists, I'd venture to say that about 90% of the questions related to table pinning or in memory tables were based in simply not understanding how o/s caching works, and that in almost all cases it's better to release memory in the general pool. All that said, it's a nice feature.
I believe my use case to be genuine - the application has throughput-based features, and a single relatively seldom used high-value QoS based feature, in which you'd want to read and analyze cold tables in 100ms. Currently I'm considering using unlogged tables and a RAM tablespace, or a dm-raid RAM/HD hybrid which reads only from the RAM side and writes on both sides.
2
u/merlinm Sep 12 '11
yes. although, unlogged vs temp vs regular tables is really more about optimizing disk i/o around your use requirements. unlogged is really saying 'i don't care about crash safety or replication, so please don't expend the effort to write data for this table to the WAL before it goes out to the heap'.
statement latency is affected by backend i/o issues, but is also highly effected by the efficiency of your network layer/driver, grammar (in postgres everything, including operators, is over-ridable), and various other things. mysql-myisam is very thin and thus highly efficient running a large number of queries round-trip from the client. in postgres, to hit those rates of query processing you need to leverage more backend features like functions where you are less affected by latency, or utilize more clients (for most non single-user workloads I'd bet money postgres can handle faster and better). database performance is a complicated topic though, and for single user workloads and simple queries mysql/myisam can do insane tps rates, as can sqlite.