mslot

> Would it be feasible to make Podyn run in a scheduled Lambda function? would save us from having an EC2 around...

That's an interesting idea! My mental model for running Podyn was a container or T2 instance, but I think it wouldn't be hard to add Lambda support.

I've created an issue for tracking it: https://github.com/citusdata/podyn/issues/2

Author here.

I'm interested in learning a little more about DynamoDB "schemas" in the wild. For DynamoDB users: Is translating the top-level keys in items into columns a reasonable thing to do?

(schema conversion rules: https://github.com/citusdata/podyn#schema-conversion-rules )

Author here. This is a pretty new tool. If you have any thoughts on how to best translate a DynamoDB schema to postgres or other feature ideas, I'd love to hear!

Github page: https://github.com/citusdata/podyn

We don't have TPC-C numbers. I did run some pgbench numbers and put together a little video comparing Citus to Postgres:

https://s3.amazonaws.com/citus-conferences/postgresopen-keyn...

(no audio)

Consider UPDATEs that depend on each other:

    UPDATE table SET value = value + 2 WHERE key = 'world';
    UPDATE table SET value = value * 1.1 WHERE key = 'world';

When these UPDATEs are executed concurrently, PostgreSQL guarantees that they are serialised such that the value becomes either (value+2) * 1.1 or (value * 1.1)+2, i.e. there’s no lost update. To achieve this, PostgreSQL blocks the second update until the first one is committed using a row-level lock.

An alternative mechanism could have been to create a log of changes. The order in which the changes are added to the log then determines the order of execution. That way, we don’t have to block the UPDATE, right?

Unfortunately, things are a little more complicated in an RDBMS. Each of the UPDATEs could be part of a bigger transaction block that could (atomically) roll back. Moreover, the RDBMS provides consistency which means that the new value should be visible immediately after the UPDATE. However, the new value cannot be determined until all preceding UPDATEs have been committed or rolled back. So in the end, the UPDATE will have to block until all preceding transactions that modified the same rows are done and the new value can be computed, just like what would happen with row-level locks, except with more bookkeeping (and serialisation issues).

If you strip out some of the functionality of an RDBMS such as multi-statement transaction blocks or consistency, then you could potentially avoid locks, but Citus distributes PostgreSQL, which does provide this functionality.

Author here.

For the purpose of the article, adding a user ID column to the items table primarily helps with distributing the data, since all of a user's lists and items can be placed on the same node based on the value in the user ID column, which enables transactions and efficient joins.

Distribution aside, it can be a good idea to separate user data by explicitly adding a user ID column for performance and data integrity reasons. It's difficult/inefficient to guarantee inside the database that item IDs aren't reused (e.g. due to bugs in the application, serial overflow). For row-level security [1], it's also required that all tables have a user column.

Always adding a filter or join by user ID to your queries also allows for more efficient query execution. You often access many or all items of a user at once, in which case it's more efficient for the database to use an index on user ID than doing multiple lookups using an index on list ID alone (this applies generally for multi-tenant apps).

Finally, you might have to scale out one day :).

[1] https://www.postgresql.org/docs/current/static/ddl-rowsecuri...

Biased view of a Citus engineer here :)

Any enhancement to PostgreSQL is also an enhancement to Citus, or rather, the PostgreSQL ecosystem as a whole. For example, PostgreSQL 10's declarative partitioning feature will help enable sharding+partitioning in Citus, which is one of the most frequently requested features.

PostgreSQL 10 also gives you the possibility of setting up a partitioned table in which the partitions are postgres_fdw tables, which allows a basic form of manual sharding without Citus. However, as we've learned over the years, there's a huge difference between the ability to distribute a table across multiple servers and addressing a use case.

A sweet spot for Citus is multi-tenant (SaaS) workloads, in which all queries and transactions are specific to a particular tenant. In that case, you can typically distribute most of your tables by tenant ID, and use (replicated) reference tables for data that is shared across tenants. Citus makes sure that data for the same tenant is automatically co-located and as long as your query filters by a particular tenant and joins by tenant, you get full SQL pushdown (with parallelism in PG10), and ACID transactions. At the same time, you can perform parallel DDL commands across all tenants to enable migrations, bulk load data through COPY, perform parallel rollups or transformations through INSERT..SELECT, and run parallel analytical queries. Overall, the combination of these features and the trade-offs that Citus makes ensure that if you need to scale out a multi-tenant app, sharding through Citus solves it. In many cases, the only changes you need to make in your app are adding a tenant_id column to your tables [1], being explicit about the tenant in your queries or ORM [2], and adding create_distributed_table calls.

None of the Citus features that allow you to scale out a multi-tenant app are available if you do sharding through partitioning+postgres_fdw so far. I also wouldn't expect core postgres to make aggressive trade-offs to optimise for specific use cases. The PostgreSQL way is to make everything pluggable and let extensions specialise.

[1] https://www.citusdata.com/blog/2016/08/10/sharding-for-a-mul... [2] https://www.citusdata.com/blog/2017/01/05/easily-scale-out-m...

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

Isn't the opposite true, though? SQL offers you a lot of flexibility, whereas NoSQL follows a rigid data model.

I always think there are huge opportunities for growth in intra-EU trade. It's a market with over 500M people and a $17 trillion GDP. While the EU has taken away a lot of trade barriers, language and unfamiliar regulations remain a huge barrier, but also a huge potential for growth.

If a company in California has ample opportunities to sell in Florida (>2000 miles away), why then is it significantly more difficult for a company in Greece to sell in Denmark, which is a much shorter distance.

There is a notable lack of an open European marketplace along the lines of Alibaba. There are many challenges in making that model work for the EU, especially ~24 languages and big cultural differences, but the tech industry is in a good position to overcome such boundaries.

(Marco from Citus Data)

It depends on the use-case, but PostgreSQL and Citus can be a sensible alternative to NoSQL.

RDBMS and NoSQL are on the opposite ends of the scaling spectrum. RDMBS provide very rich functionality and put few restrictions on your data model, but require the data to be stored one big lump on a single machine. NoSQL databases provide minimal functionality and put severe restrictions on your data model (must be a key->value set), but can distribute the data across many machines.

Citus sits somewhere in the middle. The ideal use-case for Citus is one in which your data is kind of lumpy/relational, but can still be distributed by a particular key. The most notable example is multi-tenancy [1]. If you're running a (B2B) SaaS application with many customers, your queries are usually specific to a particular tenant, which means you can probably add a tenant_id column to all your tables and distribute the tables by that column. Citus automatically co-locates [2] data to support joins between different tables on tenant_id. You can then use full SQL and ACID transactions as long as you're always filtering by one specific tenant, and a useful subset of SQL for parallel queries across all tenants (incl. inner/outer joins). Citus is far more capable than most databases in this area, partly because it can build on the querying features of PostgreSQL, rather than having to implement them from scratch.

The main advantage of using Citus over PostgreSQL in this case is that you can scale out memory, CPU, and storage space to keep up with your read workload. In addition, it provides parallel aggregations through INSERT..SELECT and parallel indexing when performing a COPY, allowing you to keep up with bulk write workloads. The main drawback is that you cannot perform arbitrary SQL queries across all the data and some queries may require a different data model.

The main advantage of using Citus over NoSQL in this case is that you can use arbitrary single-tenant SQL queries and indexes, which makes data modelling far simpler and thus cheaper. Being able to perform parallel cross-tenant analytical queries and parallel aggregation [3] also avoids the need for maintaining separate systems for analytics (again, cheaper). Bulk loading through COPY can provide extremely high write throughput at relatively low cost [4]. A drawback is that, to provide consistency, Citus has designated primary nodes for taking writes. If a primary goes down there will be ~1 minute of down-time for the keys stored on that primary until the secondary takes over (similar to e.g. Kafka). However, this is not a huge concern for most applications, especially considering that PostgreSQL is very stable software so crashes are usually only hardware related.

In a pure key-value storage use-case, the extra functionality provided by Citus is not as beneficial as it is in the multi-tenant use-case, though features like JSONB, GIN indexes, SQL functions and ACID transactions can still be invaluable. In terms of short request throughput, regular Citus is comparable to a single PostgreSQL node in being able to sustain around ~50k writes/sec, which is less than typical NoSQL databases. However, if you need more than 50k/s writes, then Citus MX will be able to scale out your write workload horizontally [5].

For key-value use-cases, I would say Citus (MX) is competitive with NoSQL databases, but not specifically better or worse, just making different trade-offs. The big advantage is mainly that, if one day you decide you also need analytics, or a new query, or a new filter, or a new conversion, then you don't need to deploy a new system and probably won't have to remodel your data.

[1] https://www.citusdata.com/blog/2016/08/10/sharding-for-a-mul...

[2] https://www.citusdata.com/blog/2016/12/22/scaling_out_sql_wi...

[3] https://www.citusdata.com/blog/2016/11/29/event-aggregation-...

[4] https://www.citusdata.com/blog/2016/06/15/copy-postgresql-di...

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

Don't worry ;)

Citus users typically have a working set of a few hundred GB or more and run into memory and CPU limitations on a single machine on both the write and the read side.

Provided your data has a natural sharding dimension (e.g. tenant ID in a multi-tenant application), Citus can distribute tables across many servers to scale out memory, CPU and storage. It can also parallelise queries, index creation, aggregations, and deletion across the cluster, and it can horizontally scale out write throughput. This gives you the necessary tools to deal with very large data volumes.

e.g. 500k writes/sec https://www.citusdata.com/blog/2016/09/22/announcing-citus-m...

(Marco from Citus Data)

Pre-aggregation is less common in data warehouses use-cases than it is in real-time use-cases. Data warehouses are generally used by a small number of users that run ad-hoc reporting queries and can take anywhere from several seconds to a day to complete. With queries changing frequently and high query times being acceptable, pre-aggregation does not provide that much benefit.

Citus is meant for applications with a large number of users (e.g. dashboards [1]). Investing in pre-aggregation is often worth doing because it makes for more responsive applications and lower hardware cost.

> It's fragile: in this example, it depends on your data coming in exactly-once, in-order.

The solution proposed in the blog post does allow out-of-order writes since it only looks at the database clock. Exactly-once delivery depends on a) the database clock not going backwards, b) writes taking less than a minute. However, it is also possible to guarantee exactly-once delivery with a more complex solutions (e.g. create staging table, or use a version number that gets increased atomically through some locks).

[1] https://blog.cloudflare.com/scaling-out-postgresql-for-cloud...

The "modern left" is not some kind of hive mind to which we all belong. It's a very diverse contingent of society, that cannot simply be compared to rural America.

Clearly, if you are an uneducated white man from a small village, your prospects in life are not very good. However, blaming underprivileged minorities, whose prospects and social problems are typically even worse, is highly counter-productive and likely to bring the hone of both those minorities and the more privileged people who live with them.

Something should be done about the impending poverty of rural America. In 2016, uneducated workers are no longer competitive. Factories, farms, even battle ships only require a few people.

Without a concrete economic outlook, the path forward most likely lies in government support through social security, health care, education, i.e. the very policies for which the "coastal elite" tries to vote and pay. However, these policies do create an often-ignored identity crisis: Men want to feel proud of what they are.

Without traditional jobs, or people around them that show them the path to modern jobs, all they have left to be proud of is the values and colour they share with the people around them.

Was going to say, RX100 is in a class of its own :). I always have it with me when I travel and often have it my pocket just in case. I don't always use it, but it's small enough not to care. Especially for outdoors photography, an RX100 generally takes better pictures than a DSLR, because you're more likely to have it with you when a good picture opportunity arises.

I think it's pretty clearly specified which ones are apartments. I travel very frequently and have had various bad experiences with AirBnB. Nowadays I almost always book an apartment via booking.com. It's nice to have an actual customer service who takes care of issues, not always having to pay absurd cancellation fees, not having to wait for approval, and hosts not cancelling at the last moment.

Or rather, an almost instant traffic fine between $100 and $400.

I wonder if the 2 cameras can be used as a stereo camera for augmented reality.

Well, we can buy mozzarella that's actually mozzarella, so that's something.

But what if you need to partition and possibly scale out part of your database? At that point a normalized data model becomes a huge road block. A balanced approach to normalization often allows you to more adaptive to requirements. If possible, it should be avoided for tables that can grow large.

I assume Alpha Go has been trained using a large number of games played by Go masters so the games it played itself probably won't provide much more information, unless it can learn its own weaknesses from those games.

WhatsApp came to Europe at a time when there was a strong need to replace badly overpriced SMS. In the US most telco's offered subscriptions with free, unlimited SMS and WhatsApp didn't provide any immediate benefit. Facebook Messenger came later, but had the ability to send messages without contact information, giving people in the US a reason to adopt it.

By now, most people in Europe also have Messenger, but still mainly use WhatsApp (slightly generalising). I think that primarily comes down to WhatsApp being a much better messenging app. It's quick, reliable, easy to use, power-efficient, small, well-integrated into the OS, secure, and most importantly: media sharing just works.

Literally most countries in the world: http://www.techtimes.com/articles/160897/20160525/top-messag...

Sure, I have messenger and hangouts and telegram as well, but WhatsApp is like a primary mode of communication.

I don't believe that humans take split-second (binary?) decisions based on morality, only survival. Most of the time, people's instinct tells them to get off the road if there are no alternatives. This actually seems like a reasonable decision to increase the chance of survival. Self-driving cars should probably do the same.

Reads do involve making some network round-trips, but it's only a few milliseconds and there's no specific throughput limitation. Write throughput is limited by network latency. There is also a relaxed consistency mode in pg_paxos, which avoids making round-trips on reads, but might give stale results in case of failure.

Paxos provides strong consistency and can proceed even if some nodes fail. 2PC has intermediate states in which a transaction is only partially committed, and all nodes need to be available to perform writes. The downside is that Paxos' write throughput is bounded by network latency and it requires network round-trips on both reads and writes. 2PC is more suitable when you require low read latency or high write throughput.

> Does this only work for append only unique data structures (ex: immutable log style)?

Paxos is based on a technique called state machine replication. It replicates an append-only log of changes to an initial state, which allows you to replicate arbitrary data structures. For example, pg_paxos logs SQL commands on a table (e.g. UPDATE).

Once a query has been logged there's no turning back. The problem with transactions is mainly that Paxos is fundamentally incompatible with read committed mode. It is technically possible to log a multi-statement transaction as a single string, which makes it serializable.

Yes, Citus may be a good fit, for a complete example see:

https://www.citusdata.com/blog/15-marco-slot/402-interactive...

(Marco from Citus Data)

> If I use CitusDB as an analytical database, then it's comparable with Redshift, Hive etc.

A particular difference is in response times and concurrency. Data warehouses and Hive are great for reporting use-cases, but not for use-cases that require fast responses and have many users like analytical dashboards. This is a use-case for which Citus is particularly well-suited (see for example the CloudFlare dashboard).

> Can I use cstore_fdw in CitusDB and able to take advantage of real-time nature of Postgresql?

Yes, since cstore_fdw and Citus are both developed by Citus Data we made sure they're fully integrated. We've even seen some deployments that use a mixture of columnar- and row-based storage in a single distributed table.

We find that row-based storage generally has better ingestion performance and more indexing possibilities. Citus can do very fast execution of analytical queries by parallelizing over row-based shards and using the indexes on each of them. However, if you only need a small number of columns and have analytical queries that are not very selective, you can use columnar storage just as easily and even mix and match (might require some support).

> I guess another use case is using CitusDB as distributed data store

Yep, Citus can definitely be used for that by using hash-partitioned tables.

(Marco from Citus Data)

It seems at least theoretically possible.

Since the Citus master executes distributed queries by sending regular SQL queries to the Citus workers, you could already use BDR servers as workers and replicate the data between pairs of workers in different data centers and copy over the metadata on the master manually. However, some distributed joins and data loading features wouldn't work.

For all features to work, and to replicate the master, you would have to compile Citus against BDR, which probably requires a few code changes.