News for hackers. Stuff that matters.
Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by doh on Thursday February 16, 2017 @07:02PM (#13664797)
We're using Citus for almost a year, 8 months in production. Currently running 22 workers [32 core, 3TB space, 120GB RAM] containing almost a trillion (yep) rows in one single table.

Citus has a lot of quirks and one could write a book about it. But for shortness here is just couple of the top things you should know.

- Citus is not PG. It runs on PG, it looks like PG but it doesn't behave like PG. By using Citus you lose majority of advance strategies, especially ones that manipulate data (UPDATE from SELECT, DELETE, etc.). In very short, you have to forget about everything you know about PG.

This means that you have to rethink your table schemas, distribution strategies, etc. This is common in every distributed database (No/SQL).

- Citus deployed in a cloud is a bit sensitive. As the master node keeps track of each worker in almost realtime fashion, any breaks in communication results in corrupted shards. If you don't use just one user (postgres) but have different rights for different users, fixing the shards can be a pain (if you don't pay for the enterprise version, which provides you with helpful scripts).

- Adding new worker is easy, but rebalancing them to spread the shards evenly is not (again different for enterprise version).

- PG's planner can be a bit stubborn while using citus. It sometimes behaves differently from the native PG. The result is that you have to rethink you strategies and rewrite queries up until you don't get the desired result.

- Writing functions and automatic triggers can be challenging. The usefulness of them is determined by how well you designed you original tables. Once the tables are colocated and sharded on the same column you get impressive results. Writing the functions can be challenging though as you have to deploy the function to each table. They offer a script to help you with it https://github.com/citusdata/citus/blob/a2f6b29a6d4537b626f8...

- You do want to consider how many shards you need. More automatically means that Citus gets slower, but you want to be able to grow so chose more than you have workers.

There is much more, but those are the things that you should consider. As for us, we learned to live with the quirks and now we're able to run aggregation queries through billions of rows in under 600ms.

I would suggest to consider Citus only if you're considering something like Cassandra or Hbase.

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by mslot on Friday February 17, 2017 @11:03AM (#13669158)
> Citus deployed in a cloud is a bit sensitive. As the master node keeps track of each worker in almost realtime fashion, any breaks in communication results in corrupted shards. If you don't use just one user (postgres) but have different rights for different users, fixing the shards can be a pain (if you don't pay for the enterprise version, which provides you with helpful scripts).

Citus supports two replication models: statement-based replication and streaming replication. [1]

In statement-based replication the master directly writes to several replicas. If writing to a particular replica fails due to a network issue, then to maintain both availability and read-your-writes consistency, the master marks that replica as inactive to be repaired at a later time.

In streaming replication there is primary and secondary worker. The master only writes to the primary which replicates its changes to the secondary. If writing to the primary fails, then the transaction rolls back and no repair is necessary. If the primary goes down, it can be replaced by the secondary. In this model the master doesn't have to keep track of any state or perform any special locking, which is also what enables Citus MX [2].

Citus Cloud exclusively uses streaming replication with automated failover, meaning shards never have to be repaired.

[1] https://www.citusdata.com/blog/2016/12/15/citus-replication-...

[2] https://www.citusdata.com/blog/2016/09/22/announcing-citus-m...

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by doh on Friday February 17, 2017 @11:50AM (#13669644)
I'm aware of the streaming replication. But the cost doubles immediately. Which in our case represents tens of thousands dollars every month.

Now this is a tradeoff. Do I mind to fix some shards from time to time or burn salaries of 6 engineers for extra convenience.

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by jpgvm on Thursday February 16, 2017 @07:13PM (#13664857)
Are you using the commercial distribution or the OSS one?

If the OSS release how have you implemented rebalancing? It seems to me the APIs are there in the extension, just user defined functions are missing to actually do the rebalance.

I have been looking at integrating Citus into Flynn as an option for more scale-out workloads but needing non-OSS features to recover from workers going missing or adding new workers put me off implementing it for now.

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by doh on Thursday February 16, 2017 @07:25PM (#13664914)
We're still stubbornly using the OSS version. I wrote my own scripts that help with the rebalancing but it's a pain to say the least.
Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by sroussey on Thursday February 16, 2017 @08:42PM (#13665256)
Since the OSS version is AGPL, where is your code? Would be interesting to see the full use case.
Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by doh on Thursday February 16, 2017 @08:47PM (#13665276)
I did not modify anything Citus related, just wrote scripts that are using their API.

Also the code is messy (as everything built for an in-house use).

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by sroussey on Thursday February 16, 2017 @09:02PM (#13665342)
It is AGPL, not GPL, so code using the API must be AGPL as well. That's the difference.
Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by sroussey on Thursday February 16, 2017 @09:22PM (#13665439)
Oh God, please don't quote stackoverflow for legal opinion. An interesting confirmation bias problem. :/

But no copy/paste without attribution, which is the usual thing (and a reason why some companies block SO). So that's good! :)

Love what Citus is doing as well! And looking into the product now, talking with the team, and considering purchasing.

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by jpgvm on Friday February 17, 2017 @01:19AM (#13666324)
That is an interesting point.. if it's AGPL and you are calling user defined functions from your application that are implemented in the AGPL postgresql extension is your codebase affected by virality clause?

Or would that only count when you write new UDFs that call into the extension? Or are neither affected?

Would really love to know because that might affect my willingness to spend time making automated Citus stuff a reality.

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by anarazel on Friday February 17, 2017 @01:38AM (#13666390)
Disclaimer: work for Citus, but am not a lawyer, nor an executive.

There's some additional information at https://www.citusdata.com/faq under "How do I license Citus Data Products?".

I hope that clarifies the intent of using AGPL somewhat?

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by doh on Friday February 17, 2017 @11:43AM (#13669570)
I don't think it does.

> Require users who choose to modify Citus to fit their needs, to release the patches to the software development community.

Do you consider a script that uses your API a patch? In my opinion there is no modification to Citus directly. It's used as intended and the only change is how is used instead of what it does. But it's not clear to me.

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by doh on Thursday February 16, 2017 @09:09PM (#13665384)
Based on this [0] the code doesn't have to be shared. Also would not like to publish code that goes against commercial interest of the Citus team, which I like quite a lot.

[0] http://softwareengineering.stackexchange.com/a/314908

Re:Citus 6.1 Released – Horizontally scale your Postgres dat... by manigandham on Friday February 17, 2017 @08:40PM (#13673151)
If anyone is looking at the enterprise version then it's probably best to try memsql.com too, which is a much more polished product with a mysql dialect.