94
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 index ...
- 1,576
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. ...
- 111k
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.8k
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 ...
- 82.3k
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 ...
- 751
15
votes
Accepted
Why is there (practically) no 6-byte integer in common usage?
6 byte integers present a couple of issues related to alignment. A 6 byte integer needs to have an alignment of 2 or less. An alignment of 4 or 8 would result in needing 2 packing bytes in arrays, ...
- 857
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 ...
- 221k
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, ...
- 136k
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;
}
...
- 84.5k
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 ...
- 18.6k
7
votes
Constraint to enforce pairwise distinctness of values in two columns in table
Obviously it's a complicated constraint for which you can't expect there to be a direct, built-in form of expression.
The way I'm reading it, it's that the pairing of X/Y can recur multiple times in ...
- 12.7k
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 ...
- 618
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.4k
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 ...
- 136k
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
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 ...
- 82.3k
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 ...
- 15k
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-...
- 82.3k
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 ...
- 62.4k
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 ...
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 ...
- 136k
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 ...
- 82.3k
4
votes
Why is there (practically) no 6-byte integer in common usage?
There are two separate issues here:
Memory usage
Processor support for 6 byte integer math.
These issues are somewhat independent, in that a lot of the time numbers are just shuffled around between ...
- 4,647
4
votes
Accepted
How does a PostgreSQL cursor actually work? What are their tradeoffs?
I recently read about database cursors and how they are often presented as a magic solution for fetching large datasets. This seems like it could solve my exact problem.
This is a confused idea on a ...
- 31.1k
4
votes
How does a PostgreSQL cursor actually work? What are their tradeoffs?
Currently, my program fetches all the data from Database A in one shot, which sometimes causes OutOfMemoryErrors because the dataset can be enormous.
JimmyJames already explained that the cursor is ...
- 139k
3
votes
Generate UUID in Application or Database level?
You may want to go with "both".
Generating the UUIDs in the application usually makes it easier to run tests as mocking DB logic can be quite fragile.
On the other side of things, if you're ...
- 1,081
3
votes
DB Enumerations vs App Enumerations vs Lookup tables
There is a dichotomy between two opposing viewpoints
Do you want the ability to add new items to the set without recompiling the application - this favours a lookup table in the database.
Is there any ...
- 1,246
3
votes
DB Enumerations vs App Enumerations vs Lookup tables
I would suggest database tables, one per list.
The enumerations exist to list the permissible values for one or more attributes. Likely you'll want to enforce these restrictions within the database, ...
- 923
Only top scored, non community-wiki answers of a minimum length are eligible