This story is a just another example of what I call
Using varchar(255) for everything won’t make you a sorcerer.
Every database has many different types. They are there on purpose, not by accident. I’ve seen that too many times: a programmer (most companies don’t have DBAs for that) creates a database strsucture using only ONE type. Sometimes they use more… but hardly ever they use all those nice features they database engine provides.
This is a common pattern:
Pay thousands of dollars for the database licence – use it like a CSV file.
A couple of weeks ago I had quite a strange situation: a PostgreSQL table with small number of huge geometries. A program had to query the table really often, so the speed was a crucial thing.
The table was really small, but the spatial query, using PostGIS, was very slow.
A couple of days ago a colleague of mine had quite a normal problem. A query was slow. The whole application was written in Java, using Hibernate, all the tables were really small.
There were about 30 rows in one table. 10 rows in another. One small join. One call of a PostGIS function.
The query took several seconds to return just about 20 rows. Quite bad.
In a simple project I work sometimes on, I needed to generate a lot of random data in a PostgreSQL database. I’ve got a couple of functions for that, but all of them are based on the simple random() function.
The random() function returns value from the range [0, 1):
Let’s imagine a business requirement that there should be only one active row in a table. Active means that the column is_active is set to true. There can be many rows with the false or null values. The row with the only one value can be only one.