PostgreSQL Scalability: Towards Millions TPS • Hacker News Board

Re: PostgreSQL Scalability: Towards Millions TPS

#171

Post by gjolund » Tue, May 10, 2016, 11:16 AM UTC

LoSboccacc wrote: post #146

takeda wrote: post #133

Earlier quoted context omitted.

Because NoSQL is a hype. Many of the NoSQL essentially takes us back to 60s before Codd came up with relational model[1] These ideas tend to come back once in a while [2][3], but so far nothing is better than relational model. NoSQL still makes sense in many cases (generally when your specific use case does not need all guarantees of ACID), you can get in return higher performance or horizontal scalability. MongoDB i…

> so far nothing is better than relational model my favorite quote: "the relational model is rooted depth in the set theory and math is hard to fool" it was on a post on how all NoSQL are ending up having to reinvent having, group and join clauses on their API because that's what apps do with data

A recent candidate said basically this during his interview, he got the job.

Re: PostgreSQL Scalability: Towards Millions TPS

#172

Post by gjolund » Tue, May 10, 2016, 11:25 AM UTC

nileshtrivedi wrote: post #160

_w3e8 wrote: post #82

Postgres has been my DB of choice for nearly a decade. The only times I wind up working with another db are because: (1 it is a better technical fit for a very specific problem (2 there is already a legacy db in place I have been voted down at a couple of startups that wanted to run a "MEAN" stack, invariably all of those startups moved from MongoDB or shutdown. The only time I will advocate for anything other than P…

Also check Postgraphql: https://github.com/calebmer/postgraphql Inspired from Postgrest linked above, it automatically builds a GraphQL API by reflecting on postgres schema.

I was looking at that the other day, really exciting project.

Re: PostgreSQL Scalability: Towards Millions TPS

#173

Post by gjolund » Tue, May 10, 2016, 11:30 AM UTC

takeda wrote: post #136

gjolund wrote: post #128

Earlier quoted context omitted.

For processing, 100% agree. However if the write load is very high then Mongo is better suited as the intial store. I then replicate to other dbs.

Mongo actually has worse performance than postgres, including inserting data. You can of course improve Mongo's speed by using weaker write concern, but you can also disable WAL in Postgres too.

For a single server, I believe you are correct.

However I found scaling write load past a single server's limits more difficult using Postgres than MongoDB.

Fortunately that is part of what the OP addresses in his post.

Re: PostgreSQL Scalability: Towards Millions TPS

#174

Post by jordanthoms » Tue, May 10, 2016, 11:35 AM UTC

jrapdx3 wrote: post #164

jcoffland wrote: post #139

Earlier quoted context omitted.

Why? WordPress would not be any better or easier to use unless you already had PostgreSQL installed. Frankly for a use case as simple as WordPress you don't need to over optimize your database.

> WordPress would not be any better or easier to use unless you already had PostgreSQL installed. Sounds like a situation I'm facing: assisting a non-profit org that wants a new website. Some members push for using Wordpress as the CMS. However the org has an established pgsql db containing lots of data that optimally could be employed on the site, e.g., lists of members/events, searchable documents, etc. Also, there…

It is possible to have plugins use their own DB and have things call out to that to retrieve info - e.g. CiviCRM uses it's own database to store CRM info (although it's also MySQL) and can provide forms for Wordpress pages etc.

You can also do some pretty nice data syncing/access between the two using Postgres' Foreign Data Wrappers to access MySQL data within Postgres - unfortunately it doesn't look like MySQL has a equivalent for accessing Postgres data from within MySQL.

Re: PostgreSQL Scalability: Towards Millions TPS

#175

Post by gjolund » Tue, May 10, 2016, 11:35 AM UTC

oconnor663 wrote: post #138

manigandham wrote: post #102

Earlier quoted context omitted.

> invariably all of those startups moved from MongoDB Why? Especially after point #1 and assuming the document-store was a good fit for the data model.

http://www.sarahmei.com/blog/2013/11/11/why-you-should-never...

diaspora was such an interesting project, I dream about resurrecting it often.

Re: PostgreSQL Scalability: Towards Millions TPS

#176

Post by gjolund » Tue, May 10, 2016, 11:37 AM UTC

eventemitter wrote: post #152

_w3e8 wrote: post #82

Postgres has been my DB of choice for nearly a decade. The only times I wind up working with another db are because: (1 it is a better technical fit for a very specific problem (2 there is already a legacy db in place I have been voted down at a couple of startups that wanted to run a "MEAN" stack, invariably all of those startups moved from MongoDB or shutdown. The only time I will advocate for anything other than P…

Relared ORM is another orm supporting postgres and mysql. It builds the models by itself by loading the required definitions from the database. https://www.npmjs.com/package/related

I'll have to give that a shot, seems like a nice compromise between fully automated solutions like Postgrest and a conventional ORM.

Re: PostgreSQL Scalability: Towards Millions TPS

#177

Post by manigandham » Tue, May 10, 2016, 11:40 AM UTC

e12e wrote: post #135

manigandham wrote: post #103

Earlier quoted context omitted.

> Postgres, but that anyone uses Microsoft or Oracle at all. Not only do they cost a lot, but from a purely technical standpoint they are worse. Based on what? Both commercial databases have incredible features, tooling and extensions that leave postgres behind. Postgres today doesn't even have a solid scale-up or scale-out strategy. It does have nice SQL support and makes developer lives a little easier but this isn…

I'm curious what you mean by Postgres not having a "scale-up" strategy. Are you saying that eg MS SQL works better with a few TBs of ram and 128 cores than Postgres does?

Yes. Postgres famously has been single-threaded for queries for a long time. The upcoming version 9.6 is finally introducing some basic parallel scan/join/aggregate functionality.

MS SQL, Oracle and pretty much all the commercial databases are much farther along in scaling up and making the most of a single machine.

Re: PostgreSQL Scalability: Towards Millions TPS

#178

Post by jeltz » Tue, May 10, 2016, 11:42 AM UTC

manigandham wrote: post #103

combatentropy wrote: post #85

Earlier quoted context omitted.

> why nearly every RDBMS post on Hacker News is about Postgres and almost never MySQL You'll see the same phenomenon on Slashdot. MySQL is popular among a subset of programmers: web developers. In corporations, Microsoft SQL and Oracle are more popular. Further, MySQL is popular among a subset of web developers: those who use PHP. Among web developers who use Python, Postgres seems more popular. My suspicion is that…

> Postgres, but that anyone uses Microsoft or Oracle at all. Not only do they cost a lot, but from a purely technical standpoint they are worse. Based on what? Both commercial databases have incredible features, tooling and extensions that leave postgres behind. Postgres today doesn't even have a solid scale-up or scale-out strategy. It does have nice SQL support and makes developer lives a little easier but this isn…

The very article you are commenting on is about scaling up with PostgreSQL. PostgreSQL is excellent at scaling up with many small queries, but has problems with few large ones.

Re: PostgreSQL Scalability: Towards Millions TPS

#179

Post by manigandham » Tue, May 10, 2016, 11:46 AM UTC

jeltz wrote: post #178

manigandham wrote: post #103

Earlier quoted context omitted.

> Postgres, but that anyone uses Microsoft or Oracle at all. Not only do they cost a lot, but from a purely technical standpoint they are worse. Based on what? Both commercial databases have incredible features, tooling and extensions that leave postgres behind. Postgres today doesn't even have a solid scale-up or scale-out strategy. It does have nice SQL support and makes developer lives a little easier but this isn…

The very article you are commenting on is about scaling up with PostgreSQL. PostgreSQL is excellent at scaling up with many small queries, but has problems with few large ones.

This is a brand new commit, it's not today. There's been some good progress recently but Postgres historically hasn't been the best at scaling up, especially compared to the commercial engines.

Re: PostgreSQL Scalability: Towards Millions TPS

#180

Post by [deleted] » Tue, May 10, 2016, 11:47 AM UTC

jeltz wrote: post #178

manigandham wrote: post #103

Earlier quoted context omitted.

> Postgres, but that anyone uses Microsoft or Oracle at all. Not only do they cost a lot, but from a purely technical standpoint they are worse. Based on what? Both commercial databases have incredible features, tooling and extensions that leave postgres behind. Postgres today doesn't even have a solid scale-up or scale-out strategy. It does have nice SQL support and makes developer lives a little easier but this isn…

The very article you are commenting on is about scaling up with PostgreSQL. PostgreSQL is excellent at scaling up with many small queries, but has problems with few large ones.

[deleted]