anarazel

With the associated context switch it's bigger, if anything. Busy looping to wait if some memory changes isn't really viable for many scenarios.

And the dispatch overhead will be a few orders of magnitude higher.

You can see this stuff often even in test workloads. But even if you disregard that kind of issue, you still have stuff like needing to integrate networked database connections into e.g. event loops, which you don't really need to do for things like sqlite.

I'd say that in-memory/not-in-memory isn't the big difference - it's whether your database is in-process or not. Even with just a database running on the same node, but in a different process, connected to via unix socket, the context switches alone lead to very different performance characteristics. Actually going over the network obviously changes more. It's very easy to miss antipatterns like N+1 queries when you test on sqlite but run on a shared database in prod.

Hm? I live in SF and it's considerably below that on average? https://www.bls.gov/regions/west/news-release/averageenergyp... says the area averages ~0.35$

If some annotations or such would make that easier, it might be possible to do that upstream. Postgres currently has hand-generated code for tab-completion in psql and that's uh, not great (it has gotten large enough that msvc has trouble compiling the file).

Have you investigated generating your parser from postgres' gram.y instead of basically basing it on the bison output?

When will you contribute?

https://sqlite.org/draft/jsonb.html references postgres:

> The "JSONB" name is inspired by PostgreSQL, but the on-disk format for SQLite's JSONB is not the same as PostgreSQL's. The two formats have the same name, but they have wildly different internal representations and are not in any way binary compatible.

My main issue with them is not their content proper, but that their SEO ranks them above much more topical results. That's clearly also Google's fault, but it's also clearly w3school's strategy.

I've seen lost completed FUA writes on enterprise drives quite a few times.

Getting a driver's license in Germany costs well over an order of magnitude more than in the US and takes quite a few weeks. And speed limits are much more enforced in the Germany compared to the US.

> My gut feeling tells me that the chances of having an upstream library that contains the parser/parse analysis/planner are slim.

Yea. The parser alone would be doable and not even that hard. But once you get to parse analysis and planning, you need to access the catalogs (for parse analysis to look up object names and do permission checks, for planning to access operator definitions, statistics etc). Which in turn needs a lot of the catalog / relation cache infrastructure. By that point you've pulled in a lot of postgres.

Of course you could try to introduce a "data provider" layer between parse analysis and catalogs, but that'd be a lot of work. And it'd be quite hard to get right in places - e.g. doing name lookups without acquiring heavyweight locks on objects, before having done permission checks, in a concurrency safe way, relies on a bunch of subsystems working together.

> The applied heuristic in this case apparently being "join tables by order of lower row count".

Not really - it's all cost based analysis. There are a few main culprits that might be at work:

1) Selectivity estimates can get very off if you have cross-column correlations or your joins are over multiple aggregations / DISTINCT or such.

2) Join order planning is exponential in the number of joins. Once there are more than geqo_threshold (default = 12) joins, we don't explore the whole space anymore.

3) If you have more than join_collapse_limit / from_collapse_limit joins, subqueries in the from list, we'll not flatten them anymore, to prevent the join ordering problem to become even harder. It might be worth trying to just increase them with your query.

I don't think <stacktrace> is async signal safe, and thus shouldn't be used for in-process crash reporting.

For GPUs it only really works because the code is translated to the relevant instruction steam close to the time of executing, where you can afford to optimize in a highly uarch specific way. Whereas VLIW at the time of itanium never was in that position... It just doesn't compute for me how Intel thought this was a good plan. It's not like they didn't know that existing compiled binaries are going to continue being used on newer uarchs

My experience with folks proudly proclaiming "100% coverage" is that it's typically "just" line coverage, even if you're obviously right.

When writing in loosely typed languages, some folks aim for 100% line coverage, to make it more feasible to refactor down the line. Which then of course ends up being a lot of tests for fairly basic stuff that the type system could help you with...

Yea. I've long since given up on that ever being correct. Too many people with too many mail clients. Thus for many mime types I just force that to be redone:

  mime_lookup application/octet-stream application/x-gzip application/gzip application/x-bzip2 binary/octet-stream application/x-download invalid/octet-stream

Not that that's a good answer...

> > I find lkml style patch handling bad as well, particularly with every patchset revision getting its own thread. Very easy to loose track.

> This is a good point, but if it works for the largest open-source software project in the world, I think it could work for Postgres too. I find that being able to reply inline to an email without having to copy-paste huge sections of an attachment is pretty valuable.

I had actually forgotten that issue, having scripted it years and years ago to be automatic. My beef around this is gmail attachment being randomly ordered...

> - It is hard to get patches looked at, even small ones. With better name recognition in the community, comes more patch reviews it seems, which is most likely the case in most projects, but still, it is a circular issue.

I agree that this is a significant issue. I'm less sure about the "better name recognition" bit, I feel there's also a significant drop off at the other end. But that might just be biased by my level of experience.

> - The organization of the Postgres mailing list is not very good. You are forced to drink from the firehose that is pgsql-hackers, whereas the LKML is organized into various subsystems. Modern code forges have value in that you can subscribe to certain tags on PRs/issues, which isn't the case with the current state of pgsql-hackers.

Yep. And it has gotten a lot worse in the last couple years, I'd say.

> - Adding things to a commitfest is a little burdensome. The only way to get a patch through the entire Postgres CI is adding it to the commitfest, and even then, you have to be proactive about checking it, or hope that a committer will tell you to look at the CI failure.

My main reason to reply here was this: You can also enable CI in your repository: https://github.com/postgres/postgres/blob/master/src/tools/c... - that's the same CI that happens for commitfest entries.

> - Bug reports are also sent to a mailing list (pgsql-bugs). There is no equivalent to the Linux bugzilla for Postgres for instance.

Yep, I hate this. I loose track of things all the time, I'm way too easily distractable. I think the kernel bugzilla is pretty useless, but it's not that hard to do better than that.

> - Patches are sent as attachments to emails, and not necessarily git-format-patched either, whereas the LKML uses git-send-email exclusively from what I can tell.

I find lkml style patch handling bad as well, particularly with every patchset revision getting its own thread. Very easy to loose track.

> All in all, it kind of seems like tooling in the Postgres contributor community works best for those that have been ingrained in it for 15+ years, which I guess is the case for most things.

I personally wouldn't say it works particularly well, even after participating in development for about 15 years... I'd also say that the development process has evolved some in that time, just not as far as it'd be good. It's a lot of hard work to get a community as grey-beardy as the PG community to evolve. Not impossible, but ...

> I don't want this to turn into a "Use GitHub/GitLab" post.

Personally I strongly dislike using either for nontrivial work. But: I still think we ought to accept PRs/MRs via one of the two, just to make it easier for newer contributors. But it isn't just my call...

> Let it be known that I actually think email is the superior way to communicate about patches, but the tooling around the mailing list could improve.

I suspect you'd actually have a hard time finding more than 2-3 people disagreeing with that notion. One of the problems is that many of us end up preferring to spend time hacking on postgres than on development-process tooling / integration....

> How much bigger (in lines of code) is Postgres now versus the one from 15 years ago?

I was curious as well and wrote a, very crude, script to measure it:

  for t in $(git tag -l|grep -E 'REL.*_0$|REL[67]_[0-4]$'|grep -v REL2);do echo -ne "$t\t"; git ls-tree -r $t --object-only |xargs git show |grep -a -v '^\s+$'|wc -l;done
  REL6_1          270033
  REL6_2          320297
  REL6_3          386532
  REL7_0          630771
  REL7_1          843219
  REL7_2          986991
  REL7_3          1363668
  REL7_4          1492418
  REL8_0_0        1649775
  REL8_1_0        1702325
  REL8_2_0        1806170
  REL8_3_0        2017685
  REL8_4_0        1924918
  REL9_0_0        2011704
  REL9_1_0        2225796
  REL9_2_0        2290872
  REL9_3_0        2405598
  REL9_4_0        2487304
  REL9_5_0        2527906
  REL9_6_0        2632559
  REL_10_0        2534653
  REL_11_0        2771914
  REL_12_0        2697892
  REL_13_0        2822066
  REL_14_0        2980221
  REL_15_0        3054963
  REL_16_0        3351147

This is counting non-empty lines. It's definitely not a good measure of overall code size, as it includes things like regression tests "expected" files. But as that's true for all versions, it should still allow for a decent comparison.

8.3.0 was released 2008-02-01, with 2M non-empty lines, we're now at 3.4M.

(postgres hacker)

Yes, I think that will be the case. Obviously not a scientifically measured, by I think we're already seeing that average C skills for new contributors are lower than what they used to be - of course that might just be my slowly greying beard speaking. So far I think people just "learn on the job", but how much delta that bridge I am not sure.

I think eventually we'll have to make it easier to use some other language in parts of the system (e.g. in-core data type implementations). But realistically I think that's still a bit off.

Isn't the loss for dimensional lumber more due to drying than finishing?

From very dim memory: I think there's some flag you can set that marks your test network as not suitable for that kind of thing. But even if not, IIRC you can set routing information for stuff like 911 on a subscriber basis in your test network - which you'd just do for the handset you're testing.

Can't you stand up your own test network for that? Other phones won't connect to that, so you don't need to fully rf isolate, as long as you have some trust in the test network not doing crazy things. It's well over a decade ago, in Europe, and I was only very peripherally involved, but it didn't seem that hard to get permits for such a test network indoors.

> postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts (although presumably this could be changed?).

Not in any sort of general way - the sequence can be part of the unique key that you're conflicting on.

> Postgres is VERY committed to making sure a replication slot’s consumer doesn’t miss any data. This means that if a consumer stops consuming data from the slot, Postgres will helpfully store all that missed data… right up until the disk fills up and the database falls over.

You can configure a size limit after which the slot gets marked as invalid, instead of continuing to retain space. See https://www.postgresql.org/docs/current/runtime-config-repli...

What other behaviour would you like?

> The other reason I don’t use it: the code path to get the initial snapshot of a table is totally different from the code path to read changes.

Hm. For anything dealing with larger data volumes you IME want to handle those things differently (so you can initialize in parallel, initialize from physical backups and similar things). But I can see why it could be useful to optionally stream out existing data out data after slot creation.

> Initializing the read from the replication slot so you never miss any changes is nontrivial.

That part shouldn't be hard - what gave you difficulty?

> Did the switch? Does postgres use a VM now?

Yes (to me my comment seemed to make that clear, but...).

Didn't really remember how long ago that was, so I dug up the commit. Main commit went in ~6.5 years ago. There were lots of related changes but the main commit is https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

It really depends on the query. If there's a bunch of math in the select list, it's easy for that to be the biggest bottleneck, even if there are joins etc. Also you'll sometimes have to evaluate expressions to check join conditions depending on the join implementation and condition.

> I do think that a VM could mess up CPU branch prediction so I can see that as being a problem

It definitely happens. It can be partially addressed with things like computed gotos, but it's far from perfect. At least for postgres, it's just that the tree walk overhead was far higher.

The branch prediction handling for opcode dispatch loops definitely improved in CPUs - for Intel somewhere around Haswell (it's been a while, so this is a guess). I remember that, at the time, the gains of using computed goto on my aging workstation (nehalem) were far bigger than a few uarchs later, even when controlling for cache size etc.

Semi related anecdote: Both opcode dispatch switches and computed goto based dispatch are good for stressing compilers. I found newly introduced >= quadratic behavior in all the major c compilers in the years since.