DB

Collections

• SQL for the Weary

• Use cases of soft delete

  • You want to delete records, but also want to retain them for n number of days, just for a safer side against accidental deletion.
  • You want to exclude some records (permanent retain) under explicit requirements, even if they match the criteria of an eligible record to be deleted.
  • You don't want to delete the actual resource before returning the resource back. Basically, deletion before returning the value is not desired.
  • You don't want to modify existing table schema(s) to accommodate soft delete key.
  • You want the tables as loosely coupled as possible without having to worry about deletion logic.

• Soft Deletion Probably Isn't Worth It

  • https://news.ycombinator.com/item?id=32156009
  • Easy, alternative soft deletion: deleted_record_insert

• Index Merges vs Composite Indexes in Postgres and MySQL

  Composite 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.

• MySQL for Developers #bookshelf

• How does database sharding work?

  why are you not using a database that does sharding for you? Over the past few years the so-called “serverless” database has gotten a lot more traction.

  This is the most important paragraph in the article. In a world where things like Dynamo/Cassandra or Spanner/Cockroach exist, manually-sharded DB solutions are pretty much entirely obsolete. Spanner exists because Google got so sick of people building and maintaining bespoke solutions for replication and resharding, which would inevitably have their own set of quirks, bugs, consistency gaps, scaling limits, and manual operations required to reshard or rebalance from time to time. When it's part of the database itself, all those problems just... disappear. It's like a single database that just happens to spread itself across multiple machines. It's an actual distributed cloud solution.
  My current employer uses sharded and replicated Postgres via RDS. Even basic things like deploying schema changes to every shard are an unbelievable pain in the ass, and changing the number of shards on a major database is a high-touch, multi-day operation. After having worked with Spanner in the past, it's like going back to the stone age, like we're only a step above babysitting individual machines in a closet. Nobody should do this. — GeneralMayhem

• A 5 years+ tech lead said they shard a database to scale but then he failed to answer this question

• OrmHate

  Essentially the ORM can handle about 80-90% of the mapping problems, but that last chunk always needs careful work by somebody who really understands how a relational database works.
  ... A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.
  To avoid the mapping problem you have two alternatives. Either you use the relational model in memory, or you don't use it in the database.

• Data Engineering Design Patterns #bookshelf

• It's Time To Get Over That Stored Procedure Aversion You Have

• UUIDv7: The Time-Sortable Identifier for Modern Databases

• Identity Crisis: Sequence v. UUID as Primary Key

PostgreSQL

• MySQL vs PostgreSQL in 2023.
• Postgresql is enough

  • As an application grows in complexity, you start to realize why there's a stack, rather than just a single technology to rule them all. Trying to cram everything into Postgres (or lambdas, or S3, or firebase, or whatever other tech you're trying to consolidate on) starts to get really uncomfortable.
  • ... both worked: the PG queue was never grown out of, and generally SQS was easy to work with & reliable. But what I've also seen is "Let's introduce bespoke tech that nobody on the team, including the person introducing it, has experience in, for a queue that isn't even the main focus of what we're building" — this I'm less fine with.

• Simplify: move code into database functions
  • Databases outlive the applications that access them.
  • the database is actually quite smart.
  • examples: constraints, triggers, functions, create JSON directly.
  • If you like JavaScript, check out the promising plv8.
  • branching? source code history(Migrations)?
    • How to work with stored procedures and not die trying?
    • You can use a DDL trigger to keep all revisions in a table in a separate database (and of course back up that database frequently). - SQL Server DDL Triggers to Track All Database Changes

Features I'd like in PostgreSQL

• –i-am-a-dummy mode
• Unit test mode (random result sorting)
• Query progress in psql
• Pandas-like join validation
• JIT support for CREATE INDEX
• Reduce the memory usage of prepared queries

---

Children

1. DB tools
2. Efficient Pagination Using Deferred Joins
3. MySQL EXPLAIN ANALYZE
4. SQLite
5. SQLite the only database you will ever need in most cases
6. Temporary tables in SQLite
7. Things You Should Know About Databases
8. We switched to cursor-based pagination
9. postgres