Postgresql – Should I not use camelCase in the column names

database-designnaming conventionpostgresql

I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase.

In my PostgreSQL database, I currently have all my columns named following a camelCase convention, but I am thinking: is that the best idea?

Should I use snake_case in the database columns and convert them in the back-end?

Best Answer

Use lower case in Postgres. Postgres folds identifiers to lowercase, unless you double-quote your identifiers. To make Postgres operations easier, use lower_snake. If you need to bind to an API, etc, you can create a view with “CamelCase” aliases. For example, we do this with data stored in Postgres that is pulled by Domo.

Related Solutions

Why do people recommend not using the name “Id” for an identity column

The table name prefix has very good reasons.

Consider:

TableA (id int identity, stringdata varchar(max))

TableB (id int identity, stringdata varchar(max))

We want to DELETE from TableA records that exist in both tables. Easy enough, we will just do an INNER JOIN:

DELETE a
FROM 
  TableA A
INNER JOIN 
  TableB B
    ON b.id = B.id

....and we just wiped out all of TableA. We inadvertently compared B's ID to itself - every record matched, and every record got deleted.

If the fields had been named TableAId and TableBId this would be impossible (Invalid field name TableAid in TableB).

Personally I have no issue with using the name id in a table, but it's really a better practice to preface it with the table name (or entity name, if TableA were people then PeopleId would work fine too) to avoid accidentally comparing to the wrong field and blowing something up.

This also makes it very obvious where fields come from in long queries with lots of JOINs.

PostgreSQL Indexing – Should Non-Key Columns Be Indexed?

The selection of enough indexes is often difficult. In your case it should be useful to create two indexes.

You should only create one index with both columns if your query always include the first column as a condition:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id [AND "OWNER_B_ID" = owner_b_id]

The whole B-Tree is built upon the order of columns in the index! You can't fully use a multi-column index on a, b on the following queries:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id OR "OWNER_B_ID" = owner_b_id
SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = "OWNER_B_ID"

If you're only using equality checks you might consider a hash index. But postgresql has some disadvantages you should check first.

On other dbms you should consider adding additional columns in the index as data. This would be useful if you query these specific columns and not * because the dbms wouldn't need to feed the data from the table after using the index.

An important factor: indices fragment over the time (unless you aren't performing any insert/update/delete on the table). Please check whether your dba has some optimization operations installed.

Pleae check the documentation for additional options like FILLFACTOR or partial indexes: http://www.postgresql.org/docs/9.3/static/sql-createindex.html

Best Answer

Related Solutions

Why do people recommend not using the name “Id” for an identity column

PostgreSQL Indexing – Should Non-Key Columns Be Indexed?

Related Question