Generally speaking, data integrity is more important than performance, so denormalize1 only if you have performed measurements on representative amounts of data and the results indicate a strong need for better performance. More often that not, a normalized schema will perform just fine, especially if you got your physical design right (such as indexing). In … Read more
The answer depends on many factors like Postgres version, encoding and locale – LC_COLLATE in particular. The bare expression lower(description) LIKE ‘%abc%’ is typically a bit faster than description ILIKE ‘%abc%’, and either is a bit faster than the equivalent regular expression: description ~* ‘abc’. This matters for sequential scans where the expression has to … Read more
This has come up a few times recently, both on SO and on the PostgreSQL mailing lists. The TL;DR for your last two points: (a) The bigger shared_buffers may be why TRUNCATE is slower on the CI server. Different fsync configuration or the use of rotational media instead of SSDs could also be at fault. … Read more
The physical database size doesn’t matter. The number of records don’t matter. In my experience the biggest problem that you are going to run in to is not size, but the number of queries you can handle at a time. Most likely you are going to have to move to a master/slave configuration so that … Read more
I want to see which method is more efficient in terms of querying in the database. In a multi-tenant database, querying is only part of the problem. Other parts of the problem are cost, data isolation and protection, maintenance, and disaster recovery. These are significant; you can’t consider only query efficiency in a multi-tenant database. … Read more