[1]: https://www.gwern.net/docs/technology/2004-03-30-shirky-situ...
516 karma · joined April 27, 2016
[1]: https://www.gwern.net/docs/technology/2004-03-30-shirky-situ...
If you listen to some of the classic bluegrass recordings, there's an incredible amount of nuance and control in the vocals, and trying to sing along and learn those subtleties is challenging and rewarding. And because there's a strong tradition of harmony in this music, the sustained vowel sounds Eno talks about are everywhere.
While the group singing Eno talks about is generally frowned upon in bluegrass, nailing a close harmony stack with one to three other people (depending on the parts) is a fantastic feeling. Building relationships around that, where you're looking forward to the next festival or music camp because you'll get to sing with someone again, is even better.
I got into this music for playing my instrument with others, but while I still enjoy that, I appreciate the singing a lot more now.
This is the terrifying part about RLS to me: having to rely on managing the user id as part of the database connection session seems like an easy way to shoot yourself in the foot (especially when combined with connection pooling). Adding WHERE clauses everywhere isn't great, but at least it's explicit.
That said, I've never used RLS, and I am pretty curious: it does seem like a great solution other than that one gotcha.
Memory usage and I/O can be less efficient. Postgres handles table data in 8kb pages, so even if you're just reading a single row, that reads 8kb from disk and puts 8kb in the Postgres buffer cache, with that row and whatever happens to be next to it in the physical layout of the underlying table. Postgres does this because of locality of reference: it's cheaper to bulk-load data from disk, and, statistically speaking, you may need the adjacent data soon. If each user is touching separate tables, you're loading a page per row for each user, and you're missing out on some of the locality benefits.
Another problem is monitoring (disclosure: I work for pganalyze, which offers a Postgres monitoring service). The pg_stat_statements extension can track execution stats of all normalized queries in your database, and that's a very useful tool to find and address performance problems. But whereas queries like "SELECT * FROM posts WHERE user_id = 123" and "SELECT * FROM posts WHERE user_id = 345" normalize to the same thing, schema-qualified queries like "SELECT * FROM user_123.posts" and "SELECT * FROM user_345.posts" normalize to different things, so you cannot easily consider their performance in aggregate (not to mention bloating pg_stat_statements by tracking so many distinct query stats). This is the case even when you're using search_path so that your schema is not explicitly in your query text.
Also, performance of tools like pg_dump is not great with a ton of database objects (tables and schemas) and, e.g., you can run into max_locks_per_transaction [1] limits, and changing that requires a server restart.
I wouldn't say you should never do schema-based multi-tenancy (you point out some good advantages above), but I'd be extremely skeptical of using it in situations where you expect to have a lot of users.
[1]: https://www.postgresql.org/docs/current/runtime-config-locks...
[1]: https://www.postgresql.org/message-id/flat/20220628083230.c4...
That's not entirely true: variable-width row values under a size threshold are stored inline (possibly compressed).
The beauty part about TOAST is that they're mostly just regular tables (any table that needs its data TOASTed gets its own TOAST table). You can look them up in the system catalogs and query them directly.
The schema for these is (chunk_id, chunk_seq, chunk_data). Chunk "pieces" are limited in size so they can be stored inline (obviously there's not much benefit to a TOAST table itself having a TOAST table), so any row values that need to be TOASTed may also be broken up into multiple pieces. Row values stored inline in the "owning" table reference a chunk_id where the TOASTed data is stored, and queries that need to return that data look up all the chunks for a chunk id, ordered by chunk_seq.
You're right that this scheme avoids reading TOASTed data when you're not actually using it (a good reason to avoid "SELECT *" if you've got big TOASTed data).
Also, at any point in time, it's likely to have zero open issues and zero open pull requests, which is pretty impressive for a project of its size.
[1]: (PDF) https://countrymusichalloffame.org/content/uploads/2019/05/W...
User.where("status = ?", "active").all
ActiveRecord itself sanitizes the input here and the query that's sent to Postgres does not use parameter markers at all. <Layout>
<Header />
<Content page={page} />
<Footer />
</Layout>
const Content = ({page}) => {
switch (page) {
case 'home': return <Home />
case 'about': return <About />
default: return <NotFound />
}
}[1]: https://www.postgresql.org/docs/current/sql-createindex.html
There's a bunch of other comparisons out there, too.
[1]: https://remix.run/blog/remix-vs-next [2]: https://news.ycombinator.com/item?id=29983950
Allow SQL-language functions and procedures to use SQL-standard function bodies (Peter Eisentraut)
Previously only string-literal function bodies were supported. When writing a function or procedure in SQL-standard syntax, the body is parsed immediately and stored as a parse tree. This allows better tracking of function dependencies, and can have security benefits.
[1]: https://www.postgresql.org/docs/14/release-14.html[1]: https://www.postgresql.org/docs/current/storage-toast.html
[1]: https://github.com/uhoh-itsmaciek/fretboard.cool/blob/master... (there's a quirk noted in the TODO there so you need to shift the fifth string down a fourth when specifying the tuning).
Postgres is a fantastic community.
[1]: https://www.postgresql.org/message-id/flat/CA%2BTgmoZgapzekb... [2]: https://www.postgresql.org/message-id/flat/CAH2-Wz%3DsSvMX5H...