Re: Storing large documents - one table or partition by doc?

Thank you Mike & Jim for your responses.

> Are there other issues/requirements that are creating other performance
concerns
> that aren’t obvious in your initial post

Yes, there are a few things:

*1. Full document queries really are necessary*

> it’s too much data to show on a web page, for instance

The documents are like spreadsheets so the whole document must be in memory
to view a part of it or make a change. Client apps don't have to render the
whole document but the data must be there.

This means that the applications are sensitive to the size of ids. A
previous incarnation used GUIDs which was a brutal overhead for large
documents.

*2. The documents are graph structures*

The data structure is technically a property graph. The default text format
is hierarchical with cross links.

There are currently no requirements to query the graph structure of the
document so postgresql can be ignorant of the internal relationships. It is
possible for the API to return an unordered flat list of rows and require
the client to build the graph.

However, the most desirable data returned by the API would be an ordered
hierarchical format. I'm not sure if postgresql has a role building the
tree or whether to do it in code externally. I imagine using CTEs to
process a column containing an array of child ids is brutal but maybe its
more efficient than loading all rows into memory and processing it in code.
I guess doing it in code means the cpu load is on the api-server which can
be scaled horizontally more easily than a db server.

There is no interest in integrity constraints on element relationships. If
there were an application bug creating an invalid document structure, it
would be better to store it than reject it. The application can fix broken
documents but not data loss.

*3. Element ids are only unique within a document*

The element ids the application uses are not globally unique across
documents. Modelling what we currently have in a single table would mean
the primary key is a composite of (document_id, element_id) which I don't
believe is good practice?

Using current ids, a pseudo schema might look like:

CREATE TABLE document (
id serial primary key,
revision int,
name text,
root_element_id int);

CREATE TABLE element (
document_id int REFERENCES document(id),
element_id int,
data text,
children int[],
primary key (document_id, element_id));

The two main queries are intentionally trivial but any advice on indexes
would be helpful e.g.

a) Fetch a document without creating the tree:

select * from element where document_id = DOCID

b) Update an element:

update element
set data = "new data"
where document_id=DOC_ID and element_id=EL_ID

+ update history & increment revision

*4. Storing history of changes*

The application is to view change history, wind back changes, restore old
revisions and provide a change-stream to clients. Its not an initial
performance concern because its not part of the main workflow but history
tables will be much larger than the documents themselves but append only
and rarely queried.

Updating them would be part of the update transaction so maybe they could
become a bottleneck? A single edit from a client is actually a batch of
smaller changes so a pseudo schema supporting change-sets might look
something like:

CREATE TABLE changeset (
id bigserial primary key,
document_id int REFERENCES document(id),
user_id int REFERENCES users(id),
modified_at timestamp);

CREATE TABLE changes (
id bigserial primary key,
changeset_id int REFERENCES changeset(id),
change_type int,
element_id int,
old_value text,
new_value text);

CREATE TABLE history (
id bigserial primary key,
element_id int,
data text,
children int[],
valid_from bigint REFERENCES changeset(id),
valid_to bigint REFERENCES changeset(id));

Where [history] is used for fetching complete revisions and [changes] is
used to store the change stream to support winding recent changes back or
enable offline clients with old revisions to catch up.

*4. My Nightmares (fighting the last war)*

In a previous life, I had bad experiences with un-partitioned gargantuan
tables in Sql Server standard. Table-locking operations could take 24 hours
to complete and DBAs had to spend weekends defragging, rebuilding indexes,
performing schema migrations, data migrations or handling backups. It
always felt on the edge of disaster e.g. a misbehaving query plan that one
day decides to ignore indexes and do a full table scan... Every fix you
wanted to do couldn't be done because it would take too long to process and
cause too much downtime.

My nightmares are of a future filled with hours of down-time caused by
struggling to restore a gargantuan table from a backup due to a problem
with just one tiny document or schema changes that require disconnecting
all clients for hours when instead I could ignore best practice, create 10k
tables and process them iteratively and live in a utopia where I never have
100% downtime only per document unavailability.

thanks for you help