IS 'simple count of users who have seen the post' The migration to make it happen isn't too difficult:ĪLTER TABLE posts ADD COLUMN seen_by_count It's obvious, and it's almost guaranteed to work - but maybe not work well. The easiest obvious way to do this is to maintain a counter on every tuple in the posts table. Try #1: The naive way, a simple counter on every Post Like any good tinkerer we'll start with the simplest solutions and work our way up in complexity to try and get to something outstanding, testing our numbers as we go. Well we can't pat ourselves for our miraculous and suspiciously simple DB architecture all day, let's move on to the task at hand. Scaling up is the new (and old) scaling out. This basic setup has taken the (imaginary) company quite far - even though the posts table has millions and millions of entries, Postgres chugs along and serves our queries with impressive speed and reliability. Create the posts table CREATE TABLE posts ( Uuid uuid NOT NULL DEFAULT uuid_nonmc_v1(),Ĭreated_at timestamptz NOT NULL DEFAULT NOW()
Id bigserial PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY , Create the users table CREATE TABLE users ( Create a email domain to represent and constraing email addresses CREATE DOMAIN emailĬHECK ( LENGTH( VALUE ) <= 255 AND value ~ ) ĬOMMENT ON DOMAIN email is 'lightly validated email address'
We've got the following simple table layout:ĬREATE EXTENSION IF NOT EXISTS uuid - ossp Let's see how you might solve this problem, as that imaginary DBA. Sure, this scenario isn't real, but it could be - that last part about Postgres definitely is. Postgres's open source pedigree, robust suite of features, stable internals, and awesome mascot Slonik make it a strong choice, and it's what you're already running. There's been a complexity freeze at the company, so you're not allowed to bring in any new technology, but you don't mind that because for v1 you would have picked Postgres anyway. Well, it's time to figure out how you're going to do it.
You start to explain why it will be non-trivial, but the meeting ends before you can finish. "It sounds pretty simple" a colleague at the meeting remarks - "just an increment here and an increment there and we'll know which posts are seen the most on our platform". Why VARCHAR(256)? No particular reason, but you don't have time to get hung up on that or ask why - you just found out that the priority this quarter is tracking content views across all posts in the app. Your startup is seeing eye-boggling growth because everyone loves fitting their hot-takes in posts restricted to VARCHAR(256). You're head DBA at the hot new social site, SupaBook. For a quick & dirty prototype, use hstore, which also performs the best with integer IDs. Tl dr: Use HyperLogLog, it's a reasonable approach with great trade-offs and no large architectural liabilities.
0 kommentar(er)
