111–116 of 116 posts
Very nice. Did they migrate into Amazon RDS while doing this? For smaller projects I've stopped doing the self managed postgresql thing. The pricing is higher (75%?) for RDS for some use cases but can be worth it. Going to try RDS Proxy next.
Thanks! We stuck with plain EC2. RDS has a limit of 80,000 provisioned IOPS and our read replicas on Postgres 9.6 would regularly hit near double that during peak
Earlier quoted context omitted.
You're not the only one. There are lots of better alternatives to SQL databases for most use cases (I'm lucky enough to have worked in some places where SQL datastores were the exception rather than the rule). But it takes a long time for cultural change to happen.
Would you mind mentioning some good options? I've always been interested in databases, but find it hard to know which ones to learn more about and when they'd actually be worth investing in (especially since it's hard to build knowledge from toy projects).
This is a very difficult thing to do. Very impressive. I have so many questions but my number one is: were you able to evaluate alternatives to your existing vertical scaling based setup? For example, cockroachdb, multi-master postgres, using sharding instead of a single DB, etc. At that database size, you are well past the point in which a more advanced DB technology would theoretically help you scale and simplify y…
basically what paulryanrogers said. We thought about migrating to Citus, but I don't have a good idea of how to shard our dataset efficiently. If we were to shard by user id, then creating a match between two people would require cross-shard transactions and joins. Sharding by geography is also tough because people move around pretty frequently.
That does seem like a fun exercise :).
The general rules of the game are: You can only scale up throughput of queries/transactions that only access 1 shard (some percentage going to 2 shards can be ok). You can only scale down response time of large operations that span across shard since they are parallelized. You should only join distributed tables on their distribution column. You can join reference tables on any column.
The thing that comes to mind is to use a reference table for any user data that is used to find/score matches. Reference tables are replicated to every node and can be joined with distributed tables and each other using arbitrary join clauses, so joining by score or distance is not a problem, but you need to store the data multiple times.
One of the immediate benefits of reference tables is that reads can be load-balanced across the nodes, either by using a setting (citus.task_assignment_policy = 'round-robin') or using a distributed table as a routing/parallelization scheme.
CREATE TABLE profiles (
user_id bigint primary key,
location geometry,
profile jsonb
);
SELECT create_reference_table('profiles');
CREATE TABLE users (
user_id bigint primary key references profiles (user_id),
name text,
email text
);
SELECT create_distributed_table('users', 'user_id');
-- replicate match_score function to all the nodes
SELECT create_distributed_function('match_score(jsonb,jsonb)');
-- look up profile of user 350, goes to 1 shard
SELECT * FROM users u, profiles p WHERE u.user_id = p.user_id AND u.user_id = 350;
-- find matches for user #240 within 5km, goes to 1 shard
SELECT b.user_id, match_score(a.profile, b.profile) AS score
FROM users u, profiles a, profiles b
WHERE u.user_id = 240 AND u.user_id = a.user_id
AND match_score(a.profile,b.profile) > 0.9 AND st_distance(a.location,b.location) Where it gets a bit more interesting is if your dating site is opinionated / does not let you search, since you can then generate matches upfront in batches in parallel.
CREATE TABLE match_candidates (
user_id_a bigint references profiles (user_id),
user_id_b bigint references profiles (user_id),
score float,
primary key (user_id_a, user_id_b)
);
SELECT create_distributed_table('match_candidates', 'user_id_a', colocate_with :='users');
-- generate match candidates for all users in a distributed, parallel fashion
-- will generate a match candidate in both directions, assuming score is commutative
INSERT INTO match_candidates
SELECT a.user_id, b.user_id, match_score(a.profile,b.profile) AS score
FROM users u, profiles a, profiles b
WHERE u.user_id = a.user_id
AND match_score(a.profile,b.profile) > 0.9 AND st_distance(a.location,b.location) CREATE TABLE interests (
user_id_a bigint references profiles (user_id),
user_id_b bigint references profiles (user_id),
initiated_by_a bool,
mutual bool,
primary key (user_id_a, user_id_b)
);
SELECT create_distributed_table('interests', 'user_id_a', colocate_with :='users');
-- 240 is interested in 350, insert into 2 shards (uses 2PC)
BEGIN;
INSERT INTO interests VALUES (240, 350, true, false);
INSERT INTO interests VALUES (350, 240, false, false);
END;
-- people interested in #350, goes to 1 shard
SELECT * FROM interests JOIN profiles ON (user_id_b = user_id) WHERE user_id_a = 350 AND NOT initiated_by_a;
-- it's a match! update 2 shards (uses 2PC)
BEGIN;
UPDATE interests SET mutual = true WHERE user_id_a = 240 AND user_id_b = 350;
UPDATE interests SET mutual = true WHERE user_id_a = 350 AND user_id_b = 240;
END;
-- people #240 is matched with, goes to 1 shard
SELECT * FROM interests JOIN profiles ON (user_id_b = user_id) WHERE user_id_a = 240 AND mutual;
CREATE TABLE messages (
user_id_a bigint,
user_id_b bigint,
from_a bool,
message_text text,
message_time timestamptz default now(),
message_id bigserial,
primary key (user_id_a, user_id_b, message_id),
foreign key (user_id_a, user_id_b) references interests (user_id_a, user_id_b) on delete cascade
);
SELECT create_distributed_table('messages', 'user_id_a', colocate_with :='interests');
-- user 350 sends a message to 240, goes to 1 shard
INSERT INTO messages VALUES (240, 350, false, 'hi #240!');
-- user 240 sends a message to 350, goes to 1 shard
INSERT INTO messages VALUES (240, 350, true, 'hi!');
-- user 240 looks at chat with user 350, goes to 1 shard
SELECT from_a, message_text, message_time
FROM messages
WHERE user_id_a = 240 AND user_id_b = 350
ORDER BY message_time DESC LIMIT 100;
(Citus engineer who enjoys distributed systems puzzles)
Earlier quoted context omitted.
If data is sharable, it doesn't mean that it is trivial. In your example with shards by user, simple message sent between users in app becomes are very non trivial dance to be done reliably.
If you recognize that there isn't a good automated solution for the DB to smartly join messages than it becomes a fairly straightforward problem once again. e.g. the simple solution is to denormalize the table and have each message keyed by recipient. In a dating app you'll roughly double your message count this way, and even in most messenger apps the proportion of messages sent person to person is likely the most s…
Both your schemas require writes to at least 2 shards transactionally.
Earlier quoted context omitted.
basically what paulryanrogers said. We thought about migrating to Citus, but I don't have a good idea of how to shard our dataset efficiently. If we were to shard by user id, then creating a match between two people would require cross-shard transactions and joins. Sharding by geography is also tough because people move around pretty frequently.
Sharding a matching engine is indeed pretty hard, and requires redundancy and very deliberate data modelling choices. That does seem like a fun exercise :). The general rules of the game are: You can only scale up throughput of queries/transactions that only access 1 shard (some percentage going to 2 shards can be ok). You can only scale down response time of large operations that span across shard since they are par…
It sounds like making user profiles a reference table would solve the cross-shard join problem, but what would the performance implications look like?
The docs just say that it does a 2PC, which I'm assuming won't perform very well in a high-write workload
Earlier quoted context omitted.
still, changes between 9.6 and 12 are _numerous_, both in features and performance: llvm based query compilation, CTE de-materialisation, proper procedures, and that's just off the top of my head. i wish the process for upgrading postgres were easier/more dynamic. i'm sure plenty of people are still using versions 9.6 or earlier.
The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code? And if you didn't adapt: Would your code still running after upgrading (backwards compatible)? I'm asking as a dev mostly working with MS-SQL and seriously considering moving to PostgreSQL one day.
testing the llvm-based optimiser (in postgres 11) showed little benefit, and in a few circumstances slowed things down. had i had more time to poke around, i'd have probably got it to a place where it represented an improvement, but, up until i left that role, it was a feature that remained switched off - postgres doesn't generally introduce features that represents "breaks" in code.
having not had _that_ much experience with MS-SQL, i'd still recommend the switch. looking through the things in postgres that i take for granted vs. what's in MS-SQL, i don't think i could cope with not having postgres :)