PostgreSQL Scalability: Towards Millions TPS • Hacker News Board

Re: PostgreSQL Scalability: Towards Millions TPS

#211

Post by developer2 » Wed, May 11, 2016, 4:15 AM UTC

devishard wrote: post #185

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…

MongoDB is really, really bad. I've never come across another product that was so horrible and yet so widely used. MongoDB: for when you don't need consistency, availability, or partition tolerance. There are some really good NoSQL products out there. I seriously think RethinkDB is on par with Postgres. I've also used Cassandra and BerkeleyDB and they're both decent. But unless some core part of your business logic i…

No no no, you don't understand at all. Mongo DB is webscale. /slashdot-comment

Re: PostgreSQL Scalability: Towards Millions TPS

#212

Post by frostymarvelous » Wed, May 11, 2016, 7:44 AM UTC

ap22213 wrote: post #106

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

The only issue that I have with this top-most comment is that it presents PG as a silver bullet. But, there are a lot of different types of databases for a reason. For instance, at my current startup, we employ at least 7 different databases (including PG). And, I don't say that to brag - each has a specific use for the problem at hand. You have to consider the needs and trade offs of your specific project. And, if y…

I think it's more because Postgresql is a good starting point. You can start with it and gradually introduce special/niche databases where required.

It doesn't matter whether you have your needs clearly defined, Postgresql is a jack of all trades and a master of many.

Re: PostgreSQL Scalability: Towards Millions TPS

#213

Post by frostymarvelous » Wed, May 11, 2016, 7:52 AM UTC

gjolund wrote: post #126

ap22213 wrote: post #106

Earlier quoted context omitted.

The only issue that I have with this top-most comment is that it presents PG as a silver bullet. But, there are a lot of different types of databases for a reason. For instance, at my current startup, we employ at least 7 different databases (including PG). And, I don't say that to brag - each has a specific use for the problem at hand. You have to consider the needs and trade offs of your specific project. And, if y…

I agree with you 100%. An abstracted interface for your datalayer is a must have. Many startups and projects begin with a single db and grow into new dbs as the business requirements change. Obviously if you know your data model well enough you can foresee a lot of these requirements and pick the right tool for the job. However, you will usually need to pick a db to start with and hope that it will accommodate as man…

Funny. I just said the same thing before reading your comment.

Somehow that makes me feel better that at least, I'm on the right path.

Re: PostgreSQL Scalability: Towards Millions TPS

#214

Post by antb123 » Wed, May 11, 2016, 12:32 PM UTC

pjmlp wrote: post #157

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

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.

1) Coming to a discussion on Postgres and saying Oracle is better is clearing trolling

2) "server side programming" - as you have the source code with Postgresql and their a plugins for most major programming languages I don't buy this.

3) If you of spent the same on Enterprisedb or CitusDB I can guarantee you would get similar polish and support. People don't.

Re: PostgreSQL Scalability: Towards Millions TPS

#215

Post by fapjacks » Wed, May 11, 2016, 1:44 PM UTC

devishard wrote: post #185

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…

MongoDB is really, really bad. I've never come across another product that was so horrible and yet so widely used. MongoDB: for when you don't need consistency, availability, or partition tolerance. There are some really good NoSQL products out there. I seriously think RethinkDB is on par with Postgres. I've also used Cassandra and BerkeleyDB and they're both decent. But unless some core part of your business logic i…

Right, absolutely true. Mongo is just such a bad piece of software.

Re: PostgreSQL Scalability: Towards Millions TPS

#216

Post by takeda » Wed, May 11, 2016, 4:02 PM UTC

ap22213 wrote: post #106

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

The only issue that I have with this top-most comment is that it presents PG as a silver bullet. But, there are a lot of different types of databases for a reason. For instance, at my current startup, we employ at least 7 different databases (including PG). And, I don't say that to brag - each has a specific use for the problem at hand. You have to consider the needs and trade offs of your specific project. And, if y…

It is true, that you should not chose blindly, but a majority of use cases do require a relational database. You use one of specialized databases when you have a specialized use case.

The reason why Postgres is so popular here is because if you want to have an open source solution, Postgres is the best available. I'm sure there are better proprietary solutions, but even though it is free, PG is pretty darn close to them.

Re: PostgreSQL Scalability: Towards Millions TPS

#217

Post by hibearpanda » Wed, May 11, 2016, 4:11 PM UTC

Keepo wrote: post #134

bulkan wrote: post #124

Earlier quoted context omitted.

There is also Sequelize [1] which has more activity 1 - http://docs.sequelizejs.com/en/latest/

I would also like to mention Objection[1] it is very minimalistic and doesn't get into your way. Most of the time you are writing queries with Knex[2] or you can write raw sql if you feel like it. I tried a lot of ORMs for node and this is the one I liked the most. The guy working on it is also very responsive and have superb documentation. 1 - http://github.com/Vincit/objection.js 2 - http://knexjs.org

We've been using Objection over bookshelf recently and we really like it. One thing that caused us to choose Objection over bookshelf is because bookshelf doesn't support composite keys:

https://github.com/tgriesser/bookshelf/issues/720

Re: PostgreSQL Scalability: Towards Millions TPS

#218

Post by takeda » Wed, May 11, 2016, 4:18 PM UTC

olavgg wrote: post #166

graffitici wrote: post #62

Are there any best practices for using PostgreSQL for storing time series data? Would it be comparable in performance to some of the NoSQL solutions (like Cassandra) for reasonable loads?

I save 2 billion rows of timeseries data every year. I use a regular btree index for "hot data" that is less than 6 months old and BRIN index for older data. You can do this by writing a functional index. You also have to spend some time to tune the query cost settings to avoid sequential scans if you're only gonna work with a subset of the data. Another optimization could be implementing table inheritance so you hav…

> Oh and I run PostgreSQL on ZFS with LZ4 compression

I'm generally a big advocate of ZFS, but I heard that COW file systems (ZFS and btrfs) are generally not good choice for a database workload.

How does it perform for you?

Re: PostgreSQL Scalability: Towards Millions TPS

#219

Post by takeda » Wed, May 11, 2016, 4:22 PM UTC

lfittl wrote: post #74

graffitici wrote: post #62

Are there any best practices for using PostgreSQL for storing time series data? Would it be comparable in performance to some of the NoSQL solutions (like Cassandra) for reasonable loads?

Do you mean timeseries data as in IoT/sensor data/etc, DevOps monitoring metrics (i.e. server load, app performance, etc), or something else? Curious since I'm currently researching how PostgreSQL could do better in this space :)

Not the TP, but I personally am interested in the later one (metrics).

There doesn't seem to be any silver bullet yet. And it is also hard to even see how relational database compares to the existing solutions, since most people dismiss it immediately.

Re: PostgreSQL Scalability: Towards Millions TPS

#220

Post by olavgg » Wed, May 11, 2016, 4:57 PM UTC

takeda wrote: post #218

olavgg wrote: post #166

Earlier quoted context omitted.

I save 2 billion rows of timeseries data every year. I use a regular btree index for "hot data" that is less than 6 months old and BRIN index for older data. You can do this by writing a functional index. You also have to spend some time to tune the query cost settings to avoid sequential scans if you're only gonna work with a subset of the data. Another optimization could be implementing table inheritance so you hav…

> Oh and I run PostgreSQL on ZFS with LZ4 compression I'm generally a big advocate of ZFS, but I heard that COW file systems (ZFS and btrfs) are generally not good choice for a database workload. How does it perform for you?

It performs as well as any other file system, but ZFS shines when you are IO bound as compression really helps when you do sequential scans.

I have also tested ZFS with Microsoft SQL Server by exporting a ZVOL over iSCSI(FreeBSD) over 10G ethernet. But without compression as it has no benefit on 4k blocks. Performance was similar to what you would get with the same drives striped on Windows Server 2012. The big win here is of course ZFS's data checksumming. Not sure about snapshot as backup though, I need to figure out how to talk to the Windows SQL Writer Service so it can tell SQL Server to flush and lock so I can take a consistent snapshot. Microsoft really needs to improve their documentation, because this would be really helpful for several enterprises when it comes to backup speed.