646 karma · joined May 14, 2012
You can use Citus with pg_partman for time series data: http://docs.citusdata.com/en/latest/use_cases/timeseries.htm...
pg_partman does not have all the features of TimescaleDB, but it uses the built-in partitioning primitives in PostgreSQL.
On its own, even worst case sea level rise is unlikely to have meaningful impact on the Netherlands over the next century, unless we stop maintaining our water infrastructure.
The real problem is droughts, which could simultaneously pulverize dikes, cause ground to sink, cut off our water supply, and ruin the farming industry. When it comes to climate change in the Netherlands, one bad summer is a lot more dangerous than a century of sea level rise.
In Citus (a sharding extension for Postgres by Microsoft) we recently introduced a stored procedure call delegation feature.
If your tables are distributed by person_id and you have a stored procedure that takes person_id as a parameter, you can run e.g. "SELECT create_distributed_function('lineitem_add', 'person_id')". The procedure calls for a given person_id value will then be fully delegated to the PostgreSQL server that stores the data for that person_id without additional round-trips between statements or distributed planning overhead. It'll "magically" take care of procedures that access data from other servers as well.
I realize we're basically going full circle to PL/Proxy, except in Citus almost everything else (distributed queries, DML, DDL, transactions) works as well.
At some point people realized multi-server systems within one AZ are prone to failure. They then started deploying their system redundantly to multiple AZs within the same region to increase availability. This helped, but created consistency issues. To fix this people started building multi-AZ software systems, creating dependencies across AZs that weakened overall availability.
At some point people realized multi-AZ systems within one region are prone to failure. They then started deploying their system redundantly to multiple regions of the same cloud platform to increase availability. This helped, but created consistency issues. To fix this people started building multi-region software systems, creating dependencies across regions that weakened overall availability.
At some point people realized multi-region systems within one cloud platform are prone to failure. They then started deploying their system redundantly to multiple cloud platforms to increase availability. This helped, but created consistency issues. To fix this people started building multi-cloud software systems, creating dependencies across cloud platforms that weakened overall availability.
There's a question of what you actually want to do with the time-series data. If you don't expect to have much data or just want to store it and maybe occassionally query it, then a single server with partitioning (e.g. through pg_partman, Timescale) might be enough. If you want to build an analytical dashboard that needs to remain fast even if you're dealing with many users and terabytes of data per day, then you probably need Citus.
Citus can load, aggregate and query the data in parallel using all the cores in the cluster. It also supports Postgres' native partitioning and pg_partman: https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partm...
The locking on partitioned tables is a little clunky, but the overhead of these locks is very low. The main performance problem in Postgres 10 was the partitioning pruning, which used an exhaustive linear search. That has been fixed in Postgres 11 (due in September) which uses binary search and introduces various other partitioning improvements [1].
[1] https://www.postgresql.org/docs/11/static/release-11.html#id...
Spanner uses Paxos (consensus) for replication within a key range (shard), but two-phase commit across shards: https://ai.google/research/pubs/pub39966
Citus relies on PostgreSQL's streaming replication, which gives higher throughput than Paxos, but Paxos has better availability characteristics. On the other hand, Paxos with leader leases as used by Spanner is similar to streaming replication both in terms of performance characteristics and short downtime during failover.
There are also some potential availability benefits to storing data in consensus groups, though most implementations use leader leases to improve performance, which reduces availability in case of leader failure. A master-slave system that does synchronous replication could do failovers that are just as fast as leader changes in a consensus protocol.
The pain of storing data in consensus groups is in performance, especially concurrency. Every write to the log requires some synchronization with a quorum to ensure consensus on the order. That introduces extra round-trips and makes it hard to handle high concurrency.
There are quite a few bleeding edge distributed databases that have trouble matching the throughput of a single node RDBMS unless you're willing to spend an order of magnitude more on hardware or your workload follows a very specific pattern.
Postgres 10 partitioning does have a few limitations and inefficiencies that will be resolved in Postgres 11. Partitioning is the most actively developed area of postgres.
Note that Citus shards across multiple nodes, and can then partition on disk using native partitioning, which is automated by pg_partman. TimescaleDB so far only works on a single node.
Citus can also run parallel, distributed SQL queries, perform distributed transactions, and build rollups tables in parallel, and is used in Postgres clusters with up to a petabyte of data.
High contention means that there are many concurrent transactions modifying the same rows. Deadlocking means transactions modify the same rows in opposite orders. The former definitely does not imply the latter. Most workloads are simple enough to never have any deadlocks.
> which is exactly what (many) avoidance systems do for you in the first place.
This might work for very simple database models which require the user to specify all the keys that are going to be modified in the transaction upfront (NoSQL). In PostgreSQL, a transaction can contain any number of statements in any order, results are returned at the end of each statement - before the next statement is known, and modifications can have complex, non-deterministic WHERE clauses.
The database has no way of knowing which rows will be affected by an UPDATE and DELETE in advance, and therefore it does not which row locks it needs to take in advance. Even if it first read and sort all the data, it still doesn't know what the next statement is going to be. The only way in which the database could avoid deadlocks is by taking very aggressive predicate locks on the table, which is obviously bad for concurrency. Another possibility is to use wound-wait, but this introduces retry delays when there is contention even when there are no deadlocks. Moreover, because results (e.g. number of rows modified) are returned after each statement, a transaction cannot restart from the beginning (since it might modify different rows the second time), so in practice wound-wait would have to unnecessarily abort transactions if you were using it to avoid deadlocks between multi-statement transactions.
It is generally much, much, much more efficient to just let UPDATEs run without predicate locks or retry delays. They will only get into an occasional deadlock if the user has transaction blocks that make changes in a different order. In those cases you can have deadlock detection save the day, and tell the user how to avoid running into it again.
> What's the advantage of putting this burden on the user?
If a transaction has multiple statements that modify the same rows in different order then the two choices are: reduce concurrency, or, detect deadlocks as they occur. The former is a severe pain that all transactions have to pay, the latter is an occasional, preventable pain that only deadlocking transactions pay. Clearly, the former would be the higher burden.
Where deadlock prevention becomes useful/necessary is single UPDATE/DELETE statements that span across multiple nodes. When those are executed in parallel they could deadlock against each other due to non-deterministic execution order.
Citus currently uses predicate locks to avoid these deadlocks, but there's probably some room for improvement there. On the other hand, for Citus use cases UPDATE and DELETE across shards are mainly batch operations (e.g., delete old data), so there's not a strong need for it yet.
I think you might have them backwards. Deadlock avoidance becomes costly at high contention workloads (due to aggressive locks, retry loops). Deadlock detection only becomes costly at deadlocking workloads.
The only cost to the application is a very occasional deadlock error and a trace in the log on how the error occurred. You could then use this information to change the order in which you perform your transaction (e.g. always perform UPDATEs in account ID order), to make sure that the deadlock does not occur again.
Other than that, the CPU/network cost of deadlock detection is negligible, and it allows every transaction to run with as much concurrency as the system can attain.
A commit doesn't become visible until it is synchronously replicated, regardless of whether its ack fails or succeeds. So in the case you're describing the commit is never acked and never observed.
> there's nothing preventing the old master from still accepting writes; so you need to tell all the clients to failover at the same time
In Citus Cloud we detach the ENI to make sure no more writes are going to the old primary, and the attach it to the new primary.
Without such infrastructure, an alternative is to have a shutdown timer for primaries that are on the losing side of a network partition. The system can recover after the timeout.
If you're using a single coordinator, then this only applies to the coordinator. The workers can just fail over by updating the coordinator metadata.
> how does one choose what slave to failover to when a failover is needed?
You can pick the one with the highest LSN among a quorum, since it's guaranteed to have all acknowledged writes.
The IBM deal meant that DOS was going to be the Operating System for IBM's Personal Computer and became the de facto OS for all IBM PC clones. That was the deal that made Microsoft and shaped the tech industry for a generation. Indeed they won it.
Because clients talk to Citus using the synchronous postgres protocol, latency is one of the most important factors in performance. A single connection can never do more than 1/latency transactions per second. While you could use more connections, that is often complex, not always possible (e.g. long transaction blocks), and also comes with a significant performance penalty (fewer prepared statements, high SSL overhead, ...). It's important to optimise for latency.
We find the Citus transaction model is sufficient for most purposes. If you think of the typical balance transfer example, then there is no way to double spend or overspend. Distributed transactions are atomic and locks in postgres will serialise distributed transactions that update the same rows and ensure constraints are preserved. Importantly, Citus has distributed deadlock detection, which allows generous concurrency at almost no cost to the application (apart from maybe having to reorder transaction blocks).
Postgres supports different modes of replication, including synchronous replication to avoid losing any data on node failure. Many users actually prefer asynchronous replication, to avoid the extra round-trip and the broader performance implications that would have. It's also the reason we use streaming replication over other replication mechanisms that might give higher availability. Streaming replication supports higher concurrency and thus gives better throughput.
At the end of the day, people use Citus because a single node postgres (or MySQL) database is not performant enough to keep up with their workload. Performance is key. Citus is not a silver bullet for every postgres performance problem, but it has two particular strengths:
Minimal pain scaling out for multi-tenant applications that can shard by tenant.
- Any SQL query is supported when filtering by partition column, including CTEs, window functions, complex joins, etc,
- N times more CPU, memory and I/O bandwidth to process queries, and 1/N as much data to read
- High throughput, low-latency transactions, including UPDATE/DELETE with subqueries
- Citus Warp can do live migration from postgres (e.g. RDS) using logical decoding
- Major ORMs are supported after small data model changes
- Bulk loading, up to millions of rows/sec
- Massively parallel SQL with subqueries containing outer joins, lateral joins, etc. across all shards when joining by partition column
- Massively parallel roll-ups (again with joins) using INSERT...SELECT
- Massively parallel DELETE and UPDATE
- Integration with other Postgres extensions, such as HLL, PostGIS, pg_partman
- Advanced index types, including GIN, GiST, partial indexes
- Table partitioning (for bulk delete, faster scans)
Another caveat is that all secondaries will end up having more or less the same stuff in their cache. As your data set grows bigger that becomes unsustainable, because you're going to read from disk more and more.
When you shard across N nodes you can keep N times as much data in the cache. Combined with N times higher I/O and compute capacity, that can actually give you way more than N times higher read throughput than a single node (for data sets that don't fit in memory), and you can get much higher write throughput as well.