In Oracle, NULL values are not indexed, i. e. this query: SELECT * FROM table WHERE column IS NULL will always use full table scan since index doesn’t cover the values you need. More than that, this query: SELECT column FROM table ORDER BY column will also use full table scan and sort for same … Read more
Updated to 2.9 : autoConnectRetry simply means the driver will automatically attempt to reconnect to the server(s) after unexpected disconnects. In production environments you usually want this set to true. connectionsPerHost are the amount of physical connections a single Mongo instance (it’s singleton so you usually have one per application) can establish to a mongod/mongos … Read more
I try to avoid text only answers; including code is best practice but the question about storing images comes up frequently and it’s not really covered in the documentation so I thought it should be addressed at a high level. Generally speaking, Realm is not a solution for storing blob type data – images, pdf’s … Read more
While there is no index skip scan in Postgres yet, emulate it: WITH RECURSIVE cte AS ( ( — parentheses required SELECT product_id FROM tickers ORDER BY 1 LIMIT 1 ) UNION ALL SELECT l.* FROM cte c CROSS JOIN LATERAL ( SELECT product_id FROM tickers t WHERE t.product_id > c.product_id — lateral reference ORDER … Read more
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 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
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
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