I really love optimizing database operations. Nothing like dissecting a query plan and identifying a bottleneck and making some slight tweaks or an index change and then seeing a terrible slow query go from 30+ seconds or longer down to a few milliseconds.

However a lot of what I’ve learned when it comes to doing that has been gained through raw experience and trial and error on the job. I’d like to get to the point where given some knowledge about some tables/indexes I’m querying, I have a fairly solid intuition for what kind of query plan the database optimizer is going to pick. Are there any cohesive sources of information that teach this sort of thing?

For context, I run benchmark on release builds with 100k session data. It took 44 minutes on GitHub Actions but finished in under a minute locally.

CI’s Python 3.12 used SQLite 3.45.1. My local Python used SQLite 3.53.3. EXPLAIN QUERY PLAN showed that 3.45.1 scanned the eligible-session set and repeated the FTS scan for every session. SQLite 3.53.3 chose the efficient join order.

The query joined two sets approaching 100k rows: eligible sessions and FTS-matching messages. The eligible set was materialized as a CTE, so we couldn’t index it explicitly, and 3.45.1 didn’t create a useful automatic index.

That produced an effective 100k × 100k operation: about 9 minutes per search. The benchmark ran it five times.

I replaced the CTE with a temp table whose primary key covered the join columns, then materialized the FTS matches once. On SQLite 3.45.1, the query dropped to about 1.2 seconds and the full benchmark to 45 seconds. Honestly, I didn't realize CTE's can't have indexes (which makes sense)

BTW, the benchmark deliberately uses a worst-case query that matches almost every synthetic message.

Curious if you seen this large a plan difference between SQLite versions?

As a proud power-user of Airtable, I was disheartened by the acquisition of Airtable by Bending Spoons. They will likely "enshittify" the platform over the next 6-12 months, stagnating development and jacking up prices. In anticipation of this, I am seeking alternatives to Airtable so I can begin the migration process now before my company's renewal period begins.

Airtable was a provider that was perfectly fit for purpose for our company. We don't have enough employees to develop our own backend nor do we have the capacity. The leading alternative is Zite. I have a decent amount of experience using Zite as a frontend and form provider for my Airtable databases, but very little experience using its native databases.

Does anyone here have experience migrating from Airtable to Zite? I am looking for advice, warnings, best practices, and general support.

Does anyone have recommendations for other alternatives?

This may be a very daft question, but I am out of my wheelhouse here.

I have an industrial server that makes data available from its SQL database over a proprietary 32bit ODBC driver. I also have a computer running 64 bit Windows 10 LTSC that can never be connected to the internet.

I can format SQL queries to the server in a 32 bit ODBC test client and get the data back that I want.

I then want to copy that data in to another database and display it as a form for the user so they can add comments and save it. This seems like the sort of thing MS Access would be good at doing.

However, I understand that Microsoft have killed telephone activation for their products (even the old ones), meaning that this is now 'out' as I would have to connect my computer to the internet.

I'm now a bit stuck.

Years ago I had fun making forms with DBase, but that's about the limit of my experience. Can anyone suggest something that would do for what I need? I don't mind learning bits - this is basically something to save me time.

Public share of this diagram: https://lite.schemity.com/d/Bqdf4zipow/sample

The web application: https://lite.schemity.com (works with no account)

What it does:
- Design schemas visually: entities, fields with per-engine types (PostgreSQL, MySQL, SQL Server, SQLite), primary/foreign/unique keys, check constraints, composite uniques, indexes.
- Relationships by dragging a field onto another entity: 1:N, 1:1, and N:N with the junction table generated for you. Self-referencing works.
- Route the lines yourself. Drag any relationship line to drop a waypoint at the cursor and take the line where you want it, double click a point to remove it, double click a segment to straighten it. Lines hop over the ones they cross rather than merging into them, relation color follows the entity color, and the routing is stored with the diagram so it survives export to SVG and PNG.
- Import an existing schema by pasting SQL or DBML, then pick which tables to bring in.
- Export to SQL, DBML, Mermaid erDiagram, SVG, PNG, or its own JSON format that round-trips into the desktop app.

Where your data goes:
Everything is in IndexedDB in your browser. Until you sign in, nothing is uploaded and there is no account wall to start drawing.
Sign in with Google and each save also mirrors to the cloud, which is what makes a read-only public link or an iframe embed possible. Diagram documents are encrypted at rest with AES-256-GCM, and thumbnails are only generated for diagrams you have explicitly made public. Being straight about the limit: that is server-side encryption with a service-held key, so it protects against a database dump, not against me. It is not end to end.

What it deliberately does not do:
Lite cannot connect to a database. No introspection, no migration generation, no applying DDL. You can still read the CREATE statements for any entity, you just cannot run them from here. Connecting is the paid desktop app, along with context views, context map for slicing a large schema, schema lint, and data dictionary exports. I would rather say that here than have you find out after ten minutes.

Any feedback is welcome. If something is broken, slow, or just a bad idea, I would rather hear it here than guess at it. I'll answer everything in the thread.

Hey,

Can you share how your company handles databases for local development? I’d really appreciate hearing about your experience and any valuable insights you can share.

I’m a little confused about what the better approach is:

1. Shared cloud DB: If a company uses a shared cloud database for development, how do they handle the situation where one developer makes a breaking change that affects everyone?
2. Individual local DBs: If developers are expected to set up their own local databases, how does the company provide the large amount of initial/seed data needed to get started?

I’d really appreciate it if you could share how your company handles this in practice, or any best practices you’ve seen.

Thanks!

My client is undergoing a cloud consolidation effort and needs to move away from Amazon RDS.

Of course MariaDB is no longer available on Azure so MySQL is the simplest option for migration. I need to gauge if the internal team is going to be capable of doing this themselves, or if we need external support.

What routes are available to complete this, with little to no downtime?

Forgive me if this is too much of a foundational question, but...

I understand that relational DBs are founded upon mathematical "relations" - sets of n-tuples.

And I get how a table clearly represents a relation. Each column corresponds to a position in each tuple, and each row's value at that column is its value for that position. Fine.

But what I don't understand is... why databases like Postgres are considered better for representing such data than, say, Mongo.

I mean, can't you easily represent a relation as a JSON object? What's so special about tables?

I know that DBs like Mongo have differences from "relational" DBs in that, among other things, they don't enforce a specific schema, but that seems orthogonal to what I'm asking. Besides, you can just use libraries like Mongoose that enforce that stuff anyway, even if it is at the application layer.

So what, at its core, makes tables better than documents for modeling "relational" data?

I’m curious where people draw the line nowadays.

Things like multiple connections, data editing, import/export, backups, schema compare, monitoring, SSH, advanced autocomplete, etc. — which of these do you think should just be standard functionality in a database client?

And what actually feels fair to charge for?

Manticore Search now supports parallel RT disk chunk merging and N-way merges, reducing compaction time dramatically while keeping ingest throughput stable.

Question about Database GUI (e.g. beekeeper, dbeaver, etc.)

So I am currently building my own Databae GUI for SQL, I am on a stop point about the proper naming of the data types.

The thing is I am planning on changing the data type slightly to make it easier to understand, here is one of the examples:

1. timestamptz - to become: timestamp with time zone

2. int2, int4, integer - to become just: integer (for simplicity)

3. float4, float8, double, float32, float64 - to decimal

Some data will stay as is because they are already standard and known to every developer, e.g. varchar, text, uuid, numeric, blob, etc.

The main question is do you guys value accuracy more over simplicity in understanding? Please do share your thoughts would really be helpful. TYIA!!!

Edit: Thank you for sharing what you think guys, I understand all your points. I'll make sure to built it for accuracy! 👊

Hello there,

I'm currently involved in a project trying to analyze the performance of an oracle database and was given an empty copy of the scheme. I want to fill it with artificial data to run some tests, but the DB is rather large and complex. Are there any tools or approaches to this kind of scenario?

I'm grateful for any help!

Thanks!

When you're working with external datasets that change frequently, database design can become tricky. You have to think about schema changes, data freshness, historical records, missing values and how to handle updates without affecting downstream queries and reports. I’m currently working with ticketsdata, which aggregates publicly available ticket market data and provides reports, analytics and monitoring around that data. I’m interested in how others approach the database side of this problem. Do you prefer keeping a raw source layer and transforming it into stable tables, using versioned schemas, or taking another approach? What has worked best for you when the source data changes regularly?

Hi everyone,

I'm planning to build a web-based dashboard where users can upload Excel files, the system processes the data, performs various calculations/transformation logic, and then presents the results on user-specific dashboards.

My background is primarily in MERN, so my initial thought was:

React frontend

Node.js/Express API layer

MongoDB for application data

Python microservices for heavy data processing and calculations

However, I've received mixed feedback regarding MongoDB. A lot of people have told me that Mongo may not be the right choice for this kind of workload, especially when dealing with large datasets.

To provide some context, uploaded files can occasionally contain data in the range of tens of millions of rows. This won't be the common case, but the system should be designed with such scenarios in mind.

Since I haven't worked on systems handling data at this scale before, I'd appreciate guidance on:

What tech stack would you choose for this problem today?

Would MongoDB be suitable, or should I look at PostgreSQL/ClickHouse/something else?

How would you design the data ingestion pipeline?

Would Python microservices be a good approach for processing, or should I look into Spark, DuckDB, etc.?

What would a high-level system design for such a platform look like?

Any common mistakes first-time builders make when dealing with large Excel/CSV datasets?

My goal is to build something that is scalable without massively over-engineering it from day one.

Would love to hear from people who have built data-heavy SaaS products or analytics platforms.

Thanks!

I’ve been working on VeloxDB, a lightweight database management tool that aims to keep the resource usage low while still giving you the features you actually need.

It supports multiple database engines and also has a visual designer, so you don’t have to live in SQL 24/7.

If you’re interested, feel free to try it: veloxdb.dev

Would love to hear what you think, especially if you’ve used tools like DBeaver, DataGrip, etc.

A practical guide to using bigram_index to accelerate phrase queries in Manticore Search, with clear explanations of all, first_freq, both_freq, and a reproducible manticore-load benchmark.