Numeric
For exact calculation and/or numbers with many digits, choose numeric.
For small storage space and faster calculation, choose integer types (smallint, int, bigint) and floating-point types (real, double precision, float).
decimal type is an alias for numeric. ltsql's \d and lt_dump output decimal columns as numeric instead of decimal.
Timestamp
timestamp without time zone ignores the TimeZone parameter. The value is stored and returned as-is.
timestamp with time zone honors the explicit time zone in the input value or otherwise the TimeZone parameter. The input value is converted to UTC, and the output value is converted from the stored value, according to the time zone in effect.
Binary
Available methods to store binary data
bytea data type
Large object: Use filesystem-like open/close/read/write interface, data is stored in pg_largeobject, the user table column contains an OID value that points to a row in pg_largeobject.
External file: The application manages data in filesystems, or object storage and stores the file path in a table character column.
How to choose:
Need transactional (ACID) properties? -> bytea, large object
Handle 1 GB or larger column value? -> large object, external file
Need random and/or piecemeal access? -> large object, external file
Want best performance with 100 MB or larger column values? -> external file
Tips for using large objects
Do not use large objects. They can be problematic. Use bytea columns or external file storage such as an OS file system and object storage.
Removing lots of LOBs
Trying to remove many large objects within a single
transaction, e.g.,
"SELECT lo_unlink(lo_oid) FROM mytable;"
can fail with the following message:
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
Cause: When a large object is deleted, it is locked with Access Exclusive mode. Therefore, as many entries as the deleted LOBs are required in the lock table.
Solutions: Do either or both of:
Increase max_locks_per_transaction. The database server has to be restarted.
Delete LOBs in chunks, e.g., 100 LOBs per transaction.
Dealing with orphaned LOBs
An orphaned LOB is a large object whose OID does not appear in any oid or lo data column of the database.
There is no gapless sequence
A sequence produces gaps when:
The transaction rolls back: Because nextval() and setval() calls are never rolled back, allocated sequence values are not reclaimed.
Cached values are unused: If the caching is enabled for a sequence, nextval() preallocates the specified number of values and caches them in the session's local memory. Subsequent nextval() calls fetch values from the cache until the cache is empty, and then preallocate some values again. So, if the session ends without using all the cached values, those will be gaps.
The server crashes: Even with a NO CACHE sequence, you can see a gap in these steps: nextval() -> crash recovery -> nextval(). For performance, Lightdb does not WAL-log every fetching of a value from a sequence. nextval() WAL-logs a value 32 numbers ahead of the current value, and the next 32 calls to nextval() don’t WAL-log anything. As a result, some numbers appear to be skipped.
Lightdb Documentation