Patitioning: the third pillar of scalability

partitioning.md

Interesting articles and content about data partitioning

Slotted Counter Pattern

Some articles and tips about Slotted Counter Pattern:

• PostgreSQL Lock Contention For Frequently Updated Rows
  • Example of how partitioning a counter can improve throughput without giving up locks
• PlanetScale: The Slotted Counter Pattern
• Hacker News: The Slotted Counter Pattern
• ChatGpt: Studying about Slotted Counter Pattern (🇧🇷)
• Prevent Locking Issues For Updates On Counters
• Sharding counters
• Active Record Slotted Counters (gem)
  • Github repository
• Design patterns every data engineer should know
• LinkedIn: Você já ouviu falar sobre "Slotted Counter Pattern"? - by rponte
• Twitter thread: Combining Slotted Counter with SKIP LOCKED

Shuffling and randomizing rows with SQL

• Vlad Mihalcea: SQL ORDER BY RANDOM
  • shuffling rows - with Oracle it's better to use the SAMPLE(N) function;
  • interessante para diminuir a contigência quando usando FOR UPDATE;
  • outra forma de diminuir a contigência é usando batches (lotes);
  • emulando shuffling com PostgreSQL e spatial columns
• Using the MOD() function (aka modulo/remainder) into SQL to partitioning the data:
  • https://www.w3schools.com/sql/func_mysql_mod.asp
  • https://twitter.com/rponte/status/1590751858213416962?s=20&t=NsJ_AWtqtBV2h5p4mDm6-A
  • https://stackoverflow.com/questions/3756928/selecting-rows-where-remainder-modulo-is-1-after-division-by-2
  • https://stackoverflow.com/questions/49291926/how-to-partition-a-sql-server-table-with-a-modulus-function
  • http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=AEAC4DE8-2337-4A3D-9AAA-1A3C221226A6&tkw=uses-of-the-modulo-(%)-operator
• How to Avoid Hotspots on Range-based Indexes in Distributed Databases
  • Tem exemplo legal no uso de modulo com random();

Data partitioning

• Wiki: Partition (database)
• Microsoft: Data partitioning guidance
• Scalable Job Queue in SQL (YugabyteDB)
  • YugabyteDB and SKIP LOCKED + partitioning via range-sharded tables;
  • "With 500 threads processing each job in 1 second";
  • Testes de performance com Pgbench;
  • Faz fanout (partitioning) através de uma View para distribuir de forma aleatoria + range-sharded tables para distriuir linhas entre os nodes/tablets;
  • https://twitter.com/rponte/status/1600190673566646273?s=20&t=NeV6IW9w64Cbz5cEJe0dTw
• Scalable Sequences with Postgres

Database sharding

• PlanetScale blog: Database Sharding

Example of using the MOD() function to partition the result set:

    SELECT t.*
      FROM tasks t
     WHERE t.status = 'PENDING'
       AND mod(t.id, :total_machines) = :machine_key   -- that's how we partition the tasks among the machines
  ORDER BY t.created_at
     LIMIT 50