DB Architecture & Conventions

[ctapobep]

Naming

• In case we have to support Oracle some day: any name (table, index, constraint, column, etc) must be <= 30 characters
• Since people keep forgetting to uppercase names now and then, let's abandon this ideas - just lowercase names everywhere (Peaksel will keep using uppercase since it started that way). Don't forget to use exactly the same letter case in SQL queries too. PG doesn't care, but some DBs on some OSes (e.g. MySQL on Windows) are case sensitive. It's better to be as DB-neutral as possible.
• Foreign key names: fk_table_a__table_b (this is a BEM-like convention where the separator between 2 entities is double underscore)
• Index: idx_table_name__col_name, Unique constraint: uq_table_name__col_name. Composite indices that include multiple columns are more challenging. Maybe: idx_table_name__col_name1__col_name2 or idx_table_name__colname1_colname2?

Handlig CLOBs

Again, on the off chance we have to support Oracle some day: CLOBs cannot be 8000 symbols. So for CLOB fields we need to add Java logic to add spaces to get to 8001. There's a bug in Oracle JDBC Driver (?) which confuses the encoding and suddenly starts to use UTF16 for 8000 CLOBs which screws everything up.

Indices

Most Foreign Keys should have indices. Otherwise some JOINs will be slower and DELETEs too (when deleting a record DB has to check if it's referenced by some FKs).

If you suspect a full table scan ('seq scan' in terms of Postgres), use this to find which table is scanned:

SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables;

Sorting by ID

Usually, when DB was just created, selecting records w/o sorting returns them in the same order they were inserted. But in PG this isn't always the case. So if the order is important, you must add order by id explicitly.

Cascades

(More research may be needed) It seems like DBs handle "on cascade=delete" just fine, so prefer those cascades instead of Hibernate's cascades. DB cascades should be faster. Of course these should be used with cautious - only for cases when child object can't exist without the parent.

Transactions

We mostly work in Snapshot Isolation level. Java and PG call this isolation level Repeatable Read, but in reality in PG it corresponds to Snapshot level. Snapshot level is activated when connection is created by C3P0 DB Pool, see DbConnectionCustomizer.java.

Repeatable Read has problems with race conditions that can result in dirty writes (when we override the same row in parallel transactions). While on Snapshot level one of the transactions will get:

ERROR: could not serialize access due to concurrent update

How to use Snapshot level:

• If we get a tx serialization error, we'll have to restart the tx. Need some generic mechanism to restart processing of user requests.
• If a parallel tx commits data, we won't see those changes. So if we started a long-running tx and want to see fresh data, we need to start a yet another tx to get them.
• We must be careful with Hibernate Sessions - if we create Nested Transactions, this basically takes a separate DB connection and creates a separate Hibernate Session. If we associate the same entity with 2 sessions - both of them are going to flush the changes, and one of the transactions will fail with "couldn't serialize access".

Explicitly changing Isolation Level in code

It's possible that 2 parallel transactions update completely irrelevant data. Getting an error in such situations is very unfortunate. If we know what we're doing, we can explicitly lower the isolation level, at least for one of the transactions - at least this way only 50% of cases will result in the error.

Caution

We should prefer doing this with Spring's @Transactional as it reverts the isolation level back to the original state after the tx completes. Otherwise, do it in try-finally block. If at some point we forget to revert the isolation level back, the results will be disastrous - we'll gradually turn all connections into Read Committed without realizing it.