1,425 karma · joined December 2, 2015
Paratrooper to programmer.
http://github.com/jschaf
hn@schafer.dev
I tried using README files in the repo but there’s far too much friction to get most folks to bother. Google Docs tend to disappear content due to a lack of structure.
Why PostgreSQL doesn't have query hints
https://news.ycombinator.com/item?id=2179433 (60 comments, 2011)
The official stance from the Postgres wiki https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion:
> We are not interested in implementing hints in the exact ways they are commonly implemented on other databases.
> Problems with existing Hint systems: Poor application code maintainability, Interference with upgrades, Encouraging bad DBA habits, Does not scale with data size
I don't fault their stance, but it's frustrating when Postgres picks a stupid plan and can't be convinced to do something reasonable.
“Luckily”, the problem was unrelated to my querying but two coincidences are proper scary.
See “Virtual machine warmup blows hot and cold”. The authors run a variety of benchmarks (with a pristine methodology) and find 43% of them provide “bad inconsistent” results.
Paper summary: https://blog.acolyer.org/2017/11/07/virtual-machine-warmup-b...
> u8 divmod 58 can be reduced to a u8->u16 multiply, a right shift, and three conditional subtractions; that's not great, but on a modern CPU it's a afterthought compared to the quadratic loop over the input size.
Same topic from 2018: https://news.ycombinator.com/item?id=18409344
Same downsides as runtime dependency injection frameworks like Guice—the framework would explode if it couldn’t connect the graph.
Some other upsides: you could decorate methods with all sorts of distributed goodness like retries and hedging. The approach also enable reusable methods that you snap into your server.
- Use FOR NO KEY UPDATE instead of FOR UPDATE so you don't block inserts into tables with a foreign key relationship with the job table. [1]
- We parallelize worker by tenant_id but process a single tenant sequentially. I didn't see anything in the docs about that use case; might be worth some design time.
[1]: https://www.migops.com/blog/select-for-update-and-its-behavi...
From Oxford Languages:
> the practice of claiming to have moral standards or beliefs to which one's own behavior does not conform
The transition is a classic example of a disruptive innovation—-initial tech is worse along every dimension (range, damage, reliability, firing rate) except one, training time.
Some of the comparisons are wildly biased. The effective range of an M4 is 550m, much better than the M9’s 50m. A standard rifleman’s combat load is 180 rounds which is surely lighter than 180 arrows.
Changing columns must be done to both tables in a transaction. Unit tests verify that the table columns match.
The core ideas of the implementation:
- The current table is a typical Postgres table. The past table's primary key is (curr_tbl.pk, asr), where the asr column is the valid period of the row using a domain on tstzrange.
- Create statement-based triggers on the current table to copy the after-image of modified rows to the past table. Statement-based triggers amortize function call overhead compared to row-base triggers (I think depesz compared the performance and found about a 10x difference, but I can't find the link).
- Copying the row after-image on update and delete to the past table is advantageous because inserting a new row has no overhead. The disadvantage is that it's harder to figure out when a row was deleted (requires checking for gaps in the past table).
Some pointers if you dive into uni-temporal tables in Postgres:
- Separate the past table and current table into different schemas. Most database users shouldn't modify the past tables. It's easier to grant permission by schema and it makes autocomplete nicer. By convention, we use erp.invoice and erppast.invoice.
- Use a domain type instead of tstzrange to blunt the sharp edges of tstzrange. See the temporal.period domain type in [1].
- Resist the temptation to query against the past table. Temporal joins are fabulously complex without first-class database support (like Oracle's AS OF).
- Optimize for the typical case: querying the current table. Our first temporal implementation used table inheritance consisting of three tables: a parent table, the current table, and a past table. Theoretically, the parent table lets you query data transparently across the parent and child tables. In practice, we didn't use the parent query capability at all. Having a parent table made code review harder by requiring the reviewer to check that the code queried the current table and not the parent table. It's easy enough and rare enough to query all versions of a row by using:
SELECT * FROM current_table
UNION ALL
SELECT * FROM past_table
- Unfortunately, you can't use declarative partitioning because the primary key of the past table differs from the current table. The past table must include the valid period column to differentiate it from other versions of the same row.
[1]: https://gist.github.com/jschaf/cd1c1a3c2a5897282929ee5e16f94...
> Advanced inter-document relations and analysis. No JOINs. No pain. […] queries allow for multi-table, multi-depth document retrieval, efficiently in the database, without the use of complicated JOINs
That sounds like they have a networked database, not a relational database. That might be fine, but as I understand it, relational databases won because they offer more flexible access patterns and it’s easier to write correct queries.
1. Each test runs all migrations on a fresh database. Each test spends 2.1 seconds on db setup.
2. Each test suite runs all migrations. Each test copies the template database from the test suite. Each suite takes 2.1 seconds to run migrations, but cloning a template database takes 300 ms.
3. Bazel caches the data dir and rebuilds it once for all test suites. Reduces the initial test suite setup from 2.1 seconds to 160 ms (to copy the datadir).
4. Each test in a suite uses clonefile to copy the datadir. Reduces db setup overhead from 300 ms (to copy a template database) to 80 ms (to clonefile a datadir).
Currently, most of our testing overhead is clonefile and cleaning up the datadir. I'm interested in a single file sqlite FS because I could be clever and use LD_PRELOAD to replace Postgres's file system operations with sqlite, avoiding most syscalls altogether.
Another problem is that parallel tests can exhaust memory.
The slowest part of our tests is syscall overhead. A mostly empty Postgres data dir for a medium-sized database with a few hundred tables consists of 3k files. On my M1 macOS, it takes 120 ms to delete the entire data dir. Copying is cheaper at 80 ms using clonefile.
I wired Postgres up for our local dev. I don't believe in mocking the database, so all our tests and local dev run against a real Postgres instance.
The main tricks:
- Store the Postgres installation in a known location.
- Set the dynamic loader path to the installation lib dir, e.g., LD_PRELOAD.
- Don't run CI as root (or patch out Postgres' check for root)
- Create and cache the data directory based on the source code migrations.
- Use clone file to duplicate the cached data directory to give to individual tests.
One thing I'd like to pursue is to store the Postgres data dir in SQLite [1]. Then, I can reset the "file system" using SQL after each test instead of copying the entire datadir.
You can sort of do it with IntelliJ with git blame annotations but it’s clunky. I don’t really care about the author’s commits; I care about the entire set of changes or the changes since my last review.
You can also sort of do it with IntelliJ’s pull request mode but it’s also clunky since it’s not a “real” editor and you lose highlighting if you jump to the source code.
My cursory digging indicates that the secret sauce is to grant large IPv6 prefixes and delegate routing to the prefix. An informative-looking Reddit comment says there are 100k IPv6 prefixes (as of Oct. 2020), and each active route takes 1 KiB. [1]
So, IPv6 differs significantly from MAC addresses because you only need to track prefixes.
[1]: https://old.reddit.com/r/networking/comments/j9twgq/how_much...
Framing appears higher up the stack, as an RPC transport, or structured storage like the recordio format referenced by the author. The article sounds like the client expected application/protobuf but the server sent application/custom-protobuf-framing.
Personally, I’d prefer to create plv8 functions out of band (in a migration) and call them explicitly using a normal DB connection. Much less to go wrong.
Each booking likely represents dozens to hundreds of requests. Then, for every visit resulting in a booking there’s probably hundreds of non-booking visits.
- create two ring buffers (left/right; I called them hot/cold)
- store the hot ring buffer in an atomic pointer
- single reader swaps hot and cold and waits for writers to finish
I think using a separate language is massive overkill.
For example: https://pkg.go.dev/golang.org/x/exp/slog#example-Group
I think coupling commits with review progress was a mistake.
If so, I would have expected a much longer tail.