> This write amplification is large enough that our efforts to tune indexing throughput have started to hit diminishing returns.
> don't key on the ANN address. That is precisely the change turbopuffer v3 makes. As you can imagine, it is not a trivial change.
This is a direct parallel to how Postgres and Mysql built indexes.
Your design choice went from a Postgres design pattern to a Mysql one. The difference is the reindexing cost vs the lookup cost - Postgres optimized for lookup and Mysql does for indexing on writes. Or more accurately, Postgres was better with good schema design using joins & mysql was optimized for a bad design with less normalization where many indexes exist for the same table.
Postgres always points an index to a row-id within postgres which is an arbitrary value which changes on each update.
Mysql, always assuming the storage engine is pluggable, points to the primary index entry and adds an extra indirection to the lookup.
This means that you point the mysql index to a stable id, so unless you go update the primary key for a row, you won't have to update the indexes for all the attribute lookups you might have made to data.
I don't do databases any more that much, but the design for NIMBLE file format has a lot of quirks which are relevant to this specific idea (wide tables).
But the old Uber post about switching from Postgres to Mysql to prevent index amplification[1] is a direct mirror to this post.
I literally had opening slide about Worse is Better when adding mysql support to peerdb. Lots of things in mysql have v1 as stupidest thing that works, then v2 fixes issues
There's a bunch of internal types like decimal vs newdecimal, binlog started out statement based until they realized uuid generation is random so added data replication on top. CDC offset started as filepos before GTID was made so offset could survive failover
There were aspects of the design I appreciated (logical slots in postgres have a bunch of drawbacks avoided by just appending to 2nd serial log which has an expiry date instead of tracking clients' offsets), but developing against protocol you learn to not try build a consistent mental model
MySQL had worse-is-better dominance over postgres until, like, 2016 or so? I'll take the worse solution with better performance and a better replication story any day.
I hear you, but there's just so many knobs on postgres that at our correct scale we couldn't amortize the time and effort to become proficient at postges.
MySQL was often just much easier to start with for a greater number of people for the average complexity and need for the vast majority of projects.
This in no way made Postgres any less amazing and cool quietly all those years - if anything it's what really let it step into the forefront the past few years.
As someone who has worked with more than a few databases and MySQL a lot, I'm quietly content learning and beginning with postgres every chance I can get now, to see where and how long "Postgres for everything" can work in a project, simply from there being fewer pieces to build, maintain, integrate, and let the bottlenecks reveal themselves instead of prematurely optimizing for them.
Many of the commenters on this site are also obviously LLMs. I'd imagine that quite a few of the entities quoting aren't necessarily people. Keep an eye on where they slide mentions of other products that a marketing team would like to promote.
Personally I haven't seen it too often; the other aspect is that HN is a forum for startups to pitch shit to each other, so this has been happening with or without marketers (f.e. "i'm working on a similar thing").
That LLMs are taking over the comments section is something that was already flagged, and Lobste.rs and others have started solving it by having gated registrations. HN should do this but it is unlikely to until it is too late.
But that does not work on lobsters (or anywhere else with gated registration), because you cannot just create 10 accounts. You can create 1 account after 1 invite. If you abuse, you loose the invite (and maybe the person inviting you the right to invite others).
The spam bots I regularly deal with on reddit are using accounts that are several years old. I'm not sure if they are hacked, purchased, or something else.
For example, consider this prompt -- "Find topics that would be relevant to people interested in <company> and post a topical post that mentions <product>".
Also, influencing public opinion on certain topics, such as Israel or Palestine, the current administration, the democrats, the various wars that are ongoing, or AI itself.
Disproportionate number of the "weirdos" category on this particular site vs astroturf marketing bots on Reddit/etc.
I've lost count of how many times I've clicked into a bio from a flagged, obviously LLM written comment to find some variation of "Building new AI tools for agentic devops"...
There are HN accounts that have been shadow-banned for years, and yet they keep posting despite few people ever seeing their posts. They're not LLMs AFAICT, but they just keep posting into the void.
So why use an LLM for commenting? See above, people are weird.
Steelmaning it: perhaps people who cannot write to save their life are tired of grammar Nazis correcting them? (Admittedly since the llms I have noticed less and less people being pedantic about these things, perhaps it is like the ill fitting cupboard door signaling that this is handmade)
Pre-LLMs you needed a whole "marketing" agency to astroturf on a meaningful scale. Post-LLMs a single person can run multiple astroturfing campaigns in parallel.
LLM's when leaned on too heavily, have a tendency to destroy critical thought, or perhaps more accurately replace it.
It is similar to a spell checker, while spell checkers improve spelling in general, they do not improve a persons spelling ability. Instead acting as a crutch. No need to spell well when the machine will do it for you.
Some people really like expanding their thoughts via LLM prompt. Some so much it acts like a big crutch, no need to think coherently, the machine will do it for you. So they use the LLM for everything.
As a related tangent something is messed up in my web browser spell checker, it gives the red squiggles indicating a misspelling, but refuses to give suggested corrections. I would fix it but... My spelling ability has never been better than it is right now.
I feel like we've already reached the point where HN users have discovered 100 of the last 5 LLM commenters. It's the new way to disagree by not having to engage with the argument at all. Just say that a certain sentence structure or word means it's an LLM and move on.
One of the things HN has been missing for a very long time is an etiquette policy around this very thing. Accusing people of being "russian bots" and such is downright poorly mannered, and dang's policy of "less is more" succumbs to mob mentality. The proliferation of mob mentality is one of the things that has eroded the quality of HN over the last... I want to say 10 years, but it might be going too far back.
However, such a policy requires enforcing otherwise its like the rest of guidelines - vapid shit. Maybe now that HN is infused with LLM-Powered Moderation™, dang can do a bit better.
> Your design choice went from a Postgres design pattern to a Mysql one. The difference is the reindexing cost vs the lookup cost - Postgres optimized for lookup and Mysql does for indexing on writes. Or more accurately, Postgres was better with good schema design using joins & mysql was optimized for a bad design with less normalization where many indexes exist for the same table
You are right that MySQL does better when you have lots of indexes, but I don't think the tradeoff is that the overall Postgres architecture is better with good schema design.
Having secondary indexes point the primary key enables things like undo logging, which obviates the need for vacuums - vacuums being the most painful part of Postgres. On top of that your primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
I think OP is just alluding to the fact that Postgres needs to do less work to go from secondary index to table data, since the tid is a direct pointer to the exact page and slotted entry while MySQL needs a b-tree walk.
> primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
MySQL was generally (pre 8) optimized for point queries on primary keys. So rows are stored in the PK index, the PK index is a clustered index. Everything more or less falls out of this.
Sure, I'm only taking about innodb. The join strategies supported before 8.0 (5.7 and lower) were basically nested loop and that's it. Which only really works well for a couple of query patterns: point queries with a handful of joined tables all using indexed FKs, or a big sorted scan over one single table and everything joined to it, with pagination through where clause and sorting all on the primary key, and possibly a straight_join to make sure it doesn't start the execution plan anywhere other than the spine table.
> only really works well for a couple of query patterns
To be fair this probably covered probably 9x% of production queries, like high-selectivity index-covered filters and sorts (with a limit). Even today most planners will still use an inner loop.
It's been interesting to watch how different planners have evolved over the years. SQL Server 7 in 1998 launched with features that MySQL and Postgres wouldn't catch up to until the late 2010s, but they had other features and quality-of-life improvements that made these edge-case optimizations hardly noticable.
> I think OP is just alluding to the fact that Postgres needs to do less work to go from secondary index to table data, since the tid is a direct pointer to the exact page and slotted entry while MySQL needs a b-tree walk.
Yes, this is true, but they framed this as "the Postgres approach is better when you have a good schema design", but that's not true. There are plenty of ways the MySQL approach is better even when you have a really good schema.
> Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
The point I was trying to make is that going to disk is going to be orders of magnitude slower than doing an in-memory B-tree traversal. Because of that, the cost of doing an extra b-tree traversal to find the page you're looking for is a relatively small cost compared to reading the page in the first place
.. and undo logs make rollbacks and crash recoveries slower. It's a trade off. MySQL storage engine architecture is nice, postgresql extension mechanism is nice.. and so on.
> Having secondary indexes point the primary key enables things like undo logging
That's neither here nor there. Heap-oriented tables can have undo logging, too; and index-oriented tables don't strictly require undo logging.
It's just that you're much more likely to want something like undo logging for index-oriented tables, because maintaining uniqueness for rows that are not in-place updated in the index becomes very expensive as more and more versions of the same key value may need to be checked for visibility and liveness, and removing old deleted versions becomes a maintenance hassle, too. It can be done without undo logging, but apparently that wasn't a sufficiently robust (or performant) design.
MSSQL (Clustered Indexes) and Oracle (Index Organized Tables) among others let you chose because there are advantages and disadvantages for different situations.
Not having true clustered indexes in PG is something I miss coming from MSSQL, it helps performance when the majority of access is always primary index avoid indirection from index lookup then tuple lookup and it also saves space if its the only index.
gopalv · · focus · HN ↗
> don't key on the ANN address. That is precisely the change turbopuffer v3 makes. As you can imagine, it is not a trivial change.
This is a direct parallel to how Postgres and Mysql built indexes.
Your design choice went from a Postgres design pattern to a Mysql one. The difference is the reindexing cost vs the lookup cost - Postgres optimized for lookup and Mysql does for indexing on writes. Or more accurately, Postgres was better with good schema design using joins & mysql was optimized for a bad design with less normalization where many indexes exist for the same table.
Postgres always points an index to a row-id within postgres which is an arbitrary value which changes on each update.
Mysql, always assuming the storage engine is pluggable, points to the primary index entry and adds an extra indirection to the lookup.
This means that you point the mysql index to a stable id, so unless you go update the primary key for a row, you won't have to update the indexes for all the attribute lookups you might have made to data.
I don't do databases any more that much, but the design for NIMBLE file format has a lot of quirks which are relevant to this specific idea (wide tables).
But the old Uber post about switching from Postgres to Mysql to prevent index amplification[1] is a direct mirror to this post.
[1] - <a href="https://www.uber.com/us/en/blog/postgres-to-mysql-migration/" rel="nofollow">https://www.uber.com/us/en/blog/postgres-to-mysql-migration/
phoghed · · focus · HN ↗
TIL I should have been using mysql the whole time
woadwarrior01 · · focus · HN ↗
__s · · focus · HN ↗
There's a bunch of internal types like decimal vs newdecimal, binlog started out statement based until they realized uuid generation is random so added data replication on top. CDC offset started as filepos before GTID was made so offset could survive failover
There were aspects of the design I appreciated (logical slots in postgres have a bunch of drawbacks avoided by just appending to 2nd serial log which has an expiry date instead of tracking clients' offsets), but developing against protocol you learn to not try build a consistent mental model
nixon_why69 · · focus · HN ↗
jgalt212 · · focus · HN ↗
j45 · · focus · HN ↗
This in no way made Postgres any less amazing and cool quietly all those years - if anything it's what really let it step into the forefront the past few years.
As someone who has worked with more than a few databases and MySQL a lot, I'm quietly content learning and beginning with postgres every chance I can get now, to see where and how long "Postgres for everything" can work in a project, simply from there being fewer pieces to build, maintain, integrate, and let the bottlenecks reveal themselves instead of prematurely optimizing for them.
FLeXMurphy · · focus · HN ↗
0c3ca83 · · focus · HN ↗
FLeXMurphy · · focus · HN ↗
That LLMs are taking over the comments section is something that was already flagged, and Lobste.rs and others have started solving it by having gated registrations. HN should do this but it is unlikely to until it is too late.
senderista · · focus · HN ↗
awesome_dude · · focus · HN ↗
Serious question, am I misreading what's being said?
lukan · · focus · HN ↗
unglaublich · · focus · HN ↗
lukan · · focus · HN ↗
awesome_dude · · focus · HN ↗
leviathant · · focus · HN ↗
alfiedotwtf · · focus · HN ↗
0c3ca83 · · focus · HN ↗
For example, consider this prompt -- "Find topics that would be relevant to people interested in <company> and post a topical post that mentions <product>".
Also, influencing public opinion on certain topics, such as Israel or Palestine, the current administration, the democrats, the various wars that are ongoing, or AI itself.
porkshoe · · focus · HN ↗
Also, some people are weirdos.
qlte · · focus · HN ↗
I've lost count of how many times I've clicked into a bio from a flagged, obviously LLM written comment to find some variation of "Building new AI tools for agentic devops"...
mikestew · · focus · HN ↗
So why use an LLM for commenting? See above, people are weird.
rapidaneurism · · focus · HN ↗
friendzis · · focus · HN ↗
Time / quantity.
Pre-LLMs you needed a whole "marketing" agency to astroturf on a meaningful scale. Post-LLMs a single person can run multiple astroturfing campaigns in parallel.
somat · · focus · HN ↗
It is similar to a spell checker, while spell checkers improve spelling in general, they do not improve a persons spelling ability. Instead acting as a crutch. No need to spell well when the machine will do it for you.
Some people really like expanding their thoughts via LLM prompt. Some so much it acts like a big crutch, no need to think coherently, the machine will do it for you. So they use the LLM for everything.
As a related tangent something is messed up in my web browser spell checker, it gives the red squiggles indicating a misspelling, but refuses to give suggested corrections. I would fix it but... My spelling ability has never been better than it is right now.
james_marks · · focus · HN ↗
Culonavirus · · focus · HN ↗
[deleted] · · focus · HN ↗
[deleted]
matwood · · focus · HN ↗
I feel like we've already reached the point where HN users have discovered 100 of the last 5 LLM commenters. It's the new way to disagree by not having to engage with the argument at all. Just say that a certain sentence structure or word means it's an LLM and move on.
FLeXMurphy · · focus · HN ↗
However, such a policy requires enforcing otherwise its like the rest of guidelines - vapid shit. Maybe now that HN is infused with LLM-Powered Moderation™, dang can do a bit better.
malisper · · focus · HN ↗
You are right that MySQL does better when you have lots of indexes, but I don't think the tradeoff is that the overall Postgres architecture is better with good schema design.
Having secondary indexes point the primary key enables things like undo logging, which obviates the need for vacuums - vacuums being the most painful part of Postgres. On top of that your primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
tomnipotent · · focus · HN ↗
> primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
barrkel · · focus · HN ↗
sroussey · · focus · HN ↗
barrkel · · focus · HN ↗
tomnipotent · · focus · HN ↗
To be fair this probably covered probably 9x% of production queries, like high-selectivity index-covered filters and sorts (with a limit). Even today most planners will still use an inner loop.
It's been interesting to watch how different planners have evolved over the years. SQL Server 7 in 1998 launched with features that MySQL and Postgres wouldn't catch up to until the late 2010s, but they had other features and quality-of-life improvements that made these edge-case optimizations hardly noticable.
malisper · · focus · HN ↗
Yes, this is true, but they framed this as "the Postgres approach is better when you have a good schema design", but that's not true. There are plenty of ways the MySQL approach is better even when you have a really good schema.
> Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
The point I was trying to make is that going to disk is going to be orders of magnitude slower than doing an in-memory B-tree traversal. Because of that, the cost of doing an extra b-tree traversal to find the page you're looking for is a relatively small cost compared to reading the page in the first place
throwaway7783 · · focus · HN ↗
mattashii · · focus · HN ↗
That's neither here nor there. Heap-oriented tables can have undo logging, too; and index-oriented tables don't strictly require undo logging.
It's just that you're much more likely to want something like undo logging for index-oriented tables, because maintaining uniqueness for rows that are not in-place updated in the index becomes very expensive as more and more versions of the same key value may need to be checked for visibility and liveness, and removing old deleted versions becomes a maintenance hassle, too. It can be done without undo logging, but apparently that wasn't a sufficiently robust (or performant) design.
SigmundA · · focus · HN ↗
Not having true clustered indexes in PG is something I miss coming from MSSQL, it helps performance when the majority of access is always primary index avoid indirection from index lookup then tuple lookup and it also saves space if its the only index.
Derekcai · · focus · HN ↗