Some of the suggestions make PostgreSQL seem less mature than InnoDB, still: "[don't put] sessions in the DB", "[don't put] constantly-updated counters in the database", and "[don't put] task queues in the database."
My forum gets almost a million page views daily; we store all our data in a(n) InnoDB database, including sessions, task queues, and constantly updated counters. They work just fine and are not even bottlenecks when the isolation level is REPEATABLE READ.
We use SERIALIZABLE though, as a matter of principle, so we had to implement a more fancy schema, but everything happens in the InnoDB database. We don't have to worry about running VACUUM periodically either. I feel PostgreSQL may be a wee bit overhyped.
Sessions in the db are fine, depending on what you are doing with them. LedgerSMB for example uses them to track who is doing what right now in the db, as well as maintain per-user locks that have to persist across db transactions.
In most workflows there is no significant performance penalty here. The only problem is where we are checking those locks and trying to obtain them if they are not held by someone else. This is a significant problem and currently makes a query in a large db take about 20x as long.
It all depends on what you are doing. But yeah trying to have extra-transactional locks so you can do reliable locking across HTTP requests tying it to the session sucks :-)
My take is those suggestions are RDBMS generic, not PostgreSQL specific - although many do store those types of data successfully in PostgreSQL, MySQL, etc., because of the mention of Redis as a counter store.
You don't worry about VACUUM with InnoDB, but that doesn't mean you never feel the pain of the cleanup of old row versions. See 'purge thread spiral of death' for more details.
> Some of the suggestions make PostgreSQL seem less mature than InnoDB, still: "[don't put] sessions in the DB", "[don't put] constantly-updated counters in the database", and "[don't put] task queues in the database."
It's not that you can't do them in postgresql, or even that it will perform slowly. But when you have the chance to offload work from the database server that doesn't need to be there, it's often a good idea to do so.
I wouldn't have worded it the way the author did, either --it's certainly situation-dependent whether those are a good idea. But I wouldn't take it as a "postgres isn't up to the task".
I read a similar argument against git recently: too many things to do manually, but the counter argument is that at this level you need full control on what happens and there should be no magic.
So If you have counters table you need to analyse it more often, by hand, because you don't want your rdbms to guess on your behalf if a table is a write only or update only. MySQL do a lot of guessing, like, say, PHP.
>Some of the suggestions make PostgreSQL seem less mature than InnoDB, still
I like the way you throw "still" on the end, as if innodb has ever been more mature than postgresql. That's very amusing. The suggestions you reference are not postgresql specific, they are general suggestions to not do really dumb stuff with your database, and apply to every database.
>My forum gets almost a million page views daily
Of course you can do dumb stuff, especially if you have a tiny site like that where even the crappiest server you can buy is going to be way overkill. In a case like that, go right ahead and store sessions in the DB if you want, it won't matter at all. The suggestion is for people who expect to end up with a busy site, where updating your database constantly with every page request is going to hurt your scalability and gains you nothing.
>We don't have to worry about running VACUUM periodically either.
I don't think anyone is suggesting that running a 10 year old version of postgresql is a good idea.
>I feel PostgreSQL may be a wee bit overhyped
I wonder if that has anything to do with your obvious ignorance of the subject.
My forum gets almost a million page views daily; we store all our data in a(n) InnoDB database, including sessions, task queues, and constantly updated counters. They work just fine and are not even bottlenecks when the isolation level is REPEATABLE READ.
We use SERIALIZABLE though, as a matter of principle, so we had to implement a more fancy schema, but everything happens in the InnoDB database. We don't have to worry about running VACUUM periodically either. I feel PostgreSQL may be a wee bit overhyped.