PostgreSQL Scalability: Towards Millions TPS (opens in new tab)

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 and documentation behind them.

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.

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

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

I haven't worked with Sequelize personally, but a friend has been recently and curses the day it was born - he wishes he'd used bookshelf.

Not disagreeing necessarily, but having many different databases has a cost.

Data has more value when combined, so a wise database choice also depends on what you already have. You can combine data from different systems (e.g. FDWs, which postgres has great support for), but something is usually lost along the way.

I think postgres is a good default, because it is good for a lot of things. But sure, if you make an informed decision otherwise, there's nothing wrong with that.

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.

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 many of those unknowns as possible.

I believe that Postgres is the best choice in that scenario, as it is extremely mature and has an incredible amount of flexibility.

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.

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 is aim to be generic database but rides on the wave of NoSQL "coolness". It was created by people who had no experience in databases and are learning as they go[4]. As they started adding things that are essential for database they realized it's not that simple.

Currently MongoDB is outperformed by Postgres. In fact there is an application called ToroDB which provides protcol compatibility for Postgres that emulates MongoDB and even that outperforms Mongo. Mongo also doesn't scale well horizontally, so essentially you don't really don't get any significant advantage.

[1] https://en.wikipedia.org/wiki/Hierarchical_database_model

[2] https://en.wikipedia.org/wiki/Object_database

[3] https://en.wikipedia.org/wiki/XML_database

[4] For example they started with mmap'ed memory regions to store the data. Did not initially use fsync() to make sure data is saved on disk. In a way it reminds me of MySQL several years ago. It's much better now than it was in the past, but it has a lot of warts left of from the past.

I conjecture that it's rare that someone just needs a document DB.

The main problem someone is thinking about may be solved by a document DB, but then they also have 1000 other problems they didn't think about that are a horrible fit for a document DB.

And due to the nature of databases, where data has greater value when combined, using a new database system for every application isn't a great option.

SQL is great for business data, and passable-to-good for everything else. So the only way to beat it is by being way better at some specific thing, and finding users where that specific thing is so important that it's OK if the data is on an island.

Can't speak for the original poster, but at this point Postgres is a very capable and performant document-store database as well with the addition of the JSONB type and associated indexing and modification (in 9.5) functions. It's also more mature, has generally better durability than MongoDB, and it is easy to fall back/extend into relational queries on your document-store data if need be.

IMHO, unless you have a situation such that Mongo's horizontal scaling is actually required, it's better to use Postgres even if you're doing document-store stuff.

Because MongoDB is chasing a moving target. Hype, built in sharding and built in automatic failover are what it has now. The PostgreSQL project was strong when Mongo was conceived and has been accelerating. PostgreSQL has already gobbled up and improved on other MongoDB features like BSON and is where you go when you grow out of MongoDBs more primitive document model. Easy sharding and failover will fall too, years before MongoDB catches up with other must have features like transactions (you will need them eventually) or a half decent query language so you can actually maintain your code rather than having to plan your own queries and encode them as a sequence of JSON documents.

Postgres is a better document store than Mongo.

The only time I would recommend Mongo is for storing geo-spatial data, as it has several built ins that make it much easier to work with. Even then I find it a lot more convenient to keep that data in Postgres and replicate it to Mongo.

Out of curiosity, how often do your detached DBs update old records? The reason I ask is nostalgia. A company I worked with used MySQL as an embedded DB (laptops under their control). Yesterday, I thought about how absurd this was. The DB only inserted new records and read static records. An embedded SQL would have worked great here. Would that be true for you too?

Honestly that's not something I would handle at the db layer. I would build a service that is responsible for keeping client db's in sync and abstract that away from my database entirely.

Are you H2database author ?

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

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 flexible from a Dev view (performance is generally more predictable, much wider feature set with document storage / PostGIS / etc., …).

I think he meant that MySQL is the only supported DB for Wordpress, and while you might be able to get it running on another DB after jumping through some hoops, it's probably not worth the effort.

IIRC PHP has built-in support for MySQL out of the box.

I'd bought one of the first haswell notebooks to play around with tsx. Before I'd time to do so Intel found the tsx bug...

I hope to have time to play around with it one I have new hardware (I refuse to do performance development on virtual).

But honestly, most remaining performance/scalability problems in pg are more algorithmically caused. So micro optimization, and that's what I'd call tax/hle, aren't likely to biggest bottleneck.

I could've sworn that Intel actually demonstrated TSX on a Postgres fork they created, but my Google-fu is too weak to find the slides again.

What would you say those learning points are? What key factors would you introduce into another OSS project that PostgreSQL currently employs?

> 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?

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.

/plug/ Riak TS was just released open source that does just this. I wrote a post on it, http://basho.com/posts/technical/time-series-the-new-shiny/ .

Disclaimer: I work for Basho, makers of Riak TS.

Postgres has fairly robust support for temporal-style data. In particular, with 9.5 BRIN indices are often a great fit for heavy write workloads where most queries are range scans. It's not the best database for it, though.

> From what I learned, mysql's purpose was to stop people from using text files as storage points in the early 2000's.

Well shit, SQLite has that market sewn up these days.

> after spending a few weeks on this project I discovered that mysql only supported a small subset of SQL

Did you not research this beforehand?

> 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).

The funny thing is is that the trend for analyzing data is going from doing things in SQL databases backed data warehouses with queries to running that stuff on clusters with Spark or Hadoop based solutions.

> 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 isnt anywhere close to making it the absolute winner technically.

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?

>>The strangest thing to me is not that MySQL is more popular than 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.

A lot of decisions are made holistically instead of a "purely technical standpoint". In large companies where there are still IT departments (e.g. use of Active Directory), MS is still very much the de facto platform, and SQL Server is the "logical" choice.

Consider that PG is adding wait events in the upcoming 9.6 release, a feature Oracle has had for about 20 years...

I'm curious abour another thing: like the previous comment asked about MySQL and MariaDB, and your answer cuts away MariaDB from the quote.

However, the trend seems to be from MySQL to MariaDB. Red Hat and many other Linux distros, for example, have moved to MariaDB. It doesn't feature Larry Ellison. So why not address it?

While I love me some PG, SQL Server is nothing to sneeze at. Was fairly impressed with it, never had any serious issues on an under-specced server once occupied by terrible programmers

"MySQL is popular among a subset of programmers: web developers."

Slapdash web developers.

Most web developers I know now consider postgres the base standard.

Why "nowadays"? I can't really remember a time when it wasn't. If anything, MySQL is far less incomplete than it once was (like, it even supports transactions now, which once was one of the most important reasons for using Postgres over MySQL).

What you are describing is no longer the default configuration.

Say what you like about oracle, they have been a great steward of MySQL.

From what I can tell the mainline Linux kernel has code developed by Oracle, so you don't trust any developers that ever rely on Linux?

I am in no way a fan of Oracle but, c'mon, that's a little over the top, don't you think?

That's why there's MariaDB.

Do you have evidence that you can share?

Yes. Mysqlserverteam.com links usually do not show up on HN, but we have similar benchmarks showing RO throughout to 1.6m qps on Intel

Python has had rock-solid, widely used MySQL libraries for well over a decade. I've never seen evidence that MySQL and Python aren't a strong (and popular) combination.

Pretty sure there are more or at least as much startups using Ruby, JS, PHP, Java before Python.

seems like that would be pretty tough to get reliable stats on something like that.

http://www.tiobe.com/tiobe_index

Err, no. Python probably isn't even in the top 5 of languages startups choose for their main stack.

Yeah, if I start a new project, I default to Postgres: https://journal.dedasys.com/2015/02/21/i-default-to-postgres...

I'd consider something else if I'm really, really sure that it's better suited to whatever niche problem than Postgres, but it'd take a lot of thinking and convincing myself.

In specific, it is a column store, which is advantageous to do things like real time analytics over millions of data points via streaming market data. This has uses for HFT, but also for anyone who wants to do their own day trading.

It also came about before CAP was postulated, and does nothing to automatically resolve partitions. It doesn't -die-, but it was clearly built with a "we'll run this in our own data center" mentality. In the event of a partition, it goes immediately to split brain mode, which each side of the partition running separately, allowing both writes and reads, and doing nothing to try and automatically heal the partition, even if your Erlang nodes reconnect.

This isn't necessarily a bad thing; it's a very easy model to reason about, it rarely has an issue if your cluster doesn't span more than one data center, and it lets you know when it happens via events you can subscribe to. But doing anything different, up to and including healing the partition automatically, is left to the user.

There's also a few warts due to its history; disc only tables have a rather small max size (so if you're not expecting everything to be stored in RAM as well, you don't want to use Mnesia), indexes are shockingly inefficient for writes, and a few other odds and ends that I don't really remember. For persisted, but fast, storage in Erlang, where partitions aren't common, it's great, but outside of those sorts of use cases there's probably something better.

> Dets uses 32 bit integers for file offsets, so the largest possible mnesia table (for now) is 4Gb.

Ehhhhhhhh, kinda, but not really. A couple of things: [0]

* If you use a disc_only_copies table, your max table size is ~2GB because that's apparently the largest file that DETS will work with. What's more, if your table grows to ~2GB, future writes (to that table) will silently fail until the table size is reduced.(!!!) You can kinda get around this limitation by sharding your table [1], but the hash used to determine what key goes to which shard isn't very balanced... sometimes you luck out and your shards are pretty much all the same size. Other times you end up with a few outsized shards.

* However! Everyone on the erlang-questions mailing list says that disc_copies and ram_copies tables are _NOT_ subject to this limit, so they can grow arbitrarily large.

* And, the general consensus seems to be that you really don't want to be using Mnesia in disc_only_copies mode... if you have too much data to fit into RAM, you should probably consider using something else to store your data. This isn't to say that using Mnesia in disc_only mode is bad or hazardous, [2] but that it's substantially slower than disc_copies or ram_copies, and you run the risk of bumping up against the DETS file size limitation.

[0] All observations valid for Erlang 17->18 only. Check Erlang release notes to see if major changes have occurred.

[1] Mnesia handles sharded tables really well, even if the documentation for the feature isn't the best.

[2] I use it in disc_only mode for one of my projects... it's how I learned about that mode's limitations. :p

>32 bit integers for file offsets, so the largest possible mnesia table (for now) is 4Gb

Do they mean 4GB? Surely 0.5GB/4Gb is a bit small even for 32bit?

Would it be possible to dynamically choose the padding at server start time? Given that cache line sizes vary that seems like it might be a prudent choice.

    Compatible up to: 3.4.2
    Last Updated: 2 years ago
    Active Installs: 400+

WordPress 4.5 is current. Anyone doing WordPress will just skip this if they don't have developers that can fix the issues. Also while the base may work a lot of popular plugins (used to?) don't utilize WP_Query or whatever else WordPress offers.

It's also probably easier to add indexes to the code or rewrite the logic. Mostly it's bad database code that sucks on MySQL and would likely suck the same way on PostgreSQL.

Besides that if you use an object cache like memcache or redis you can avoid a lot of database accesses and InnoDB seems to be able to deal with concurrent tables like wp_comment.

Would be cool to see in WordPress itself but I doubt it.

> 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's a separate web app for accessing the database using SQL features mysql doesn't support. Lack of Wordpress/pgsql compatibility makes integrating the existing db and folding in the webapp functionality just about impossible, at least I'm not seeing how that could be done.

Since Wordpress isn't going to embrace pgsql, I've recommended considering alternative CMSs. Decisions are still pending.

Thanks :)

Ah! Yeah. It's just we're putting new coversheets on all the TPS reports before they go out now. So if you could go ahead and try to remember to do that from now on, that'd be great. All right!

No fun allowed here.