Designing DB partitions you don't have to babysit
explainanalyze.com
Designing DB partitions you don't have to babysit
1–10 of 20 posts
Re: Designing DB partitions you don't have to babysit
#2Or just use kdb+ and 1bn rows a day is par for the course.
Re: Designing DB partitions you don't have to babysit
#3For 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.
Re: Designing DB partitions you don't have to babysit
#4Or 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.
Re: Designing DB partitions you don't have to babysit
#5This 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.
Re: Designing DB partitions you don't have to babysit
#6This 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 cre…
Your primary key is there to uniquely identify a record. You need additional indexes on fields you will search on.
Re: Designing DB partitions you don't have to babysit
#7Or 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 prob…
With the other comment about kdb+ and this show a misread of the article: The article AND ANY use of a columnar DB show the same issue: Point lookups are badly pessimized with both partitions on other columns and/or columnar.
That is why columnar DBs, not matter how impressive, are not used by OLTP workloads (and here this article point you can undo the advantages without pay attention at the consequences of partitions).
Re: Designing DB partitions you don't have to babysit
#8Or 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 prob…
With the other comment about kdb+ and this show a misread of the article: The article AND ANY use of a columnar DB show the same issue: Point lookups are badly pessimized with both partitions on other columns and/or columnar. That is why columnar DBs, not matter how impressive, are not used by OLTP workloads (and here this article point you can undo the advantages without pay attention at the consequences of partitio…
[flagged]
Re: Designing DB partitions you don't have to babysit
#9This 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 cre…
Allow me to clarify a bit more.
You “search” for a record (perhaps based on username [and then you verify the password hash, but I digress]) and now you know the ID. Carry the ID in a session (the app doesn’t display this) and you can modify this specific user’s record. This is especially useful if you allow editing fields on which searches happen. Want to change a username? Great, you can.
If the username is your primary key, and you allow users to change them, there are edge cases and nuances and headaches … just use something immutable to identify records - autoincremented int, UUID, etc
Re: Designing DB partitions you don't have to babysit
#10[flagged]