Storing X Data: Schema Decisions You Can't Undo Later
Storing X Data: Schema Decisions You Can't Undo Later
Most X data projects start the same way: dump the JSON into a table, one row per tweet, get on with the analysis. It works for a week.
Then you re-run the collector and get duplicate rows. Then you notice a tweet's like count in your database is three weeks stale, and you can't tell when it changed. Then you want to know which posts gained engagement after someone quoted them, and discover your schema threw that information away on the first insert.
None of these are hard problems in advance. All of them are expensive to fix once you have real data, because the fix is a migration. This is about the decisions worth getting right on day one.
If you're still choosing how to fetch the data, the four options compared is the earlier question. This assumes you have JSON arriving and need somewhere to put it — there's a free demo endpoint if you want real field shapes to design against.
The decision that costs the most: one table or two
The instinct is one row per tweet, with the counts as columns:
-- the version everyone writes first
CREATE TABLE tweets (
id TEXT PRIMARY KEY,
author TEXT,
text TEXT,
created_at TIMESTAMPTZ,
like_count INTEGER, -- ← the problem
retweet_count INTEGER,
reply_count INTEGER
);
The problem is that tweet content is immutable and engagement counts are not. The text never changes. The like count changes constantly. Putting both in one row means every re-fetch either overwrites history or creates a duplicate, and you have no way to answer "how fast did this post grow?"
Two tables, instead:
CREATE TABLE tweets ( -- immutable: write once
id TEXT PRIMARY KEY,
author_id TEXT NOT NULL,
text TEXT,
created_at TIMESTAMPTZ NOT NULL,
conversation_id TEXT,
in_reply_to TEXT,
is_retweet BOOLEAN DEFAULT FALSE,
first_seen TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE tweet_metrics ( -- mutable: append a row per observation
tweet_id TEXT NOT NULL REFERENCES tweets(id),
observed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
like_count INTEGER,
retweet_count INTEGER,
reply_count INTEGER,
quote_count INTEGER,
view_count BIGINT, -- ← BIGINT, see below
PRIMARY KEY (tweet_id, observed_at)
);
Now re-fetching is free: content upserts to the same row, metrics append a new observation. Growth rate is a query rather than something you needed to plan for.
If you genuinely only ever need the latest number, keep the single table — but add an updated_at column, so at least you know how stale it is. The two-table split is for when "how did this change?" is a question you might ask later. In practice it usually becomes one.
Field-level decisions that bite
Tweet IDs are strings, always. They're 19-digit snowflake values that exceed the 2^53 limit of a double-precision float, which is what JSON parsers and JavaScript hand you by default. Store them as TEXT or BIGINT, never FLOAT or REAL, and never let them pass through a JSON layer that treats numbers as doubles.
The useful consequence: because snowflake IDs embed a timestamp, they sort chronologically as integers, and as strings while they're the same length. That gives you cheap ordering and cheap high-water marks without parsing dates.
view_count needs BIGINT. It's routinely orders of magnitude above the other counters — a post with 2,000 likes can have millions of views. A 32-bit integer column overflows at around 2.1 billion, which a viral post will reach.
Counts can go down. Accounts get deleted, users unlike, posts get removed. If you've written a constraint or an alert assuming monotonic growth, it will fire on normal behaviour. Don't add CHECK (like_count >= 0)-style assumptions about direction.
created_at is not ISO 8601. X's format parses with an explicit format string, not a naive ISO parser. Convert on write, store as a real timestamp type, and keep it in UTC — the field shapes are covered in reading the tweet object.
Upsert: making re-runs harmless
The single most useful property a collector can have is that running it twice does nothing the second time. That's one SQL clause:
import psycopg
from psycopg.rows import dict_row
UPSERT_TWEET = """
INSERT INTO tweets (id, author_id, text, created_at, conversation_id,
in_reply_to, is_retweet)
VALUES (%(id)s, %(author_id)s, %(text)s, %(created_at)s, %(conversation_id)s,
%(in_reply_to)s, %(is_retweet)s)
ON CONFLICT (id) DO NOTHING
"""
INSERT_METRICS = """
INSERT INTO tweet_metrics (tweet_id, like_count, retweet_count,
reply_count, quote_count, view_count)
VALUES (%(id)s, %(like_count)s, %(retweet_count)s,
%(reply_count)s, %(quote_count)s, %(view_count)s)
ON CONFLICT (tweet_id, observed_at) DO NOTHING
"""
def store(conn, tweets):
with conn.cursor() as cur:
cur.executemany(UPSERT_TWEET, tweets) # content: write once
cur.executemany(INSERT_METRICS, tweets) # metrics: one row per run
conn.commit()
DO NOTHING rather than DO UPDATE on the content table is deliberate. Tweet text doesn't change, so an update is either a no-op or a sign something's wrong. If you do want edits tracked (X does allow editing within a window), that's a third table of versions, not an overwrite that destroys the original.
Batch with executemany, and commit per batch, not per row. Per-row commits dominate runtime on anything large — you're paying a disk sync for each tweet. One commit per page of results is the right granularity, and it also means an interruption loses at most one page.
The part people skip: recording what you didn't get
A collector that only writes successes can't tell you where the holes are. Six weeks later, a gap in the data is indistinguishable from a quiet period — the same ambiguity that makes monitoring failures invisible.
CREATE TABLE collection_runs (
id BIGSERIAL PRIMARY KEY,
target TEXT NOT NULL, -- username or query
started_at TIMESTAMPTZ NOT NULL,
ended_at TIMESTAMPTZ,
cursor_end TEXT, -- resume point
rows_new INTEGER DEFAULT 0,
status TEXT NOT NULL, -- ok | partial | rate_limited | failed
error TEXT
);
This table costs almost nothing and answers the questions that actually come up: did this account's collection ever complete, where do I resume, and is this gap real or a failure. A status of partial is the important one — it marks a window you know is incomplete, rather than letting it look finished.
Scaling: when a single table stops being enough
Rough thresholds, from where the pain actually starts:
| Volume | What to do |
|---|---|
| Under ~1M tweets | One Postgres table, indexes on author_id and created_at. Don't over-engineer. |
| 1M–50M | Partition tweet_metrics by month — it's the table that grows per observation, not per tweet. |
| Above that | Keep relational for content, move metrics history to columnar storage (Parquet, ClickHouse). |
tweet_metrics is what grows, not tweets. One tweet observed hourly for a year is 8,760 metric rows. Sizing your thinking around the tweet count underestimates by orders of magnitude — and it's why partitioning the metrics table buys more than partitioning content.
Keep the raw JSON if you can afford it. A raw JSONB column, or the original responses in object storage, means a schema mistake is recoverable by reprocessing instead of re-fetching. Re-fetching costs API calls and may be impossible for deleted posts; reprocessing is free. This is the cheapest insurance in the whole design.
What to index, and what not to
CREATE INDEX ON tweets (author_id, created_at DESC); -- the common query
CREATE INDEX ON tweets (conversation_id); -- thread reconstruction
CREATE INDEX ON tweet_metrics (tweet_id, observed_at DESC);
That composite on (author_id, created_at DESC) serves "this account's recent posts," which is most of what anyone asks. A separate index on each column doesn't — the planner can't combine them as efficiently as one composite.
Don't index the text column with a btree. It's large, rarely matched exactly, and the index will be bigger than the data. If you need text search, that's a GIN index on a tsvector, and it's worth adding only once you know you need it.
Our API returns the field shapes these tables are built around, so the mapping is direct rather than something you reverse-engineer. Signing up with Google includes $0.20 in free credits and no card, which is enough to collect a few thousand tweets and see whether this schema fits your questions before you commit to it.
FAQ
How should I store tweets in a database? Two tables: immutable content keyed on tweet ID, and an append-only metrics table keyed on tweet ID plus observation time. That split is what makes re-running a collector harmless.
Should engagement counts be columns on the tweet row? Only if you'll never ask how they changed. Counts move constantly while text never does, so keeping them together forces you to choose between overwriting history and duplicating rows.
What data type should a tweet ID be?
TEXT or BIGINT. They're 19-digit values above the 2^53 float limit, so anything that treats them as a double will silently corrupt the last digits.
Why does view_count need BIGINT? Because it's routinely orders of magnitude larger than likes — a modest post can have millions of views, and a 32-bit column overflows around 2.1 billion.
Can engagement counts decrease? Yes. Unlikes, deleted accounts and removed posts all reduce counts. Don't write constraints or alerts that assume counts only go up.
What's a good primary key for a tweets table? The tweet ID alone. It's globally unique and stable. For the metrics table, use the composite of tweet ID and observation timestamp.
How do I make my collector idempotent?
ON CONFLICT (id) DO NOTHING on content, and a composite key on metrics. Then a re-run inserts nothing new rather than duplicating or erroring.
Why DO NOTHING instead of DO UPDATE? Because tweet text is immutable — an update is either pointless or a signal that something is wrong. If you want to track X's edit feature, use a separate versions table instead of overwriting.
Should I commit per row or per batch? Per batch. Per-row commits pay a disk sync for every tweet and will dominate your runtime. One commit per page also means an interruption costs at most one page.
How do I store the author — same table or separate? Separate, keyed on author ID, with the tweet holding a foreign key. Profiles change over time and repeating them per tweet both wastes space and makes "what was their follower count then?" unanswerable.
Should I keep the raw JSON? If you can afford the space, yes. It means a schema mistake is fixable by reprocessing rather than re-fetching — and re-fetching is impossible for posts that have since been deleted.
How do I handle X's created_at format? Parse it with an explicit format string rather than an ISO parser, convert to UTC, and store it as a real timestamp type. Don't keep it as text.
How do I detect gaps in my collected data? Record every run — target, cursor, row count, status. Without that table, a gap and a quiet period look identical, and you can't tell which you're looking at.
What does a 'partial' run status mean in practice? That you know the window is incomplete, usually from a rate limit or an error mid-pagination. Marking it is what lets you re-collect that window deliberately later.
When should I partition my tables? Around a few million rows, and partition the metrics table first — it grows per observation, so it outpaces the content table by whatever your sampling frequency is.
Which table actually grows fastest? The metrics table. One tweet observed hourly for a year produces 8,760 metric rows against one content row. Size your planning on observations, not tweets.
Postgres, SQLite, or something columnar? SQLite is genuinely fine up to a few million rows for a single-process collector. Postgres once you have concurrency. Columnar storage for metrics history once analytical queries dominate.
What indexes do I actually need? A composite on author plus date descending, one on conversation ID for threads, and one on tweet ID plus observation time for metrics. Add others only in response to a slow query you've measured.
Should I index the tweet text?
Not with a btree — the index would exceed the data size for little benefit. Use a GIN index on a tsvector if and when you need full-text search.
How do I reconstruct a thread from stored tweets?
Group by conversation_id and order by ID. Every reply in a thread carries the conversation ID of its first tweet, so one indexed lookup rebuilds the whole thing.
How do I store deleted tweets?
You keep what you already collected and mark it. Add a deleted_detected_at column rather than removing the row — the fact that something was deleted is often the interesting part.
How much disk does this use? Content is small; metrics are what accumulate. Estimate from observation frequency times tweet count times row width, and the metrics table will dominate by a wide margin.
Can I do this without a database at all? For one-off analysis, newline-delimited JSON or Parquet files are fine and avoid all this. The database earns its place when you're collecting continuously and querying across runs.
What's the one thing most people get wrong? Putting mutable counts in the immutable content row. Everything else here is adjustable later; that one requires a migration and usually a re-collection.
Do I need a developer account from X for this? Not for reading public data through a third-party API — you use that provider's key. Official API access is a separate arrangement, mainly needed for write operations.
How do I avoid re-fetching tweets I already have? Keep the highest tweet ID you've stored per target, and on each run discard anything at or below it before inserting. Because the IDs are time-ordered, that comparison is an exact "everything newer than this" filter — no timestamp parsing needed.
The two-table split is the one decision here worth making before you have data, because it's the only one that needs a migration afterwards. A Google sign-up includes $0.20 in free credits and no card, which is enough to load a real sample and check the schema against your own queries.