91
votes
Storing a re-orderable list in a database
Same answer from here https://stackoverflow.com/a/49956113/10608
Solution: make index a string (because strings, in essence, have infinite "arbitrary precision"). Or if you use an int, increment ...
- 1,536
39
votes
How can I efficiently diff a CSV file against a database?
Since your goal is to produce a list of changes, not to change the stored records, the way to go is to simply export the database to text and wrangle it once to get into exactly the same CSV format. ...
- 110k
31
votes
Accepted
What are the best practices around retiring obsolete database columns?
If you want to keep the data, then it's not obsolete. Just leave it where it is. It's fine if some class mapped to a table doesn't map every column.
- 33.7k
22
votes
Accepted
How can I efficiently diff a CSV file against a database?
Since the roundtrip seems to be the issue, you could:
either opt for a local solution, with the scaling issue you mentioned. (You could still try to split the task across several local nodes, each ...
- 79.9k
15
votes
Storing a re-orderable list in a database
OK I face this tricky problem recently, and all the answers in this Q&A post gave many inspiration. The way I see it, each solution has its pros and cons.
If the position field has to be ...
- 711
15
votes
Storing a re-orderable list in a database
I have seen people using a self-reference to refer to the previous (or
next) value, but again, it seems like you would have to update a whole
lot of other items in the list.
Why? Say you take a ...
- 251
12
votes
Accepted
Do I really need triggers for relational database, for example, PostgreSQL?
It depends on what kind of application system you are building:
if you are creating an application-centric system which contains just one main application, with a dedicated database specifically for ...
- 212k
9
votes
Accepted
Django web app: how to save thousands of variables per user
Using a column per question makes it difficult to add more questions later.
Instead, model the relation between users and questions in your table. I.e. the questions are encoded explicitly as data, ...
- 135k
9
votes
How can I efficiently diff a CSV file against a database?
There are already some good answers, but here's another possibility: if you could sort the CSV file by key and add a similar ORDER BY to the SQL query, you could compare the rows obtained from the ...
8
votes
What are the best practices around retiring obsolete database columns?
OK so your situation is that you want the old rows to have property C but the new ones not.
This is equivalent to having a class inheritance relationship
class All
{
string A;
string B;
}
...
- 78.6k
7
votes
HOWTO: Full-text search over an encrypted database?
You don't.
The whole point of messaging privacy is that nobody except the sender and intended recipient are able to decrypt the messages. Not the platform provider, not their sysadmin, not some spy ...
- 17.1k
6
votes
Django web app: how to save thousands of variables per user
You should normalise your database schema. This means that instead of just having a User table, you would also have a Questions table containing information about each question, then you would also ...
- 617
6
votes
Accepted
Sharding rule updating on DB scaling
Starting from V11,
Postgres supports Declarative Partitioning where you can divide the table into partitions with built-in Hash partitioning strategy (modulus or round-robin).
It also allows you to ...
- 3,242
6
votes
HOWTO: Full-text search over an encrypted database?
Obviously, you shouldn't have the key to decrypt messages, as pointed out by others. So how do you search on encrypted data?
Well, there is homomorphic encryption. Theoretically it lets you apply any ...
- 12.2k
5
votes
How to store product and pricing history in a product database?
I'd opt to leave the current table as it is, and add a table to hold the pricing histories, which are related via your product id and carrying a timestamp, and a daily job to snapshot today's pricing ...
- 1,333
5
votes
How to get real time notifications, when a database change (insert, update, delete) occurs?
For PostgreSQL I know a way to get notification from the database when a row changes.
use trigger when insert / update / delete occurs.
when event occurs send a notify to a client socket.
be sure you ...
- 51
5
votes
Race conditions in API calls within Golang microservices
If you are using a distributed system, you get the drawbacks of distributed system: ensuring consistency of your data model requires real care. TL;DR: avoid distributed systems if consistency is ...
- 135k
4
votes
Storing a re-orderable list in a database
Yes, the question is rather old and already has a couple of answers. Still, all of the solutions offered here are pretty complex. How about some simpler ones?
The original question is about Wish List ...
4
votes
Accepted
Compile analytics from millions of rows in PostgreSQL
I don't see a necessity to change the whole db technology or infrastructure just because you need a little bit of optimizing here. Start with something simple like writing a stored procedure (or maybe ...
- 212k
4
votes
Normalisation Vs Join , trade-off
Well, first of all, normalization in a relational database is not an absolute goal. There are certainly situations where maintaining a denormalized table or column does make sense from a performance ...
- 200k
4
votes
In the context of nested database relations (A->B->C), what are the considerations for having an additional "shortcut" foreign key (A->C)?
The pros of this approach are:
the ease of a direct access to the connected object if you don't need the objects in the middle;
potential performance improvement due to the reduction of joins (but ...
- 79.9k
4
votes
Architecture for RESTful API and a web admin
Q: For the functionalities that are similar for the mobile and web admin, how can avoid duplicating the code?
Essentially, encapsulating the domain and the business logic in their own components and ...
- 14.8k
4
votes
Should I de-normalize my DB schema for a multi tenant application
There is a linguistic trap in your question. Multi-tenant can mean different things in your example:
If tenant just happens to be an entity like any other and “multi-tenant”
is about indirect one-to-...
- 79.9k
4
votes
Accepted
Worried about too many joins when fetching multiple datatypes in a single postgres query
You will have to measure to see if performance is an actual problem. But your intuition about query performance is wrong:
I am just worried that this many cascading queries might heavily
affect ...
- 60k
4
votes
HOWTO: Full-text search over an encrypted database?
The nature of (good) encryption is that the cypher text (the encrypted data) bears no resemblance to the plain text (the unencrypted data). That makes it impossible to perform a full text search on ...
- 75.7k
4
votes
Best way to store quite large JSON objects?
You have an interesting scenario with lots of solution that might be appropriate.
You raise a good point that the amount of data might be an issue. As a worst case estimate, you might have 0.5MB per ...
- 135k
4
votes
Is it worth it to use a NoSQL database only for high availability and fast reading, while already using Postgres?
Choosing a technology to solve a problem before the problem is well understood is a good way for wasting time and energy.
First of all, the volumes that you indicate are not very high. I have seen a ...
- 79.9k
3
votes
Accepted
Is it possible to store and query combinations of boolean-like information in an integer?
You can query a bit array using bitwise operators, and you can store bit arrays in an integer type. Bitwise operators are not an ANSI SQL specified feature, but most RDBMS have implemented them. Some ...
- 9,207
3
votes
Scaling resolution of time series data
The query you gave us isn't very scalable because it will still have to scan the entire or a large range of rows. You've helped reduce the amount of data returned, but I believe it will still put more ...
- 9,207
Only top scored, non community-wiki answers of a minimum length are eligible