4 ms·

This seems to be the mantra of the PostgreSQL fanboys. I use PostgreSQL. I have found, in my experience, that it has superior join performance when compared w

by djist 18y ago

This seems to be the mantra of the PostgreSQL fanboys. I use PostgreSQL. I have found, in my experience, that it has superior join performance when compared with MySQL and that's what is important to be in my line of work. That said, I'm not silly enough to think that performance = scalability. Neither MySQL or PostgreSQL scale naturally. They require strategies to be implemented whether that be sharding, caching, replication, etc. This just isn't a useful suggestion for scaling.

ltbarcly 18y ago
Well, it depends how big you need to scale. Postgresql can scale far far better than mysql on a single multiprocessor machine. Postgres scales up to about 16 cpu's without much difficulty. If you put heavily used tables on lots of seperate spindles, you can go very far. Also, postgresql supports partitioning of tables. This lets you effectively 'shard' a single database, without having to change your queries. As long as the planner can determine which partitions the data could possibly come from, it will ignore any other partitions. So if you have your table in 30 partitions, and the planner determines the query could only return rows from 8 of those tables, it doesn't scan/check indexes for the other 22. If you partition the data such that it is effectively 'archive' and 'live' data, in that the 'archive' data partitions are rarely accessed, this is a HUGE win, because the data is indexed per-partition, meaning the relatively small 'live' indexes get kept around in memory, and combining with other indexes for bitmap-indexed joins is therefore that much faster as well. If you don't need concurrency, use sqlite, if you do, use Postgresql or Oracle. <conspiracy>Why do you think Sun (a hardware company) bought Mysql? Maybe because you need a server installed for every 100 users on your site? </conspiracy>
kingkongrevenge 18y ago
There is so much ink spilled about going through conniptions to scale mysql and postgres. I really wonder if anyone has actually bothered to confirm that it's not better to just buy some big iron and run db2 or oracle once you hit scalability problems. There are a ton of corporate data centers running oracle/db2 handling WAY more throughput than your web app ever will, and they're not using memcached or sharding.
- DanielBMarkham 18y ago
  It's a build-buy decision, right? When you're seven guys sitting in a garage eating pizza, you build it because it makes the most sense. If you've got a million records going through an hour and you're making the big bucks, get the commodity solution as close to wholesale as you can. The problem is that if you're giving away your app for free, you're spending dough -- perhaps lots of it. Then you're stuck on the back-end of the curve, scrambling to tweak every little bit you can.
  - kingkongrevenge 18y ago
    > It's a build-buy decision, right? Exactly, but the entry level DB2 and Oracle solutions are not super cost prohibitive and time is money. We're talking about a pretty low man-hour investment in futzing with MySQL before you'd have been better off calling an IBM rep. If you're doing some popular free web app maybe you can stick a "powered by IBM" icon in the corner and get the stuff for free. Remember those?
- gaius 18y ago
  You might be surprised about the number of database "experts" who have never heard of Postgres. They know about MySQL because Slashdot uses it, and they vaguely know about Access, tho' they've never used it and don't know what it's good for. Sharding, for instance, is a hot topic right now but "big iron" DBAs like myself are completely mystified... Isn't this just the partitioning/shared nothing feature that we've had for over a decade? (Answer: yes of course it is).

by djist 18y ago

ltbarcly 18y ago

Well, it depends how big you need to scale. Postgresql can scale far far better than mysql on a single multiprocessor machine. Postgres scales up to about 16 cpu's without much difficulty. If you put heavily used tables on lots of seperate spindles, you can go very far. Also, postgresql supports partitioning of tables. This lets you effectively 'shard' a single database, without having to change your queries. As long as the planner can determine which partitions the data could possibly come from, it will ignore any other partitions. So if you have your table in 30 partitions, and the planner determines the query could only return rows from 8 of those tables, it doesn't scan/check indexes for the other 22. If you partition the data such that it is effectively 'archive' and 'live' data, in that the 'archive' data partitions are rarely accessed, this is a HUGE win, because the data is indexed per-partition, meaning the relatively small 'live' indexes get kept around in memory, and combining with other indexes for bitmap-indexed joins is therefore that much faster as well. If you don't need concurrency, use sqlite, if you do, use Postgresql or Oracle. <conspiracy>Why do you think Sun (a hardware company) bought Mysql? Maybe because you need a server installed for every 100 users on your site? </conspiracy>

kingkongrevenge 18y ago

There is so much ink spilled about going through conniptions to scale mysql and postgres. I really wonder if anyone has actually bothered to confirm that it's not better to just buy some big iron and run db2 or oracle once you hit scalability problems. There are a ton of corporate data centers running oracle/db2 handling WAY more throughput than your web app ever will, and they're not using memcached or sharding.

DanielBMarkham 18y ago
It's a build-buy decision, right? When you're seven guys sitting in a garage eating pizza, you build it because it makes the most sense. If you've got a million records going through an hour and you're making the big bucks, get the commodity solution as close to wholesale as you can. The problem is that if you're giving away your app for free, you're spending dough -- perhaps lots of it. Then you're stuck on the back-end of the curve, scrambling to tweak every little bit you can.
- kingkongrevenge 18y ago
  > It's a build-buy decision, right? Exactly, but the entry level DB2 and Oracle solutions are not super cost prohibitive and time is money. We're talking about a pretty low man-hour investment in futzing with MySQL before you'd have been better off calling an IBM rep. If you're doing some popular free web app maybe you can stick a "powered by IBM" icon in the corner and get the stuff for free. Remember those?
gaius 18y ago
You might be surprised about the number of database "experts" who have never heard of Postgres. They know about MySQL because Slashdot uses it, and they vaguely know about Access, tho' they've never used it and don't know what it's good for. Sharding, for instance, is a hot topic right now but "big iron" DBAs like myself are completely mystified... Isn't this just the partitioning/shared nothing feature that we've had for over a decade? (Answer: yes of course it is).