5 ms·

Does anyone have thoughts on why Postgres shouldn't provide: - Automatic sharding of tables per-"shard key". - Automatic sharding of those keyed shards by the

by evdev 8y ago

Does anyone have thoughts on why Postgres shouldn't provide: - Automatic sharding of tables per-"shard key". - Automatic sharding of those keyed shards by the range of some primary index. Doesn't this get you 90%+ of the way there? (There's no "adaptive" time bucketing, I guess.) For the record, I am a veteran of a naive Postgres time series scheme that was brought to its knees by seek times.

deleted 8y ago
[deleted]
akulkarni 8y ago
I think what you are asking is why something like TimescaleDB has to exist in the first place; i.e., why doesn't PostgreSQL just naturally do this? Here's why: There are scenarios with time-series data that rarely occur with standard/vanilla PostgreSQL OLTP workloads. So PostgreSQL simply isn't designed to handle these scenarios well on its own. Having 100s-1,000s of partitions is one such example. We found that insert rate on standard PostgreSQL drops quickly as the number of partitions increases, because PostgreSQL decides to hold a lock on every partition on insert, even though the insert may only touch one partition. [1] When we asked the core PostgreSQL devs about this, they explained that they did this because sorting out the appropriate locks was a hard problem, and that they saw this scenario as so unlikely for OLTP that they instead directed their resources to other more pressing problems. But with time-series data this is a very common scenario, so we (TimescaleDB) had to sort it out ourselves. And this is just one example. There are quite a few query optimizations that we also had to develop for working with time-based data more efficiently. At a high-level, every project has to optimize for something. PostgreSQL understandably optimizes for OLTP workloads. But the beauty of PostgreSQL is that it allows extensions to optimize for other workloads, such as time-series. [1] https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5 https://blog.timescale.com/time-series-data-postgresql-10-vs...
- evdev 8y ago
  Thanks for your comment. I also am guessing that the wall between the worlds of "create table" and "insert" is pretty ingrained in the SQL developers, so solutions where inserts actually create database objects isn't something they're interested in. This is why the documentation on "DDL Partitioning" is a long tutorial on what's ultimately a static scheme. Compare this to Cassandra where creating a new "table" per routing key is the obvious thing. It does raise the question of whether "HyperTableDB" (for Postgres) would make its own coherent offering? (I don't mean to comment on your business strategy, this is an architectural/technical question about what hurdles there are.)
- mslot 8y ago
  > When we asked the core PostgreSQL devs about this, they explained that they did this because sorting out the appropriate locks was a hard problem, and that they saw this scenario as so unlikely for OLTP that they instead directed their resources to other more pressing problems. 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-1.11.6.5.5 https://www.postgresql.org/docs/11/static/release-11.html#id...
  - cevian 8y ago
    I believe what akulkarni meant to talk about is relation accesses (and not just locks). While the partition pruning certainly improved things, two sources of inefficiency still remain in PG 11: 1) Fetching statistics for each table during queries (which happens by reading from the data file off of disk). This happens /before/ pruning, even on PG 11. 2) The overhead of locking each table is still there. Although it's a smaller issue than (1). We at TimescaleDB found (1) to be the most significant overhead and in fact we have significantly improved things there [1]. [1]https://github.com/timescale/timescaledb/commit/b7257fc8f483475382019cadcc7a75fae0b72f0a https://github.com/timescale/timescaledb/commit/b7257fc8f483...
    - anarazel 8y ago
      You could also just have worked on lowering those overheads in PG, just saying. It's easy to blame "PG devs", but most of us could get changes quicker to our company's respective customers by just fixing everything in forks.
      - enordstr 8y ago
        Timescaler here. We're not blaming "PG devs". We have great respect for the PostgreSQL developers and what they are doing; so much, in fact, that we chose to base our product on PostgreSQL. And, TimescaleDB is not a fork--it is an extension to PostgreSQL that can be loaded in existing PostgreSQL installations. We would be happy to contribute to PostgreSQL, but I think the issue here is that, as a business that is focusing on a very particular use case, we are not perfectly aligned with the PostgreSQL roadmap. We want to be able to move quickly and adapt to customers needs, focusing on the pain points and issues they have. This simply isn't compatible with the more conservative development pace that main PostgreSQL understandably has. From another perspective, I think one strength of PostgreSQL is, in fact, its support of extensions, enabling innovation alongside main PostgreSQL while the core developers can focus on a rock solid and extensible foundation. So, from where I am coming from, this is a feature and not a bug.
manigandham 8y ago
Postgres should provide it, it just doesn't (yet). Usable native partitions didn't really arrive until v10 and v11 finally makes them production capable with range+hash, default partitions, moving rows when the partition key changes, pruning partitions in queries, etc. Until then, extensions like Partman, Timescale, and Citus make up the functionality along with specializations for your use-case.
snaky 8y ago
> PostgreSQL 10 provides native partitioning. Despite the flexibility, this approach forces the planner to perform an exhaustive search and to check constraints on each partition to determine whether it should be present in the plan or not. Large amount of partitions may result in significant planning overhead. > The pg_pathman module features partition managing functions and optimized planning mechanism which utilizes knowledge of the partitions' structure. https://postgrespro.com/products/extensions/pg_pathman https://postgrespro.com/products/extensions/pg_pathman

by evdev 8y ago

deleted 8y ago

[deleted]

akulkarni 8y ago

I think what you are asking is why something like TimescaleDB has to exist in the first place; i.e., why doesn't PostgreSQL just naturally do this? Here's why: There are scenarios with time-series data that rarely occur with standard/vanilla PostgreSQL OLTP workloads. So PostgreSQL simply isn't designed to handle these scenarios well on its own. Having 100s-1,000s of partitions is one such example. We found that insert rate on standard PostgreSQL drops quickly as the number of partitions increases, because PostgreSQL decides to hold a lock on every partition on insert, even though the insert may only touch one partition. [1] When we asked the core PostgreSQL devs about this, they explained that they did this because sorting out the appropriate locks was a hard problem, and that they saw this scenario as so unlikely for OLTP that they instead directed their resources to other more pressing problems. But with time-series data this is a very common scenario, so we (TimescaleDB) had to sort it out ourselves. And this is just one example. There are quite a few query optimizations that we also had to develop for working with time-based data more efficiently. At a high-level, every project has to optimize for something. PostgreSQL understandably optimizes for OLTP workloads. But the beauty of PostgreSQL is that it allows extensions to optimize for other workloads, such as time-series. [1] https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5 https://blog.timescale.com/time-series-data-postgresql-10-vs...

evdev 8y ago
Thanks for your comment. I also am guessing that the wall between the worlds of "create table" and "insert" is pretty ingrained in the SQL developers, so solutions where inserts actually create database objects isn't something they're interested in. This is why the documentation on "DDL Partitioning" is a long tutorial on what's ultimately a static scheme. Compare this to Cassandra where creating a new "table" per routing key is the obvious thing. It does raise the question of whether "HyperTableDB" (for Postgres) would make its own coherent offering? (I don't mean to comment on your business strategy, this is an architectural/technical question about what hurdles there are.)
mslot 8y ago
> When we asked the core PostgreSQL devs about this, they explained that they did this because sorting out the appropriate locks was a hard problem, and that they saw this scenario as so unlikely for OLTP that they instead directed their resources to other more pressing problems. 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-1.11.6.5.5 https://www.postgresql.org/docs/11/static/release-11.html#id...
- cevian 8y ago
  I believe what akulkarni meant to talk about is relation accesses (and not just locks). While the partition pruning certainly improved things, two sources of inefficiency still remain in PG 11: 1) Fetching statistics for each table during queries (which happens by reading from the data file off of disk). This happens /before/ pruning, even on PG 11. 2) The overhead of locking each table is still there. Although it's a smaller issue than (1). We at TimescaleDB found (1) to be the most significant overhead and in fact we have significantly improved things there [1]. [1]https://github.com/timescale/timescaledb/commit/b7257fc8f483475382019cadcc7a75fae0b72f0a https://github.com/timescale/timescaledb/commit/b7257fc8f483...
  - anarazel 8y ago
    You could also just have worked on lowering those overheads in PG, just saying. It's easy to blame "PG devs", but most of us could get changes quicker to our company's respective customers by just fixing everything in forks.
    - enordstr 8y ago
      Timescaler here. We're not blaming "PG devs". We have great respect for the PostgreSQL developers and what they are doing; so much, in fact, that we chose to base our product on PostgreSQL. And, TimescaleDB is not a fork--it is an extension to PostgreSQL that can be loaded in existing PostgreSQL installations. We would be happy to contribute to PostgreSQL, but I think the issue here is that, as a business that is focusing on a very particular use case, we are not perfectly aligned with the PostgreSQL roadmap. We want to be able to move quickly and adapt to customers needs, focusing on the pain points and issues they have. This simply isn't compatible with the more conservative development pace that main PostgreSQL understandably has. From another perspective, I think one strength of PostgreSQL is, in fact, its support of extensions, enabling innovation alongside main PostgreSQL while the core developers can focus on a rock solid and extensible foundation. So, from where I am coming from, this is a feature and not a bug.

manigandham 8y ago

Postgres should provide it, it just doesn't (yet). Usable native partitions didn't really arrive until v10 and v11 finally makes them production capable with range+hash, default partitions, moving rows when the partition key changes, pruning partitions in queries, etc. Until then, extensions like Partman, Timescale, and Citus make up the functionality along with specializations for your use-case.

snaky 8y ago

> PostgreSQL 10 provides native partitioning. Despite the flexibility, this approach forces the planner to perform an exhaustive search and to check constraints on each partition to determine whether it should be present in the plan or not. Large amount of partitions may result in significant planning overhead. > The pg_pathman module features partition managing functions and optimized planning mechanism which utilizes knowledge of the partitions' structure. https://postgrespro.com/products/extensions/pg_pathman https://postgrespro.com/products/extensions/pg_pathman