
Reddit's database has only two tables - kevinburke
http://kev.inburke.com/kevin/reddits-database-has-two-tables/
======
tzs
> Adding a column to 10 million rows takes locks and doesn’t work.

It does not take locks, other than for very briefly.

1\. Make a new empty table that has the same structure as the table you wish
to add a column to. Add your new column to the empty table.

2\. Put triggers on the old table that, whenever a row is added or updated,
makes a copy of the row in the new table or updates the copy already there.

3\. Run a background process that goes through the old table doing dummy
updates:

    
    
        UPDATE table SET some_col = some_col WHERE ...
    

where the WHERE clause picks a small number of rows (e.g., just go through the
primary key sequentially). Since you aren't actually modifying the table, all
this does is trigger the trigger on the specified rows.

4\. When you've hit everything with a dummy update, rename the current table
to a temp name, and rename the new table to the current table. This is the
only step that needs a lock.

There are tools for MySQL to automate much of this. There was a post either
here or on Reddit a while back about this which linked to them. I'm sorry but
I didn't save a link to it so you'll have to search if you want it.

~~~
saurik
Alternatively, you can use a database such as PostgreSQL, which stores
metadata about tables in other tables, allowing you to not only add a column
without locking the table but do so as part of a transaction with other
changes that can all be rolled back atomically on failure.

PostgreSQL also supports concurrent index creation, so if you realize later
you need an index on your amazingly large table you can have it built in the
background while you are still using the table. (Managing indexes were another
locking issue mentioned in the article.)

~~~
fdr
> PostgreSQL also supports concurrent index creation

I use this _all_ the time, and am flabbergasted how people can do without it.
I feel like migration frameworks should make it the default with Postgres.

It's too bad it can't be mixed with transactional DDL, but because indexes are
not logical changes, I don't really care as much, even if it is dissatisfying.

So, all in all, for those who want to take advantage of this feature in
Postgres:

Stop doing this:

CREATE INDEX foo ...

Start doing this:

CREATE INDEX foo CONCURRENTLY ...

For the cost of one keyword, your index additions can be a non-event.

~~~
fdr
True to forgettable SQL-ish (did you know that indexes are not addressed by
the SQL standard?) syntax, I got it slightly wrong:

    
    
        $ psql
        fdr=> \h CREATE INDEX
        Command:     CREATE INDEX
        Description: define a new index
        Syntax:
        CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
            ( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
            [ WITH ( storage_parameter = value [, ... ] ) ]
            [ TABLESPACE tablespace ]
            [ WHERE predicate ]
    

So, rather:

    
    
        CREATE INDEX CONCURRENTLY foo ....

------
prodigal_erik
This is called a <http://en.wikipedia.org/wiki/Entity-attribute-value_model>
or <http://en.wikipedia.org/wiki/Triplestore>. I think the author is
understating the price, though. There's a lot of existing software you could
reuse if your data was stored in more conventional relations, and "manually
enforce consistency" is a pipe dream. Your code has expectations about your
data, so in the abstract you do still have a schema, and not writing it down
merely prevents any tools from helping you keep your data sane over time. I've
seen Notes databases decay to the point that not even the dev team could
explain how a document got into its contradictory state nor how the apps
should (much less currently would) handle it. The few people diligent enough
to do completely correct work without a checkable schema, aren't the people
who would be tempted to try.

~~~
aed
I recently read The Pragmatic Programmers book _SQL Antipatterns: Avoiding the
Pitfalls of Database Programming_ (<http://pragprog.com/book/bksqla/sql-
antipatterns>) and chapter 6 talks specifically about this kind of DB design
(entity-attribute-value) and the benefits/pitfalls. It's a great read and I
highly recommend it.

~~~
mikegirouard
I bought that book after my first time developing on top of Magento.

Pure curiosity: Do you know of any other OSS projects that went down this
route?

~~~
bigiain
Yeah, I'd been thinking "Magento" from about halfway into that article.

Wordpress uses the EAV db pattern too (have a look at the wp_options and
wp_meta tables).

Of course, Magento and Wordpress use it in fundamentally different ways - so
this project I've got to build a combined search across a site with both
Magento (EAV for products) and Wordpress (non-EAV for posts/pages) in use, is
in that "it'll sit on the backburner until it becomes critical or somebody
else solves it for me first" state…

------
raldi
Note: Steve was exaggerating a bit, or this post misrepresents what he said. I
can think of 12 just off the top of my head.

That said, many of them fit the same pattern:

    
    
        reddit_thing_link
        reddit_thing_comment
        reddit_thing_user
    

...and each had a complement with "data" in place of "thing".

Though there were also ones like:

    
    
        reddit_xref_subscription_user_subreddit
        reddit_xref_vote_user_link
        reddit_xref_vote_user_comment
    

...for the many-to-many relations.

That accounts for most of the big ones (though there are also a handful of
smaller specialty tables, too.)

Also, the above is as of March 2011 -- a lot has moved to Cassandra since
then.

~~~
yaix
Thanks for the comment, that makes more sense. I was trying to figure out in
my head how to do the lookups to reander comments with votes and what comments
the user voted on all from one data table.

Also I guess the table schemas are slightly different for the different
"thing" tables.

~~~
raldi
Nope, all "thing" tables have exactly the same schema as each other. Ditto
"data" and "xref" tables.

------
ocharles
That quote is just painful to read, littered with FUD and not a single bit of
evidence to back it up.

You _should_ worry about the database because it's probably your canonical
storage of data, which for most of us is the most important part of our
product/service/whatever. A good schema enforces consist data, invariants, and
all sorts of other stuff that you don't want to be dealing with a manual (and
buggy) basis.

Schema updates do not need to be slow. They might not always be as elegant as
you hope but the big databases are improving on that front, and as tzs
mentions - there are tricks that can be employed. With the latest and greatest
PG, I believe we're even starting to get event triggers, so it may well be
possible to do schema updates with replication. I also have a feeling the
binary replication in PG 9 and up can even do it out of the box, with hot
standby to still allow responses. I'm not entirely convinced replication is a
backup solution, so maybe that was an operations antipattern. That's some
baseless assertion from me though :)

If deployments are a pain, work to alleviate pain. They are pretty mechanical,
even if involved, which lead very nicely to being automated.

Seriously, we're smart people, let's not throw at least 30 years of research
out the window in favour of glorified entity-attribute-value schemas.

------
zzzeek
Pretty sure Reddit has thousands of tables - last time I looked, it was really
hard to see this but this is how it seemed like it was working. 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). While I'm honored they
use my library (SQLAlchemy) for relational database access, their actual usage
of the relational DB couldn't be more...well... let's just say please don't
imitate their style. If you want to build a Reddit, use Mongo/Cassandra or
something like that. 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).

Edit: if any reddit devs want to correct me here, feel free, as I found the
reddit source extremely difficult to follow back when I looked.

~~~
jedberg
> 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.

~~~
zzzeek
> 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
Every "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
> "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?

------
kjhughes
This is a thin wrapper around a good, but two year old, High Scalability post:

[http://highscalability.com/blog/2010/5/17/7-lessons-
learned-...](http://highscalability.com/blog/2010/5/17/7-lessons-learned-
while-building-reddit-to-270-million-page.html)

which was discussed on HN recently:

<http://news.ycombinator.com/item?id=4467077>

and on HN long ago:

<http://news.ycombinator.com/item?id=1354542>

EDIT: Fixed "long ago" vs "recently". Thanks, sync.

------
ionforce
My fear with headlines like this is that people with no business working at
the scale that Reddit does will suddenly eschew years of best practice SQL and
by like "my dad's pizza shop CRM only needs two tables, JUST LIKE REDDIT".

Everyone please, use your brain before repeating such specific configurations.
Reddit is quite exceptional. Your burgeoning to-do list app is not.

------
rfurmani
Umm, assuming the github repo is what they actually use (i assume so given how
often it is committed to) there are two tables _per object_.
Reddit_thing_link, reddit_data_link, reddit_rel_thing_savehide, etc etc

------
lazyjones
Seriously? Sounds like a half-baked reimplementation of a
<https://en.wikipedia.org/wiki/Triplestore> to me ...

The post he refers to is more than 2 years old, things may have changed.

------
SideburnsOfDoom
They've built a database in their database. Also known as the inner platform
antipattern: <http://en.wikipedia.org/wiki/Inner-platform_effect>
[http://thedailywtf.com/Articles/The_Inner-
Platform_Effect.as...](http://thedailywtf.com/Articles/The_Inner-
Platform_Effect.aspx)

------
sjs
No, they have <thing> and <thing data> tables for each entity. This is a grave
misunderstanding of what was actually said.

------
t_hozumi
Datomic takes similar approach. A unit of Entity, Attribute, Value and Time is
called datom. Although in this approach we don't have to have schema, datomic
forces us to define schema for some benefits.

------
gioele
Basically they rediscovered RDF.

They should switch to an RDF triplestore, at least they would be able to
exploit some of the RDF-only optimizations and their validation tools.

------
SoftwareMaven
This is roughly the path I'm taking on my current project. There are some
things that have a consistent, "traditional" schema. However, many of the
feature-important things are being stored as JSON blobs. This means that I
don't have to worry about schema migrations when I add a new feature; instead,
I just define its JSON schema. This works particularly well because I don't
need to join on these items. The downside is that I do have to do occasional
manual processing on them (sum up all values of type FOO) that would be
trivial if everything were perfectly normalized.

~~~
SideburnsOfDoom
> many of the feature-important things are being stored as JSON blobs. ...
> when I add a new feature I just define its JSON schema.

Some no-sql stores work like that. You may be better off using one of them.

~~~
SoftwareMaven
I looked at that (and have done that in the past), but I really missed some of
the ORM features I got with relational. I would up writing more glue code than
I liked for things that would otherwise be free. This is my compromise world
(and Postgres is getting better at handling its JSON).

------
cageface
I used to work at a company with a huge LDAP database that had been growing in
uncontrolled and organic ways for years. If you've never worked with LDAP
before, it's basically a big key-value store, with some optional schema
enforcement. We spent an inordinate amount of time manually cleaning it up and
trying to enforce some kind of data integrity and we were always bemoaning the
lack of a real structural schema.

I think the kind of ad-hoc generic data typing described in this article is
sometimes the right solution but it comes at a cost.

------
pud
I wish I read this article a year ago before I started developing my latest
app. I have that exact problem, where adding a column to a 10M+ row table
takes over an hour. So instead I end up with "things" tables all over the
place.

Sigh.

~~~
MBCook
The last company I worked at made an application that used the Entity-
Attribute-Value pattern in the database. The stated reason was to be dynamic,
so we didn't have to worry about adding new columns and the associated
downtime (assuming the DB got huge, which of course it would because this app
would surely be a huge success). We had that problem on our main app (with 10s
of millions of rows) where adding a column was always tricky, so I think
management over-corrected. The other supposed win was that since the model
didn't change, the code didn't need to be updated.

The data that was being stored fit into the relational model pretty well. But
thanks to E.A.V. it was very difficult to query. The kinds of questions we
often looked at (how many records from this zip code) would have been trivial
without the E.A.V. Today you might use a NoSQL database (which were just
starting to get noticed at the time), but in reality it fit into MySQL just
fine.

The real sad part is, we never used that functionality in the 2-3 years after
it was developed while I was there. The app wasn't big enough for adding
columns to take much time at all. All that "flexibility" we needed? We didn't
use it, because it would have taken additional time to implement the
additional front-ends and update the other backend systems.

Even if we wanted to keep things smaller, we could have gone with a table per
type of record (record_type_one, record_type_two, etc) instead of one big
records table. That would have made schema changes easier.

And of course, the code did need to be updated. Always. Sure there were no new
columns that might cause problems if they didn't have default values, but you
can never make changes to an app without code changes. We still had to
implement the new interface. We had to implement the code to post that new
kind of record to the systems it got processed by. Making the code handle the
DB changes would have taken less time than the day or two a designer might
work on the front end. It never would have been a bottleneck.

~~~
amitdugar
Looks like you guys did a bit of a premature optimization there. I know how
painful it can get :(

~~~
MBCook
That's exactly what it was.

It was designed to replace a growing set of systems that were all tiny forks
of the same basic code base over a couple of years. Managing all that had
become a mess, and it did need to be replaced. But there wan an opportunity
and we ended up trying to reach for the stars when we should have aimed a bit
lower.

The irony is that after the system had been in production for a few months, we
noticed it had terrible performance that was getting worse with load.

It turned out the programmer who had written some parts of the system had it
recalculating _way_ too much data, things that didn't apply to what was going
on and that couldn't have changed. It was probably an artifact from initial
development (I'll do it this way to get it going, then cleanup later...). Once
we caught and fixed that, it was much much faster.

That was the only optimization I remember it needing while I was there.

