drob

Heap CTO here. I’ll be around for a little while and am happy to answer any questions you have.

Heap CTO here – would love to hear about measures you've taken to make your APIs solid or answer any questions you have!

This is great. Can't wait to be using it.

We've been using WAL-E for years and this looks like a big improvement. The steady, high throughput is a big deal – our prod base backups take 36 hours to restore, so if the recovery speed improvements are as advertised, that's a big win. In the kind of situation in which we'd be using these, the difference between 9 hours and 36 hours is major.

Also, the quality of life improvements are great. Despite deploying WAL-E for years, we _still_ have problems with python, pip, dependencies, etc, so the switch to go is a welcome one. The backup_label issue has bitten us a half dozen times, and every time it's very scary for whoever is on-call. (The right thing to do is to rm a file in the database's main folder, so it's appropriately terrifying.) So switching to the new non-exclusive backups will also be great.

We're on 9.5 at the moment but will be upgrading to 10 after it comes out. Looking forward to testing this out. Awesome work!

Batch inserts usually increase throughput by reducing the number of write IOPS, fsyncs, etc. They usually aren't associated with a 10x _CPU_ savings, which is the finding here.

There are a million 'db best practices' you can go implement blindly, but the point is that this methodology – determining the bottlenecking resource and then profiling to determine exactly what is consuming it – will _reliably_ yield huge wins, whereas implementing 'best practices' on gut alone is a very inefficient way to improve performance.

There are a number of factors that make relocation harder and less common than it needs to be. One is the number of underwater mortgages – mortgages in which the remaining balance is greater than the value of the home – which make selling all but impossible.

Another is that the parts of the country with growing economies and lots of great jobs are generally cities with incredibly high rents. It's not easy to move halfway across the country to take an entry-level job and somehow live off that if you need to pay Bay Area / NY Area / DC Area / etc rents, and doubly so if you have a family.

This is one reason I think it's so critical to build a serious urbanist movement in US. The status quo of restrictive zoning, low building, and sky-high rents keeps hundreds of thousands of people from being able to access the places that would enable them to build better lives. If we allowed our cities to grow and invested in the transit infrastructure to keep them livable at even moderately higher densities, it could unleash a tremendous amount of prosperity.

The data is sharded by customer and then sub-sharded by end user within the customer. For all but the tiny customers, 100% of the data on a logical shard will belong to the same customer. That means our subqueries will never touch data from more than one customer unless the customer is very small. (And, if the customer is that small, it should be easy to make the query fast anyway.)

The expression index will make it fast to retrieve the rows for which that predicate is true, but it won't help the planner know that this will be the case for 50% of rows, so I don't think it will change the join that the planner selects (which is the problem here).

In fact, this might make the query slower. If postgres thinks it is selecting a very small number of rows, it will prefer an index scan of some kind, but a full table scan will be faster if it's retrieving 1/8th of the table (at least, for small rows like these). So, you might get a slower row retrieval and the same explosively slow join.

This isn't live yet, but we expect it to be across our citus cluster. The ~30% figure comes from the profiling we did on individual postgres nodes.

We have a bag of utils internally to paper over the missing JSONB functions. This was definitely a headache at first.

This is mostly fixed in 9.5: http://blog.2ndquadrant.com/jsonb-and-postgresql-9-5-with-ev...

Author here. Curious what experiences y'all have had with JSONB.

We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far.

Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan works particularly well.

We've hit a lot of the same fundamental limits scaling PostgreSQL at Heap. Ultimately, I think a lot of the cases cited here in which PostgreSQL is "slower" are actually cases in which it does the Right Thing to protect your data and MySQL takes a shortcut.

Our solution has been to build a distribution layer that makes our product performant at scale, rather than sacrificing data quality. We use CitusDB for the reads and an in-house system for the writes and distributed systems operations. We have never had a problem with data corruption in PostgreSQL, aside from one or two cases early on in which we made operational mistakes.

With proper tuning and some amount of durability-via-replication, we've been able to get great results, and that's supporting ad hoc analytical reads. (For example, you can blunt a lot of the WAL headaches listed here with asynchronous commit.)

We've used both at Heap. I wouldn't expect much of a performance change from switching in either direction. JSONB has the advantage of being in a data format that lots of tools speak, and has generally been less annoying to use. But if your app is pretty stable and all of your code already speaks hstore, probably not worth it.

I'm actually halfway through writing this post! I was hoping to finish it today. :)

Or if you need GiST indexing, can't wait for someone to write the bindings for JSONB, and have non-nested data, in which case hstore is probably the right choice.

> Mostly, a select group of people are getting richer, while everyone else stagnates.

Actually this isn't true. Middle classes in the developed world are doing poorly relative to the richest in the developed world, but global poverty is on a steep decline.

Throughout the developing world, economic development is pulling hundreds of millions of people out of poverty at breakneck speed. Check out some of the data here, for starters: http://ourworldindata.org/data/growth-and-distribution-of-pr...

Profiled this. Using first_value instead of the additional sub-select made this perform slower at a factor of about 2x for arrays of 1M events.

Interesting, and very surprising! I'm going to dig around and see if I can't figure out why this is the case.

I would have guessed there would be a minor performance improvement, because the first_value approach effectively allows PostgreSQL to discard the data sooner, instead of materializing it in an additional subquery.

Author here. I didn't know about first_value. Thanks for the tip!

I'll play with this later today and see if it performs any better. In any case, it makes the query easier to read, so we'll probably use this unless it somehow makes the function slower.

Thanks again!

I'm silly -- turns out I did mention this in the post. See footnote 3.

The multi-column index takes up 755 mb, vs 1026 mb for the set of three single-column indexes.

I wonder if this has something to do with differential support from different DBs. I don't think MySQL has an equivalent feature, though I think SQLite does.

Yep, looks like a minimum delay. Interesting!

Agreed in general. No one-size-fits-all answers, and schematizing your data well is going to require case-by-case attention and experimentation for the foreseeable future.

In this example, a relational schema would work fine, and might have been easier to read. We use HSTORE at heap (until JSONB ships!) because we're processing event blobs with thousands of different fields, of which most are irrelevant for most events.

The added flexibility benefit is also nice. The ability to add a new property without migrating a schema has saved a lot of work.

This is a scenario in which you do know the query set ahead of time, and there's still a considerable performance benefit to using partial indexes over any combination of multi-column indexes. The main insight is that the "high value" events are a tiny slice of the table -- about 0.05% of rows for each type of event.

Let's say you care about ten different types of "high value" events, which reference a total of six columns. I'll assume we can cover them with three multi-column indexes, although it wouldn't be hard to cook up a realistic scenario in which you'd need more. That means, for each INSERT/UPDATE, you need to write to three different indexes.

Given that the event definitions are selective, a single partial index requires a write for about 0.05% of INSERT/UPDATEs. Ten of these will cumulatively require one write on ~0.5% of inserts -- a 600x improvement over the conservative estimate above. That is, the cost of of maintaining the set of multi-column indexes should be much, much higher than the cost of maintaining the set of partial indexes.

As I mentioned in the article, the partial index approach also allows a more flexible set of predicates. What if you want to index for rows with a field that matches a fixed regex?

Edit: Apparently I can't respond to your response -- does HN have a chain length limit?

Yes -- for indexing the single event definition used for the profiling here, a multi-column index would absolutely be preferable to three single-column indexes. I didn't think to include the option because I assumed it wouldn't scale to a case in which you have ten different event types that use an overlapping but not identical set of fields. Definitely would have been a good idea to include a comment to this effect in the post.

Separate single-column indexes can be reused more readily. In this scenario, you might have a few different types of "high value" events, and you'd want your indexes to be applicable to more than one of them.

A multi-column index on (a, b, c) can't be used for a query that filters on b and c but not a. More detailed documentation here: http://www.postgresql.org/docs/9.3/static/indexes-multicolum...

Sure, but that itself is interesting: context around data systems is changing so fast that even the mature ones have to innovate pretty quickly to keep up. (Or maybe especially the mature ones?)

I wonder how long before postgres includes an index type optimized for in-memory workloads, e.g. a skiplist.

I did see that, although it's plausible that an analogous change would be helpful in core postgres. The readme seemed to suggest that they tried this on cstore_fdw because that was easier on which to develop, not because there was more low-hanging fruit.

I'm always surprised at how much room there is for big performance improvements, even in a system as mature as PostgreSQL. Evaluating an agg node is a pretty core piece of functionality, and there's still room for a pretty big win here.

PostgreSQL is powerful and feature-rich, and there's still so much work to be done. (Consider that index-only scans themselves are new as of 9.2!)

Very cool project.

Very nice! That's a thoroughly earned upvote.

Measure theory is super cool.

Really neat announcement. This opens up a bunch of interesting possibilities for applications that are cpu-bound once you're on SSD, but for which the delta between SSD-backed EBS and ephemeral storage doesn't change the bottleneck.

In particular, in that sort of scenario, you might be able to mix-and-match and get a compute-optimized instance with loads of cpu power and a healthy amount of sufficiently performant storage on EBS.

Does anyone know the specific rules or statutes that underpin this argument? What are the actual rules designating how much experience a pilot/team needs with a particular airline before they're allowed to fly?

It'd be interesting to dig into the history behind them. (Did pilots lobby for them, or did they make sense at some point for safety?)

It totally is. Learning!