Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Scalability: Towards Millions TPS

akorotkov.github.io

Post reply on HN
Print view View original

191–200 of 222 posts

Re: PostgreSQL Scalability: Towards Millions TPS

#191

Post by scurvy »

creshal wrote: post #153
EGreg wrote: post #144

Earlier quoted context omitted.

Just out of curiosity, why when wordpress is involved? Doesn't wordpress support PG? What about drupal and other frameworks, would you say the same? Our framework only has a mysql adapter for now, but it should be pretty easy to add a postgres one.

Wordpress does not support anything other than MySQL, which is somewhat unique as far as CMSes go. There seem to be a few Drupal addons that don't mix with Postgres, but other than that it should be fine. Same with all others. Generally, if you can, you should consider supporting Postgres in your framework. It's a much saner and robuster database from an Ops point of view (replication doesn't fail as often) and more…

I like Postgresql, but it's far from superior to MySQL from an ops monitoring point of view. MySQL exposes a lot more information and run-time stats than Postgresql does. Simple things in MySQL are also impossible in Postgresql. How do you simply and effectively guarantee that your Postgresql replicas are not lagged and are indeed connected to the master and successfully writing down new logs in a streaming hot standby replica? You can, but it requires that all of your replication accounts also have superuser access on the master. Fun stuff, that.

Re: PostgreSQL Scalability: Towards Millions TPS

#192

Post by acveilleux »

jeltz wrote: post #170
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.

Hm, fast bulk write is an area where PostgreSQL generally handily beats MongoDB. I do not see why it would be different for geodata. MongoDB beats PostgreSQL for some things, but not bulk load.

Seems like the poster is talking about high write load and not necessarily bulk load.

Re: PostgreSQL Scalability: Towards Millions TPS

#193

Post by pjmlp »

Intermernet wrote: post #182
pjmlp wrote: post #157

Earlier quoted context omitted.

I have been using it a lot lately, and do like it. It isn't a popular opinion on HN, but I will still advise for Oracle or SQL Server in terms of tooling, cluster scaling, server side programming and DB drivers. Then again, we work with customers whose Oracle and SQL Server licenses costs aren't an issue.

Have an upvote, I'm not sure why you're being down-voted. If you take the issues of open-source and licensing out of the equation (both important issues in their own right, but not related to the point at hand) then Oracle and SQL Server are both ridiculously good. I personally try to avoid them (due to the cost, and the lock-in) but they are astoundingly performant and featureful RDBMSs with a huge amount of support…

Thanks for the upvote.

I guess some took it personally, although I mentioned I do like Postgres.

I just happen to like the other ones even more, since I was lucky to be able to use them in a few projects.

Re: PostgreSQL Scalability: Towards Millions TPS

#194

Post by nathan_long »

threeseed wrote: post #80
esaym wrote: post #31

Earlier quoted context omitted.

There's been a large migration off of mysql to postgres simply because Oracle got the rights to mysql when they purchased Sun. That is what made me consider , ditching mysql. The final straw came when I stood up a mysql 5.6 instance to use as a data warehouse for about 5TB of data (15 billion rows). To my horror after spending a few weeks on this project I discovered that mysql only supported a small subset of the SQ…

> There's been a large migration off of mysql to postgres No there hasn't: http://db-engines.com/en/ranking Which makes sense since they aren't really playing in the same space. The benefits of PostgreSQL are largely lost on typical MySQL use cases (light load, simple CRUD access patterns, limited use of JSON/BSON etc).

> Which makes sense since they aren't really playing in the same space. The benefits of PostgreSQL are largely lost on typical MySQL use cases (light load, simple CRUD access patterns, limited use of JSON/BSON etc).

What benefits do you see that MySQL has over PostgreSQL? What defines its niche?

Re: PostgreSQL Scalability: Towards Millions TPS

#195

Post by takeda »

gjolund wrote: post #173
takeda wrote: post #136

Earlier quoted context omitted.

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.

Apologies, when I was talking about horizontal scaling I meant compared to other NoSQL databases[1].

The biggest selling point of NoSQL databases really is increasing performance and horizontal scalability due to removing some guarantees that RDBS provides. With MongoDB you get neither of the advantages.

Generally with Postgres you can't scale easily, unless you only do reads, but postgres outperforms MongoDB. You can get some marginal improvement by running multiple MongoDB nodes, but a single Postgres will still outperform it.

Also with benchmarks like this, it is often overlooked that in a relational database you often can store data in a smarter way and get extra boost of performance.

For example in my previous job we had 3 MongoDB which were used for mapping IP address to a zip code. Those databases were 12GB each. They run on a beefy instances in AWS because they wanted to make sure all the data could fit in RAM.

I did a POC and put the same data in Postgres and it essentially was just 600MB of data. All queries were sub milisecond, on smallest instance.

How come? Well in Postgres I stored ranges of IPs using ip4r extension. And put an GiST index which works with ranges. Mongo did not understand IP addresses so what they essentially did is they computed every possible IPv4 address and stored it into 64 bit integer, then they placed an index which probably was bigger than the data itself.

Their solution also won't scale with IPv6.

[1] http://www.datastax.com/wp-content/themes/datastax-2014-08/f...

Re: PostgreSQL Scalability: Towards Millions TPS

#196

Post by combatentropy »

breakingcups wrote: post #154
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…

I like and often use Postgres, but professionally use MSSQL a lot and I vastly prefer it for my use case. It has all the DB features I need for my products and it is easier to manage (when it comes to backups, for example). One could argue ease of use falls under a purely technical standpoint. Could you elaborate a bit more on why you view Microsoft SQL Server as worse?

MSSQL's text comparison by default is case insensitive. Its limit clause is nonstandard (select top 10 * from . . .). Its syntax for other things, like trimming or casting, is just much more typing. Installing the driver on a Linux server is several times harder than Postgres. When I get the syntax wrong, all it gives me "General Error." The command-line tool to Postgres, psql, works much more smoothly and has many more features than MSSQL's tsql (and Oracle's isql, for that matter).

If you use MSSQL all the time, I have no doubt that you get along fine. You can get used to its ways, and most applications don't need features that aren't shared by almost all databases.

I didn't mean that if you use MSSQL then the app will always noticeably work worse than if you based it on Postgres. It's just that for someone who switches back and forth, MSSQL makes my life less enjoyable, provides no benefit, and costs a lot more money.

Re: PostgreSQL Scalability: Towards Millions TPS

#197

Post by gshulegaard »

_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…

As others have mentioned, with Citus Data open sourcing CitusDB, Postgres is now capable of fairly easily handling Mongo sized records.

Something else you might be interested in:

https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr...

I have always been a Postgres fan, but now I find it very difficult to imagine a problem MongoDB would be better suited for.

Re: PostgreSQL Scalability: Towards Millions TPS

#198

Post by pritambarhate »

malloryerik wrote: post #141

Any opinions about AWS' SQL database, Aurora?

These 2 articles on Aurora by Vadim Tkachenko, the CTO and co-founder of Percona are very informative - https://www.percona.com/blog/2015/11/16/amazon-aurora-lookin... and https://www.percona.com/blog/2015/12/03/amazon-aurora-sysben...

Re: PostgreSQL Scalability: Towards Millions TPS

#199

Post by b3550943 »

ddorian43 wrote: post #16
jrcii wrote: post #12

Slightly tangential but I'm genuinely curious, does any have a theory as to why nearly every RDBMS post on Hacker News is about Postgres and almost never MySQL or MariaDB? Considering the relative obscurity of the former it seems somewhat inexplicable.

Because.... people that use postgresql are ~usually better developers, they care more + post more + upvote more.

nobody cares

Re: PostgreSQL Scalability: Towards Millions TPS

#200

Post by Jweb_Guru »

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

Neither HAVING nor GROUP BY are part of the relational model, though. Really, aggregation isn't part of the traditional relational model. People just tend to associate it with RDBMSes.
Post reply on HN