Skip to main content >
LightDB distributed database
Architecture
Concepts
Nodes
Coordinator and Workers
Distributed Data
Table Types
Type 1: Distributed Tables
Type 2: Reference Tables
Type 3: Local Tables
Shards
Shard Placements
Co-Location
Parallelism
Query Execution
Develop
Determining Application Type
At a Glance
Examples and Characteristics
Choosing Distribution Column
Multi-Tenant Apps
Best Practices
Real-Time Apps
Best Practices
Timeseries Data
Best Practices
Table Co-Location
Data co-location in LightDB distributed database for hash-distributed tables
A practical example of co-location
Using Regular LightDB Tables
Distributing tables by ID
Distributing tables by tenant
Co-location means better feature support
Query Performance
Migrating an Existing App
Identify Distribution Strategy
Pick distribution key
Identify types of tables
Prepare Source Tables for Migration
Add distribution keys
Backfill newly created columns
Prepare Application for LightDB distributed database
Set up Development LightDB distributed database Cluster
Include distribution column in keys
Add distribution key to queries
Other (SQL Principles)
Enable Secure Connections
Check for cross-node traffic
Migrate Production Data
Small Database Migration
SQL Reference
Creating and Modifying Distributed Objects (DDL)
Creating And Distributing Tables
Reference Tables
Distributing Coordinator Data
Co-Locating Tables
Dropping Tables
Modifying Tables
Adding/Modifying Columns
Adding/Removing Constraints
Using NOT VALID Constraints
Adding/Removing Indices
Types and Functions
Manual Modification
Ingesting, Modifying Data (DML)
Inserting Data
“From Select” Clause (Distributed Rollups)
COPY Command (Bulk load)
Caching Aggregations with Rollups
Updates and Deletion
Maximizing Write Performance
Querying Distributed Tables (SQL)
Aggregate Functions
Count (Distinct) Aggregates
Estimating Top N Items
Limit Pushdown
Views on Distributed Tables
Joins
Co-located joins
Reference table joins
Repartition joins
Query Processing
Distributed Query Planner
Distributed Query Executor
Subquery/CTE Push-Pull Execution
LightDB planner and executor
Manual Query Propagation
Running on all Workers
Running on all Shards
Limitations
SQL Support and Workarounds
Workarounds
Work around limitations using CTEs
Temp Tables: the Workaround of Last Resort
LightDB distributed database API
LightDB distributed database Utility Functions
Table and Shard DDL
truncate_local_data_after_distributing_table
undistribute_table
alter_distributed_table
remove_local_tables_from_metadata
create_reference_table
canopy_add_local_table_to_metadata
mark_tables_colocated
update_distributed_table_colocation
create_distributed_function
create_time_partitions
drop_old_time_partitions
Metadata / Configuration Information
canopy_add_node
canopy_update_node
canopy_set_node_property
canopy_add_inactive_node
canopy_activate_node
canopy_disable_node
canopy_add_secondary_node
canopy_remove_node
canopy_get_active_worker_nodes
canopy_backend_gpid
canopy_check_cluster_node_health
canopy_set_coordinator_host
get_shard_id_for_distribution_column
column_to_column_name
canopy_relation_size
canopy_table_size
canopy_total_relation_size
canopy_stat_statements_reset
Cluster Management And Repair Functions
canopy_move_shard_placement
rebalance_table_shards
get_rebalance_table_shards_plan
get_rebalance_progress
canopy_add_rebalance_strategy
canopy_set_default_rebalance_strategy
canopy_remote_connection_stats
canopy_drain_node
canopy_create_restore_point
LightDB distributed database Tables and Views
Coordinator Metadata
Partition table
Shard table
Shard information view
Shard placement table
Worker node table
Distributed object table
LightDB distributed database tables view
Time partitions view
Co-location group table
Rebalancer strategy table
Query statistics table
Distributed Query Activity
Tables on all Nodes
Connection Credentials Table
Connection Pooling Credentials
Configuration Reference
General configuration
canopy.max_worker_nodes_tracked (integer)
canopy.use_secondary_nodes (enum)
canopy.cluster_name (text)
canopy.enable_version_checks (boolean)
canopy.log_distributed_deadlock_detection (boolean)
canopy.distributed_deadlock_detection_factor (floating point)
canopy.node_connection_timeout (integer)
canopy.node_conninfo (text)
canopy.local_hostname (text)
canopy.show_shards_for_app_name_prefixes (text)
Query Statistics
canopy.stat_statements_purge_interval (integer)
canopy.stat_statements_max (integer)
canopy.stat_statements_track (enum)
Data Loading
canopy.multi_shard_commit_protocol (enum)
canopy.shard_count (integer)
canopy.shard_max_size (integer)
canopy.replicate_reference_tables_on_activate (boolean)
Planner Configuration
canopy.local_table_join_policy (enum)
canopy.limit_clause_row_fetch_count (integer)
canopy.task_assignment_policy (enum)
Intermediate Data Transfer
canopy.binary_worker_copy_format (boolean)
canopy.max_intermediate_result_size (integer)
DDL
canopy.enable_ddl_propagation (boolean)
canopy.enable_local_reference_table_foreign_keys (boolean)
Executor Configuration
General
Explain output
Administer
Cluster Management
Choosing Cluster Size
Shard Count
Multi-Tenant SaaS Use-Case
Real-Time Analytics Use-Case
Initial Hardware Size
Multi-Tenant SaaS Use-Case
Real-Time Analytics Use-Case
Scaling the cluster
Add a worker
Rebalance Shards without Downtime
How it Works
Adding a coordinator
Dealing With Node Failures
Worker Node Failures
Coordinator Node Failures
Tenant Isolation
Viewing Query Statistics
Statistics Expiration
Resource Conservation
Limiting Long-Running Queries
Security
Connection Management
Setup Certificate Authority signed certificates
Increasing Worker Security
Row-Level Security
LightDB extensions
Creating a New Database
Table Management
Determining Table and Relation Size
Vacuuming Distributed Tables
Analyzing Distributed Tables
Troubleshoot
Query Performance Tuning
Table Distribution and Shards
LightDB tuning
Scaling Out Performance
Distributed Query Performance Tuning
General
Subquery/CTE Network Overhead
Advanced
Connection Management
Task Assignment Policy
Intermediate Data Transfer Format
Binary protocol
Scaling Out Data Ingestion
Real-time Insert and Updates
Insert Throughput
Update Throughput
Insert and Update: Throughput Checklist
Insert and Update: Latency
Staging Data Temporarily
Bulk Copy (250K - 2M/s)
Useful Diagnostic Queries
Finding which shard contains data for a specific tenant
Finding the distribution column for a table
Detecting locks
Querying the size of your shards
Querying the size of all distributed tables
Identifying unused indices
Monitoring client connection count
Viewing system queries
Active queries
Why are queries waiting
Index hit rate
Cache hit rate
Common Error Messages
Could not receive query results
Resolution
Canceling the transaction since it was involved in a distributed deadlock
Resolution
Could not connect to server: Cannot assign requested address
Resolution
SSL error: certificate verify failed
Resolution
Could not connect to any active placements
Resolution
Remaining connection slots are reserved for non-replication superuser connections
Resolution
PgBouncer cannot connect to server
Resolution
Relation
foo
is not distributed
Resolution
Unsupported clause type
Resolution
Cannot open new connections after the first modification command within a transaction
Resolution
Cannot create uniqueness constraint
Resolution
Resolution
STABLE functions used in UPDATE queries cannot be called with column references
Resolution
FAQ
Frequently Asked Questions
Can I create primary keys on distributed tables?
How do I add nodes to an existing LightDB distributed database cluster?
How does LightDB distributed database handle failure of a worker node?
How does LightDB distributed database handle failover of the coordinator node?
Are there any LightDB features not supported by LightDB distributed database?
How do I choose the shard count when I hash-partition my data?
How do I change the shard count for a hash partitioned table?
How does LightDB distributed database support count(distinct) queries?
In which situations are uniqueness constraints supported on distributed tables?
How do I create database roles, functions, extensions etc in a LightDB distributed database cluster?
What if a worker node’s address changes?
Which shard contains data for a particular tenant?
I forgot the distribution column of a table, how do I find it?
Can I distribute a table by multiple keys?
Why does pg_relation_size report zero bytes for a distributed table?
Why am I seeing an error about max_intermediate_result_size?
Can I shard by schema on LightDB distributed database for multi-tenant applications?
Articles
Related Articles
LightDB Parallel Indexing in LightDB distributed database
Real-time Event Aggregation at Scale Using LightDB with LightDB distributed database
How Distributed Outer Joins on LightDB with LightDB distributed database Work
Distributed Outer Joins with LightDB distributed database
Designing your SaaS Database for Scale with LightDB
Sharding a Multi-Tenant App with LightDB
Tenancy
Multi-tenancy and co-location, a perfect pair
In conclusion
Sharding LightDB with Semi-Structured Data and Its Performance Implications
One large table, without joins
Enter LightDB distributed database
The query workload
Every distribution has its thorns
Scalable Real-time Product Search using LightDB with LightDB distributed database
LightDB distributed database
»
LightDB distributed database API
LightDB distributed database API
LightDB distributed database Utility Functions
Table and Shard DDL
Metadata / Configuration Information
Cluster Management And Repair Functions
LightDB distributed database Tables and Views
Coordinator Metadata
Tables on all Nodes
Configuration Reference
General configuration
Query Statistics
Data Loading
Planner Configuration
Intermediate Data Transfer
DDL
Executor Configuration