Hello all, Adron here, your host on Hasura Coding. Today I’ve collected together a number of video shorts, extra details, and information about primary keys, primary key data types, and data types in general. In this post I’ve included links to mapping from Postgres types to JavaScript types via GraphQL and Hasura.
Primary Keys
Primary Key: A primary key in a relational database uniquely identifies the record among many records stored in a table.
Primary keys can be made up of one or more columns, of existing data, or made up data that serves only the purpose to identify the record itself. The following are the kinds of primary keys a table can have.
- Super Key — A super key is a single or multiple keys that make up a group of keys which identifies a row within a table.
- Candidate Key — A candidate key are attributes that uniquely identify rows in a table. A candidate key is a super key with none of the attributes repeated, thus a natural key within the existing stored data.
- Alternate Key — An alternate key is a column or group of columns that uniquely identify every row in the table. It is a key that isn’t particularly selected as a primary key, but can be used as one if the primary key weren’t available for some reason.
- Compound Key — A compound key has two or more columns included as the primary key of a table to uniquely identify each row of data.
- Composite Key — An artificial key (think of a generated autonumber, short ID, CUID, or UUID) that uniquely idenfies each record. Also referred to as a surrogate key, these keys are used when a natural key doesn’t exist in the schema of the existing table.
- Surrogate Key — See Composite Key.
I put together two videos related to specific primary keys: UUIDs & creating Short URL Safe UUIDs. Check those out on Youtube.
The short ID function I add in this short, is embedded below and provided in this gist for easy and quick access!
CREATE OR REPLACE FUNCTION gen_unique_short_id() returns text
language plpgsql
as $$
DECLARE
id text;
BEGIN
id := encode(gen_random_bytes(6), 'base64');
id := replace(id, '/', '_');
id := replace(id, '+', '_');
RETURN id;
END;
$$;alter function gen_unique_short_id() owner to postgres;
The above definitions and details I discuss in “A Hasura Bit — What is the UUID column type good for and what exactly is a UUID?” leave some additional details that should be added. UUIDs are great as surrogate keys for many situations, but there are some situations where they can cause significant problems.
For example. Some of the characteristics of UUIDs can become significant problems in higher scale scenarios or in certain hardware situations. Being the UUID is a largely random value and numbered value, the database engine attempts to order these during writes, which if there are too many writes that then require reordering immediately come in, it can cause disk thrashing. Something that in the end can become a significant performance issue! At the same time, 128-bit can be excessively large with large volumes of data just for the key that may not be needed.
These problems, as with any trade off, just emphasize the fact that for each and every scenario the pluses and minuses need to be measured carefully for the intended purpose and outcome for the mission at hand.
Other considerations with both the UUID and short ID above include the issue with writes incurring a read/write and possible disk thrashing if the insert volume gets too high too fast. Another issue for the short IDs is that there is a slightly higher possibility of duplicates being generated. However, in the sense of a high possibility it’s still absurdly low and statistically almost nonexistent. However, it doesn’t hurt to have a check and write a new key if a duplicate does occur to prevent that error. But the investment in that level vs. just dealing with the error might not be worth it, every project might have a different tolerance for a possible insert error of that sort. Maybe a retry is good enough?
Character Data & Numeric Data Types
In the next two shorts I tackle a few details about character data and numeric data in GraphQL, how it maps back against Postgres types and reference the key grid that maps Postgres types to their respective API types one would get in Hasura served GraphQL JSON result objects here, and below the videos for quick reference in this article.
Originally published at https://hasura.io on April 27, 2021.
