Hacker News Reader

mulanderon SQLite the only database you will ever need in most cases

> I use Gitea(Github clone) locally with SQLite running on ZFS where I can take atomic snapshots of both the SQLite database and git repositories.

ZFS snapshots may not result in a consistent databases backup of sqlite. You should use VACUUM INTO and then do a snapshot or use the sqlite Backup API.

See my other comment: https://www.sqlite.org/backup.html

Essentially, while you have a proper atomic snapshot of changes on disk the in flight transactions won't be there and you will be on the mercy of having a sucessful recovery from the journal/wal if you have that.

mulanderon SQLite the only database you will ever need in most cases

This is untrue and a sure way to corrupt your database[1].

From sqlite.org on how to corrupt your database:

> 1.2. Backup or restore while a transaction is active Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt.

> The best approach to make reliable backup copies of an SQLite database is to make use of the backup API that is part of the SQLite library. Failing that, it is safe to make a copy of an SQLite database file as long as there are no transactions in progress by any process. If the previous transaction failed, then it is important that any rollback journal (the -journal file) or write-ahead log (the -wal file) be copied together with the database file itself.

What you need is to follow the guide[2] and use the Backup API or the VACUUM INTO[3] statement to create a new database on the side.

[1] https://www.sqlite.org/howtocorrupt.html

[2] https://www.sqlite.org/backup.html

[3] https://www.sqlite.org/lang_vacuum.html#vacuuminto

mulanderon An unexpected find that freed 20GB of unused index space in PostgreSQL

Let us say that in our example table we have 100 000 records with severit_id < 7, 200 000 with severity_id = 7 and 3 records with severity_id = 8.

Statistics claim 100k id < 7, 200k id = 7 and 0 with id > 7. The last 3 updates could have happened right before our query, the statistics didn't update yet.

Let us assume that we blindly trust the statistics and they currently state that there are absolutely no values with severity_id > 7 and you have a query WHERE severity_id != 7 and a partial index on severity_id < 7.

If you trust the statistics and actually use the index the rows containing severity_id = 8 will never be returned by the query even if they exist. So by using the index you only scan 100 k rows and never touch the remaining ~200k. However this query can't be answered without scanning all ~300k records. This means, that on the same database you would get two different results for the exact same query if you decided to drop the index after the first run. The database can't fall back and change the plan during execution.

Perhaps I misunderstood you originally. I thought you suggested that the database should be able to know that it can still use the index because currently the statistics claim that there are no records that would make the result incorrect. You are of course correct, that the statistics are there to guide the planner choices and that is how they are used within PostgreSQL - however some plans will give different results if your assumption about data are wrong.

mulanderon An unexpected find that freed 20GB of unused index space in PostgreSQL

> The problem we always ran into with deletes is them triggering full table scans because our indexes weren't set up correctly to test foreign key constraints properly.

This is a classic case where partitioning shines. Lets say those are logs. You partition it monthly and want to retain 3 months of data.

- M1 - M2 - M3

When M4 arrives you drop partition M1. This is a very fast operation and the space is returned to the OS. You also don't need to vacuum after dropping it. When you arrive at M5 you repeat the process by dropping M2.

> Another solution is tombstoning data so you never actually do a DELETE, and partial indexes go a long way to making that scale. It removes the logn cost of all of the dead data on every subsequent insert.

If you are referring to PostgreSQL then this would actually be worse than outright doing a DELETE. PostgreSQL is copy on write so an UPDATE to a is_deleted column will create a new copy of the record and a new entry in all its indexes. The old one would still need to be vacuumed. You will accumulate bloat faster and vacuums will have more work to do. Additionally, since is_deleted would be part of partial indexes like you said, a deleted record would also incur a copy in all indexes present on the table.

Compare that to just doing the DELETE which would just store the transaction ID of the query that deleted the row in cmax and a subsequent vacuum would be able to mark it as reusable by further inserts.

mulanderon An unexpected find that freed 20GB of unused index space in PostgreSQL

No, I don't think statistics can let you get away with this. Databases are concurrent, you can't guarantee that a different session will not insert a record that invalidates your current statistics.

You could argue that it should be able to use it if the table has a check constraint preventing severity_id above 7 being ever inserted. That is something that could be done, I don't know if PostgreSQL does it (I doubt it) or how feasable it would be.

Is SQL Server able to make an assumption like that purely based on statistics? Genuine question.

mulanderon An unexpected find that freed 20GB of unused index space in PostgreSQL

Partial indexes are amazing but you have to keep in mind some pecularities.

If your query doesn't contain a proper match with the WHERE clause of the index - the index will not be used. It is easy to forget about it or to get it wrong in subtle ways. Here is an example from work.

There was an event tracing structure which contained the event severity_id. Id values 0-6 inclusive are user facing events. Severity 7 and up is debug events. In practice all debug events were 7 and there were no other values above 7. This table had a partial index with WHERE severity_id < 7. I tracked down a performance regression, when an ORM (due to programmer error) generated WHERE severity_id != 7. The database is obviously not able to tell that there will never be any values above 7 so the index was not used slowing down event handling. Turning the query to match < 7 fixes the problem. The database might also not be able to infer that the index can be indeed used, for example when prepared statements are involved WHERE severity_id < ?. The database will not be able to tell that all bindings of ? will satisfy < 7 so will not use the index (unless you are running PG 12, then that might depend on the setting of plan_cache_mode[1] but I have not tested that yet).

Another thing is that HOT updates in PostgreSQL can't be performed if the updated field is indexed but that also includes being part of a WHERE clause in a partial index. So you could have a site like HN and think that it would be nice to index stories WHERE vote > 100 to quickly find more popular stories. That index however would nullify the possiblity of a hot update when the vote tally would be updated. Again, not a problem but you need to know the possible drawbacks.

That said, they are great when used for the right purpose. Kudos to the author for a nice article!

[1] - https://postgresqlco.nf/doc/en/param/plan_cache_mode/

mulanderon Elixir Is Erlang, not Ruby

An erlang BitTorrent client exists: https://github.com/jlouis/etorrent

mulanderon Graviton Database: ZFS for key-value stores

Yes and no or to be precise - to a certain degree but not through an exposed language feature.

PostgreSQL still does copy-on-write so the old versions of the row exist and are present in storage. However now there is an autovacuum process going over the records regularly marking those no longer seen by any transactions as re-usable so eventually the old records would get overwritten.

You can get at the older versions of the rows directly on disk or perhaps it would be possible to get the db to return such older versions of the rows. It seems that by default even trying to get at them with `ctid` is not possible so that may require hacking PostgreSQL itself or using some extension which seem to actually exist[1].

[1] - https://github.com/omniti-labs/pgtreats/tree/master/contrib/...

mulanderon Graviton Database: ZFS for key-value stores

> I like how the time travelling/history is always touted as a feature (which it is), but it really just means the garbage collector/pruning part of the transaction engine is missing. Postgres and other mvcc systems could all be doing this, but they don't.

Postgres actually did tout it as a feature in "THE IMPLEMENTATION OF POSTGRES" by Michael Stonebraker, Lawrence A. Rowe and Michael Hirohama[1] search for "time travel" in the PDF. I added the relevant quotes below for easier access ;)

This was back when PostgreSQL had the postquel language (before SQL was added) there was special syntax to access data at specific points in time:

> The second benefit of a no-overwrite storage manager is the possibility of time travel. As noted earlier, a user can ask a historical query and POSTGRES will automatically return information from the record valid at the correct time.

Quoting the paper again:

> For example to find the salary of Sam at time T one would query:

    retrieve (EMP.salary)
    using EMP [T]
    where EMP.name = "Sam"

> POSTGRES will automatically find the version of Sam’s record valid at the correct time and get the appropriate salary.

[1] - https://dsf.berkeley.edu/papers/ERL-M90-34.pdf

mulanderon LinkedIn to cut 960 jobs worldwide

My mistake, I wrote it from memory instead of copying or looking at the parent post. Edited and fixed.

mulanderon LinkedIn to cut 960 jobs worldwide

> Every tenth man in a group was executed by members of his cohort

That is a 10% reduction. Killing nine out of ten would be a 90% reduction but 'in the classical sense' it would also mean that the lucky guy would have to kill the 9 remaining people.

mulanderon Drug cartel ‘narco-antennas’ make life dangerous for Mexico’s repairmen

Not OP but here is one also from HN[1][2] on Mexico. Selected quote from the article:

> “It’s good business,” El Polkas says with a shrug. “It makes a lot of money.” When I ask how gasoline compares to narcotics, in terms of overall revenue to Los Zetas, he rubs his index fingers together. “Fifty-fifty,” he says. “It’s approximately as profitable as drugs.”

[1] - https://news.ycombinator.com/item?id=18101141

[2] - https://www.rollingstone.com/culture/culture-features/drug-w...

mulanderon MariaDB Temporal Data Tables

Funny historical and architecture fact about PostgreSQL. It actually can do this, for all tables without special features. Unfortunately the facility to perform a query like this is no longer exposed but it shouldn't be impossible to re-add in a more modern way.

Essentially PostgreSQL has copy-on-write semantics, so historical records exist unless a vacuum marks them as no longer needed and subsequent insert/updates overwrite the values.

In the past when PostgreSQL had the postquel language (before SQL was added) there was special syntax to access data at specific points in time:

This is nicely outlined in "THE IMPLEMENTATION OF POSTGRES" by Michael Stonebraker, Lawrence A. Rowe and Michael Hirohama[1]. Go ahead open the PDF and search for "time travel" or read the quotes below.

Quoting the paper again:

> For example to find the salary of Sam at time T one would query:

    retrieve (EMP.salary)
    using EMP [T]
    where EMP.name = "Sam"

> POSTGRES will automatically find the version of Sam’s record valid at the correct time and get the appropriate salary.

[1] - https://dsf.berkeley.edu/papers/ERL-M90-34.pdf

mulanderon NetBSD Code Study

Hi, I'm the guy who did the OpenBSD code reads.

The daily started in June 2017 we kept up doing it daily for 42 days straight. In total there were 47 code reads.

http://blog.tintagel.pl/2017/06/09/openbsd-daily.html

http://blog.tintagel.pl/2017/09/10/openbsd-daily-recap.html

mulanderon Wolf Species Rebounds in Southwest

My wife is an animal behaviorist specializing in pastoral dogs, non-lethal prevention of livestock loss and wolves in general.

She blogged how simple things like fences[1], live stock guarding dogs[2] and human behavior and awareness[3] can reduce loses. We have a much higher wolf presence here in Europe (and especially in Poland where we both live) and a mix of the above plus government reimbursements for eventual loses help co-exist with the species without issues.

[1] - https://k9hacking.com/the-fence-color-for-livestock-protecti...

[2] - https://k9hacking.com/lgd-adolescence-draft/

[3] - https://k9hacking.com/yellowstone-wolf-pups-accident-flight-...

mulanderon PostgreSQL's Imperfections

> Every time an on-disk database page (4KB) needs to be modified by a write operation, even just a single byte, a copy of the entire page, edited with the requested changes, is written to the write-ahead log (WAL). Physical streaming replication leverages this existing WAL infrastructure as a log of changes it streams to replicas.

First, the PostgreSQL page size is 8KB and has been that since the beginning.

The remaining part. According to PostgreSQL documentation[1] (on full page writes which decides if those are made), a copy of the entire page is only written fully to the WAL after the first modification of that page since the last checkpoint. Subsequent modifications will not result in full page writes to the WAL. So if you update a counter 3 times in sequence you won't get 3*8KB written to the WAL, instead you would get a single page dump and the remaining two would only log the row-level change which is much smaller[2]. This is further reduced by WAL compression[3] (reducing the segment usage) and by increasing the checkpointing interval which would reduce the amount of copies happening[4].

This irked me because it sounded like whatever you touch produces an 8KB copy of data and it seems to not be the case.

[1] - https://www.postgresql.org/docs/11/runtime-config-wal.html#G...

[2] - http://www.interdb.jp/pg/pgsql09.html

[3] - https://www.postgresql.org/docs/11/runtime-config-wal.html#G...

[4] - https://www.postgresql.org/docs/11/runtime-config-wal.html#G...

mulanderon PostgreSQL is the worlds’ best database

You don't need to log every auto explain. Just enable track_io_timing and pg_stat_statements and you get per query IO performance metrics much cheaper.

Table F.21. pg_stat_statements Columns

https://www.postgresql.org/docs/11/pgstatstatements.html

blk_read_time

double precision

Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

blk_write_time

double precision

Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

mulanderon PostgreSQL is the worlds’ best database

PostgreSQL has track_io_timing and passing buffers will then include IO timing.

\set track_io_timing=on;

explain (analyze, verbose, buffers) your-query;

Additionally, it will also include things like times for triggers executed from the query.

mulanderon System design hack: Postgres is a great pub/sub and job server

> Anyone know how connection pooling works with listeners?

They don't. LISTEN is per connection and pgbouncer multiplexes many sessions onto a single one. The poller holds the connection and has no way to propagate back the notification while still maintaining multiplexed sessions as isolated.

mulanderon Amazon’s Consumer Business Turned Off Final Oracle Database

> replace rownum <= 1 with LIMIT 1

rownum is a pseudocolumn numbering entries, sorting the results will give you records in non increment rownum. On such queries rownum <= N will give you the record that was first in the results before sorting.

LIMIT will give you the first record after sorting.

So rownum != LIMIT and if you really want to implement the same logic you would need to use select row_number() over () as rownum in the ported query, put that into a subquery and filter with where rownum <= N in the outer level.

Example:

select * from (select row_number() over () as rownum, * from pg_class order by relname) as x where x.rownum < 5;

select * from pg_class order by relname LIMIT 5;

Will give two very different results.

The first one maps to oracle:

select * from pg_class where rownum < 5 order by relname;

The second one doesn't.

mulanderon OpenBSD Crossed 400k Commits

Two years ago I did something similar. Plotting the surviving lines of code in the OpenBSD code base across commits:

https://twitter.com/mulander/status/809120593606049792

mulanderon Perl is dying

> Perl – 1987

> Python – 1989

> Ruby on Rails – 2005

Ruby itself is 1995. In 2005 when Rails was coming out Perl had it's peak according to Tiobe:

> Highest Position (since 2001): #3 in May 2005[1]

> That makes one wonder about who else is still using Perl? if any? Can’t remember the last time I’ve heard about it.

Duckduckgo? [2]

Sure, Perl is loosing popularity but I don't think it's anywhere near dead or extinct.

[1] - https://tiobe.com/tiobe-index/perl/

[2] - https://github.com/duckduckgo?language=perl

mulanderon Game of Trees: A Version Control System for OpenBSD

Undeadly provides more context:

https://undeadly.org/cgi?action=article;sid=20190810123007

Including a link to the lobste.rs with comments from stsp@ (main developer on got):

https://lobste.rs/s/sxpmar/game_trees_version_control_system...

mulanderon SSH gets protection against side-channel attacks

Sure:

https://github.com/openbsd/src/commit/707316f931b35ef67f1390...

mulanderon EU government websites have undisclosed adtech trackers from Google and others

GDPR aside, the most annoying thing I saw was Polish 'Agencja Wywiadu' (the CIA equivalent) having a recruitment page[1] stating how careful people should be when applying. To not tell friends, to do it in person etc. and when you look at it, the whole page is filled with tracking from Facebook, Twitter and Google. I tweeted at them but they don't seem to care... [2]

[1] - https://aw.gov.pl/rekrutacja/

[2] - https://mobile.twitter.com/mulander/status/10239817413951283...

mulanderon Ask HN: What are some niche communities you enjoy?

https://reddit.com/r/openbsd_gaming

We just (10 minutes ago) finished playing a round of Quake 2. Some games are streamed (https://www.twitch.tv/thfrw/videos/all | https://www.twitch.tv/communities/openbsd_gaming | https://www.youtube.com/channel/UCF2MeFBWJoFZtz0ADX3Us0Q/vid...), we even have some more modern FNA games and a list of games available on GoG for the platform (https://www.gog.com/mix/openbsd_engine_available). We generally hang out on irc #openbsd-gaming @ freenode.

mulanderon Don’t Fix Facebook, Replace It

> Turn on the feature to hide photos you're tagged in

And how should I do that if I don't have a Facebook account? How do I tell Facebook to not steal my phone number and texts from phones of my friends?

mulanderon Pale Moon Official Branding Violation – Issue #86 – Jasperla/openbsd-wip

It's now also reviewed for removal from FreeBSD:

https://lists.freebsd.org/pipermail/freebsd-ports/2018-Febru...

mulanderon OpenSSL wins the Levchin prize

The reward was given to the OpenSSL team.

OpenSSH is a different project, developed by the OpenBSD project. OpenBSD also works on LibreSSL which is a fork of OpenSSL.

OpenSSL itself has NOTHING to do with OpenBSD and OpenBSD related projects.

mulanderon OpenBSD and the modern laptop

The auto layout depends on the target drive size, your needs may vary but I set /usr/X11R6 to 1 GB and rarely see more used than 250 MB.

Generally I use the auto layout as a strong hint on how many partitions to make and the general size suggestions - then alter based on my needs (ie. bumping var instead of home for servers).