PostgreSQL Scalability: Towards Millions TPS

Post by Jweb_Guru » Tue, May 10, 2016, 5:10 PM UTC

merb wrote: post #183

anarazel wrote: post #148

Earlier quoted context omitted.

> There's no reason for SELECT COUNT(DISTINCT x)) to perform badly in Postgres, as long as you have an appropriate table design and indexes. Meh. Postgres' planner doesn't know how to generate a skip-scan/loose index scan for DISTINCT. You can write it yourself, but it's a bit painful: https://wiki.postgresql.org/wiki/Loose_indexscan If you have a low cardinality that can be a huge efficiency difference.

Actually that's wrong: EXPLAIN ANALYZE SELECT COUNT(DISTINCT(calc)), calc FROM price_history GROUP BY calc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=10713.04..11381.06 rows=10 width=5) (actual time=1010.383..1073.263 rows=11 loops=1) Group Key: calc -> Sort (cost=10713.04..10935.68 rows=89056 width=5…

Post by takeda » Tue, May 10, 2016, 5:55 PM UTC

stickfigure wrote: post #145

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…

Currently MongoDB is outperformed by Postgres I hate hearing absolutist dogma like this. Some things are faster in Postgres, some things are faster in Mongo. We are migrating our analytics db from Mongo to PG but we hit a wall because SELECT COUNT(DISTINCT x)) performs abysmally in Postgres. Mongo's aggregation framework is an immature PITA but it performs this little trick well enough that we're pretty much stuck ke…

    SELECT COUNT(*) FROM (SELECT DISTINCT x FROM table) AS temp;

    SELECT COUNT(DISTINCT x))

Post by ris » Tue, May 10, 2016, 7:06 PM UTC

stickfigure wrote: post #145

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…

Currently MongoDB is outperformed by Postgres I hate hearing absolutist dogma like this. Some things are faster in Postgres, some things are faster in Mongo. We are migrating our analytics db from Mongo to PG but we hit a wall because SELECT COUNT(DISTINCT x)) performs abysmally in Postgres. Mongo's aggregation framework is an immature PITA but it performs this little trick well enough that we're pretty much stuck ke…

Post by TheMiller » Tue, May 10, 2016, 11:12 PM UTC

takeda wrote: post #133

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.

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…

Post by m_mueller » Wed, May 11, 2016, 2:09 AM UTC

nileshtrivedi wrote: post #161

m_mueller wrote: post #158

Earlier quoted context omitted.

I'm not a fan of rolling our own sync code if I don't have to.

RethinkDB's new project Horizon could be a solution. But of course, that's not PostgreSQL.