Re: Storing large documents - one table or partition by doc? - Mailing list pgsql-performance
| From | Dev Nop |
|---|---|
| Subject | Re: Storing large documents - one table or partition by doc? |
| Date | |
| Msg-id | CACjtUOQZf7k+Xs+kPoLoE2zb8n7E3aaOsiiBxT7ejOCb7bfQ=Q@mail.gmail.com Whole thread Raw |
| In response to | Storing large documents - one table or partition by doc? (Dev Nop <devnop0@gmail.com>) |
| Responses |
Re: Storing large documents - one table or partition by
doc?
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
|
| List | pgsql-performance |
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
pgsql-performance by date: