Citus Data Blog

It is possible, if you pay for it. You can do Multi-AZ Clustered Instances in RDS, where you get the benefits of Multi-AZ failover with traffic sharing. If you can run your own infra – at least on an EC2 level – you can do things like Citus [0] for Postgres, which is about as close to "just add database nodes" as you'll get. [0]: https://www.citusdata.com/

I've been following Citus Data, it might be interesting for you. https://www.citusdata.com/

That's a great point. This isn't really what you're saying, but citus [1] ships a distributed Postgres. A lot of the things they improve would help massively with analytical workloads actually. 1: https://www.citusdata.com/

This depends what you mean by "scaling". Postgres can easily handle 10s of Terabytes on a single server (though this is dependent upon your use case), but when it comes to multiple host sharded databases at the moment MySQL has some more mature options like Vitess [1], where your options in Postgres are things like Citus [2] which just recently open sourced their shard mover, but still have the HA and replication feature behind expensive enterprise pricing. For most people not dealing with the multiple terabyte database size, Postgres is what I would definitely recommend. But once you get above that, then there are no easy answers and you would need to look at what YouTube did with Vitess [3] [1] https://vitess.io/ [2] https://www.citusdata.com/ [3] https://vitess.io/docs/overview/history/

I am sure MySQL is a great fit for your use case. For others reading your comment though, I did want to list some things I have used with Postgres that relate to connection pooling and data partitioning: * PGBouncer for connection pooling/sharing. * Postgres Table Inheritance for table partitioning (https://www.postgresql.org/docs/12/ddl-inherit.html) * PGPartman for automating the creation of partitions (https://github.com/pgpartman/pg_partman) * Citus for low-barrier data sharding (it's a Postgres Extension like PostGIS) (https://www.citusdata.com/)

If you'd like to give Postgres a serious shot, I'd recommend taking a look at Citus: http://citusdata.com/. It's a distributed database forked from Postgres, and can easily handle billions of rows in realtime for certain use-cases. We use it at MixRank to store and run analytics on for ads we crawl in the internet (a respectable amount of data), and it handles extremely well. (Full disclosure: I know the Citus team well). I've also pushed Postgres to its limits and suspect you'd be able to make it work with that much data, though I wouldn't consider it a trivial exercise executing that transition on a production system. If you're seriously considering it though, feel free to reach out via the email in my profile.

Postgres. * PostgreSQL supports most of the major features of SQL:2016. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features. It might be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2016. * Scales Vertically and Horizontally with tools such as citus (https://www.citusdata.com/) * Postgres supports RLS (Row Level Security) allowing you to implement authorization at the database level. * It can support many 1000's of transaction per second running on commodity hardware. * NoSQL support. Postgres can store and search JSON and other types of unstructured data. * Postgres has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.

Citus Data (https://www.citusdata.com/) | Technical Solutions Engineer | Full-time | San Francisco Citus is worry-free Postgres. Built to scale out, Citus is an extension to Postgres that is available as open source, as on-prem enterprise software, and as a fully-managed database as a service. We are a fast-growing database startup in San Francisco. We founded Citus Data with a vision to combine the best aspects of relational databases with the elastic scale of NoSQL. And we’re building a team with some of the best minds in distributed systems, databases, and Postgres—along with engineers with extensive operational experience managing millions of Postgres databases in the cloud. As a solutions engineer, you will be responsible for analyzing customer requirements, understanding their use-case, and building an end-to-end solution using Citus technologies. You will need to quickly prototype solutions, run proof-of-concepts to demonstrate value, and also help customers move to production. Every customer is different, and thus adaptability and being a quick learner is crucial. Your day to day role will involve talking to customers, writing code, performance and query tuning and debugging and solving problems. You can learn more about the position and apply here: https://www.citusdata.com/jobs/solutionsengineer

Beyond other reason listed in the comments, Microsoft is heavily invested in Postgres. They bought Postgres sharding company (https://www.citusdata.com) and apparently it sees a ton of use internally even for newer Greenfield projects. Also, if check recent commits to Postgres, you will find Microsoft employees all over commit history. As I've commented several times before, MSSQL appears to be on life support. It makes them a ton of money and it's supported in same way AS/400 is supported. Sure, Microsoft will take your money but don't expect a lot in return.

ok, that seems like a bit of a moved goal post, but you do you. is there any particular impact you see that having? Postgres has had it's share of data integrity issues due to things like locale differences between primary and replica, where strings would get sorted differently. I'm not advocating for one over the other. I love both, but neither are perfect. https://www.citusdata.com/blog/2020/12/12/dont-let-collation-versions-… https://wiki.postgresql.org/wiki/Todo:ICU

This is just vanilla Postgres. To start with I wonder how ScyllaDB will compare against Postgres extended with AGPL-licensed Citus [1] Note: Like Github, Citus Data has been a subsidiary of Microsoft for a while now. [1] https://www.citusdata.com/

It is worth noting that Citus was acquired by Microsoft a few years ago (not a secret, but may not be obvious to everyone), and they have since shifted heavily towards the Azure side of the world. Back when it happened we tried to buy their product and they were not sure if the Citus standalone product was even going to exist, and they refused to demo it even. Odd timing possibly, but it's a data point. On the positive side of things, the shard rebalancing was not open source back then iirc, which made the open source version pretty useless. Now it seems to be open source: https://www.citusdata.com/product/comparison -- pretty cool. I'd still be careful to bank on it as a Citus only customer or open source user.

Connection scalability was greatly improved in v14 and above. https://www.citusdata.com/blog/2020/10/25/improving-postgres-connectio…

See also: postgres with SKIP LOCKED. Great talk on it from Citus Con 2022: Queues in PostgreSQL | Citus Con: An Event for Postgres 2022 -- https://www.youtube.com/watch?v=WIRy1Ws47ic&list=PLlrxD0HtieHjSzUZ… Citus is a horizontal scale out extension for postgres -- imagine the power of those two things together! JK you don't have to imagine: https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partman-creatin…

I am not sure exactly what Galera does, but you may want to look into Citus (https://www.citusdata.com/).

pg_auto_failover is good https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover Disclosure: I work for Citus

I remember building a system about 15 years ago which automatically failed over in under a second - the application connected to the most recent one and set up replication to the other one. If the master went down then the slave was promoted to master and when the original master came back it was configured as slave. Looks like https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover… will sort out a Postgres master master for you. I certainly don’t have auto updates on all my servers (wouldn’t be good if a tv program going out to millions of people suddenly vanished — there’s always a glitch when it fails over), I do have it on public facing servers though, so in your case I’d have it on the web servers but not on the database.

I think Citus can be used effectively for that scenario now. https://www.citusdata.com/

Getting back space is real important. Otherwise you have runaway table growth that never ends. Not saying do it all the time, but it is important to use surgically. You can easily calculate wasted table space https://www.citusdata.com/blog/2017/10/20/monitoring-your-bloat-in-pos… When it gets to be too big, it's a good time to do a vacuum full. I normally do it a few times a year on a few giant tables. Yes it locks up a table for a few minutes, but it is a big win and totally worth it.

There are a lot of products that either fork or directly extend PostgreSQL for OLAP workloads. Greenplum[0], Materialize[1], Citus[2], PipelineDB[3] etc etc. [0] https://greenplum.org/ [1] https://materialize.io/ [2] https://www.citusdata.com/ (edit: I said TimescaleDB, I was thinking of Citus) [3] https://github.com/pipelinedb/pipelinedb Disclosure: I work for VMware, which sponsors Greenplum development and sells commercial offerings.

You can use Citus(recently bought by Microsoft) to scale postgres https://www.citusdata.com/

That is one way, not necessarily the most efficient. And having gaps in the id sequence can complicate pagination. Recommended reading: https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

> I believe Citus DB(?) is something similar. "Citus Unforks From PostgreSQL, Goes Open Source" https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes-open-sour… https://news.ycombinator.com/item?id=11353322 As for mysql - I don't know why anyone wouldn't prefer mariadb - but I suppose it's conceivable mysql has features that makes it worth dealing with oracle licensing. But I doubt it.

To scale a PG db horizontally, you may want to look at https://www.citusdata.com/ (they were recently bought by Microsoft but I don't expect any change on the Open Source part).

HyperLogLog is a really interesting and powerful data type. Though a few things seem missed in the article. HyperLogLog has been around I believe over 10 years now. It's been supported for Postgres for over 5 years [1] now as an extension[2]. It is great to see it growing further with Facebook adding support for it for Presto. [1]. https://research.neustar.biz/2012/10/25/sketch-of-the-day-hyperloglog-… [2]. https://www.citusdata.com/blog/2017/06/30/efficient-rollup-with-hyperl…

Check out what Citus are doing with PG https://www.citusdata.com

CitusDB? https://www.citusdata.com/ There are a few other solutions for postgres as well.

Guys behinds PostgreSQL / CitusDB wrote awesome article on which methods of pagination work how well and where they fail. https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/ Highly recommended read.

For Aurora/RDS Postgres there's https://www.citusdata.com/ or https://www.heroku.com/postgres

Honorable mention - CitusDB It installs as Postgres extension and supports parallelization of queries across nodes. https://www.citusdata.com/