4 ms·
> It has "thing"/"data" tables for every subreddit - created on the fly (a crime for which any DBA would have you put to death, normally). That's not correct.
by jedberg 14y ago
> It has "thing"/"data" tables for every subreddit - created on the fly (a crime for which any DBA would have you put to death, normally).
That's not correct. There in't "table" for a subreddit. There is a thing/data pair that stores metadata about a subreddit, and there is a thing/data pair for storing links. One of the properties of a link is the subreddit that it is in. Same with the comments. There is one thing/data pair for comments and the subreddit it is in is a property.
> They'd very likely have done so themselves if NoSQL products were mature when they first developed their platform (I am vaguely recalling/guessing here on that one).
Actually, still today I tell people that even if you want to do key/value, postgres is faster than any NoSql product currently available for doing key/value.
- scragg 14y agoHow do you handle the size of the table (which I assume has many rows). Do the inserts start to slow down as it grows? How much RAM did you have for a master DB instance? Thanks for any input!
- jakejake 14y agoThe main issue I run into with storing key/values in MySQL/Postgress is querying those values if they hold complex data (json, serialized data, etc) and the NoSQL products have features that let you dig into those meta fields when querying. (I haven't used a lot of NoSQL products yet, just dabbled with them) Do you guys know of good techniques to do that with a traditional database like MySQL? I know MySQL has some XML parsing features if you store meta data as XML. I've experimented with it but never used it in production. I sometimes put JSON into a column for dynamic data. Usually I only do that for fields that I know won't need to be queried but that occasionally comes back to haunt me.
- gizzlon 14y agoDon't know for MySQL, but postgres just added some support for JSON in the latest (beta) versions. Haven't used it and I don't know what it's capable of..
- zxoq 14y agoThe support for JSON is only validation, otherwise it's just like a plain text column. A better choice would be to use a hstore column, which is sort of like JSON but we different syntax. Postgres supports indexing hstore subfields, querying on them etc.
- deleted 14y ago[deleted]
- wisty 14y agoPostgres has XML, binary, JSON, geometry ... And of course, hstore.
- masklinn 14y agoAnd array.
- zzzeek 14y ago> That's not correct. There in't "table" for a subreddit. There is a thing/data pair that stores metadata about a subreddit, and there is a thing/data pair for storing links. One of the properties of a link is the subreddit that it is in. Same with the comments. There is one thing/data pair for comments and the subreddit it is in is a property. Having a bit of trouble parsing this, but I think you mean that the "thing/data" tables are per type, where type is "subreddit", "comment", "links". Which would indicate a fixed schema. Can you clarify if Reddit just has a fixed number of tables? I remember seeing some "table.create()" in there but I wasn't sure what I was looking at.
- wisty 14y agoEvery "thing" (a single table) has an attribute called "type". Type is a string, and is something like "comment" or "post" or "badge". "table.create()" doesn't create a new table, I think it creates in index on "type", so you can treat it a bit like a separate table. Yes, it is not a "real" database. It's a key-value store, which doesn't lose data.
- zzzeek 14y ago> "table.create()" doesn't create a new table, if its a sqlalchemy.schema.Table, create() emits DDL for "CREATE TABLE" to the database (trust me, I wrote it). I'm guessing "table" here is some other object local to the reddit codebase. anyway, how many "thing" tables are there total?
- jedberg 14y agoYou are correct that reddit has a fixed number of tables (waves hands a bit). There is one table per type, so when a new type is created, a new table is created. That rarely happens. The reason you see those create statements in the code is for bootstrapping for open source users -- the first time they run reddit, it creates the tables.