5 ms·

Besides parallel queries (specifically parallel index scans), the Postgres 10 feature I'm most looking forward to is the work Andres Freund has done on using LL

Besides parallel queries (specifically parallel index scans), the Postgres 10 feature I'm most looking forward to is the work Andres Freund has done on using LLVM to JIT compile expressions[0]. Based on the the layout of the database I work on[1], an insane amount of CPU goes to processing of partial index predicates. If the improvements are anywhere near what is being described in the mailing list, we're going to start saving a ton of money on CPU. Parallel index scans will be an absolutely massive win for us. Currently Postgres does not perform any sort of prefetching. When performing an index scan, this results in serialized random I/O. In our case, queries wind up using ~1/10th the I/O they could be using if there were to do more I/O in parallel. We are expecting a dramatic speedup just from parallel index scans. This is in addition to the bugfix in 9.6 (we're currently on 9.5) which added proper support for index only scans over partial indexes. That bugfix should also result in a pretty large speedup for a lot of our queries. As soon as Postgres 10 comes out, we are immediately going to upgrade. There are just so many huge wins for us. [0] https://www.postgresql.org/message-id/20161206034955.bh33paeralxbtluv%40alap3.anarazel.de https://www.postgresql.org/message-id/20161206034955.bh33pae... [1] https://blog.heapanalytics.com/running-10-million-postgresql-indexes-in-production/ https://blog.heapanalytics.com/running-10-million-postgresql...

colanderman 10y ago
Don't forget native logical replication.
- malisper 10y ago
  Logical replication doesn't matter as much to us. We are using CitusDB (in other words sharded Postgres), and we have our own code for handling replication.
  - qeternity 10y ago
    Citus should already be saturating i/o during a scan unless you're only hitting one shard. What am I missing about your use case?
    - throwaway91111 10y ago
      Doesn't this inherently depend on the query? Shard-local i/o saturation affects the latency of the entire query if you're not expecting the i/o to the client to saturate.
      - qeternity 10y ago
        This was exactly my point. But usually you're doing a full scan across all shards, so disk i/o is being saturated by virtue of the MPP sharding.
    - anarazel 10y ago
      In many cases all your data is actually in memory, especially when you can distribute your workload across many machines. If all your data is in memory, you'll very often hit CPU bottlenecks before memory bandwidth issues. Partially because bandwidth, disregarding latency, is just quite high, partially because expression evaluation in postgres isn't that well optimized (yet).
    - malisper 10y ago
      We shard our data by customer and individual queries touch only data for one customer. We do split a customer's data into multiple shards, but the number of shards we would need to split each customer into is not worth the overhead of maintaining that many shards.
      - manigandham 10y ago
        Why? Is this a security issue? If you're sharing the same distributed table then sharding by customer is limiting the number of nodes/scans in parallel that can run.
        malisper 10y ago
        Having millions of shards is a bit of a pain ops wise. There is also some other costs to having lots of shards. For example, the current Citus distribution planner takes time linear in the number of total shards. This isn't an issue 99% of the time, but since we use a composite type as our distribution key, we have an extremely high constant overhead for planning time. I believe the current median Citus Master planning time is ~350ms. We've talked to Citus about this and have discussed possible solutions. Currently we are trying to purge the composite type from our database.
fnord123 10y ago
It should be interesting, but LLVM jit brings in a substantial up front cost. Maybe Andres found a way to mitigate it.
- jordanthoms 10y ago
  Is it significant when you are talking about analytical queries which are running for minutes or hours? Seems like it wouldn't be a huge amount of code to JIT compared to the payoff?
  - electrum 10y ago
    Presto, which is designed for analytics, does JIT to bytecode on every query and still works fine on sub-second queries. One potential difference with LLVM is that the JVM will profile and only generate machine code for hot code, so short queries might use JVM interpreted bytecode. But you are absolutely correct that for long running analytic queries, the up front cost of generating optimized code pays off.
- anarazel 10y ago
  The plan is to just use interpretation of expressions for short-running queries. I've spent a lot of work in this release cycle making that faster, and allowing for an easier integration w/ less duplication between JIT and interpreted evaluation. There's two approaches for deciding when to JIT: a) based on query cost estimation. b) just do it after a threshold number of executions. I've not yet decided / experimented enough to make a call. Additionally, at a later stage, caching of compiled programs will play a role, to reduce the frequency of llvm invocation.
anarazel 10y ago
Please share profiles on the list. Without known problematic production workloads, it's very hard for us to optimize appropriately. You mention predicate evaluation - it's possible this won't be affected by these changes (the index internals use lower level accesses than generic expression evaluation). Re prefetching: PG does that for bitmap index scans, if you enable effective_io_concurrency > 1.

colanderman 10y ago

Don't forget native logical replication.

malisper 10y ago
Logical replication doesn't matter as much to us. We are using CitusDB (in other words sharded Postgres), and we have our own code for handling replication.
- qeternity 10y ago
  Citus should already be saturating i/o during a scan unless you're only hitting one shard. What am I missing about your use case?
  - throwaway91111 10y ago
    Doesn't this inherently depend on the query? Shard-local i/o saturation affects the latency of the entire query if you're not expecting the i/o to the client to saturate.
    - qeternity 10y ago
      This was exactly my point. But usually you're doing a full scan across all shards, so disk i/o is being saturated by virtue of the MPP sharding.
  - anarazel 10y ago
    In many cases all your data is actually in memory, especially when you can distribute your workload across many machines. If all your data is in memory, you'll very often hit CPU bottlenecks before memory bandwidth issues. Partially because bandwidth, disregarding latency, is just quite high, partially because expression evaluation in postgres isn't that well optimized (yet).
  - malisper 10y ago
    We shard our data by customer and individual queries touch only data for one customer. We do split a customer's data into multiple shards, but the number of shards we would need to split each customer into is not worth the overhead of maintaining that many shards.
    - manigandham 10y ago
      Why? Is this a security issue? If you're sharing the same distributed table then sharding by customer is limiting the number of nodes/scans in parallel that can run.
      - malisper 10y ago
        Having millions of shards is a bit of a pain ops wise. There is also some other costs to having lots of shards. For example, the current Citus distribution planner takes time linear in the number of total shards. This isn't an issue 99% of the time, but since we use a composite type as our distribution key, we have an extremely high constant overhead for planning time. I believe the current median Citus Master planning time is ~350ms. We've talked to Citus about this and have discussed possible solutions. Currently we are trying to purge the composite type from our database.

fnord123 10y ago

It should be interesting, but LLVM jit brings in a substantial up front cost. Maybe Andres found a way to mitigate it.

jordanthoms 10y ago
Is it significant when you are talking about analytical queries which are running for minutes or hours? Seems like it wouldn't be a huge amount of code to JIT compared to the payoff?
- electrum 10y ago
  Presto, which is designed for analytics, does JIT to bytecode on every query and still works fine on sub-second queries. One potential difference with LLVM is that the JVM will profile and only generate machine code for hot code, so short queries might use JVM interpreted bytecode. But you are absolutely correct that for long running analytic queries, the up front cost of generating optimized code pays off.
anarazel 10y ago
The plan is to just use interpretation of expressions for short-running queries. I've spent a lot of work in this release cycle making that faster, and allowing for an easier integration w/ less duplication between JIT and interpreted evaluation. There's two approaches for deciding when to JIT: a) based on query cost estimation. b) just do it after a threshold number of executions. I've not yet decided / experimented enough to make a call. Additionally, at a later stage, caching of compiled programs will play a role, to reduce the frequency of llvm invocation.

anarazel 10y ago

Please share profiles on the list. Without known problematic production workloads, it's very hard for us to optimize appropriately. You mention predicate evaluation - it's possible this won't be affected by these changes (the index internals use lower level accesses than generic expression evaluation). Re prefetching: PG does that for bitmap index scans, if you enable effective_io_concurrency > 1.