brasetvik

That's a pretty reasonable approach, and similar in spirit to what I find to work well:

- Create a template with necessary extensions, or just install them to "template1" which is the template if you don't explicitly specify a template. Installing PostGIS, for example, takes a few seconds - which is annoying if you create the schema from scratch in test runs. (`create extension if not exists postgis` can still be around in your schema, it'll just return right away) - Create a template for the test session, based on the template you've pre-installed extensions in, and apply the schema there. - Create a database based on the second template for whatever scope makes sense for your test.

If your Postgres cluster is only serving test workloads, `fsync=off` can speed up things as well. (Which a stock postgresql.conf will point out can cause irrecoverable data loss, which I don't care about for test data)

E-trade: Your password can’t be >32 chars.

Their 2FA TOTP gets concatenated to the end of your password. Got a >26 char password? You’ll be locked out after enabling 2FA. No worries, though, you just call support, provide your email, and they disable 2FA for you..

Yep! A lot of older posts about CTEs largely advice against them for this reason.

Postgres 12 introduced controllable materialization behaviour: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

By default, it'll _not_ materialise unless it's recursive, or if there are >1 other CTEs consuming it.

When not materializing, filters may push through the CTEs.

Regarding "Match Against Multiple Patterns", the examples are about finding the _suffixes_ of something, email domains in the example.

An attempt to find a suffix like that will not be able to use an index, whereas creating a functional index on the reverse and looking for the reversed suffix as a prefix will be:

  # create table users (id int primary key, email text);
  CREATE TABLE
  # create unique index on users(lower(email));
  CREATE INDEX
  # set enable_seqscan to false;
  SET
  
  # insert into users values (1, 'foo@gmail.com'), (2, 'bar@gmail.com'), (3, 'foo@yahoo.com');
  INSERT 0 3
  # explain select * from users where email ~* '@(gmail.com|yahoo.com)$';
                                  QUERY PLAN
  --------------------------------------------------------------------------
   Seq Scan on users  (cost=10000000000.00..10000000025.88 rows=1 width=36)
     Filter: (email ~* '@(gmail.com|yahoo.com)$'::text)

  # create index on users(reverse(lower(email)) collate "C"); -- collate C explicitly to enable prefix lookups
  CREATE INDEX

  # explain select * from users where reverse(lower(email)) ~ '^(moc.liamg|moc.oohay)';
                                               QUERY PLAN
  ----------------------------------------------------------------------------------------------------
   Bitmap Heap Scan on users  (cost=4.21..13.71 rows=1 width=36)
     Filter: (reverse(lower(email)) ~ '^(moc.liamg|moc.oohay)'::text)
     ->  Bitmap Index Scan on users_reverse_idx  (cost=0.00..4.21 rows=6 width=0)
           Index Cond: ((reverse(lower(email)) >= 'm'::text) AND (reverse(lower(email)) < 'n'::text))

(Another approach could of course be to tokenize the email, but since it's about pattern matching in particular)

They're cool, but keep in mind that this often means you cannot run them on managed Postgres services.

Two things that aren't exactly lesser-known, but that I wish more used continuously as part of development:

- generate_series(): While not the best to make _realistic_ test data for proper load testing, at least it's easy to make a lot of data. If you don't have a few million rows in your tables when you're developing, you probably don't know how things behave, because a full table/seq scan will be fast anyway - and you'll not spot the missing indexes (on e.g. reverse foreign keys, I see missing often enough)

- `EXPLAIN` and `EXPLAIN ANALYZE`. Don't save minutes of looking at your query plans during development by spending hours fixing performance problems in production. EXPLAIN all the things.

A significant percentage of production issues I've seen (and caused) are easily mitigated by those two.

By learning how to read and understand execution plans and how and why they change over time, you'll learn a lot more about databases too.

(CTEs/WITH-expressions are life changing too)

Thanks for the background :)

Lock behaviour of migrations are indeed very important to be aware of.

Braintree (which are heavy Postgres users) have a pretty good post on that - https://medium.com/paypal-tech/postgresql-at-scale-database-...

Kudos for being open about your mistakes.

Could you share a little bit about what your thought process was in general when picking a database technology?

You call out "easy migrations and database branches" outweighing other quirks, so some pros and cons weighing must've happened :)

Is it easy, for example, to test things in your dev environment with realistic amounts of data, and to get an understanding of how the queries will execute, etc? These seem somewhat basic, and would've probably caught this (also kinda basic, sorry :) problem early. (As in discovering "why is this query that should be a few ms with an index lookup taking so long?" early on)

Happy to be unvisionary on this one, but the change log I wish for the next major release is this one:

- We changed nothing, but spent most our time on Bluetooth reliability, and remaining time on general reliability.

That’d meaningfully impact most working days, whereas most of the last few majors really haven’t.

This reminds me of a certain bar scene from Good Will Hunting.

"relevancy" is often scoped to the _textual_ relevancy of matching documents, where things like boosting matches in the title vs. the body and tf/idf scores of the involved terms matter a lot.

In practice, a lot of relevancy tuning is about properly factoring in other "signals", such as something's recency, distance, popularity, stock availability, the section of the website being searched from, etc.

Thus, there's often a lot more than just the textual relevancy being involved.

Postgres will never compromise on MVCC correctness, and therefore doesn't do any caching of partial results beyond having things in shared buffers and/or the page cache.

Elasticsearch will err on prioritising speed and cacheability. The "signals" that appear again and again in the same queries will likely be pretty compactly cached as (roaring) bitmaps in a filter cache. That enables potentially being a _lot_ faster, because it's ok with "you're seeing approximately everything that was ingested about a second a go".

In addition to that, Elasticsearch(/Lucene) comes with a lot more tooling for text processing, stemming, compound word splitting, etc.

I'm glad SSRF is getting more attention. It's incredibly potent, especially in cloud/k8s/micro services eras.

Some good examples: - From screenshot service to Kubernetes account takeover at Shopify: https://hackerone.com/reports/341876 - SSRF and the Capital One breach: https://blog.appsecco.com/an-ssrf-privileged-aws-keys-and-th...

Cognite | Oslo, Norway and Austin, US | Full time | On-site (we help relocate) or remote | https://www.cognite.com/en/careers

Cognite provides contextualised data as a service, such as digital twins, focusing on asset heavy industries to start with.

We're hiring for several positions. Most are on-site, but we're looking for a senior/principal DBRE with Elasticsearch, Kafka, or FoundationDB experience. The DBRE positions are offered remote in EU + US time zones, the rest are mostly in on-site Oslo, Norway. We help with relocation.

https://www.cognite.com/en/careers

> ksmbd is a new kernel module which implements the server side of the SMB3 protocol. […] The bigger goal is to add new features more rapidly […] which are easier to develop on a smaller, more tightly optimized kernel server than for example in Samba.

That seems like a quite significant (external) attack surface to add to the kernel? (With plans on adding more)

Cognite | Oslo, Norway and Austin, US | Full time | On-site (we help relocate) or remote | https://www.cognite.com/en/careers

Cognite provides contextualised data as a service, such as digital twins, focusing on asset heavy industries to start with.

We're hiring for several positions. Most are on-site, but we're looking for a senior/principal DBRE with Elasticsearch, Kafka, or FoundationDB experience. The DBRE positions are offered remote in EU + US time zones, the rest are mostly in on-site Oslo, Norway. We help with relocation.

https://www.cognite.com/en/careers

Their initial post blamed the problem on “human error”, an anti-pattern in getting to the root cause of things.

This post attempts to blame their CDN vendor.

“All these reviewers couldn’t spot it.”

Cognite | Oslo, Norway and Austin, US | Full time | On-site (we help relocate) or remote | https://www.cognite.com/en/careers Cognite provides contextualised data as a service, such as digital twins, focusing on asset heavy industries to start with. We're hiring for several positions. Most are on-site, but we're looking for a principal database reliability engineer (DBRE) with Kafka and Kubernetes chops, and a senior/principal DBRE with Elasticsearch, Kafka, or FoundationDB experience. The DBRE positions are offered remote in EU + US time zones, the rest are mostly in on-site Oslo, Norway. We help with relocation. https://www.cognite.com/en/careers

C^WSee also: http://www.underhanded-c.org/

I tried Paperspace and Blender would just crash the instance when rendering the splash screen demo files.

Would really like a service that works well for casual Blender usage. Vagon didn’t crash, but was too slow, even though the ping was in the low tens of ms.

Cognite | Oslo, Norway and Austin, US | On-site (we help relocate) or remote | https://www.cognite.com/en/careers

Cognite provides contextualised data as a service, such as digital twins, focusing on asset heavy industries to start with.

We're hiring for several positions. Most are on-site, but we're looking for a principal database reliability engineer (DBRE) with Kafka and Kubernetes chops, and a senior/principal DBRE with Elasticsearch, Kafka, or FoundationDB experience.

The DBRE positions are offered remote in EU + US time zones, the rest are mostly in on-site Oslo, Norway. We help with relocation.

https://www.cognite.com/en/careers

> That's a pretty shitty thing to do, in my opinion.

The founder and main developer has shared some of his thoughts in this matter here: https://marijnhaverbeke.nl/blog/sustainable-maintenance.html

He has put thousands of hours into these projects, and are giving them away for free – that is, if you can stomach the slight hint that if you're making money on it, then maybe contribute a tiny bit.

He hasn't blogged about burnout yet, like many other open source maintainers do. A recurring theme is about toxic community members. I hope he scrolls past your comment.

> I know how to read the source. You aren't as familiar with PostgreSQL as you think you are.

Oh, maybe you have read some of Laurenz Albe's many contributions to Postgres, then. https://git.postgresql.org/gitweb/?p=postgresql.git&a=search...

Apache 2 license explicitly does grant the right to sublicense. See §2.

> Amazon can easily fund developers, they just didn't because they didn't have to.

Looking at the various projects at https://github.com/opendistro-for-elasticsearch/, the most prominent ones seem to only have a handful of somewhat active developers, almost two years after launch.

If AWS hires a lot of people to work full time on their ASL2-licensed Elasticsearch and Kibana forks, then ... that's not necessarily _just_ bad for Elastic? I doubt Elastic execs thought AWS would just fall over and discontinue something that's probably billions in revenue at this point. If AWS actually starts contributing code beyond trivial bug fixes, code Elastic can legally fold into their own Elasticsearch (via the ASL2-licensed fork) if it's good enough, then ... good for both?

While it's great that AWS has indeed contributed fixes to upstream Elasticsearch, they link to 9 PRs that are generally on the trivial end of the scale. (Though I don't doubt the PR that adds a missing synchronized keyword might have been gnarly and time consuming to debug, and that diff size does not necessarily correlate to importance)

For a project AWS was making hundreds of millions in revenue on four years ago (as per an ex AWS employee), patting your own shoulder for such a trivial amount of contributions is a bit disingenuous. They might have contributed more, but if there was something significant, they probably would have mentioned.

Notable new features like "ultrawarm" they did not attempt to contribute upstream, nor open source at all: https://aws.amazon.com/about-aws/whats-new/2020/05/aws-annou...

> This change does not affect how you build or license plugins to Elasticsearch or Kibana. For the avoidance of doubt, building a plugin to be used in Elasticsearch or Kibana does not constitute a derivative work, and will not have any impact on how you license the source code of your plugin.

- https://www.elastic.co/pricing/faq/licensing#im-building-plu...

They already do. https://www.elastic.co/about/press/elastic-partners-with-ali...

Cognite | Oslo, Norway and Austin, US | On-site (we help relocate) or remote | https://www.cognite.com/en/careers

Cognite provides contextualised data as a service, such as digital twins, focusing on asset heavy industries to start with.

We're hiring for several positions. Most are on-site, but we're looking for a principal database reliability engineer (DBRE) with Kafka and Kubernetes chops, and a senior/principal DBRE with Elasticsearch, Kafka, or FoundationDB experience.

The DBRE positions are offered remote in EU + US time zones, the rest are mostly in on-site Oslo, Norway. We help with relocation.

https://www.cognite.com/en/careers

Cognite | Oslo, Norway and Austin, US | On-site (we help relocate) or remote | https://www.cognite.com/en/careers

Cognite provides contextualised data as a service, such as digital twins, focusing on asset heavy industries to start with.

We're hiring for several positions. Most are on-site, but we're looking for a principal database reliability engineer (DBRE) with Kafka and Kubernetes chops, and a senior/principal DBRE with Elasticsearch, Kafka, or FoundationDB experience.

The DBRE positions are offered remote in EU + US time zones, the rest are mostly in on-site Oslo, Norway. We help with relocation.

https://www.cognite.com/en/careers