Birbla

Discussion summary

The discussion covers strategies for database partitioning and handling large datasets, mentioning tools like kdb+, Snowflake ID, UUID7, and ClickHouse. Different approaches to primary key design and indexing in PostgreSQL and MySQL are also debated.

What the discussion says

  • Using kdb+ for high-volume data ingestion is effective.
  • Snowflake ID and UUID7 are recommended for distributed IDs.
  • ClickHouse is suitable for daily data warehousing.
  • Partitioning by auto-incremented ID can be beneficial.
“kdb+ handles 1bn rows a day easily.”
— leprechaun1066
“Snowflake ID patterns translate well to UUID7.”
— piterrro

Join the discussion

Write your take first — we'll ask for email only when you're ready to publish.

Sign in
  • Hacker News
  • Or just use kdb+ and 1bn rows a day is par for the course.
    by leprechaun1066
  • For anyone interested in the topic I suggest reading about snowflake id [https://en.wikipedia.org/wiki/Snowflake_ID] or uuid7 the patterns from the article translate cleanly. The bigint is 64 bytes where uuid is 128. There are other caveats but its all about tradeoffs.
    by piterrro
  • Or you could just warehouse the daily data into something like ClickHouse and start fresh every day. It's built for this kind of workload and has demonstrated some absolutely insane analytical performance at massive scale. We're currently running it on an $170/month VPS, querying over 500+ billion rows daily without any issues. At that point, partitioning an ever-growing OLTP table starts looking like the harder problem.
    by djfobbz

  •   The application probably still treats id as unique, but nothing in the schema guarantees it. And you can’t recover the guarantee with a separate UNIQUE (id) constraint: both MySQL and PostgreSQL require every unique constraint on a partitioned table to include the partition key columns. The uniqueness property has effectively been traded away.
    Not really?

    MySQL has AUTO_INCREMENT [1]

    PostgreSQL has SERIAL [2] and CREATE SEQUENCE [3]

    What am I missing?

    [1] https://dev.mysql.com/doc/refman/8.4/en/example-auto-increme...

    [2] https://www.postgresql.org/docs/18/datatype-numeric.html#DAT...

    [3] https://www.postgresql.org/docs/18/sql-createsequence.html

    by Insimwytim
  • This is a topic that interests me a lot but there's a lot I find surprising since I finally started working with postgres dependent apps. Why for example is the id a good primary key? Joins are not uncommon, but I don't have anyone searching on id in my application and it is not even supposed to be user visible. I would think every possible user search would look at all partitions indexes if I did this instead of creation date.
    by yasaheblasa

Explore Birbla archives