571
CTEs as lookup tables
How to use a CTE instead of complicated CASE expressions
New
The latest blog posts to take off on Hacker News. 1,888 posts.
572
Bullshit graph database performance benchmarks
Hey HackerNews, let me just drop my mixtape, checkout my soundcloud and “Death Row” is the label that pays me. How is the Graph Database category supposed to grow when vendors keep spou…
573
Faster PostgresSQL to BigQuery Transfers
Benchmarks & Tips for Big Data, Hadoop, AWS, Google Cloud, PostgreSQL, Spark, Python & More...
574
We're wasting money by only supporting gzip for raw DNA files
Stylised graphics of money with DNA helix in the foreground.
575
Demystifying Apache Arrow (2020)
Demystifying Apache Arrow - some observations from a data scientist. Learning more about a tool that can filter and aggregate two billion rows on a laptop in two seconds
576
Modern Polars: A comparison of the Polars and Pandas dataframe libraries
A side-by-side comparison of the Polars and Pandas libraries.
577
I Migrated from a Postgres Cluster to Distributed SQLite with LiteFS
Why and how I migrated from distributed postgres to distributed sqlite on kentcdodds.com
578
Understanding the N + 1 queries problem
In this post, I am going to talk about the N + 1 queries problem. While trying to solve this problem myself recently, I noticed most of the blog posts only talk about the very simple version of the problem and the proposed solutions would break down when the use cases get complex. I will try to get into more depth and…
579
Querying the GitHub archive with the ClickHouse playground
Via this comment on Hacker News I started exploring the ClickHouse Playground . It's really cool, and among other things it allows CORS-enabled API hits that can query a decade of history from the GitHub events archive in less than a second.
580
Easy, alternative soft deletion: `deleted_record_insert`
An alternate to traditional <code>deleted_at</code> soft deletion that stays out of the way and minimizes bugs.
581
ULIDs and Primary Keys
Not all non-sequential primary keys are created equal, the ULID format is a fantastic substitute for the typical UUIDv4 choice.
582
SQLite's Automatic Indexes
SQLite's optimizer creates indexes automatically to improve query performance with only nested loop joins.
583
SQL + M4 = Composable SQL
I often work with clients who have large “data lakes” or big star schema style enterprise databases with fact and dimension tables as far as the eye can see. Invariably said clients end up with a substantial SQL codebase composed of hundreds of independent queries with lots of overlap between them. I want to be able…
584
Cracking encrypted LastPass vaults
The recent (2022) compromise of Lastpass included email addresses, home addresses, names, and encrypted customer vaults. In this post I will demonstrate how attackers may leverage tools like Hashcat to crack an encrypted vault with a weak password.
585
For Want of a JOIN
Once upon a time, I was on a small team tasked with building a data pipeline. On this team was someone we’ll call Bob, who was fresh out of grad school and hadn’t had much experience with software engineering, and no experience with SQL. As part of this data pipeline, Bob had to combine the data from two tables. JOIN…
586
SQLite internals: How the most-used database works
Explore SQLite Internals: How The World's Most Used Database Works by Abdur-Rahmaan Janhangeer. Deep dive into database internals, systems engineering, and advanced Python.
587
I almost failed to search a 37 GB text file in under 1 millisecond
... in which we check if your password has been compromised in many inconvenient ways, in a tale of destruction, obsession, and self-discovery.
588
I found a bug in SQLite
rqlite is a lightweight, open-source, distributed relational database written in Go, which uses SQLite as its storage engine. Recently I introduced a high-performance write-path into rqlite and, to my great surprise, it exposed a bug in SQLite. The SQLite team quickly addressed the issue but let's take a look at the…
589
Just use Postgres for everything
Replace Redis, MongoDB, Kafka & more with PostgreSQL. Reduce complexity, boost development speed. Complete guide with real examples.
590
Go and SQLite in the Cloud
Why SQLite and Go in the cloud are a great match, getting started, best practices, and more.
591
Datasette’s new JSON write API: The first alpha of Datasette 1.0
This week I published the first alpha release of Datasette 1.0, with a significant new feature: Datasette core now includes a JSON API for creating and dropping tables and inserting, …
592
Goodbye, data science
This is more of a personal post than something intended to be profound. If you are looking for a point, you will not find one here. Frankly I am not even sure who the target audience is for this (p…
593
Index Merges vs. Composite Indexes in Postgres and MySQL
Composite indexes aka multi-column indexes are about 10x faster than index merges. In Postgres, the gap is larger than in MySQL because Postgres doesn’t support index-only scans for queries that involve index merges.
594
Data-Oriented Programming in Python
Many users of Python deprioritize performance in favor of soft benefits like ergonomics, business value, and simplicity. Users who prioritize performance typically end up on faster compiled languages like C++ or Java.
595
Moving Away from UUIDs (2018)
If you need an unguessable random string (for a session cookie or access token, for example), it can be tempting to reach for a random UUID, which looks like this: 88cf3e49-e28e-4c0e-b95f-6a68a785a…
596
Building a BFT JSON CRDT
CRDTs are a family of data structures that are designed to be replicated across multiple computers without needing to worry about conflicts when people write data to the same place.
597
The 12-bit rainbow palette
A palette of twelve colours chosen with consideration for how we perceive luminance, chroma, and hue
598
Building a semantic search engine in Rust
If you're interested in building a semantic search engine in Rust, this article will show you how! We'll cover building a basic engine using sentence transformers and a kd-tree.
599
It's not Ruby that's slow, it's your database
Many people keep repeating that Ruby is slow. It is. But that doesn't matter, because your database is so much slower that it is the bottleneck. So, an alternative title would be "Ruby is slow, but that doesn't matter for you."
600